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

PostgreSQL-Benutzer, Rollen und Berechtigungen bestimmen, wer sich verbinden, was sie lesen und was sie ändern können. Wenn diese Berechtigungen durch Schätzung wachsen, wird Ihre Datenbank schwer zu prüfen und leicht übermäßig exponiert.

PostgreSQL verwendet ein Konzept, die Rolle, sowohl für Anmeldebenutzer als auch für Gruppen. Eine Rolle mit LOGIN kann sich verbinden. Eine Rolle ohne LOGIN wird oft als Gruppe verwendet, die gemeinsame Berechtigungen trägt.

Rollen erstellen und ändern

Erstellen Sie eine Anwendungsanmelderolle mit einem Passwort:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

Erstellen Sie eine Gruppenrolle für gemeinsamen Lesezugriff:

CREATE ROLE reporting_readonly NOLOGIN;

Häufige Rollenattribute umfassen:

Attribut	Was es erlaubt
LOGIN	Verbindung zum Datenbankserver herstellen.
CREATEDB	Datenbanken erstellen.
CREATEROLE	Viele Rollen erstellen, ändern und löschen, mit wichtigen Einschränkungen für Superuser-Rollen.
REPLICATION	Replikationsverbindungen verwenden.
SUPERUSER	Normale Berechtigungsprüfungen umgehen. Nur für streng kontrollierte Admin-Rollen verwenden.
INHERIT	Berechtigungen von Rollen nutzen, deren Mitglied es ist. Dies ist die Standardeinstellung.

Ändern Sie ein Passwort oder deaktivieren Sie die Anmeldung mit ALTER ROLE:

ALTER ROLE app_user WITH PASSWORD 'new_strong_password';
ALTER ROLE old_employee NOLOGIN;

Bevor Sie eine Rolle löschen, kümmern Sie sich um alles, was ihr gehört, und um alle Berechtigungen, die ihr gewährt wurden:

REASSIGN OWNED BY old_employee TO postgres;
DROP OWNED BY old_employee;
DROP ROLE old_employee;

REASSIGN OWNED überträgt besessene Objekte. DROP OWNED entfernt der Rolle gewährte Berechtigungen und kann Objekte löschen, die ihr noch in der aktuellen Datenbank gehören. Überprüfen Sie dies daher sorgfältig, bevor Sie es ausführen.

Gruppenrollen für saubereren Zugriff verwenden

Gewähren Sie Berechtigungen an Gruppenrollen und fügen Sie dann Personen oder Anwendungsrollen zu diesen Gruppen hinzu.

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTION ermöglicht es dem Mitglied, diese Rollenmitgliedschaft für andere zu gewähren und zu widerrufen. Es macht das Mitglied nicht automatisch zum Superuser oder gibt ihm uneingeschränkte Macht über die Datenbank.

GRANT reporting_readonly TO team_lead WITH ADMIN OPTION;

Dieses Muster hält den Zugriff leichter prüfbar. Anstatt 50 Benutzer auf Tabellenberechtigungen zu überprüfen, können Sie die Gruppenrolle überprüfen.

Objektberechtigungen gewähren

Verwenden Sie GRANT, um bestimmte Aktionen auf bestimmten Objekten zu erlauben.

GRANT SELECT ON TABLE production.orders TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.orders TO app_user;

Häufige Tabellenberechtigungen sind SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES und TRIGGER.

Schema-Berechtigungen sind getrennt von Tabellenberechtigungen. Eine Rolle benötigt oft USAGE auf dem Schema und Berechtigungen auf den Tabellen darin:

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

Für Einfügungen, die Sequenzen verwenden, gewähren Sie auch Sequenzzugriff:

GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user;

Für Funktionen gewähren Sie die Ausführung explizit:

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

Berechtigungen widerrufen

REVOKE entfernt eine direkte Gewährung.

REVOKE INSERT ON TABLE production.orders FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM reporting_readonly;

Wenn ein Benutzer nach einem Widerruf immer noch Zugriff hat, überprüfen Sie die Gruppenmitgliedschaft. Der Benutzer kann dieselbe Berechtigung über eine andere Rolle erben.

Berechtigungen für zukünftige Tabellen festlegen

GRANT ON ALL TABLES betrifft nur vorhandene Tabellen. Für später erstellte Tabellen verwenden Sie ALTER DEFAULT PRIVILEGES von der Rolle, die diese Tabellen erstellen wird.

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

Dies ist einer der häufigsten fehlenden Schritte in PostgreSQL-Berechtigungskonfigurationen. Ein Benutzer kann die heutigen Tabellen lesen, erhält aber morgen Zugriff auf die Tabelle verweigert, weil Standardberechtigungen nie konfiguriert wurden.

Aktuellen Zugriff prüfen

In psql sind dies schnelle Überprüfungen:

\du
\du+ app_user
\dp app.orders
\dn+

Katalogsichten sind besser für wiederholbare Berichte:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

Für Tabellenberechtigungen:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

Sicherheitsgewohnheiten, die sich auszahlen

Geben Sie Anwendungen dedizierte Rollen, anstatt ein menschliches DBA-Konto zu verwenden. Gewähren Sie den kleinsten Satz von Berechtigungen, den die Anwendung benötigt. Vermeiden Sie SUPERUSER und breite ALL PRIVILEGES-Gewährungen für die tägliche Arbeit.

Verwenden Sie Gruppenrollen für gemeinsamen Zugriff und überprüfen Sie die Mitgliedschaften regelmäßig. Wenn jemand das Team wechselt, ist das Entfernen einer Gruppenmitgliedschaft sicherer, als nach tabellenebenen Berechtigungen zu suchen.

Der praktische nächste Schritt besteht darin, ein Produktionsschema auszuwählen und seine Rollen, Mitgliedschaften, Tabellenberechtigungen und Standardberechtigungen aufzulisten. Beheben Sie die Lücken dort, bevor Sie dasselbe Muster auf den Rest Ihrer Datenbanken ausweiten.