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
publicschema, 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
I've tried the following to no avail,
postgres user is the user created by RDS that has the role
-- 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
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?