Postgres Pro
Downloads
Home   >   mailing lists

Re: Role for just read the data + avoid CREATE / ALTER / DROP - Mailing list pgsql-general

From Durumdara
Subject Re: Role for just read the data + avoid CREATE / ALTER / DROP
Date
Msg-id CAEcMXhm7XYHNPMaVkY810ghCVs7VHBkyt=BHeB_xBe453BPayQ@mail.gmail.com
Whole thread Raw
In response to Re: Role for just read the data + avoid CREATE / ALTER / DROP  (Thomas Kellerer <shammat@gmx.net>)
List pgsql-general
Tree view
Dear Thomas, Dear All!

Thank you for the answers, and explanations!

Thomas Kellerer <shammat@gmx.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

pgsql-general by date:

Previous
From: Luca Ferrari
Date:
Subject: PL/Perl function signatures
Next
From: duc hiep ha
Date:
Subject: Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly