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.html) 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. -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. 443.325.1357 x251 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
