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