ロールまたはユーザーが PostgreSQL に存在しない場合は作成する

1. PostgreSQL に存在しない場合、CREATE ROLE メソッドを使用してロールを作成する
2. まとめ

今日は、PostgreSQL にロールが存在しない場合に作成する方法を学習します。

ロールとは、当社が定義したデータベース内のオブジェクトを所有するエンティティであり、さまざまな権限を持たせることができます。 必要なシステムによっては、PostgreSQL データベースに複数のユーザーまたはロールが必要になる場合があります。

PostgreSQL に存在しない場合、CREATE ROLE メソッドを使用してロールを作成する

PostgreSQL は、新しいロールを定義する方法を提供します。

構文：

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

where option can be:

        SUPERUSER | NOSUPERUSER ...

構文の他のオプションは省略されており、ここ で表示できます。

データベースにログインするためのパスワードを持つユーザー MARK を作成します。

コード：

create role MARK LOGIN PASSWORD 'mark123';

ユーザーがまだ存在しない場合、上記を実行すると SUCCESSFUL が返されます。

これにより、ロール MARK にパスワードを設定できます。 LOGIN の代わりに、WITH を使用して、このユーザーのパスワードを定義することもできます。

ユーザーが既に存在する場合は、次のエラーが表示されます。

出力：

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

これを解決するために、以下に定義されているいくつかのメソッドを使用できます。

PL/PgSQL で EXCEPTION を指定して CREATE ROLE を使用する

PL/PgSQL は、単純な SQL ステートメントではなく、FOR ループと制御構造を持つために使用されます。 これを利用して、スクリプト全体が機能しなくなるのではなく、エラーを回避するために複製時に EXCEPTION を発行することができます。

コード：

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

重複するロールが見つかった場合、上記のクエリは簡単なステートメント (以下に示す) を発行し、次のクエリに進みます。

出力：

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

クエリは 168 ミリ秒で正常に返されました。

クエリがどのように機能するかを理解するには、その構造を見てください。 EXCEPTION 句の使用に気付くでしょう。これは、PostgreSQL ドキュメントの ERRORS および MESSAGES の下にあります。

次に、CASE 句を使用して、目的の条件が満たされたときに達成したい結果を指定できます。

CASE ステートメントの構文:

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

私たちの声明では、EXCEPTION が duplicate_object で捕捉されたときに NOTICE を発生させました。 重複が見つかった場合にのみエラーをスローすることを意味します。

その他のエラーや障害はスキップされ、NOTICE は発生しません。 SQLERRM は SQL ERROR MESSAGE の略で、SQLSTATE を返すエラー メッセージを説明する文字列が含まれています。

DOは、トランザクション内でコード ブロック (主にプロシージャ) を開始します。

このソリューションは、RACE 状態を防ぐのに役立ちます。 重複例外のチェックと呼び出しの間に遅延がないため、完全に機能します。

また、これを別の言語または PL/SQL スクリプトから呼び出した場合でも、正しいリターン SQLSTATE が発行されます。 以下に示す解決策とは異なり、これは他の状況を考慮すると完全に機能します。

IF EXISTS を使用して重複違反条件を定義する

重複する違反が見つかった場合は、IF EXISTS 条件を使用して RAISE NOTICE の手順を作成できます。 これは、上記のソリューションの修正である可能性があります。

コード：

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$;

出力：

NOTICE:  SKIP ROLE MAKER!
DO

クエリは 109 ミリ秒で正常に返されました。

PG_USER テーブル内にロールを作成したため、このテーブルから SELECT ステートメントで IF EXISTS を呼び出します。 ただし、カタログの下の PG_ROLES でロールが定義されている場合があり、コードを次のように変更する必要がある場合があります。

コード：

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$;

ただし、ロールが PG_ROLES テーブルで定義されていない場合は、エラーが返されることがあります。 そのため、ロールを定義する場所を確認してから、条件に合わせてコードを変更してください。

このソリューションでは、RACE 条件が問題になる傾向があります。これは、IF EXISTS チェックを呼び出して、クエリが TRUE を返す場合に通知が発生するのを待つためです。 別のトランザクションがすでにここに来て通知を発しているため、この操作が無効になる可能性があります。

RACE 状態を回避するには、上記のソリューションを使用して、ここで使用したクエリに追加します。 上記のソリューションは、コード ブロック内に追加された EXCEPTION 句のためにコストがかかる傾向があります。

