DevOps Wissensdatenbank
DevOps Wissensdatenbank

Wesentliche Befehle zur Verwaltung von Benutzern, Rollen und Berechtigungen in PostgreSQL

Meistern Sie die wesentlichen SQL-Befehle für eine robuste PostgreSQL-Sicherheit und Benutzerverwaltung. Dieser Leitfaden bietet praktische Schritte zum Erstellen, Ändern und Löschen von Rollen, zum Festlegen komplexer Attribute (wie LOGIN und CREATEDB) und zur Verwaltung von Gruppenmitgliedschaften. Erfahren Sie, wie Sie den Zugriff mithilfe der mächtigen `GRANT`- und `REVOKE`-Befehle präzise steuern können, indem Sie Berechtigungen auf Objektebene für Tabellen, Schemas und Funktionen definieren. Implementieren Sie das Prinzip der geringsten Rechte mit detaillierten Beispielen und entdecken Sie wichtige psql-Befehle zur Überprüfung der aktuellen Sicherheitseinstellungen.

373 Aufrufe

Wesentliche Befehle zur Verwaltung von Benutzern, Rollen und Berechtigungen in PostgreSQL

Datenbanksicherheit basiert auf einer robusten Zugriffskontrolle. In PostgreSQL wird diese Kontrolle primär durch Rollen und die zugehörigen Berechtigungen (oder Privilegien) verwaltet. Das Verständnis, wie diese Sicherheitsentitäten erstellt, geändert und verwaltet werden, ist grundlegend für jeden Datenbankadministrator oder Entwickler.

Dieser umfassende Leitfaden erläutert die wesentlichen SQL-Befehle, die für die sichere Verwaltung von Benutzern, die Definition von Zugriffsrichtlinien und die Implementierung des Prinzips der geringsten Privilegien in Ihrer PostgreSQL-Umgebung erforderlich sind. Wir behandeln die Rollenerstellung, die Attributfestlegung, die Gruppenmitgliedschaft und die detaillierte Objektberechtigungsverwaltung mittels GRANT und REVOKE.

Verständnis von PostgreSQL-Rollen

Im Gegensatz zu einigen anderen Datenbanksystemen, die strikt zwischen Benutzern und Gruppen unterscheiden, verwendet PostgreSQL ein einheitliches Konzept: die Rolle. Eine Rolle kann Folgendes darstellen:

  1. Einen Datenbankbenutzer (eine Entität, die sich anmelden kann und typischerweise das Attribut LOGIN besitzt).
  2. Eine Benutzergruppe (eine Entität, die ausschließlich zur Gruppierung von Privilegien verwendet wird und typischerweise das Attribut LOGIN nicht besitzt).

Effektive Sicherheit beginnt mit der Definition von Rollen, die die Verantwortlichkeiten der Personen oder Anwendungen, die auf die Datenbank zugreifen, genau widerspiegeln.

1. Rollenverwaltung: Erstellung, Änderung und Löschung

Die grundlegenden Befehle zur Verwaltung von Datenbank-Prinzipalen drehen sich um CREATE ROLE, ALTER ROLE und DROP ROLE.

Neue Rollen erstellen

Beim Erstellen einer Rolle müssen Sie deren Attribute festlegen, insbesondere ob sie sich anmelden kann (LOGIN) und ihre Authentifizierungsdaten (PASSWORD).

Einen grundlegenden Login-Benutzer erstellen

So erstellen Sie eine Standard-Benutzerrolle, die ein Passwort für die Verbindung erfordert:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

Eine Rolle mit spezifischen Attributen erstellen

Rollen können verschiedene Attribute haben, die ihre Fähigkeiten und Systemberechtigungen definieren:

Attribut Beschreibung
LOGIN Ermöglicht der Rolle, sich mit der Datenbank zu verbinden.
SUPERUSER Gewährt alle Datenbankberechtigungen (sparsam verwenden).
CREATEROLE Ermöglicht der Rolle, andere Rollen zu erstellen, zu ändern und zu löschen.
CREATEDB Ermöglicht der Rolle, neue Datenbanken zu erstellen.
REPLICATION Ermöglicht der Rolle, Streaming-Replikation zu initiieren.
INHERIT / NOINHERIT Steuert, ob die Rolle automatisch Berechtigungen von Rollen erbt, deren Mitglied sie ist (Standard ist INHERIT).

Beispiel: Erstellen einer Administrator-Rolle (Nicht-Superuser)

CREATE ROLE db_admin WITH
    LOGIN
    PASSWORD 'admin_secret'
    CREATEROLE
    CREATEDB
    VALID UNTIL '2025-01-01'; -- Optionales Ablaufdatum

Bestehende Rollen ändern

