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

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

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;

StackExchange Database Administrators Q#247417, answer score: 4