Erstellen Sie eine Rolle oder einen Benutzer, wenn sie/er in PostgreSQL nicht vorhanden ist

1. Verwenden Sie die Methode CREATE ROLE, um eine Rolle zu erstellen, wenn sie in PostgreSQL nicht vorhanden ist
2. Abschluss

Heute werden wir lernen, wie man eine Rolle in PostgreSQL erstellt, wenn sie nicht existiert.

Eine Rolle ist eine Entität, die Objekte in der von uns definierten Datenbank besitzt und mit unterschiedlichen Rechten versehen werden kann. Je nach erforderlichem System müssen möglicherweise mehrere Benutzer oder Rollen in einer PostgreSQL-Datenbank vorhanden sein.

Verwenden Sie die Methode CREATE ROLE, um eine Rolle zu erstellen, wenn sie in PostgreSQL nicht vorhanden ist

PostgreSQL stellt uns eine Methode zur Verfügung, um neue Rollen zu definieren.

Syntax:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

        SUPERUSER | NOSUPERUSER ...

Andere Optionen in der Syntax wurden weggelassen und können hier eingesehen werden.

Lassen Sie uns fortfahren und einen Benutzer MARK mit einem Passwort erstellen, um sich in unsere Datenbank einzuloggen.

Code:

create role MARK LOGIN PASSWORD 'mark123';

Wenn der Benutzer noch nicht existiert, wird beim Ausführen des obigen Vorgangs ERFOLGREICH zurückgegeben.

Dadurch kann unsere Rolle MARK ein Passwort haben. Statt LOGIN können Sie auch MIT wählen, um ein Passwort für diesen Benutzer festzulegen.

Wenn der Benutzer bereits vorhanden ist, erhalten Sie den folgenden Fehler.

Ausgang:

ERROR:  role "mark" already exists
SQL state: 42710

Um dies zu lösen, können wir einige der unten definierten Methoden verwenden:

Verwenden Sie CREATE ROLE mit einer EXCEPTION in PL/PgSQL

PL/PgSQL wird verwendet, um statt einer einfachen SQL-Anweisung FOR-Schleifen und Kontrollstrukturen zu haben. Wir können dies zu unserem Vorteil nutzen und eine AUSNAHME bei Duplizierung ausgeben, um den Fehler zu vermeiden, anstatt dass das gesamte Skript nicht mehr funktioniert.

Code:

 DO $$
BEGIN
CREATE ROLE MARK;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;

Wenn eine doppelte Rolle gefunden wird, gibt die obige Abfrage eine einfache Anweisung aus (siehe unten) und fährt dann mit der nächsten Abfrage fort.

Ausgang:

NOTICE:  role "mark" already exists, moving to the next statement
DO

Die Abfrage wurde in 168 ms erfolgreich zurückgegeben.

Um zu verstehen, wie die Abfrage funktioniert, sehen Sie sich ihre Struktur an. Sie werden die Verwendung der Klausel EXCEPTION bemerken, die in der PostgreSQL-Dokumentation unter ERRORS und MESSAGES zu finden ist.

Dann können Sie die Klausel CASE verwenden, um ein Ergebnis anzugeben, das Sie erreichen möchten, sobald die gewünschte Bedingung erfüllt ist.

Syntax der CASE-Anweisung:

CASE WHEN condition THEN result
    [WHEN ...]
    [ELSE result]
END

In unserer Erklärung haben wir eine NOTICE ausgelöst, wenn eine EXCEPTION auf einem duplicate_object abgefangen wird. Das bedeutet, dass nur dann ein Fehler ausgegeben wird, wenn Duplikate gefunden werden.

Andere Fehler oder Störungen werden übersprungen und HINWEIS wird nicht ausgegeben. SQLERRM ist die Abkürzung für SQL ERROR MESSAGE und enthält einen String, der die Fehlermeldung mit dem zurückkommenden SQLSTATE beschreibt.

DO initiiert einen Codeblock, meistens eine Prozedur, innerhalb einer Transaktion.

Diese Lösung hilft, RACE-Bedingungen zu vermeiden. Es funktioniert perfekt, da es keine Verzögerung zwischen der Überprüfung und dem Aufruf der doppelten Ausnahme gibt.

Auch wenn Sie dies von einer anderen Sprache oder einem PL/SQL-Skript aus aufrufen, wird immer noch die korrekte Rückgabe SQLSTATE ausgegeben. Im Gegensatz zu den unten angegebenen Lösungen funktioniert dies unter Berücksichtigung anderer Umstände perfekt.

Verwenden Sie WENN VORHANDEN, um die Bedingung für die Duplizierungsverletzung zu definieren

Wir können ein Verfahren mit der Bedingung IF EXISTS erstellen, um NOTICE ERHEBEN, wenn ein doppelter Verstoß gefunden wird. Dies kann eine Modifikation der oben bereitgestellten Lösung sein.

Code:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_user
        WHERE  usename = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        CREATE ROLE MARK LOGIN PASSWORD 'mark123';
    END IF;
END
$do$;

Ausgang:

