Postgres - how can I restrict the privilege to create/drop a table?

Question

I would like to create "read-only privileges" in a PostgreSQL database (including the restriction of creating or dropping tables).

My strategy is to create a group with these privileges and then add roles that have had all their privileges revoked. In that way, the only inherit privileges when part of the read-only group.

I used the following commands to create privileges but it seems roles can add, delete tables when they join the group:

role_test_db=# REVOKE ALL ON DATABASE role_test_db FROM select_access_group;
REVOKE
role_test_db=# GRANT CONNECT ON DATABASE role_test_db TO select_access_group;
GRANT
role_test_db=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO select_access_group;
GRANT

I was reading the documentation and it seems like creating tables would be under CREATE privilege but I have not granted this. Can some explain why users part of this group can still make tables?

Answer 1

There are several mistakes:

1. Revoking privileges on the database does not restrict user's rights to create objects. For that, you have to revoke privileges on the schemas.

2. You can only REVOKE privileges that were GRANTed (by default or explicitly). I doubt that select_access_group has ever been granted any privileges on the database.

You likely forgot to revoke the dangerous default CREATE privilege on schema public. Connect as superuser and run

REVOKE CREATE ON SCHEMA public FROM PUBLIC;