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;
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?
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.
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.
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:
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.
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;
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.
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';
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.