Dear Thomas, Dear All!
Thank you for the answers, and explanations!
Thomas Kellerer <shammat@xxxxxxx> ezt írta (időpont: 2023. aug. 25., P, 16:02):
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;
I forgot to write that we have a PGSQL 11.xxx version (older than 15).
What I experienced was that when I revoked CREATE from the public, the database owner also can't create tables.
But after this:
GRANT CREATE ON SCHEMA public TO u_tdb;
I got back the creation right for u_tdb.
set role to u_tdb;drop table if exists test_230824_B;create table if not exists test_230824_B(a int primary key);insert into test_230824_B values (1), (2);select * from test_230824_B;
Formerly I thought that the database owner always had rights to create tables, but in PG11.xxx seems to not.
Thank you again! I will test all operations to avoid the side effects. Because I must avoid endangering normal usage.
Best regards
dd
