Обсуждение: Using role priviledges for pg_dump
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? -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
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