Verwenden Sie ALTER ROLE, um Attribute zu ändern oder das Passwort für eine bestehende Rolle zu aktualisieren.

-- Ändern des Passworts für einen bestehenden Benutzer
ALTER ROLE app_user WITH PASSWORD 'new_strong_password';

-- Login-Fähigkeit entziehen (einen Benutzer in eine Gruppe umwandeln)
ALTER ROLE db_admin NOLOGIN;

-- Einen Benutzer sperren
ALTER ROLE old_employee NOLOGIN;

Rollen löschen

Bevor Sie eine Rolle löschen, stellen Sie sicher, dass die Rolle keine Datenbankobjekte (Tabellen, Schemata usw.) besitzt. Falls doch, müssen Sie den Besitz zuerst mit REASSIGN OWNED übertragen.

-- Alle Objekte, die der Rolle gehören, löschen und sie 'postgres' neu zuweisen
REASSIGN OWNED BY old_employee TO postgres;

-- Anschließend die Rolle löschen
DROP ROLE old_employee;

Warnung: Das Löschen einer Rolle ist unumkehrbar. Seien Sie vorsichtig, insbesondere bei Rollen, die viele Objekte besitzen.

2. Verwaltung der Gruppenmitgliedschaft

Rollen fungieren oft als Gruppen, um die Berechtigungsverwaltung zu vereinfachen. Anstatt 50 einzelnen Benutzern Berechtigungen zu erteilen, erteilen Sie diese einer Gruppenrolle und machen die Benutzer dann zu Mitgliedern dieser Gruppe.

Eine Gruppenrolle erstellen

Gruppen werden typischerweise ohne das LOGIN-Attribut erstellt.

CREATE ROLE data_analysts NOLOGIN;

Gruppenmitgliedschaft erteilen und entziehen

Verwenden Sie den Befehl GRANT, um Mitglieder zu einer Gruppenrolle hinzuzufügen, und REVOKE, um sie zu entfernen.

-- Benutzer 'alice' und 'bob' zur Gruppe 'data_analysts' hinzufügen
GRANT data_analysts TO alice, bob;

-- 'bob' aus der Gruppe 'data_analysts' entfernen
REVOKE data_analysts FROM bob;

Die WITH ADMIN OPTION

Wenn Sie WITH ADMIN OPTION beim Erteilen der Mitgliedschaft angeben, kann die empfangende Rolle anschließend anderen die Mitgliedschaft in dieser Gruppe erteilen und auch die Gruppenrolle DROPpen.

GRANT data_analysts TO supervisor WITH ADMIN OPTION;

3. Verwaltung von Objektberechtigungen (Privilegien)

Berechtigungen definieren, welche Aktionen eine Rolle auf welchen Datenbankobjekten (Tabellen, Views, Funktionen, Schemata usw.) ausführen kann. Dies ist der Kern der Datenbanksicherheit.

Die GRANT-Befehlssyntax

GRANT privilege_list ON object_type object_name TO role_name [WITH GRANT OPTION];

Gängige Privilegien und Beispiele

Tabellenberechtigungen

Privileg Erlaubte Aktion
SELECT Daten aus der Tabelle lesen.
INSERT Neue Zeilen hinzufügen.
UPDATE Bestehende Zeilen ändern.
DELETE Bestehende Zeilen entfernen.
TRUNCATE Die Tabelle vollständig leeren.
REFERENCES Fremdschlüssel-Constraints erstellen.

Beispiel: Nur-Lesezugriff auf eine bestimmte Tabelle gewähren.

GRANT SELECT ON TABLE production.orders TO data_analysts;

-- Alle DML-Operationen auf einer Tabelle gewähren
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE staging.temp_data TO app_user;

Datenbank- und Schema-Berechtigungen

Datenbank- und Schema-Berechtigungen sind entscheidend für die Kontrolle der Umgebungsstruktur.

Objekt Schlüsselprivileg Zweck
DATABASE CONNECT Erlaubt die Verbindung zur Datenbank.
DATABASE CREATE Erlaubt die Erstellung neuer Schemata, Tablespaces usw.
SCHEMA USAGE Erlaubt den Zugriff auf Objekte innerhalb des Schemas.
SCHEMA CREATE Erlaubt die Erstellung neuer Objekte innerhalb des Schemas.

Beispiel: Gewähren von Schema-Zugriff

Wenn ein Benutzer auf Tabellen innerhalb des app_schema zugreifen muss, muss er das USAGE-Recht auf diesem Schema haben.

GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_user;

Sequenz- und Funktionsberechtigungen

Die Nutzung von Sequenzen (für automatisch inkrementierende IDs) und die Ausführung von Funktionen erfordern spezifische Berechtigungen.

