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

Поиск
Список
Период
Сортировка
От Durumdara
Тема Re: Role for just read the data + avoid CREATE / ALTER / DROP
Дата
Msg-id CAEcMXhm7XYHNPMaVkY810ghCVs7VHBkyt=BHeB_xBe453BPayQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Role for just read the data + avoid CREATE / ALTER / DROP  (Thomas Kellerer <shammat@gmx.net>)
Список pgsql-general
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Luca Ferrari
Дата:
Сообщение: PL/Perl function signatures
Следующее
От: duc hiep ha
Дата:
Сообщение: Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly