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

Durumdara schrieb am 25.08.2023 um 14:38:
> 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?

With Postgres 15 it should be as simple as:

   CREATE ROLE CREATE ROLE u_tdb_ro WITH LOGIN;
   GRANT pg_read_all_data TO u_tdb_ro;

In previous versions the PUBLIC (pseudo) role was granted the CREATE privilege
on the public schema which is no longer the case since Postgres 15

For previous versions it's highly recommended to do this as well:

   revoke create on schema public from public;


> 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;

Most likey you are indeed using an older Postgres version that still
granted USAGE on the public schema to the role public and therefor
the CREATE TABLE succeeded.

Given the grants you have shown, the DROP TABLE could only succeed if u_tdb_ro was the owner of the table.

> 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?

A GRANT given to the public role will always be available to all
roles in the system regardles of the grants to that specific role.

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;