Re: Best practice running a shared DB hosting server

On Mon, 2008-08-18 at 11:48 -0400, Robert Treat wrote:

> You'll likely want to make the database owned by that role.

My intention is to have some sort of "main"-user who'll be
able to do whatever they want (because they create
all the objects) and a number of additional users
that only have the rights the "main"-user grants them.

So I'd be using the db-named role only a way to
avoid specifying users and databases in pg_hba.conf.

>  And if you really
> want, you should create a new tablespace and assign each new database to that
> tablespace. This would allow you to enforce some space limits at the OS level
> for each db.

I was thinking about that, but how could you enforce space limits
through that? At least on Linux, the quota systems works using
uids/gids, and the table space files will still be owned by the same
uid/gid as the postmaster processes, right?

Also I'm worried about what exactly will happen if you run out
of space?

> Have you tested this to make sure it works?

I did, the authentication code seems to be checking
role membership only, not whether or not this is
the active role. Hopefully this is working that way
by design.

> You would then need to disable accounts by
> revoking the login bit, or by setting up password expiry.

Well I'm creating the db-named role with option NOLOGIN
so that should have the same effect.

> I dont think this is neccessary... in your environment it's mostly helpful in
> cases where someone logs into a database they shouldn't log into.  I suppose
> the safe thing to do is to try it and see if users complain about usage
> issues... they might not.

I've changed my approach by revoking rights to schema public
from PUBLIC in the template1, and then granting all rights
to again just to the "main" user, and usage rights to
the additional users.

So people aren't forced to use schemas.

> Yes, I think the whole "security through obscurity" argument is a cop out to
> get around postgresql's design choices (in this perticular instance anyway,
> in many cases its valid).

Well, improvements that make PostgreSQL better suited to separating
databases for different users would surely help drive up
the number of users. There are lots people who can't
or don't want to afford their own database server while
still needing to have access to an SQL database.

> BTW, I'd encourage you to start a page on the wiki site to track the specific
> issues you run into, and specific fixes your using. Would certainly be
> helpful to others and probably yourself as a reference on the topic.

Hmm, yes, why not, I'll see what I can do.