pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: Role for just read the data + avoid CREATE / ALTER / DROP
Date: Fri, 25 Aug 2023 16:02:40 +0200
Message-ID: <5c41503d-ff8e-483a-9736-4a36fa64e8a1@gmx.net> (raw)
In-Reply-To: <CAEcMXhmnHSA-f_+5vtBsh8kQWuofMOzcV=zwjPuH7QxQ6WdFzg@mail.gmail.com>
References: <CAEcMXhmnHSA-f_+5vtBsh8kQWuofMOzcV=zwjPuH7QxQ6WdFzg@mail.gmail.com>

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.










view thread (4+ messages)  latest in thread

Message-ID: <5c41503d-ff8e-483a-9736-4a36fa64e8a1@gmx.net>
Permalink:  ../5c41503d-ff8e-483a-9736-4a36fa64e8a1@gmx.net/
Also on:    postgresql.org/message-id/5c41503d-ff8e-483a-9736-4a36fa64e8a1@gmx.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: shammat@gmx.net
  Subject: Re: Role for just read the data + avoid CREATE / ALTER / DROP
  In-Reply-To: <5c41503d-ff8e-483a-9736-4a36fa64e8a1@gmx.net>
  References: <CAEcMXhmnHSA-f_+5vtBsh8kQWuofMOzcV=zwjPuH7QxQ6WdFzg@mail.gmail.com> <5c41503d-ff8e-483a-9736-4a36fa64e8a1@gmx.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox