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;

• Mx Glitter: Thanks for the reply, I'm trying to use this but it doesn't seem to work either. I tried this query with my rds_superuser (I needed to add an additional GRANT read_write TO postgres because the user doesn't have the rights) and with the read_write_user directly but I still get permission denied for table my_table for read_only_user, I recreated the table after the ALTER query to be sure.
• Laurenz Albe: You have to create the table as read_write after you ran the ALTER DEFAULT PRIVILEGES in the same database, then it will work.
• Mx Glitter: Sorry I wasn't clear, I wanted to say that I did that already and it didn't work.
• Laurenz Albe: Then you did something wrong, like run the statements in different databases. Examine the settings with \ddp in psql. Try to come up with a short SQL script to reproduce your problem and add it to the question.
• Gaius: Nice answer, very succinct