Benutzer / Rolle nur Read-Only anlegen?

Moin,

Ich habe folgende Aufgabenstellung:
es gibt ein Schema ‘Schema1’ und einen Benutzer/Rolle ‘User1’, dem dieses Rolle Schema gehört.
Er hat die Rechte
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication FROM pg_roles;

User1: rolsuper=false
rolinherit=true
rolcreaterole=false
roleceratedb=false
rolcanlogin=true
rolreplication=fal

Jetzt soll ein Benutzer/Rolle angelegt werden, der auf dieses Schema zugreifen darf und Nur-Lese-Rechte auf diese Schema hat.

Wie bewerkstellige ich das?
Über ‘\dgS+’ habe ich die Rolle ‘pg_read_all_data’ gefunden.

Kann mit diesem Benutzer/dieser Rolle ein Schema ausgelesen werden?
Oder sollte man einen Benutzer anlegen, der nur auf ein bestehendes Schema zugreifen darf
und das nur lesend?

Oder anders herum gefragt:

Kann ich ‘pg_read_all_data’ benutzen, um die Daten aus ‘Schema1’ auszulesen?

OS ist Debian 11, PostgreSQL 16

Meine Idee wäre jetzt gewesen:

CREATE ROLE PowerBi;
GRANT CONNECT ON DATABASE Schema1 TO PowerBi GRANTED BY pg_read_all_data;
GRANT SELECT ON ALL TABLES IN SCHEMA Schema1 GRANTED BY pg_read_all_data;

Ich hab zur Zeit (noch) keine Test-Instanz, um das mal aus zu probieren.

Braucht der Benutzer / die Rolle zwingend ein Login/Passwort, um die Daten aus zulesen?

MfG

Ja, wie im Handbuch erklärt darf ein User mit dieser Rolle alles lesen und er hat auch das USAGE Recht auf alle Schemata. Es reicht dem Benutzer die Rolle zu “granten”:

grant pg_read_all_data to powerbi;

Wenn Du den Zugriff aber wirklich nur auf dieses eine Schema einschränken möchtest, dann musst Du das manuell machen:

create role read_only;
grant usage on schema schema1;
grant select on all tables in schema schema1;
grant user powerbi password '*****';
grant connect on database reporting_database to powerbi;
grant read_only to powerbi;

Datenbank und Schema sind zwei verschiedene Dinge. GRANT CONNECT ON DATABASE Schema1 macht deswegen wenig Sinn - es sei denn Du hast eine Datenbank die schema1 heißt.

1 „Gefällt mir“

Hallo Castorp,
danke für Deine Antwort.

Wenn ich Dich richtig verstehe, dann müsste das dann zusammen gefasst so aussehen:

CREATE ROLE PowerBi;
GRANT USER PowerBi PASSWORD ‘*****’;

CREATE ROLE read_only;
GRANT USAGE ON SCHEMA Schema1;
GRANT SELECT ON ALL TABLES IN SCHEMA Schema1;

Ich muss das morgen nochmal genau überprüfen ob das ein Schema oder eine Datenbank ist.

Die Rechte für den eigentlichen Benutzer ‘User1’ bleiben aber erhalten?

Ich möchte ja nur das read_only für den Benutzer PowerBi und nur den Zugriff auf Schema1.

User1 soll weiterhin seine Rechte auf Schema1 behalten.

MfG

GRANT USER gibt es nicht. Einen Benutzer der sich anmelden darf, legt man an mit:

create user powerbi password '****';

Du musst die read_only Rolle dann noch dem Benutzer geben:

grant read_only to powerbi;

Moin castorp,

ok, es ist eine Datenbank, kein Schema.

Also das funktioniert schon mal in einem Testsystem ( Klon des produktiven):

CREATE USER powerbi WITH LOGIN PASSWORD ‘*****’ NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT CONNECT ON DATABASE database1 TO powerbi;
GRANT pg_read_all_data TO powerbi;

Es gibt keine weiteren Datenbanken ausser postgres, template0, template1 und database1.

Anmelden klappt auch:

psql -h <hostname> -p 5432 -U powerbi -d database1

Das zeigt mir auch ensprechende Tabellen an:

\dtS+

Und Benutzer wieder löschen:

REVOKE CONNECT ON DATABASE database1 FROM powerbi;
DROP USER powerbi;

Einverstanden?

So, eine Tabelle kann er aber trotzdem anlegen:

CREATE TABLE ak_test (brand VARCHAR(255), model VARCHAR(255),year INT);