EXCEPTION 句を含むこのコード ブロックに出入りするのが難しいため、これが発生する傾向があります。 したがって、簡単な調整を行うことができます。

このコード ブロックを常に入力してチェックするのではなく、RACE CONDITION が存在する場合にのみクエリでこのコード ブロックが使用されるようにすることができます。 それ以外の場合は、スキップして通り抜けてください。 調整は次のようになります。

コード：

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$;

そのため、重複がない場合は ELSE ブロックに入り、RACE CONDITION 自体を回避できます。 クエリが別のテーブルでロールを確認するようにしたい場合は、別のわずかな変更を行うことができます。

コード：

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$;

上記のクエリは、ロールの存在を再確認し、いずれかのテーブルに重複する違反がある場合に通知を発生させます。

DROP IF を CREATE と組み合わせて使用し、違反の重複を回避する

コード：

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

このコードは、ロール作成のもう 1つの簡単なバリエーションです。 グループとして 2つのクエリを実行すると、ロールが存在する場合はそのロールを DROP し、すべての可能性のある出現を排除してから、重複違反なしでロールを CREATE する傾向があります。

ただし、このソリューションは、新しいロールを追加するときに既存のロールを破棄したくない場合に最適です。 CREATE ROLE を呼び出すことで、今度はロールを CREATE したい場合は、上記も同様に使用できます。

GEXEC で CREATE ROLE クエリを使用して重複違反を回避する

重複せずにロールを作成するもう 1つの効率的な方法は、ルート/スーパーユーザーとして PostgreSQL データベース セッションに接続した後、PSQL シェル内で次のクエリを使用することです。

コード：

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

ロールが既に存在する場合、何も返されません。 しかし、そうでない場合は、以下の結果が返されます。

出力：

CREATE ROLE

この出力は、ロールの作成が成功したことを意味します。

PSQL の \GEXEC は、グループで記述されたステートメントを実行することになっています。 したがって、上記のステートメントの最後にGEXECを追加すると、必要に応じて実行されます。

さまざまな例外エラーで CREATE ROLE を使用する

コード：

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

上記のクエリを実行すると、すべての EXCEPTION エラーに対して機能します。 最初の解決策は、このクエリを変更して、DUPLICATION ERROR に対してのみ機能するようにする傾向があります。

CREATE ROLE の DUPLICATE EXCEPTION をカスタム関数でラップする

サーバーデータベースにアクセスするユーザーが使用できる単純な関数を作成できます。

コード：

        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;

このコードは、ロール名を RNAME パラメータとして受け取り、ロールが存在しない場合は EXECUTE ステートメントで CREATE ROLE を呼び出します。 存在する場合は、ELSE ブロックで指定されたメッセージを返します。

出力：

リレーションシップが PG_CATALOG PG_ROLES テーブルに存在する場合、クエリを変更できます。

コード：

        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;

IF EXISTS ではなく、オカレンスを検索するための SQL ステートメントを変更する

古いバージョンの PostgreSQL の場合、ロールがテーブルに表示される回数を確認し、問題が発生する場合は、IF EXISTS ではなく CREATE ROLE ステートメントを発行できます。

コード：

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$

クエリを実行するたびに、出現回数が 1 を超えることはありません。 ロールが見つかった場合、ELSE ステートメントが実行されます。

出力：

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

PG_USER テーブルに役割がない場合は、PG_CATALOG テーブルに移動してクエリを実行できます。

コード：

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$

まとめ

PostgreSQL でロールを作成する際の重複エラーに対処するさまざまな方法を検討しました。 ただし、さまざまな状況ではさまざまなソリューションが機能します。

複数のクライアントがサーバー上のデータベースにアクセスしてクエリを実行する場合、回避したい RACE 条件が存在する可能性があります。 このような状況では、単一のスタンスでクエリを実行するトランザクションまたはコード ブロックを使用する方が適切です。

他の多くのインスタンスでは、異なるバージョンの PostgreSQL で問題が発生する可能性があります。 重複例外エラーを切り分けるさまざまな方法を提供しました。

上記の最適なソリューションから関数を作成することをお勧めします。 クエリを簡素化するのに役立ち、さまざまなクライアントで簡単に実行できます。