| From: | Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> |
|---|---|
| To: | |
| Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Aw: Role for just read the data + avoid CREATE / ALTER / DROP |
| Date: | 2023-08-25 13:52:54 |
| Message-ID: | trinity-0ba75213-b88c-4b24-9122-c40b627272d3-1692971574494@3c-app-gmx-bap23 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>set default_transaction_read_only can help</div>
<div> </div>
<div>Karsten</div>
<div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Freitag, 25. August 2023 um 14:38 Uhr<br/>
<b>Von:</b> "Durumdara" <durumdara(at)gmail(dot)com><br/>
<b>An:</b> "Postgres General" <pgsql-general(at)postgresql(dot)org><br/>
<b>Betreff:</b> Role for just read the data + avoid CREATE / ALTER / DROP</div>
<div name="quoted-content">
<div>Dear Members!
<div> </div>
<div>Normally we use the "db owner" role for the connection, but this can do everything (DDL-DML).</div>
<div>Somewhere they want to access a DB through a Read Only connection.</div>
<div> </div>
<div>In MS-SQL Server it is simple, but in PG it seems to be some kind of "hell".</div>
<div>Formerly we tried to use multiple roles with lower rights, but we had wrong experiences with them, so we stopped very soon.</div>
<div> </div>
<div>So: is there any easier way to make ReadOnly access to a database?</div>
<div> </div>
<div>I've tried with this command:</div>
<div> </div>
<blockquote style="margin: 0 0 0 40.0px;border: none;padding: 0.0px;">
<div><font color="#0000ff" face="monospace">CREATE ROLE u_tdb_ro WITH LOGIN;</font></div>
<div>
<div><font color="#0000ff" face="monospace">REVOKE ALL PRIVILEGES ON SCHEMA public FROM u_tdb_ro;<br/>
REVOKE ALL ON DATABASE tdb FROM u_tdb_ro;<br/>
<br/>
GRANT CONNECT ON DATABASE tdb TO u_tdb_ro;<br/>
GRANT USAGE ON SCHEMA public TO u_tdb_ro;<br/>
<br/>
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM u_tdb_ro;<br/>
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM u_tdb_ro;</font></div>
<div> </div>
<div><font face="monospace"><span style="color: rgb(0,0,255);">REVOKE CREATE ON SCHEMA public FROM u_tdb_ro;</span><br/>
<font color="#0000ff">REVOKE CREATE ON DATABASE tdb FROM u_tdb_ro;<br/>
REVOKE CREATE ON TABLESPACE pg_default FROM u_tdb_ro;<br/>
<br/>
GRANT SELECT ON ALL TABLES IN SCHEMA public TO u_tdb_ro;<br/>
<br/>
ALTER USER u_tdb_ro SET DEFAULT_TRANSACTION_READ_ONLY = ON;<br/>
<br/>
ALTER DEFAULT PRIVILEGES FOR role u_tdb GRANT SELECT ON TABLES TO u_tdb_ro;</font></font></div>
<div> </div>
</div>
</blockquote>
<div> </div>
<div>But: I can't avoid that the user can execute a CREATE TABLE command!</div>
<div> </div>
<div>
<blockquote style="margin: 0 0 0 40.0px;border: none;padding: 0.0px;">
<div><font color="#cc0000"><font face="monospace">set role to u_tdb_ro;<br/>
drop table if exists test_rororo;<br/>
create table if not exists test_rororo (roro int primary key);<br/>
select * from test_rororo;</font></font></div>
</blockquote>
</div>
<div> </div>
<div>I don't understand why these commands don't work?!</div>
<div> </div>
<div>
<blockquote style="margin: 0 0 0 40.0px;border: none;padding: 0.0px;">
<div><font face="monospace"><span style="color: rgb(0,0,255);"><b>REVOKE CREATE ON SCHEMA public FROM u_tdb_ro;</b></span><br/>
<font color="#0000ff"><b>REVOKE CREATE ON DATABASE tdb FROM u_tdb_ro;<br/>
REVOKE CREATE ON TABLESPACE pg_default FROM u_tdb_ro;</b></font></font></div>
</blockquote>
</div>
<div> </div>
<div>I read that the magic command is:</div>
<div> </div>
<div>
<blockquote style="margin: 0 0 0 40.0px;border: none;padding: 0.0px;">
<div>
<pre class="gmail-lang-sql gmail-s-code-block" style="margin-top: 0.0px;border: 0.0px;font-stretch: inherit;vertical-align: baseline;box-sizing: inherit;width: auto;max-height: 600.0px;overflow: auto;"><code class="gmail-hljs gmail-language-sql" style="margin: 0.0px;padding: 0.0px;border: 0.0px;font-style: inherit;font-variant: inherit;font-stretch: inherit;line-height: inherit;font-family: inherit;vertical-align: baseline;box-sizing: inherit;background-color: transparent;white-space: inherit;"><b><font color="#38761d"><span class="gmail-hljs-keyword" style="margin: 0.0px;padding: 0.0px;border: 0.0px;font-style: inherit;font-variant: inherit;font-stretch: inherit;line-height: inherit;font-family: inherit;font-size: 13.0px;vertical-align: baseline;box-sizing: inherit;">REVOKE</span> <span class="gmail-hljs-keyword" style="margin: 0.0px;padding: 0.0px;border: 0.0px;font-style: inherit;font-variant: inherit;font-stretch: inherit;line-height: inherit;font-family: inherit;font-size: 13.0px;vertical-align: baseline;box-sizing: inherit;">CREATE</span> <span class="gmail-hljs-keyword" style="margin: 0.0px;padding: 0.0px;border: 0.0px;font-style: inherit;font-variant: inherit;font-stretch: inherit;line-height: inherit;font-family: inherit;font-size: 13.0px;vertical-align: baseline;box-sizing: inherit;">ON</span> SCHEMA public <span class="gmail-hljs-keyword" style="margin: 0.0px;padding: 0.0px;border: 0.0px;font-style: inherit;font-variant: inherit;font-stretch: inherit;line-height: inherit;font-family: inherit;font-size: 13.0px;vertical-align: baseline;box-sizing: inherit;">FROM</span> PUBLIC;</font></b></code></pre>
</div>
</blockquote>
</div>
<div>Why does this work, and why are the 3 above not???</div>
<div>What is the meaning of these 3 if they don't work?</div>
<div>Why do the PGSQL developers create these statements if they do not work?</div>
<div> </div>
<div>I wanna understand this well, to know that Full Read Only mode is possible or not; or what are the limitations.</div>
<div> </div>
<div>Thank you for lighting my mind!</div>
<div> </div>
<div>Best regards</div>
<div>dd</div>
</div>
</div>
</div>
</div>
</div></div></body></html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 5.7 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Thomas Kellerer | 2023-08-25 14:02:40 | Re: Role for just read the data + avoid CREATE / ALTER / DROP |
| Previous Message | Stephen Frost | 2023-08-25 13:09:35 | Re: LDAP Authentication |