4

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?

3

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;
6
  • 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 '19 at 10:02
  • 1
    You have to create the table as read_write after you ran the ALTER DEFAULT PRIVILEGES in the same database, then it will work. Sep 10 '19 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 '19 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. Sep 10 '19 at 10:31
  • Nice answer, very succinct
    – Gaius
    Sep 10 '19 at 10:55

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .