Postgres roles and users - permission denied for table

Question

I configured Postgres 11.2 database running on RDS following the instructions in https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/

1. I logged in as the master user created during RDS creation
2. Executed CREATE SCHEMA myschema;
3. Executed script from the link above

-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

-- Read/write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

-- Users creation
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';

-- Grant privileges to users
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

After that I connected in as app_user1 and created a new table and added one row to it. Then, I connected using reporting_user1 and tried to SELECT * FROM that new table but saw following message on the console:

ERROR:  permission denied for table first_table
SQL state: 42501

What am I missing in my configuration? I expect the reporting_user1 to have read access to all tables created by the app_user1 in myschema.

Solution

From the documentation of ALTER DEFAULT PRIVILEGES:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. The privileges can be set globally (i.e., for all objects created in the current database), or just for objects created in specified schemas. Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type.

Therefore the effect of running ALTER DEFAULT PRIVILEGES as master doesn't affect the default privileges of tables created by app_user1.

To fix that you must execute the

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

as app_user1 too.

Comments

[Domajno]

Ad the cited AWS article explains following statement should take care of that ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

[Juned Ahsan]

Does it work on the existing table? If yes then something is wrong for the new entities, thats what my answer is addressing

[Domajno]

I did not have any existing objects (entities) so I do not know. Granting explicit permission to the new table results in an error. This is executed as the master user of the DB. GRANT SELECT ON myschema.first_table TO reporting_user1; ERROR: permission denied for table first_table.

[Juned Ahsan]

try to grant to the role instead i.e. readonly. Rerunning this should help GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;

[Domajno]

Same ERROR: permission denied for table first_table

[Juned Ahsan]

are u using the same user to GRANT permissions as you used when you intiially setup ?

[Domajno]

Yes, initial master user created when RDS instance is created.

[Domajno]

If I did not do it app_user2 should still be able to read tables because it has readwrite role, right?

[Leon]

No, because - unless app_user1 executed SET ROLE readwrite before creating the table - the owner of the table is app_user1 and the fact that app_user1 and app_user2 can assume the same role readwrite doesn't enable app_user2 to peek into app_user1's personal tables.