Using role priviledges for pg_dump

Bill Moran <wmoran@collaborativefusion.com> writes:

Given:

CREATE ROLE joe WITH LOGIN;
CREATE ROLE dumpable;
ALTER GROUP dumpable ADD USER joe;

If I have a database called db1 to which the role dumpable has enough
permissions to do a full pg_dump, but he user joe does not, how can
joe do a pg_dump? Is it possible?

Uh, given the setup you show it seems like joe should have enough
privileges. Can you give a concrete example of it going wrong?

regards, tom lane

In response to Bill Moran <wmoran@collaborativefusion.com>:

Given:

CREATE ROLE joe WITH LOGIN;
CREATE ROLE dumpable;
ALTER GROUP dumpable ADD USER joe;

If I have a database called db1 to which the role dumpable has enough
permissions to do a full pg_dump, but he user joe does not, how can
joe do a pg_dump? Is it possible?

Apologies, I left out a key piece of information:
The role dumpable has superuser privileges, and this is the reason that
dumpable is able to dump the database. As noted in the docs, superuser,
createdb, and createrole privs don't seem to inherit. If I remove
superuser from role dumpable, that role can't do pg_dump either.

As a point of explanation, this is part of a migration to get individual
users away from requiring superuser privs. Eventually, I plan to have
ACLs on all database objects such that I can remove superuser from the
dumpable role, but if I could get other roles to inherit the superuser
priv, it would make migration a lot easier.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Bill,

* Bill Moran (wmoran@collaborativefusion.com) wrote:

In response to Bill Moran <wmoran@collaborativefusion.com>:

If I have a database called db1 to which the role dumpable has enough
permissions to do a full pg_dump, but he user joe does not, how can
joe do a pg_dump? Is it possible?

Apologies, I left out a key piece of information:
The role dumpable has superuser privileges, and this is the reason that
dumpable is able to dump the database. As noted in the docs, superuser,
createdb, and createrole privs don't seem to inherit. If I remove
superuser from role dumpable, that role can't do pg_dump either.

I've got the exact same situation and I don't believe there's currently
a way to fix it in PostgreSQL. I've been asking for this ability on
-hackers and will probably come up with a patch to implement it soon.
In my ideal world it'd get into 8.4 and maybe back-patched to older
releases if it's not too invasive.

Glad to hear I'm not alone in wanting to have this ability though. :)

Thanks!

Stephen