Create true read only user in PostgreSQL

Question

I've spanned a PostgreSQL database in Digital Ocean. I now need to come with a set of users and databases for which I've thought on creating several databases (production, staging, etc) and having 2 associated roles for each database with read-only and read-write permissions (production_ro, production_rw, staging_ro, staging_rw, etc). My idea is that, by having those roles, I can now create individual users and assign them one of the roles so that I can quickly change/remove them in case of a breach.

I've been researching on this and all pages I can find have a set of instructions similar to the ones in here:

-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

-- Read/write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

-- Users creation
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';

-- Grant privileges to users
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

I've carefully followed those instructions and monitored that none of them failed but, after successfully running them all, I'm left with supposedly read-only users that can, in fact, create tables, not see the tables created by other users, and switch databases.

What am I doing wrong?

--- Edit ---

This is the result of the \dn+ command:

                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |

--- Edit 2 ---

Here is what I do (for security reasons, I'll redact the users as <USER_A>, <USER_B>, etc. those redacted users will match 1 to 1 to the real ones):

$ psql "postgresql://USER_A:<PASSWORD>@<DOMAIN>:<PORT>/<DEFAULT_DB>?sslmode=require"
psql (15.1, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

<DEFAULT_DB>=> \connect production
psql (15.1, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "production" as user "USER_A"
production=> \du
                                                                      List of roles
    Role name    |                         Attributes                         |                                 Member of
-----------------+------------------------------------------------------------+---------------------------------------------------------------------------
 USER_B          | Superuser, Replication                                     | {}
 USER_A          | Create role, Create DB, Replication, Bypass RLS            | {pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend,r_production_ro}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 production_ro   | Cannot login                                               | {}

production=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
WARNING:  no privileges could be revoked for "public"
REVOKE
production=>

--- Edit 3 ---

Got in contact with DigitalOcean. This is their response:

Just to let you know that we are investigating this issue, so far I was able to reproduce the behavior. It seems that in order to remove the create table from the public schema from a user we would need to "REVOKE CREATE ON SCHEMA public FROM PUBLIC;" which is not allowed as the doadmin use is not a superuser and revoking this privilege would impact other roles.

Answer 1

If the user can create tables it has CREATE permissions on the schema in question. Look at those permissions with \dn+ public in psql. Identify the permissions in question and REVOKE them.

Alternatively, if you use PostgreSQL v15 or above, it micht be that your database user is directly or indirectly a member of the predefiled role pg_write_all_data. Revoke that membership.

In your specific case, the correct thing to do is to revoke the default CREATE privilege on schema public from PUBLIC, that is, everyone:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

You say you already did that, and it caused a warning and had no effect. That is not what PostgreSQL would do. You'll have to ask the people who modified PostgreSQL, in your case DigitalOcean.