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.
