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

PostgreSQLにはCREATE ROLE IF NOT EXISTSがありません。ログイン可能なロールを必要に応じて作成するには、duplicate_objectだけを捕捉するPL/pgSQLブロックを使うか、競合時の制約を許容できる場合はカタログを事前確認します。PostgreSQLの「ユーザー」はLOGIN属性を持つロールです。ロールはオブジェクトの所有者になったり、権限を受け取ったりすることもできます。

PostgreSQLでロールが存在しない場合にのみ作成する

ロールを定義するにはCREATE ROLEを使います。データベースへの接続が必要ならLOGINを付け、パスワード認証を使う構成ではパスワードも指定します。たとえば、SQLコマンドはCREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret';です。安全なパスワードを選び、本番用パスワードを共有スクリプトやシェルの履歴に残さないでください。

app_userがすでに存在する場合、PostgreSQLはSQLSTATE 42710（duplicate_object）でロールがすでに存在することを報告します。通常のCREATE ROLEが文字列SUCCESSFULを返すことはありません。利用可能な属性については、PostgreSQL公式のCREATE ROLEドキュメントを参照してください。ロール属性の前に置くWITHは省略可能な構文であり、LOGINの代わりにはなりません。

ここに示すSQL例にはPostgreSQLサーバーが必要で、記事用に設定されたランタイムでは実行されていません。必要な権限を持つPostgreSQLクライアントで実行するSQLです。

次のPython例は、確認用の基本的なSQLコマンドを表示するだけで、PostgreSQLへの接続やコマンドの実行はしません。以下の例を実行してください。

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

CREATE ROLE app_user LOGIN;

PL/pgSQLで重複ロールのエラーを安全に処理する

別のセッションが先にロールを作成していても処理を続けたい場合は、重複オブジェクトの条件だけを捕捉します。

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

ロールが存在する場合、ハンドラーは通知を出してブロックを完了します。存在しない場合は、コマンドがロールを作成します。duplicate_objectはSQLSTATE 42710に対応する厳密な条件です。権限不足やコマンドの構文誤りなどは捕捉されず、呼び出し元に伝わります。これにより、本当の失敗を見える状態に保ち、無関係な問題を包括的なハンドラーで隠さずに済みます。

この例外ブロックは、同時実行された作成処理が重複条件に遭遇した場合に対処するため、確認してから作成するまでの競合時間を別途設けません。ただし、既存ロールの属性は更新しません。既存ロールのパスワードや権限を変更する必要がある場合は、適切なALTER ROLEコマンドで明示的に変更してください。

事前確認が適切な場合に既存ロールを確認する

カタログを確認すれば、ロールがすでに存在するときに作成コマンドを省略できます。ロールのカタログはpg_catalog.pg_rolesで、ロール名を格納する列はrolnameです。PL/pgSQLでは、次のように確認できます。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; $$;。

ロールの有無に応じて別の処理を行う必要がある場合、この方法が役立ちます。ただし、確認だけでは同時実行時の安全性は保証されません。2つのセッションがどちらも該当する行がないと確認した後で、両方がロールを作成しようとする可能性があります。その場合、一方はSQLSTATE 42710を受け取ることがあります。同時の作成試行を許容する必要がある場合は、前述の重複専用ハンドラーを使ってください。

ロールがpg_userに格納されているかのように問い合わせてはいけません。文書化されているロールカタログpg_catalog.pg_rolesを使い、そのrolname列とロール名を比較してください。

条件付きのロール作成にpsqlの\gexecを使う

スクリプトをpsqlで実行する場合、\gexecを使ってクエリが返すSQLテキストを実行できます。psqlのプロンプトで、次のクエリを1行で入力します。

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

次に、次の行でpsqlのメタコマンド\gexecを入力します。

ロールが存在しない場合、クエリはCREATE ROLEコマンドを返し、psqlがそれを実行します。ロールが存在する場合は行が返されないため、実行するコマンドもありません。\gexecはpsqlのメタコマンドであり、PostgreSQLのSQLではありません。同じテキストを汎用データベースドライバーや別のSQLクライアントに送っても動作しません。事前確認は別のセッションとの競合が起きる可能性もあるため、同時実行があり得る場合は重複専用の例外処理を使ってください。ロールを作成する権限を持つデータベースアカウントで実行します。OSのrootアカウントは必要ありません。

破壊的または誤解を招く方法を避ける

DROP ROLE IF EXISTS app_userの後にCREATE ROLE app_userを実行する方法を、冪等な作成パターンとして使わないでください。既存ロールを先に削除するため、所有権、付与済み権限、依存オブジェクトに影響する可能性があります。また、そのロールに設定済みの属性も保持されません。

重複エラーを抑える目的だけでWHEN OTHERSを捕捉するのも避けてください。このハンドラーは、権限、構文、その他の運用上のエラーを隠すことがあります。ロールの事前確認、該当するカタログ行の件数確認、カスタム関数のいずれも、確認と作成の間に起きる競合をなくすものではありません。再利用可能なデータベース関数がアプリケーションで特に必要でない限り、処理はシンプルに保ち、同時実行が問題になる場合は重複条件だけを限定して処理してください。

ロールを確認して方法を選ぶ

SELECT rolname, rolcanlogin FROM pg_catalog.pg_roles WHERE rolname = 'app_user';でロールの有無を確認できます。行が返れば、その名前のロールが存在し、ログイン可能かどうかも確認できます。行が返らなければ、問い合わせたデータベースにはそのロールがありません。このクエリは確認手順であり、その後の作成コマンドで競合が起きないことを保証するものではありません。

操作方法に応じて選択してください。

• 同時実行された試行にも対処し、既存ロールを変更せずに直接作成を試みる場合は、duplicate_objectハンドラーを使います。
• 周辺の処理でロールの有無に応じて分岐する必要があり、同時作成が問題にならない場合、または重複エラーを別途処理する場合は、カタログの事前確認を使います。
• psqlで簡潔な条件付きコマンドを使う場合は、同じ事前確認の競合を考慮したうえで\gexecを使います。

作成を成功させるには、必要な権限を持つデータベースロールが必要です。既存ロールを変更する必要がある場合は、削除して作り直すのではなく、ロール変更のSQLを明示的に実行してください。

まとめ

PostgreSQLにはCREATE ROLE IF NOT EXISTS形式がありません。存在しない場合に安全に作成を試みるには、PL/pgSQLでduplicate_objectだけを捕捉します。カタログの事前確認やpsqlの\gexecも、利用するクライアントと同時実行に関する制約が用途に合えば便利です。pg_catalog.pg_rolesでロールを確認し、意図的に削除または変更する場合を除いて既存ロールを保持してください。