NOTICE:  SKIP ROLE MAKER!
DO

Die Abfrage wurde in 109 ms erfolgreich zurückgegeben.

Da wir die Rolle in der Tabelle PG_USER erstellt haben, rufen wir das IF EXISTS in der SELECT-Anweisung aus dieser Tabelle auf. In anderen Fällen haben Sie jedoch möglicherweise Rollen in PG_ROLES unter dem Katalog definiert, und Sie müssen Ihren Code möglicherweise wie folgt ändern.

Code:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_roles
        WHERE  rolname = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        CREATE ROLE MARK LOGIN PASSWORD 'mark123';
    END IF;
END
$do$;

Dies kann jedoch einen Fehler zurückgeben, wenn Ihre Rolle nicht in der Tabelle PG_ROLES definiert ist. Stellen Sie also sicher, dass Sie wissen, wo Sie Ihre Rollen definieren, und ändern Sie dann den Code so, wie er für Ihre Bedingungen geeignet ist.

RACE-Bedingungen sind in dieser Lösung in der Regel ein Problem, denn wenn Sie die IF EXISTS-Prüfung aufrufen und darauf warten, dass die Abfrage die Benachrichtigung auslöst, wenn sie TRUE zurückgibt. Eine andere Transaktion wäre bereits hierher gekommen und hätte die Benachrichtigung ausgelöst, die diese Operation möglicherweise ungültig macht.

Um die Bedingung RACE zu verhindern, können wir die oben angegebene Lösung verwenden und an die hier verwendete Abfrage anhängen. Die obige Lösung ist aufgrund der in einem Codeblock hinzugefügten EXCEPTION-Klausel tendenziell teuer.

Aufgrund von Schwierigkeiten beim Eingeben und Verlassen dieses Codeblocks, der die Klausel EXCEPTION enthält, tritt dies häufig auf. Daher können wir jetzt eine einfache Anpassung vornehmen.

Anstatt diesen Codeblock ständig zur Überprüfung einzugeben, können wir sicherstellen, dass unsere Abfragen diesen Codeblock nur dann verwenden, wenn überhaupt eine RACE CONDITION vorliegt; Andernfalls überspringen Sie es und gehen Sie durch. Die Anpassung würde wie folgt ablaufen.

Code:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_user
        WHERE  usename = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        BEGIN
CREATE ROLE mark;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE;
END;
END IF;
END
$do$;

Der ELSE-Block wird also eingetragen, falls keine Duplizierung vorliegt, und wir können die RACE CONDITION als solche vermeiden. Wenn Sie sicherstellen möchten, dass Ihre Abfrage Ihre Rolle in verschiedenen Tabellen überprüft, kann eine weitere geringfügige Änderung vorgenommen werden.

Code:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_user
        WHERE  usename = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        BEGIN
        IF EXISTS (SELECT FROM pg_catalog.pg_roles
        WHERE  rolname = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
        ELSE
        BEGIN
    CREATE ROLE mark;
    EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE;
    END;
        END IF;
    END;
    END IF;
END;
$do$;

Die obige Abfrage überprüft die Existenz der Rolle noch einmal und löst dann die Benachrichtigung aus, wenn in einer der Tabellen ein doppelter Verstoß vorliegt.

Verwenden Sie DROP IF in Kombination mit CREATE, um doppelte Verletzungen zu vermeiden

Code:

DROP ROLE IF EXISTS mark;
CREATE ROLE mark LOGIN PASSWORD 'mark123';

Dieser Code ist eine weitere einfache Variante zum Erstellen einer Rolle. Das Ausführen der beiden Abfragen als Gruppe tendiert dazu, die Rolle zu DROPEN, falls sie existiert, alle möglichen Vorkommen zu eliminieren, und dann die Rolle ohne doppelte Verletzung zu CREATE.

Diese Lösung funktioniert jedoch am besten, wenn Sie beim Hinzufügen einer neuen Rolle keine bereits vorhandene Rolle zerstören möchten. In Fällen, in denen der Aufruf von CREATE ROLE bedeutet, dass Sie die Rolle dieses Mal CREATE möchten, kann das obige ebenfalls verwendet werden.

Verwenden Sie die Abfrage CREATE ROLE mit GEXEC, um doppelte Verletzungen zu vermeiden

Eine weitere effiziente Methode zum Erstellen einer Rolle ohne Duplizierung ist die Verwendung der folgenden Abfrage innerhalb der PSQL-Shell, sobald Sie als Root/Superuser mit der PostgreSQL-Datenbanksitzung verbunden sind.

Code:

 SELECT 'CREATE USER mark' where not exists (select from pg_user where usename = 'mark')\gexec

Wenn die Rolle bereits vorhanden ist, wird nichts zurückgegeben. Wenn dies jedoch nicht der Fall ist, wird das unten stehende Ergebnis zurückgegeben.

Ausgang:

CREATE ROLE

Diese Ausgabe bedeutet, dass Ihre Rollenerstellung erfolgreich war.

\GEXEC in PSQL soll die in Gruppen geschriebenen Anweisungen ausführen. Das Hinzufügen von GEXEC am Ende der obigen Anweisung würde sie also nach Bedarf ausführen.

Verwenden Sie CREATE ROLE mit verschiedenen Ausnahmefehlern

Code:

    DO
        $body$
        BEGIN
            CREATE ROLE mark LOGIN PASSWORD 'mark123';
        EXCEPTION WHEN others THEN
            RAISE NOTICE 'exception others not detected';
        END
        $body$

Das Ausführen der obigen Abfrage funktioniert bei allen EXCEPTION-Fehlern. Die erste Lösung tendiert dazu, diese Abfrage zu modifizieren und sie nur für den DUPLICATION ERROR funktionieren zu lassen.

Schließen Sie die DUPLICATE EXCEPTION für CREATE ROLE in eine benutzerdefinierte Funktion ein

Wir können eine einfache Funktion erstellen, die von Benutzern verwendet werden kann, die auf eine Serverdatenbank zugreifen.

Code:

        CREATE OR REPLACE FUNCTION create_role_ifn(rname NAME) RETURNS TEXT AS
$$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = rname) THEN
        EXECUTE format('CREATE ROLE %I', rname);
        RETURN 'ROLE CREATED SUCCESSFULLY';
    ELSE
        RETURN format('ROLE ''%I'' EXISTING ALREADY', rname);
    END IF;
