So erstellen Sie eine PostgreSQL-Rolle oder einen Benutzer, wenn sie nicht existiert

PostgreSQL unterstützt kein CREATE ROLE IF NOT EXISTS. Um bei Bedarf eine Rolle mit Anmelderecht anzulegen, können Sie einen PL/pgSQL-Block verwenden, der nur duplicate_object abfängt. Alternativ ist eine Prüfung im Systemkatalog möglich, wenn die damit verbundene Einschränkung bei gleichzeitigen Zugriffen für Ihren Anwendungsfall akzeptabel ist. Ein PostgreSQL-„Benutzer“ ist eine Rolle mit dem Attribut LOGIN; Rollen können außerdem Objekte besitzen und Berechtigungen erhalten.

Eine PostgreSQL-Rolle nur anlegen, wenn sie noch nicht vorhanden ist

Mit CREATE ROLE definieren Sie eine Rolle. Fügen Sie LOGIN hinzu, wenn sie sich mit der Datenbank verbinden können muss, und geben Sie ein Passwort an, wenn Ihre Konfiguration die Passwortauthentifizierung verwendet. Ein entsprechender SQL-Befehl lautet zum Beispiel CREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret';. Wählen Sie ein sicheres Geheimnis und speichern Sie Passwörter für Produktionssysteme weder in gemeinsam genutzten Skripten noch im Verlauf der Shell.

Wenn app_user bereits vorhanden ist, meldet PostgreSQL, dass die Rolle schon existiert; der SQLSTATE lautet 42710 (duplicate_object). Ein einfaches CREATE ROLE gibt kein wörtliches Ergebnis SUCCESSFUL zurück. Die offizielle Dokumentation zu CREATE ROLE beschreibt die verfügbaren Attribute. WITH ist eine optionale Syntax vor den Rollenattributen und ersetzt LOGIN nicht.

Für die SQL-Beispiele in diesem Artikel ist ein PostgreSQL-Server erforderlich. Sie werden von der konfigurierten Artikel-Laufzeit nicht ausgeführt. Führen Sie die SQL-Anweisungen in einem PostgreSQL-Client mit ausreichenden Berechtigungen aus.

Das folgende kleine Python-Beispiel gibt einen einfachen SQL-Befehl zur Überprüfung aus. Es stellt keine Verbindung zu PostgreSQL her und führt den Befehl nicht aus. Führen Sie das Beispiel aus:

sql = "CREATE ROLE app_user LOGIN;"
print(sql)

CREATE ROLE app_user LOGIN;

Doppelte-Rollen-Fehler in PL/pgSQL sicher behandeln

Wenn ein direkter Anlegeversuch ohne Abbruch fortgesetzt werden soll, falls eine andere Sitzung die Rolle bereits erstellt hat, fangen Sie ausschließlich die Bedingung für doppelte Objekte ab:

DO $$ BEGIN CREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret'; EXCEPTION WHEN duplicate_object THEN RAISE NOTICE 'Role app_user already exists; leaving it unchanged.'; END; $$;

Existiert die Rolle bereits, gibt der Handler die Meldung aus und der Block wird abgeschlossen. Andernfalls legt der Befehl die Rolle an. duplicate_object ist die genaue Bedingung für den SQLSTATE 42710. Fehler wie fehlende Berechtigungen oder ein fehlerhafter Befehl werden nicht abgefangen und daher an den Aufrufer weitergegeben. So bleiben echte Fehler sichtbar, und ein pauschaler Handler verbirgt keine anderen Probleme.

Der Ausnahme-Handler behandelt einen gleichzeitigen Anlegeversuch, der auf die Duplikatbedingung stößt. Dadurch entfällt das Zeitfenster zwischen einer separaten Prüfung und dem Anlegen. Attribute einer bereits vorhandenen Rolle ändert er jedoch nicht. Benötigt eine vorhandene Rolle ein anderes Passwort oder andere Berechtigungen, ändern Sie diese gezielt mit dem passenden Befehl ALTER ROLE.

Eine vorhandene Rolle prüfen, wenn eine Vorabprüfung sinnvoll ist

Mit einer Prüfung im Systemkatalog können Sie den Anlegebefehl überspringen, wenn die Rolle bereits vorhanden ist. Der Rollenkatalog ist pg_catalog.pg_roles; rolname enthält den Rollennamen. In PL/pgSQL kann die Prüfung so aussehen: DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 'app_user') THEN CREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret'; END IF; END; $$;.

Das ist hilfreich, wenn weitere Schritte davon abhängen, ob die Rolle existiert. Für sich allein ist die Prüfung jedoch nicht sicher bei gleichzeitigen Zugriffen: Zwei Sitzungen können feststellen, dass es keinen passenden Eintrag gibt, und anschließend beide versuchen, die Rolle anzulegen. Eine davon kann weiterhin den SQLSTATE 42710 erhalten. Wenn gleichzeitige Anlegeversuche abgefangen werden müssen, verwenden Sie den oben gezeigten Handler für den Duplikatfall.

