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

Dear Thomas, Dear All!

Thank you for the answers, and explanations!

Thomas Kellerer <shammat(at)gmx(dot)net> 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