> ... in fact, there is no combination of actions which will make "FOR
> ROLE" work. Any invokation of "FOR ROLE" inevitably results in a
> "permission denied" message:
>
> analytics2=> \c - webui
> You are now connected to database "analytics2" as user "webui".
> analytics2=> ALTER DEFAULT PRIVILEGES FOR ROLE webui IN SCHEMA web
> GRANT SELECT ON TABLES TO dbreader;
> ERROR: permission denied for schema web
Actually, the problem is worse than I thought. It looks like I can't
set default privs for any role which is not the owner of the schema:
[jberkus@pgx-test ~]$ psql -U webui analytics2
psql (9.2.4)
Type "help" for help.
analytics2=> ALTER DEFAULT PRIVILEGES IN SCHEMA web GRANT SELECT ON
TABLES TO dbreader;
ERROR: permission denied for schema web
In other words, ALTER DEFAULT PRIVs only works if you are the role
you're trying to grant, and that role is the owner of the schema. It
doesn't work for any other role or any schema you don't own.
This means that I have NO WAY to set default privs for the majority of
users on my system. WTF? How did we break this so badly?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com