0

I can't figure out why tables created by one user can be accessed by a read-only user, but not tables created by another user.

I have a viewers read-only group with this access:

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO viewers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO viewers;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO viewers;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO viewers;

I have two superusers in my database and one read-only user, all members of the group:

 Role name | Attributes | Member of 
-----------+------------+-----------
 a         | Superuser  | {viewers}
 b         | Superuser  | {viewers}
 readonly  |            | {viewers}

When I create a table as user a, the readonly user can access it. But when I create the table as user b, the readonly user gets permission denied for relation.

When I check the grants, when the table is created by user a, the viewers group gets granted select automatically:

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='testa'
;

 grantee | privilege_type 
---------+----------------
 viewers | SELECT
 a       | INSERT
 a       | SELECT
 a       | UPDATE
 a       | ...

But if the table is created by b, the the viewers group does not have that select privilege.

I'm sure it's simple, but I am not sure what commands will help me troubleshoot the permissions error. Where can I check to see the existing privileges and get the root of this?

1

The group to which the users a or b belong does not affect the permissions on the tables created by these users.

There must be default privileges defined for a, but not for b.

Check with \ddp in psql.

  • Ah, thanks! I thought that ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO viewers; would have covered that. Do I need to do that for each user (FOR ROLE a), or is there a blanket way to set that up? – Matt Hampel Nov 21 '19 at 20:02
  • No, you have to do it for each user that could create objects. – Laurenz Albe Nov 21 '19 at 20:27

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.