How do I create a readonly user in Postgres in AWS RDS?

Question

I have a Postgres instance spun up in RDS and I've created a user via the following commands, however, they keep creating new tables and I can't figure out how to restrict them from creating.

CREATE USER my_ro_user WITH PASSWORD 'XXXXX';
GRANT CONNECT ON DATABASE "postgres" TO my_ro_user;
GRANT USAGE ON SCHEMA public TO my_ro_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_ro_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO my_ro_user;
REVOKE CREATE ON SCHEMA public FROM my_ro_user;

I thought the REVOKE in that last line would prevent them from creating new tables but that does not seem to be the case. What am I doing wrong?

Jeremy · Accepted Answer · 2022-08-11 20:22:47Z

10

By default, the public role has create permissions on the public schema as well. Since my_ro_user is a member of public, you will also need to revoke this:

REVOKE CREATE ON SCHEMA public FROM public;

It's generally a good idea to do this on every production database.

EDIT: public will no longer be able to create objects in the public schema by default starting in Postgres 15.

edited Aug 11, 2022 at 20:22

answered Jul 8, 2019 at 18:34

Jeremy

6,79322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2719094 Over a year ago

Yup, that did it, thanks! I guess I don't really understand how schemas work because this makes it seem like my user is in some sort of public role. I didn't realize schemas themselves have permissions that users can inherit.

Jeremy Over a year ago

No, that's not quite it. There is a public schema, but it's different from the public role. To be absolutely precise, public is a keyword that refers to privileges granted to every user. The public schema is created by default and, also by default, create is granted to PUBLIC for this schema. They happen to share the same name, but aren't explicitly related.

user2719094 Over a year ago

ohhh, that makes more sense. very confusing that they have the same name. thanks for the clarification.

Collectives™ on Stack Overflow

How do I create a readonly user in Postgres in AWS RDS?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related