Обсуждение: 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