END;
$$
LANGUAGE plpgsql;

Dieser Code nimmt den Rollennamen als Parameter RNAME und ruft die CREATE ROLE in der EXECUTE-Anweisung auf, wenn die Rolle nicht existiert. Wenn dies der Fall ist, sendet es uns die Nachricht zurück, die im ELSE-Block angegeben ist.

Ausgang:

Wenn die Beziehung in der Tabelle PG_CATALOG PG_ROLES vorhanden ist, können Sie die Abfrage ändern.

Code:

        CREATE OR REPLACE FUNCTION create_role_ifn(rname NAME) RETURNS TEXT AS
$$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_catalog.pg_roles WHERE rolname = rname) THEN
        EXECUTE format('CREATE ROLE %I', rname);
        RETURN 'ROLE CREATED SUCCESSFULLY';
    ELSE
        RETURN format('ROLE ''%I'' EXISTING ALREADY', rname);
    END IF;
END;
$$
LANGUAGE plpgsql;

Ändern Sie die SQL-Anweisung zum Suchen von Vorkommen anstelle von IF EXISTS

Bei älteren Versionen von PostgreSQL können Sie ermitteln, wie oft die Rolle in einer Tabelle vorkommt, und dann die Anweisung CREATE ROLE anstelle von IF EXISTS ausgeben, wenn dies Probleme verursacht.

Code:

do
$body$
declare
    occurences integer;
begin
    SELECT count(*)
        into occurences
    FROM pg_user
    WHERE usename = 'mark';

    IF occurences = 0 THEN
        CREATE ROLE mark LOGIN PASSWORD 'mark123';
    ELSE
        BEGIN
        raise exception 'ALREADY EXISTS!';
    END;
    END IF;
end
$body$

Jedes Mal, wenn Sie die Abfrage ausführen, werden die Vorkommen 1 nicht überschreiten. Und die ELSE-Anweisung wird ausgeführt, wenn die Rolle gefunden wird.

Ausgang:

ERROR:  ALREADY EXISTS!
CONTEXT:  PL/pgSQL function inline_code_block line 14 at RAISE
SQL state: P0001

Für Bedingungen, bei denen wir die Rolle in der Tabelle PG_USER nicht haben, können wir zur Tabelle PG_CATALOG gehen und die Abfrage ausführen.

Code:

do
$body$
declare
    occurrences integer;
begin
    SELECT count(*)
        into occurences
    FROM pg_catalog.pg_roles
    WHERE rolname = 'mark';

    IF occurences = 0 THEN
        CREATE ROLE mark LOGIN PASSWORD 'mark123';
    ELSE
        BEGIN
        raise exception 'ALREADY EXISTS!';
    END;
    END IF;
end
$body$

Abschluss

Wir haben verschiedene Möglichkeiten untersucht, um den doppelten Fehler beim Erstellen einer Rolle in PostgreSQL anzugehen. Allerdings funktionieren unterschiedliche Lösungen für unterschiedliche Umstände.

Wenn mehrere Clients auf eine Datenbank auf einem Server zugreifen und dann eine Abfrage ausführen, kann es zu RACE-Bedingungen kommen, die wir vermeiden möchten. Unter diesen Bedingungen ist es besser, Transaktions- oder Codeblöcke zu verwenden, die Abfragen in einer einzigen Haltung ausführen.

In vielen anderen Fällen können Probleme mit verschiedenen Versionen von PostgreSQL auftreten. Wir haben verschiedene Möglichkeiten bereitgestellt, um den doppelten Ausnahmefehler zu beheben.

Das Erstellen einer Funktion aus der oben ausgewählten Lösung wäre der empfohlene Weg. Es hilft, die Abfragen zu vereinfachen und kann einfach von verschiedenen Clients ausgeführt werden.