ALTER DEFAULT PRIVILEGES target_role doesn't work with group roles

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