Postgresql – Alter default privileges for role

permissionspostgresqlpostgresql-9.5

I have database called Blackacre and two roles who have access to it: johnrole and janerole. janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege.

Problem is when a user from janerole creates a new table, then a user from johnrole can't SELECT from the new table. 

ERROR:  permission denied for relation mopery

I tried to use ALTER DEFAULT in different ways to automate that:

ALTER DEFAULT PRIVILEGES FOR ROLE johnrole IN SCHEMA public
   GRANT SELECT ON TABLES TO johnrole;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO johnrole;
ALTER DEFAULT PRIVILEGES FOR ROLE johnrole GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO GROUP johnrole;

None of them work and I still end up with the same error.

When I use ALTER DEFAULT to automate future tables privileges for user e.g john then everything works, he can SELECT from new tables created in the database without any error.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO john;

This is how I added people to roles:

GRANT johnrole TO adam;
GRANT janerole TO eva;

Granted usage on Blackacre database like this:

GRANT USAGE ON SCHEMA public TO johnrole;
GRANT USAGE ON SCHEMA public TO janerole;

Gave some privileges like that:

GRANT ALL ON ALL TABLES IN SCHEMA public TO janerole;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO johnrole;

After that I tried to give automatic privileges with ALTER DEFAULT as above described don't work. Any ideas how to get default privileges working properly?

Best Answer

janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege.

You got at least one aspect of the command backwards in all your attempts:
Alter default privileges for the role that creates objects - which in turn grants privileges to another role. I.e.:

ALTER DEFAULT PRIVILEGES FOR ROLE janerole IN SCHEMA public
GRANT SELECT ON TABLES TO johnrole;

Do this while logged in as janerole (or a member of the role).

Note that default privileges only apply to the target role, not to members of this role.

And don't forget to GRANT USAGE ON SEQUENCES as well if you have any serial columns. Possibly SELECT and UPDATE, too.

There may be more problems, the question is not completely clear.

Related:

Note: There is an open bug in pgAdmin, the display can be incorrect (still kicking in the current pgAdmin 1.22).

Related Question