3

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?

3

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).

  • When default privileges only apply to the target role then how I'm going to automate these privileges to members of this role aswell? Because giving rights only to role where this role users don't get these rights is kinda pointless. – Gen Apr 14 '16 at 14:13
  • @Gen: Typically, you would have a single role to create objects. Members of the role can issue a SET ROLE janerole before creating tables. Also, default privileges are not rights (or privileges), it's a convenience feature. Whoever grants the role janerole to another role can also set default privileges if applicable. – Erwin Brandstetter Apr 15 '16 at 2:42
  • "Note that default privileges only apply to the target role, not to members of this role." Is this true even if the role has INHERIT flag? – Gajus Nov 7 '18 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.