-- Dem Benutzer erlauben, die Sequenz voranzutreiben (notwendig für INSERTs)
GRANT USAGE, SELECT ON SEQUENCE app_schema.user_id_seq TO app_user;

-- Ausführung einer bestimmten gespeicherten Prozedur oder Funktion erlauben
GRANT EXECUTE ON FUNCTION audit_log_insert(text) TO app_user;

Berechtigungen entziehen

Verwenden Sie den Befehl REVOKE, um zuvor erteilte spezifische Berechtigungen zu entfernen. Die Syntax spiegelt GRANT wider.

-- Die Fähigkeit entziehen, neue Datensätze in die Tabelle 'orders' einzufügen
REVOKE INSERT ON TABLE production.orders FROM app_user;

-- Alle zuvor erteilten Berechtigungen entziehen (Hinweis: entzieht nicht den Besitz)
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM data_analysts;

Hinweis zur Entziehung: Wurde ein Privileg einer Rolle erteilt und diese Rolle ist Mitglied einer anderen Gruppe, so wirkt sich die Entziehung des Privilegs nur auf die direkt erteilte Berechtigung aus. Wenn die Rolle das Privileg weiterhin über die Gruppenmitgliedschaft erbt, behält sie den Zugriff.

Berechtigungen für zukünftige Objekte anwenden

Die Verwaltung von Berechtigungen für Objekte, die noch nicht existieren, erfordert die Verwendung von ALTER DEFAULT PRIVILEGES. Dies ist unerlässlich für Schemata, in denen Anwendungen häufig neue Tabellen erstellen.

Beispiel: Sicherstellen, dass ein Benutzer SELECT auf allen zukünftigen Tabellen ausführen kann, die von der Rolle app_owner innerhalb des app_schema erstellt wurden:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO app_user;

4. Aktuelle Berechtigungen anzeigen

Zur Überprüfung der Sicherheitseinstellungen bietet PostgreSQL verschiedene Tools und Katalog-Views.

Befehl/View Beschreibung
\du (in psql) Listet alle Rollen und ihre Attribute auf.
\du+ role_name (in psql) Zeigt detaillierte Rollenattribute und Mitgliedschaft an.
\dp table_name (in psql) Listet die auf einer Tabelle erteilten Berechtigungen (Privilegien) auf.
pg_roles Systemkatalog-View mit Rollendefinitionen.
information_schema.table_privileges View, der erteilte Berechtigungen auf Tabellen anzeigt.

Beispiel: Überprüfen von Privilegien auf einer Tabelle via psql

=> \dp production.orders

Best Practices für die Benutzerverwaltung im Bereich Sicherheit

  1. Prinzip der geringsten Privilegien (PoLP): Erteilen Sie nur das minimale Set an Berechtigungen, das eine Rolle zur Ausführung ihrer Funktion benötigt. Vermeiden Sie die Vergabe von ALL PRIVILEGES oder SUPERUSER, es sei denn, dies ist absolut notwendig.
  2. Separate Anwendungsrollen: Verwenden Sie dedizierte Rollen für Anwendungen (z. B. api_service_role), getrennt von Rollen für menschliche DBAs. Anwendungen sollten typischerweise nur DML-Berechtigungen (SELECT, INSERT, UPDATE, DELETE) und keine DDL-Berechtigungen (CREATE, DROP) haben.
  3. Umfassende Nutzung von Gruppen: Erstellen Sie Rollen ohne Login-Funktion (Gruppen), um Berechtigungssätze zu verwalten (z. B. read_only_group, etl_writer_group). Weisen Sie diesen Gruppen individuelle Benutzer zu, anstatt Berechtigungen einzeln zu vergeben.
  4. Vermeiden Sie die Verwendung von Standardrollen: Verwenden Sie niemals die Superuser-Rolle postgres für allgemeine Anwendungs- oder Entwicklungsaufgaben.
  5. Sichere Authentifizierung: Verwenden Sie immer starke Passwörter und nutzen Sie, wann immer möglich, die Client-Zertifikatsauthentifizierung oder zentrale Identitätsmanagementlösungen anstelle der passwortbasierten Authentifizierung.

Fazit

Das effektive Management von Rollen und Berechtigungen ist das Fundament der PostgreSQL-Sicherheit. Durch die Beherrschung von CREATE ROLE, ALTER ROLE, GRANT und REVOKE können Datenbankadministratoren eine granulare Kontrolle implementieren, um sicherzustellen, dass jeder Benutzer oder jede Anwendung, die auf die Datenbank zugreift, genau den erforderlichen Zugriff und nicht mehr erhält. Die konsequente Anwendung des Prinzips der geringsten Privilegien und die Nutzung von Gruppenrollen vereinfachen die langfristige Wartung und stärken die Datenbanksicherheit gegenüber unautorisiertem Zugriff.