Thread: creating users per database
Hi all, 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@thisdb ...; or similar. Was this ever possible in PostgreSQL or is my memory playing tricks on me ? I went back through the manuals all the way to 6.5 and searched archives.postgresql.com/Google, alas, to no avail. I am aware that I can (and should) restrict users to databases by way of pg_hba and friends. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Hello,
I don't think you can create users *inside* a database, but for each
database,
you can create global users and make them members of a group having the same
name than your database.
Then, in pg_hba.conf, the folllowing line will give to your users the
permission to
connect to the database they belong :
host samegroup all 0.0.0.0 0.0.0.0 md5
Bye,
Philippe Ferreira.
>Hi all,
>
>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@thisdb ...;
>
>or similar.
>
>Was this ever possible in PostgreSQL or is my memory playing
>tricks on me ? I went back through the manuals all the way
>to 6.5 and searched archives.postgresql.com/Google, alas, to
>no avail.
>
>I am aware that I can (and should) restrict users to
>databases by way of pg_hba and friends.
>
>Karsten
>
>
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> 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@thisdb ...;
There is the db_user_namespace kluge^H^H^H^H^Hparameter, but it's
pretty ugly. Do you really need DB-specific user names, or just
a more convenient way to limit which DBs a user can connect to? If
the latter, the "samegroup" technique Philippe mentions seems like
a good bet.
regards, tom lane
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@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
Karsten Hilbert <Karsten.Hilbert@gmx.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
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
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> 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 ..." ?
The 8.1 documentation uses the term "role", which seems unlikely to be
confused with Unix groups:
: database
:
: Specifies which database names this record matches. The value all
: specifies that it matches all databases. The value sameuser
: specifies that the record matches if the requested database has the
: same name as the requested user. The value samerole specifies that
: the requested user must be a member of the role with the same name
: as the requested database. (samegroup is an obsolete but still
: accepted spelling of samerole.) Otherwise, this is the name of a
: specific PostgreSQL database. Multiple database names can be
: supplied by separating them with commas. A separate file containing
: database names can be specified by preceding the file name with @.
Good enough?
regards, tom lane
On Sun, Jan 29, 2006 at 02:01:51PM -0500, Tom Lane wrote: > The 8.1 documentation uses the term "role", which seems unlikely to be > confused with Unix groups: ... > Good enough? Yes :-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346