[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Role for just read the data + avoid CREATE / ALTER / DROP
From:       Durumdara <durumdara () gmail ! com>
Date:       2023-08-28 8:22:46
Message-ID: CAEcMXhm7XYHNPMaVkY810ghCVs7VHBkyt=BHeB_xBe453BPayQ () mail ! gmail ! com
[Download RAW message or body]

Dear Thomas, Dear All!

Thank you for the answers, and explanations!

Thomas Kellerer <shammat@gmx.net> ezt =C3=ADrta (id=C5=91pont: 2023. aug. 2=
5., 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

[Attachment #3 (text/html)]

<div dir="ltr"><div>Dear Thomas, Dear All!</div><div><br></div><div>Thank you for the \
answers, and explanations!</div><div><br></div><div class="gmail_quote"><div \
dir="ltr" class="gmail_attr">Thomas Kellerer &lt;<a \
href="mailto:shammat@gmx.net">shammat@gmx.net</a>&gt; ezt írta (időpont: 2023. aug. \
25., P, 16:02):<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br> With Postgres 15 \
it should be as simple as:<br> <br>
     CREATE ROLE CREATE ROLE u_tdb_ro WITH LOGIN;<br>
     GRANT pg_read_all_data TO u_tdb_ro;<br>
<br>
In previous versions the PUBLIC (pseudo) role was granted the CREATE privilege<br>
on the public schema which is no longer the case since Postgres 15<br>
<br>
For previous versions it&#39;s highly recommended to do this as well:<br>
<br>
     revoke create on schema public from \
public;<br></blockquote><div><br></div><div>I forgot to write that we have a PGSQL \
11.xxx version (older than 15).</div><div><br></div><div>What I experienced was that \
when I revoked CREATE from the public, the database owner also can&#39;t create \
tables.</div><div><br></div><div>But after this:</div></div><blockquote \
style="margin:0 0 0 40px;border:none;padding:0px"><div class="gmail_quote"><div><font \
face="monospace">GRANT CREATE ON SCHEMA public TO \
u_tdb;</font></div></div></blockquote><div class="gmail_quote"><div><br></div><div>I \
got back the creation right for u_tdb.</div><div><br></div></div><blockquote \
style="margin:0 0 0 40px;border:none;padding:0px"><div class="gmail_quote"><div><font \
face="monospace">set role to  

u_tdb;</font></div></div><div class="gmail_quote"><div><font face="monospace">drop \
table if exists test_230824_B;</font></div></div><div class="gmail_quote"><div><font \
face="monospace">create table if not exists test_230824_B(a int primary \
key);</font></div></div><div class="gmail_quote"><div><font face="monospace">insert \
into test_230824_B values (1), (2);</font></div></div><div \
class="gmail_quote"><div><font face="monospace">select * from \
test_230824_B;</font></div></div></blockquote><div \
class="gmail_quote"><div><br></div><div>Formerly I thought that the database owner \
always had rights to create tables, but in PG11.xxx seems to \
not.</div><div><br></div><div>Thank you again! I will test all operations  to avoid \
the side effects. Because I must avoid endangering normal \
usage.</div><div><br></div><div>Best regards</div><div>dd</div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]