8

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?

Improve this question
0

1 Answer 1

Reset to default
12

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 or a superuser).

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:

Improve this answer
4
  • 2
    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, 2016 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, 2016 at 2:42
  • 1
    "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, 2018 at 17:46
  • 1
    @Gajus I think the point is that when you alter default privileges, you are only altering default privileges of objects created by the role / user that is executing the command. New tables created by janerole would be accessible by johnrole, but a new table created by postgresAdmin would not (assuming we're in a custom schema so we don't have to deal with the public schema intricacies)
    – ps2goat
    Feb 24, 2021 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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