Create a read-write user and a read-only user

Question

I'm trying to do what I think is a common setup for a PostgreSQL database on a RDS instance. I want to have:

• Either on public schema, or on a custom schema I don't especially care
• A read-write user
• A read-only user
• The read-write user should be able to create a table in the schema
• The read-only user should be able to read from this new table automatically, i.e. without an additional GRANT

I tried to follow this AWS blog post and this answer but it seems I'm hitting a wall.

I've tried the following to no avail, postgres user is the user created by RDS that has the role rds_superuser:

-- Making sure roles can't do anything on the database without explicit grant
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE my_database FROM PUBLIC;
CREATE SCHEMA my_schema;

-- Read-write
CREATE ROLE read_write;

GRANT CONNECT ON DATABASE my_database TO read_write;
GRANT USAGE, CREATE ON SCHEMA my_schema TO read_write;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_schema TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write;

GRANT USAGE ON ALL SEQUENCES IN SCHEMA my_schema TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT USAGE ON SEQUENCES TO read_write;

-- Read-only
CREATE ROLE read_only;

GRANT CONNECT ON DATABASE my_database TO read_only;
GRANT USAGE ON SCHEMA my_schema TO read_only;

GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO read_only;

-- Users
CREATE USER read_write_user WITH PASSWORD 'password_1';
GRANT read_write TO my_database;

CREATE USER read_only_user WITH PASSWORD 'password_2';
GRANT read_only TO my_database_read_only;

When I connect to the database with user read_write_user, I can create a new table my_table, but if I connect with user read_only_user, I can't access it, I get the following error: permission denied for table my_table.

I connected back with the user postgres and saw that I didn't have rights to grant access to this table to read_only role:

GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO read_only;

leads to this error: no privileges were granted for "my_table".

So if I understand correctly postgres user doesn't have enough rights to grant access to objects created by read_write_user and the ALTER DEFAULT PRIVILEGES didn't do what I thought it would do.

I tried to add the following queries on a new database before revoking/granting anything:

GRANT ALL ON DATABASE my_database TO postgres;
GRANT ALL ON SCHEMA my_schema TO postgres;

and it didn't help.

I have the feeling that this is linked to the fact the rds_superuser is not a real superuser but I don't know how to go forward, is there a way to do what I'm trying to do?

Answer 1

ALTER DEFAULT PRIVILEGES only affects tables created by the user who ran the ALTER DEFAULT PRIVILEGES statement.

You'd have to do this:

ALTER DEFAULT PRIVILEGES FOR ROLE read_write IN SCHEMA my_schema
   GRANT SELECT ON TABLES TO read_only;