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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
4
  • Ok, I see, so it will effect other user / role .. I'd better not do that ... Commented 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. Commented Dec 6, 2021 at 9:24
  • 2
    Your wish is granted in v14 with pg_real_all_data. Commented Dec 6, 2021 at 9:33
  • Oh hooooooooooo, btw, you mean pg_read_all_data. Commented Dec 6, 2021 at 16:02

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.