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