Re: creating users per database

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: creating users per database
Date: 2006-01-29 08:00:12
Message-ID: 20060129080010.GA5310@merkur.hilbert.loc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2006-01-29 08:39:21 Re: help
Previous Message Tony Caduto 2006-01-29 07:49:52 Re: Seeking a better PL/pgSQL editor-debugger