0

I have a readonly user that I defined as the following:

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE dbname TO readonly;

GRANT USAGE ON SCHEMA public TO readonly;
GRANT USAGE ON SCHEMA schema_name TO readonly;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO readonly;

I am currently running these commands as the postgres user who is the rds_superuser (I'm using AWS RDS). This works fine, and allows readonly role to read existing tables that postgres user creates, and also any new tables that postgres creates. However, when a new role creates a table, readonly cannot SELECT the table that the new role creates. For example, I also have a readwrite role, and tables created in that schema by readwrite cannot be SELECT by readonly.

How do you create a read-only user in PostgreSQL? suggests that by default ALTER DEFAULT PRIVILEGES ... only works by default on objects created by the user that issued the command.

How can I more generally just allow readonly user to have SELECT privileges on ANY table created in the schema regardless of who creates it.

Vincent
  • 7,808
  • 13
  • 49
  • 63
  • See also [here](https://stackoverflow.com/a/63192368/1048572) and [there](https://stackoverflow.com/q/19309416/1048572). I don't think there's a catch-all though, even if you are a superuser. – Bergi Aug 11 '20 at 00:11

1 Answers1

1

You will need to change the default privileges for any potential creator, e.g. for your readwrite role, use the following

ALTER DEFAULT PRIVILEGES for role readwrite
    IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
  • Thanks! The way I have it set up is I only have two roles: `readwrite` and `readonly`. However, I then create separate user logins for each person on my team. So let's say I have `User1` and `User2`. I then granted the roles to each user (e.g. `GRANT readwrite to USER1`). The behavior I found was User1 would create the table, and I actually ran the statement you posted above. I had assumed since User1 had the readwrite role, the statement you posted above would work. However, this was not the case, I actually had to run something like `ALTER DEFAULT PRIVILEGES for USER User1 ...` Why is that? – Vincent Aug 11 '20 at 17:10
  • Would it be better practice if I have a team that I don't actually create different users for each team member? I just have everyone login to the same `readwrite` role? – Vincent Aug 11 '20 at 17:11