Create readonly user in postgresql

Question

Using pg 13.4, created a readonly user reader for database abc, via:

CREATE ROLE reader WITH LOGIN PASSWORD 'psssswwwd' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

GRANT CONNECT ON DATABASE abc TO reader;
GRANT USAGE ON SCHEMA public TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reader;

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

But the user can still create table, then insert & drop that table.
I want the user unable to do any change to any of the databases in pg, and can only connect & read database abc.

I've checked https://stackoverflow.com/questions/24332588/read-only-user-able-to-create-table,
and tried: REVOKE CREATE ON SCHEMA public FROM reader;
but it can still create table.

Any suggestion? Thanks.

Answer 1

The REVOKE statement you ran revoked a privilege that was never granted, so it does nothing.

If you have a look at the permissions, you will see that schema public has CREATE granted to PUBLIC (that is, everybody), so that's what you will have to revoke:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Then you have to grant CREATE to the role that is supposed to create objects in that schema.