Fragen Sie pg_user nicht so ab, als wären Rollen dort gespeichert. Verwenden Sie den dokumentierten Rollenkatalog pg_catalog.pg_roles und vergleichen Sie dessen Spalte rolname mit dem Rollennamen.

Mit psql und \gexec bedingt eine Rolle anlegen

Wenn Sie das Skript in psql ausführen, kann \gexec SQL-Text ausführen, den eine Abfrage zurückgibt. Geben Sie an der psql-Eingabeaufforderung diese Abfrage in einer Zeile ein:

SELECT 'CREATE ROLE app_user LOGIN' WHERE NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 'app_user')

Geben Sie anschließend in der nächsten Zeile den psql-Meta-Befehl \gexec ein.

Fehlt die Rolle, gibt die Abfrage einen CREATE ROLE-Befehl zurück, den psql ausführt. Ist die Rolle vorhanden, liefert die Abfrage keine Zeile und es gibt keinen Befehl auszuführen. \gexec ist ein psql-Meta-Befehl und kein PostgreSQL-SQL. Es funktioniert daher nicht, wenn derselbe Text über einen allgemeinen Datenbanktreiber oder einen anderen SQL-Client gesendet wird. Auch diese Vorabprüfung kann mit einer anderen Sitzung konkurrieren. Wenn gleichzeitige Anlegeversuche möglich sind, verwenden Sie deshalb die gezielte Behandlung des Duplikatfalls. Führen Sie den Befehl mit einem Datenbankkonto aus, das Rollen anlegen darf; ein Root-Konto des Betriebssystems ist nicht erforderlich.

Zerstörerische oder irreführende Vorgehensweisen vermeiden

Verwenden Sie nicht DROP ROLE IF EXISTS app_user gefolgt von CREATE ROLE app_user als idempotentes Muster zum Anlegen einer Rolle. Damit wird die vorhandene Rolle zuerst gelöscht. Das kann Eigentumsverhältnisse, Berechtigungen und abhängige Objekte beeinträchtigen; außerdem bleiben die bisherigen Rollenattribute nicht erhalten.

Fangen Sie WHEN OTHERS nicht bloß ab, um einen Duplikatfehler zu unterdrücken. Dieser Handler kann Berechtigungs-, Syntax- und andere Betriebsfehler verbergen. Eine Prüfung auf vorhandene Rollen, das Zählen passender Katalogeinträge oder eine benutzerdefinierte Funktion beseitigt das Zeitfenster zwischen Prüfung und Anlegen nicht. Halten Sie den Vorgang einfach, sofern Ihre Anwendung keine wiederverwendbare Datenbankfunktion benötigt, und behandeln Sie die Duplikatbedingung gezielt, wenn gleichzeitige Zugriffe relevant sind.

Die Rolle prüfen und einen Ansatz wählen

Mit SELECT rolname, rolcanlogin FROM pg_catalog.pg_roles WHERE rolname = 'app_user'; prüfen Sie, ob die Rolle vorhanden ist. Eine zurückgegebene Zeile bestätigt den Namen und zeigt, ob die Rolle sich anmelden kann. Gibt die Abfrage keine Zeile zurück, fehlt die Rolle in der abgefragten Datenbank. Diese Abfrage dient der Überprüfung; sie garantiert nicht, dass ein späterer Anlegebefehl frei von Konkurrenz mit anderen Sitzungen ist.

Wählen Sie den Ansatz danach, wie Sie den Vorgang ausführen:

• Verwenden Sie den duplicate_object-Handler für einen direkten Anlegeversuch, der gleichzeitige Versuche tolerieren und eine vorhandene Rolle unverändert lassen soll.
• Verwenden Sie eine Vorabprüfung im Katalog, wenn die umgebende Logik abhängig davon, ob die Rolle existiert, verzweigen muss und gleichzeitige Anlegeversuche kein Problem sind oder Duplikatfehler anderweitig behandelt werden.
• Verwenden Sie \gexec für einen knappen bedingten Befehl in psql und berücksichtigen Sie dabei dasselbe Konkurrenzrisiko der Vorabprüfung.

Zum erfolgreichen Anlegen braucht das Datenbankkonto die erforderlichen Berechtigungen. Muss eine vorhandene Rolle geändert werden, verwenden Sie ausdrückliche Anweisungen zum Ändern der Rolle, statt sie zu löschen und neu anzulegen.

Fazit

PostgreSQL bietet keine Form CREATE ROLE IF NOT EXISTS. Für einen sicheren Anlegeversuch, der den Duplikatfall toleriert, fangen Sie in PL/pgSQL nur duplicate_object ab. Eine Vorabprüfung im Katalog oder psql mit \gexec kann praktisch sein, wenn die jeweiligen Anforderungen an Client und gleichzeitige Zugriffe zu Ihrem Anwendungsfall passen. Prüfen Sie die Rolle über pg_catalog.pg_roles und bewahren Sie vorhandene Rollen, sofern Sie sie nicht ausdrücklich löschen oder ändern möchten.