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?