(unnamed)
text/html
Filename: (unnamed)
Type: text/html
Part: 0
<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@gmail.com><br/> <b>An:</b> "Postgres General" <pgsql-general@postgresql.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>