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

Started by Durumdaraover 2 years ago5 messagesgeneral

durumdara@gmail.com

over 2 years ago

Dear Members!

Normally we use the "db owner" role for the connection, but this can do
everything (DDL-DML).
Somewhere they want to access a DB through a Read Only connection.

In MS-SQL Server it is simple, but in PG it seems to be some kind of "hell".
Formerly we tried to use multiple roles with lower rights, but we had wrong
experiences with them, so we stopped very soon.

So: is there any easier way to make ReadOnly access to a database?

I've tried with this command:

CREATE ROLE u_tdb_ro WITH LOGIN;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM u_tdb_ro;
REVOKE ALL ON DATABASE tdb FROM u_tdb_ro;

GRANT CONNECT ON DATABASE tdb TO u_tdb_ro;
GRANT USAGE ON SCHEMA public TO u_tdb_ro;

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM u_tdb_ro;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM u_tdb_ro;

REVOKE CREATE ON SCHEMA public FROM u_tdb_ro;
REVOKE CREATE ON DATABASE tdb FROM u_tdb_ro;
REVOKE CREATE ON TABLESPACE pg_default FROM u_tdb_ro;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO u_tdb_ro;

ALTER USER u_tdb_ro SET DEFAULT_TRANSACTION_READ_ONLY = ON;

ALTER DEFAULT PRIVILEGES FOR role u_tdb GRANT SELECT ON TABLES TO u_tdb_ro;

But: I can't avoid that the user can execute a CREATE TABLE command!

set role to u_tdb_ro;
drop table if exists test_rororo;
create table if not exists test_rororo (roro int primary key);
select * from test_rororo;

I don't understand why these commands don't work?!

*REVOKE CREATE ON SCHEMA public FROM u_tdb_ro;*

*REVOKE CREATE ON DATABASE tdb FROM u_tdb_ro;REVOKE CREATE ON TABLESPACE
pg_default FROM u_tdb_ro;*

I read that the magic command is:

*REVOKE CREATE ON SCHEMA public FROM PUBLIC;*

Why does this work, and why are the 3 above not???
What is the meaning of these 3 if they don't work?
Why do the PGSQL developers create these statements if they do not work?

I wanna understand this well, to know that Full Read Only mode is possible
or not; or what are the limitations.

Thank you for lighting my mind!

Best regards
dd

Karsten Hilbert

Karsten.Hilbert@gmx.net

over 2 years ago

In reply to: Durumdara (#1)

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

<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>set default_transaction_read_only can help</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>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>So: is there any easier way to make ReadOnly access to a database?</div>

<div>I've tried with this command:</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><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>But: I can't avoid that the user can execute a CREATE TABLE command!</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>I don't understand why these commands don't work?!</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>I read that the magic command is:</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>I wanna understand this well, to know that Full Read Only mode is possible or not; or what are the limitations.</div>

<div>Thank you for lighting my mind!</div>

<div>Best regards</div>

Thomas Kellerer

shammat@gmx.net

over 2 years ago

In reply to: Durumdara (#1)

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

Durumdara schrieb am 25.08.2023 um 14:38:

Normally we use the "db owner" role for the connection, but this can do everything (DDL-DML).
Somewhere they want to access a DB through a Read Only connection.

In MS-SQL Server it is simple, but in PG it seems to be some kind of "hell".
Formerly we tried to use multiple roles with lower rights, but we had
wrong experiences with them, so we stopped very soon.>
So: is there any easier way to make ReadOnly access to a database?

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;

But: I can't avoid that the user can execute a CREATE TABLE command!

set role to u_tdb_ro;
drop table if exists test_rororo;
create table if not exists test_rororo (roro int primary key);
select * from test_rororo;

Most likey you are indeed using an older Postgres version that still
granted USAGE on the public schema to the role public and therefor
the CREATE TABLE succeeded.

Given the grants you have shown, the DROP TABLE could only succeed if u_tdb_ro was the owner of the table.

I read that the magic command is:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Why does this work, and why are the 3 above not???
What is the meaning of these 3 if they don't work?

A GRANT given to the public role will always be available to all
roles in the system regardles of the grants to that specific role.

David G. Johnston

david.g.johnston@gmail.com

over 2 years ago

In reply to: Durumdara (#1)

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

On Friday, August 25, 2023, Durumdara <durumdara@gmail.com> wrote:

I don't understand why these commands don't work?!

*REVOKE CREATE ON SCHEMA public FROM u_tdb_ro;*

*REVOKE CREATE ON DATABASE tdb FROM u_tdb_ro;REVOKE CREATE ON TABLESPACE
pg_default FROM u_tdb_ro;*

Because at no point has the system ever actually granted those specific
permissions to that specific role. When you revoke something it has to
have been previously granted. The underlying complication is permission
inheritance. You need to know where in the inheritance chain a permission
originates in order to know your options for removing it. In this case the
grant to the public group that all roles are a member of.

David J.

Durumdara

durumdara@gmail.com

over 2 years ago

In reply to: Thomas Kellerer (#3)

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

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