Re: creating users per database

On Sat, Jan 28, 2006 at 11:04:09AM -0500, Tom Lane wrote:

> > I have a faint memory of it being possible to create users
> > "inside" *one* given database by way of a particular "create
> > user" syntax along the lines of:
> > create user newuser(at)thisdb ...;
> There is the db_user_namespace kluge^H^H^H^H^Hparameter, but it's
> pretty ugly.
Ah, I see. So my memory didn't fail that much.

> Do you really need DB-specific user names, or just
> a more convenient way to limit which DBs a user can connect to?
Well, yes and no. The situation is rather complex. Basically
I am using a create_user() SP created by "postgres" with
"security definer" (gasp). This works just fine, however, it
transfers createuser rights to *anyone* allowed to connect
to the database the function is in. I wanted to limit any
potential damage that could be done by users created that
way by restricting where they can possibly be used. I
thought that if a user only exists in the context of my
database it cannot be used to attack any other database no
matter which way pg_hba.conf and friends might be
misconfigured. Compartmentalization.

But perhaps I shouldn't count on the db_user_namespace
feature to exist in the future ?

> If the latter, the "samegroup" technique Philippe mentions seems like
> a good bet.
It isn't a given that all database users will have system
level equivalents hence samegroup won't work in all cases.
Also, will samegroup work predictably across remote
connections ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346