Thread: Delegating User creation
Hello all, I've looked at the mailing list archives but didn't see anything related to my question. Last night at our LUG meeting I gave a brief talk on how we use PostgreSQL for our clients. Afterward I was asked a question that I couldn't answer. So I was hoping someone here could answer or help out. Some staff members at local university are running a PostgreSQL installation with a number of databases. The (part-time) admin would like to delegate user creation to selected people in the various departments. However it looks like to give those folks user creation rights, they would become super users and be able to access other departments' databases. Is there a way for a user to get user creation rights only for their database and not get superuser rights across all databases? (they're considering switching to MySQL which they believe they can do this; I don't/haven't uses MySQL so cannot confirm that is so.) Thanks. -- Tom Jenkins devIS - Development Infostructure http://www.devis.com
Tom Jenkins <tjenkins@devis.com> writes:
> However it looks like to give those folks user creation rights, they
> would become super users and be able to access other departments'
> databases. Is there a way for a user to get user creation rights only
> for their database and not get superuser rights across all databases?
Not at the moment --- there isn't a "create user" right that is separate
from superuserness. Since users are global to the whole database
installation, it's not as simple as allowing owners of individual
databases to create users for their databases; they'd be implicitly
creating users who might be able to access other databases as well.
My suggestion if you want compartmentalized user creation is to set up a
separate postmaster (not only a separate database) for each department.
Then you can give people superuserness that extends no further than
their own database. BTW, you might want to turn off pg_shadow.usecatupd
for these people, to limit their ability to shoot themselves in the
foot.
regards, tom lane
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: Tom> Tom Jenkins <tjenkins@devis.com> writes: >> However it looks like to give those folks user creation rights, they >> would become super users and be able to access other departments' >> databases. Is there a way for a user to get user creation rights only >> for their database and not get superuser rights across all databases? Tom> Not at the moment --- there isn't a "create user" right that is separate Tom> from superuserness. Since users are global to the whole database Tom> installation, it's not as simple as allowing owners of individual Tom> databases to create users for their databases; they'd be implicitly Tom> creating users who might be able to access other databases as well. Tom> My suggestion if you want compartmentalized user creation is to set up a Tom> separate postmaster (not only a separate database) for each department. Tom> Then you can give people superuserness that extends no further than Tom> their own database. BTW, you might want to turn off pg_shadow.usecatupd Tom> for these people, to limit their ability to shoot themselves in the Tom> foot. Couldn't you create an INSERT rule on pg_password for the junior-superuser that narrowed the created users to only sensible permissions? Of course, you'd have to use SQL on the table rather than nice "CREATE USER" commands instead, but that seems like it would work. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
merlyn@stonehenge.com (Randal L. Schwartz) writes:
> Couldn't you create an INSERT rule on pg_password for the
> junior-superuser that narrowed the created users to only sensible
> permissions?
Obviously, if we invented a "create users" permission, it would have to
extend only to creating non-superuser users; you'd only want superusers
to be able to make more superusers.
But that's not really the point IMHO. As I understood the question,
it was about being able to delegate the right to create users *for
particular databases*. That can't be delegated because it doesn't
exist --- we have no concept of users restricted to only some databases
within an installation. (You can sort of fake it by restricting their
ability to connect in pg_hba.conf, but that's a pretty ugly approach,
and certainly not one that's available to anyone but the dbadmin.)
This should be improved, and probably will be in future. In the
meantime, though, I don't think a "create users" right would by itself
solve Tom's problem.
regards, tom lane
From: "Tom Lane" <tgl@sss.pgh.pa.us> > merlyn@stonehenge.com (Randal L. Schwartz) writes: > > Couldn't you create an INSERT rule on pg_password for the > > junior-superuser that narrowed the created users to only sensible > > permissions? > > Obviously, if we invented a "create users" permission, it would have to > extend only to creating non-superuser users; you'd only want superusers > to be able to make more superusers. > > But that's not really the point IMHO. As I understood the question, > it was about being able to delegate the right to create users *for > particular databases*. That can't be delegated because it doesn't > exist --- we have no concept of users restricted to only some databases > within an installation. (You can sort of fake it by restricting their > ability to connect in pg_hba.conf, but that's a pretty ugly approach, > and certainly not one that's available to anyone but the dbadmin.) Could you not do it with groups? All objects in database "foo" are only accessible to users in "foo_group". Operations on the user tables are only permitted against users who are in "foo_group" (via Randal's use of rules/triggers). Any user could connect to any database, but wouldn't have access to the tables. - Richard Huxton