0

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.

1 Answer 1

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.

5
  • Ok, I see, so it will effect other user / role .. I'd better not do that ...
    – Eric
    Dec 6, 2021 at 9:21
  • Wish pg can provide a simple command to create a read only user, for a given database, without these messy permission details.
    – Eric
    Dec 6, 2021 at 9:24
  • 1
    Your wish is granted in v14 with pg_real_all_data. Dec 6, 2021 at 9:33
  • Oh hooooooooooo, btw, you mean pg_read_all_data.
    – Eric
    Dec 6, 2021 at 16:02
  • Right, thanks for the correction. Dec 6, 2021 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.