Keith Fiske wrote: > Situation: > I have two roles, alice & bob. Both are members of the dev_user group role. > I have a schema called 'reports' that both of these users would like > to be able to manage. > > I thought I could use the ALTER DEFAULT PRIVILEGES option > (http://www.postgresql.org/docs/9.1/static/sql-alterdefaultprivileges.ht ml) > to set it up so that if anyone in the dev_user group role created a > table in the reports schema, then Postgres would automatically grant > all privileges to the group role. Then both Alice and Bob could access > each other's objects in a schema other than their own. This would also > make it so that any future roles added to the dev_user schema would > have this happen automatically. > > ALTER DEFAULT PRIVILEGES FOR ROLE dev_user IN SCHEMA reports GRANT ALL > ON TABLES TO dev_user; > > It turns out the "target_role" does not work for group roles. When > either Alice or Bob creates a table in the reports schema, the > dev_user grants are not automatically added. I had to explicitly set > the default privileges for each role: > > ALTER DEFAULT PRIVILEGES FOR ROLE alice IN SCHEMA reports GRANT ALL ON > TABLES TO dev_user; > ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA reports GRANT ALL ON > TABLES TO dev_user; > > This isn't ideal for long term management. I wasn't really sure if > this was a bug or a lack of clarity in the docs, so thought I'd throw > it out to General for comments first. And to make sure I'm explaining > this clearly enough for others to reproduce it and see if I'm not > asking for something unreasonable. The documentation could be more explicit about that. http://www.postgresql.org/docs/9.1/static/sql-alterdefaultprivileges.htm l#SQL-ALTERDEFAULTPRIVILEGES-DESCRIPTION says: "You can change default privileges only for objects that will be created by yourself or by roles that you are a member of." So, in your case, the original ALTER DEFAULT PRIVILEGES statement you used would work for tables that dev_user himself creates, not for tables that a member of the role creates. Alice cannot change the default privileges for Bob. Unfortunately I can't see a way to achieve what you want without granting default privileges to everybody involved. I think that there is room for improvement there. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