Genau das soll verhindert werden.

Und das löschen von Tabellen soll auch verhindert werden.

MfG

In Postgres 15 hatte die Rolle public noch das usage Recht auf dem Schema public. Das wurde mit Postgres 16 geändert. Wenn Deine Datenbank aber von 15 auf 16 migriert wurde, wurde das beibehalten, das gilt nur für neue Datenbanken.

Versuch mal:

revoke usage on schema public from public;

Das Gleiche gilt glaube ich auch für das connect Privileg:

revoke connect on database database1 from public; 

Nach vielem hin und her kann ich den Benutzer nciht mehr löschen:

DROP USER powerbi;

Privilegien für Vorgabeprivilegien für neue Relationen von Rolle postgres in Schema public

MfG

Du hast vermutlich ein ALTER DEFAULT PRIVILEGES gemacht. Wenn ich das richtig im Kopf habe, dann muss man die zurücksetzen bevor man den User löschen kann.

Das CONNECT-Recht auf die Datenbank würde ich nicht entziehen, das ist noch immer standardmäßig bei PUBLIC.

Das CREATE-Recht auf das Schema public zu entziehen ist eine gute Idee.

Um alle Rechte eines Users zu löschen (inkl. DEFAULT PRIVILEGES):

DROP OWNED BY powerbi;  -- in allen Datenbanken
DROP ROLE powerbi;
1 „Gefällt mir“

Moin castorp,
danke das hat geholfen.

Ja, in der Tat, das ist ein Upgrade von Postgre 14 auf Postgre 16.

Neues Spiel, neues Glück:
CREATE ROLE powerbi WITH LOGIN PASSWORD ‘*****’ NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
REVOKE ALL ON DATABASE database1 FROM powerbi;
GRANT CONNECT ON DATABASE database1 TO powerbi;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi;

Anmeldung als powerbi:
psql -h server1 -p 5432 -U powerbi -d database1

Ein \dtS+ kann er machen, aber kein SELECT einer Tabelle.
Obwohl ein GRANT SELECT ON ALL TABLES durch geführt wurde.

Was mich irritiert ist das GRANT SELECT auf das SCHEMA public,
wo wir doch von einer Datenbank sprechen.

Und wenn er eine Tabelle anlegt geht das trotzdem:
CREATE TABLE ak_test (brand VARCHAR(255), model VARCHAR(255),year INT);
Die ist dann im Schema public und powerbi ist der Eigentümer.

Da er mit der Datebank database1 verbunden ist, nehme ich an, das er die Tabelle
auch darein geschrieben hat.

MfG

Ich empfehle, einmal ein bißchen Dokumentation zu lesen.

Jede PostgreSQL-Instanz hat mehrere Datenbanken, in jeder Datenbank gibt es Schemas, in den Schemas sind Tabellen, Funktionen, Views und anderes mehr. Bitte Datenbank nicht mit Schema verwechseln.

Um in einem Schema ein Objekt anzulegen, brauche ich das CREATE-Recht auf das Schema. Um ein Objekt in einem Schema zu verwenden, brauche ich das USAGE-Recht auf das Schema.

Was gibt das:

SELECT nspacl FROM pg_namespace WHERE nspname = 'public';

Das zeigt die Berechtigungen dieses Schemas an.

Ich vermute mal, da gibt es noch ein GRANT CREATE für die Rolle PUBLIC.

Versuche mal ein

revoke create,usage on schema public from public;

Mit den Grundlagen hatte ich mich schon befasst.

Als Benutzer powerbi ergibt das:

           nspacl

{postgres=UC/postgres,=UC/postgres}
(1 Zeile)

MfG

revoke create,usage on schema public from public;

Als Benutzer postgres? Würde das dann nicht allen Benutzern entzogen?

MfG

Ja, natürlich. Jedes Privileg welches der Rolle “public” gegeben wurden bekommen alle Benutzer egal, welche andere Privilegien sie haben. Nachdem Du Dich gewundert hast, dass der powerbi Benutzer trotzdem weiterhin Tabellen anlegen kann, liegt der Verdacht nahe, dass er das über die Rolle public bekommt.

Nachdem das letztendlich ein Sicherheitsrisiko ist, sollte man das auf jeden Fall entfernen - deswegen wurde das ab Postgres 15 ja auch für neue neue Datenbanken entfernt.

… wobei ich das SELECT-Recht belassen würde, aber das ist Geschmackssache.

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.