Aw: Role for just read the data + avoid CREATE / ALTER / DROP

[Karsten Hilbert <Karsten.Hilbert@xxxxxxx>]

set default_transaction_read_only can help

 

Karsten

 

 

Gesendet: Freitag, 25. August 2023 um 14:38 Uhr
Von: "Durumdara" <durumdara@xxxxxxxxx>
An: "Postgres General" <pgsql-general@xxxxxxxxxxxxxx>
Betreff: Role for just read the data + avoid CREATE / ALTER / DROP

Dear Members!

 

Normally we use the "db owner" role for the connection, but this can do everything (DDL-DML).

Somewhere they want to access a DB through a Read Only connection.

 

In MS-SQL Server it is simple, but in PG it seems to be some kind of "hell".

Formerly we tried to use multiple roles with lower rights, but we had wrong experiences with them, so we stopped very soon.

 

So: is there any easier way to make ReadOnly access to a database?

 

I've tried with this command:

 

CREATE ROLE u_tdb_ro WITH LOGIN;

REVOKE ALL PRIVILEGES ON SCHEMA public FROM u_tdb_ro;
REVOKE ALL ON DATABASE tdb FROM u_tdb_ro;

GRANT CONNECT ON DATABASE tdb TO u_tdb_ro;
GRANT USAGE ON SCHEMA public TO u_tdb_ro;

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM u_tdb_ro;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM u_tdb_ro;

 

REVOKE CREATE ON SCHEMA public FROM u_tdb_ro;
REVOKE CREATE ON DATABASE tdb FROM u_tdb_ro;
REVOKE CREATE ON TABLESPACE pg_default FROM u_tdb_ro;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO u_tdb_ro;

ALTER USER u_tdb_ro SET DEFAULT_TRANSACTION_READ_ONLY = ON;

ALTER DEFAULT PRIVILEGES FOR role u_tdb GRANT SELECT ON TABLES TO u_tdb_ro;

 

 

But: I can't avoid that the user can execute a CREATE TABLE command!

 

set role to u_tdb_ro;
drop table if exists test_rororo;
create table if not exists test_rororo (roro int primary key);
select * from test_rororo;

 

I don't understand why these commands don't work?!

 

REVOKE CREATE ON SCHEMA public FROM u_tdb_ro;
REVOKE CREATE ON DATABASE tdb FROM u_tdb_ro;
REVOKE CREATE ON TABLESPACE pg_default FROM u_tdb_ro;

 

I read that the magic command is:

 

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Why does this work, and why are the 3 above not???

What is the meaning of these 3 if they don't work?

Why do the PGSQL developers create these statements if they do not work?

 

I wanna understand this well, to know that Full Read Only mode is possible or not; or what are the limitations.

 

Thank you for lighting my mind!

 

Best regards

dd