Re: creating users per database

On Sat, Jan 28, 2006 at 06:17:16PM -0500, Tom Lane wrote:

> > 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.
Duh, I forgot about this priviledge. Yeah, that serves the
purpose pretty well.

> 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.
We have a dedicated DB account "gm-dbo" (Gnumed DataBase
Operator) who owns all the database objects but which I did
not want to give superuser rights to if avoidable. I'll
grant execute to that user only. Then I'll use the standard
procedure of requesting the gm-dbo password from the user
inside the application for establishing a gm-dbo db
connection when gm_create_user() is to be called. Much like
"su - root" or switching to "System mode" in the KDE control
center.

> 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.
Well, that's fine :-)

> 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.
Aha ! Indeed, the 7.4 documentation wasn't clear enough on
that (for me):

"... The value samegroup specifies that the requested user
must a member of the group with the same name as the
requested database. ..."

Might this be amended to say "... a member of the *database*
group with ..." ?

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