Erstellen Sie einen schreibgeschützten Benutzer in PostgreSQL

1. Gewähren Sie einer einzelnen Tabelle Select/Usage
2. Mehrere Tabellen/Ansichten für PostgreSQL Version 9.0+
3. Erstellen Sie einen schreibgeschützten Benutzer
4. Verbinden Sie Postgres mit Redash

Benutzerdatenbanken mit Lesezugriff sind für die Berichterstellung von Vorteil, da sie nur eingeschränkten Lesezugriff auf die Datenbank und ihre Tabellen und Felder haben.

Ein Postgres-Ready-Only-Benutzer bedeutet, dass der Benutzer Informationen aus der Datenbank nur lesen und nicht darauf schreiben darf. Lassen Sie uns also zuerst lernen, wie Sie einen schreibgeschützten Postgres-Benutzer erstellen.

Gewähren Sie einer einzelnen Tabelle Select/Usage

Wenn Sie eine Geschichte mit einem Benutzer in Postgres hatten, haben Sie wahrscheinlich die Befehle GRANT und REVOKE gesehen. Diese haben sich als grundlegende Grundlage der Zugriffskontrolle in Postgres erwiesen.

Obwohl sie verwirrend zu handhaben sind, bietet Postgres verschiedene Zugriffskontrolleinstellungen, damit Sie sorgfältig durch sie navigieren können. Eine weitere Sache, die bei diesen Befehlen zu beachten ist, ist, dass sie nicht auf neue Objekte wie Tabellen angewendet werden, die in eine Datenbank eingeführt werden.

Es gibt diesbezüglich Standardrechte, und diese Rechte werden von Postgres auf eine Rolle angewendet, wenn ein neues Objekt erstellt wird. Sie können diese Standardberechtigungen jedoch mit dem Befehl ALTER DEFAULT PRIVILEGES ändern.

Wenn Sie also einer Datenbank nur CONNECT gewähren, darf sich der Benutzer mit der Datenbank verbinden, hat aber keine anderen Privilegien. Hier müssen Sie die Extrameile gehen und USAGE auf namespaces/schemas und individuell SELECT auf Tabellen und Views gewähren.

Das können wir wie folgt machen:

GRANT CONNECT ON DATABASE databasename TO user; -- databasename = your database name
GRANT USAGE ON SCHEMA public TO user;
GRANT SELECT ON tablename TO user;

Mehrere Tabellen/Ansichten für PostgreSQL Version 9.0+

Angenommen, Sie haben die neuesten Versionen von PostgreSQL heruntergeladen. In diesem Fall ist es Ihnen leicht möglich, Berechtigungen für Objekte im Schema wie Tabellen/Ansichten usw. zu erteilen, indem Sie nur einen einzigen Befehl verwenden, anstatt sie einzeln wie folgt einzugeben:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;

Diese Zeile wirkt sich nur auf bereits erstellte Tabellen aus. PostgreSQL gibt Ihnen auch die Möglichkeit, neuen Objekten in Zukunft automatisch Standardrollen wie folgt zuzuweisen:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO user;

Es ist wichtig zu beachten, dass der Benutzer, der diesen Befehl an erster Stelle ausgegeben hat, die einzige Person ist, die diese Standardrollen den Objekten und Tabellen zuweisen kann.

Wir können es jedoch für jede Rolle einrichten, bei der der ausstellende Benutzer das Mitglied ist. Gleichzeitig übernehmen Sie beim Erstellen des neuen Objekts nicht die Standardberechtigungen aller Rollen, in denen Sie Mitglied sind.

Sie können einen Ansatz wählen, bei dem die Datenbank eine Eigentümerrolle hat, sodass die Schemaänderungen für diese spezifische Eigentümerrolle durchgeführt werden, sodass Sie dieser Eigentümerrolle Standardberechtigungen zuweisen.

Mehrere Tabellen/Ansichten für PostgreSQL-Versionen vor 9.0

Während die Funktionen in den früheren Versionen von PostgreSQL nicht ohne Weiteres verfügbar sind, ist es dennoch möglich, Postgres-Benutzer mit Nur-Lesezugriff zu erstellen.

Bei Längenänderungen in mehreren Tabellen wird empfohlen, einen automatischen Prozess zu verwenden, um das erforderliche GRANT SELECT für jede Ansicht/Tabelle zu generieren, um Fehler zu vermeiden.

Dies geschieht auf folgende Weise:

SELECT 'GRANT SELECT ON ' || relname || 'TO user; '
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r' ,'v' , 'S');

Diese Befehlszeile sollte, wenn sie ausgeführt wird, die relevanten GRANT-Befehle an GRANT SELECT für alle Sequenzen, Tabellen und Ansichten öffentlich und zum Kopieren und Einfügen ausgeben. Wir müssen jedoch beachten, dass wir es nur auf die bereits erstellten Tabellen anwenden.

Erstellen Sie einen schreibgeschützten Benutzer

Führen Sie den folgenden Code aus, um einen schreibgeschützten Benutzer zu erstellen:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'pass321.'
SUPERUSER INHERIT
NOCREATEDB NOCREATEROLE NOREPLICATION
VALID UNTIL
'infinity';

Sie können damit fortfahren, dem soeben erstellten schreibgeschützten Postgres-Benutzer die Berechtigung zu erteilen:

GRANT CONNECTION ON DATABASE databasename TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

Diese Befehle funktionieren jedoch nur mit Tabellen, die bereits in der Datenbank erstellt wurden. So weisen Sie allen zukünftig neu erstellten Tabellen Berechtigungen zu:

ALTER DEFAULT PRIVILEGES IN SCHEMA
public GRANT SELECT ON TABLES TO Read_Only_user;

Es ist wichtig zu beachten, dass Anweisungen ab GRANT USAGE, die die Berechtigung für Tabellen erteilen, in der gewünschten Datenbank ausgeführt werden müssen. Andernfalls funktionieren die Berechtigungen nicht.

Geben Sie dazu \connect databasename; um die Datenbank zu verbinden.

Verbinden Sie Postgres mit Redash

Ohne die GRANT/USAGE-Befehle funktionieren Berechtigungen nicht. Hier ermöglicht USAGE den Zugriff auf Objekte, die in einem bestimmten Schema enthalten sind, solange die Berechtigungsanforderungen des Objekts erfüllt sind. Erstellen Sie zunächst eine schreibgeschützte Rolle:

CREATE ROLE myapp_readonly; 
GRANT CONNECT ON DATABASE database default TO myapp_readonly;
GRANT USAGE ON SCHEMA myapp TO myapp_readonly;

Fahren Sie fort, um dann die Berechtigung für die neu erstellte Rolle zu erteilen. Erstellen Sie abschließend einen Benutzer mit Redash und weisen Sie ihm die Nur-Lesen-Rolle zu, sobald Sie mit dem neuen Nur-Lesen-Benutzer eine Verbindung zur Postgres-Datenbank hergestellt haben.

Das Erstellen eines schreibgeschützten Postgres-Benutzers erweist sich beim Umgang mit der Datenbank als großer Vorteil. Abhängig von Ihren installierten Versionen kann die Schwierigkeit der Aufgabe jedoch variieren.

Dieser Artikel behandelt alle Grundlagen für verschiedene Versionen von Postgres, um sicherzustellen, dass Sie beim Erstellen eines schreibgeschützten Postgres-Benutzers gut sind. Dadurch können Sie nicht nur einen Benutzer mit Zugriffsrechten zum Lesen der Daten erstellen, sondern den Benutzer auch daran hindern, Änderungen vorzunehmen.