Thread: Hosting options on Postgres - what's best?

Hi,

I'm looking into creating a hosted application with Postgres as the
SQL server. I would like to get some ideas and oppinions about the
different ways to separate the different clients, using postgres.

The options I had in mind:

1) Create a different database per client. How much overhead will this add?

2) Use schemas and authentication. So each client is a different
schema on the database.

3) Use application level security (per object security maintained by the app).

4) ????

Any ideas? Opinnions?

js.

It depends.

We do all of the above depending on the situation.  Some programs we
want to have cross client reporting and querying, so we use just
application level security with a client_id field.  Some programs we
just want to be able to dump restore the whole DB in one go so we just
different schemas, and in some casses where an app wasn't really
designed for ASP model, we have different databases, or where each
version of the App has custom extensions, or the database with all
clients would just be too unwieldy.

That probably doesn't help much.  Assess what your needs are, and then
pick the appropriate solution :)

Alex Turner
netEconomist

On 5/10/05, Just Someone <just.some@gmail.com> wrote:
> Hi,
>
> I'm looking into creating a hosted application with Postgres as the
> SQL server. I would like to get some ideas and oppinions about the
> different ways to separate the different clients, using postgres.
>
> The options I had in mind:
>
> 1) Create a different database per client. How much overhead will this add?
>
> 2) Use schemas and authentication. So each client is a different
> schema on the database.
>
> 3) Use application level security (per object security maintained by the app).
>
> 4) ????
>
> Any ideas? Opinnions?
>
> js.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Just Someone wrote:

Hi,

I'm looking into creating a hosted application with Postgres as the
SQL server. I would like to get some ideas and oppinions about the
different ways to separate the different clients, using postgres.

The options I had in mind:

1) Create a different database per client. How much overhead will this add?

2) Use schemas and authentication. So each client is a different
schema on the database.

3) Use application level security (per object security maintained by the app).

4) ????

Any ideas? Opinnions?

js.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
 

We use a single schema or multiple clients in a web environment using whitebeam. We ridgedly enforce secure access through a client_id model. In a web environment with lots of clients this has the benefit over other appoaches that you have far fewer database connections that otherwise, and that the connections can safely be persistent.

If you can't safely control the access of course (because you're not writing the application maybe) then other solutions are more secure.

Pete
--
http://www.whitebeam.org