Re: creating users per database

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> 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.

Not if you restrict who's allowed to execute the function. Revoke
the default public EXECUTE right on it, and grant to just who you
want. A good way to manage this is to grant the EXECUTE right to
a group (say "wheel") and then be careful who you grant membership
in "wheel" to.

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

It'll go away as soon as we think of a better way ;-). I wouldn't
expect to lose functionality, but the syntax will likely change.

> It isn't a given that all database users will have system
> level equivalents hence samegroup won't work in all cases.

You do not understand: samegroup means PG users who are members
of the PG group named the same as the database can connect to the
database. This has *nothing* to do with any OS-level notions.

regards, tom lane