1

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?

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;
  • 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. – Mx Glitter Sep 10 at 10:02
  • You have to create the table as read_write after you ran the ALTER DEFAULT PRIVILEGES in the same database, then it will work. – Laurenz Albe Sep 10 at 10:09
  • Sorry I wasn't clear, I wanted to say that I did that already and it didn't work. – Mx Glitter Sep 10 at 10:20
  • 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. – Laurenz Albe Sep 10 at 10:31
  • Nice answer, very succinct – Gaius Sep 10 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.