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
