11

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.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Domajno
  • 587
  • 1
  • 5
  • 13

2 Answers2

6

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.

Leon
  • 31,443
  • 4
  • 72
  • 97
  • If I did not do it `app_user2` should still be able to read tables because it has `readwrite` role, right? – Domajno Sep 18 '19 at 06:45
  • 1
    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. – Leon Sep 18 '19 at 08:11
0

I believe it is the Postgres behavior that it applies grants only on the existing entities does not apply the privileges on future tables. You will have to update the permissions on the newly created table by re-running the grants query for your readonly user. Or you can setup a trigger to do this automatically as mentoned here

Grant permissions to user for any new tables created in postgresql

Juned Ahsan
  • 67,789
  • 12
  • 98
  • 136
  • 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;` – Domajno Sep 16 '19 at 04:30
  • Does it work on the existing table? If yes then something is wrong for the new entities, thats what my answer is addressing – Juned Ahsan Sep 16 '19 at 04:33
  • 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`. – Domajno Sep 16 '19 at 04:40
  • try to grant to the role instead i.e. readonly. Rerunning this should help GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly; – Juned Ahsan Sep 16 '19 at 04:41
  • 1
    Same `ERROR: permission denied for table first_table` – Domajno Sep 16 '19 at 04:44
  • are u using the same user to GRANT permissions as you used when you intiially setup ? – Juned Ahsan Sep 16 '19 at 04:50
  • Yes, initial master user created when RDS instance is created. – Domajno Sep 16 '19 at 04:51