PostgreSQL ユーザー一覧を取得するSQLコマンド解説

By /

PostgreSQL徹底解説：システムカタログ pg_roles とメタコマンド \du を活用したユーザー一覧取得方法

はじめに：PostgreSQLのユーザー管理とその重要性

リレーショナルデータベース管理システム（RDBMS）において、ユーザー管理はセキュリティと運用の根幹をなす重要な要素です。特に大規模なシステムや複数の開発者、アプリケーションが利用するデータベースでは、誰がどのデータベースにアクセスでき、どのような操作（テーブルの読み取り、データの更新、新しいテーブルの作成など）を許可されているのかを厳密に管理する必要があります。

PostgreSQLも例外ではなく、堅牢な認証・認可システムを備えています。このシステムの中核となるのが「ロール（Role）」という概念です。ロールは、データベースに接続するためのユーザーアカウントとして機能するだけでなく、特定の権限や属性を持つグループとしても機能します。

データベースの管理やセキュリティ監査を行う上で、「現在、システムにはどのようなロール（ユーザー）が存在しているのか？」という疑問に答えることは非常に重要です。不要になったユーザーアカウントが放置されていないか、本来アクセス権を持たないはずのアカウントが存在しないか、あるいは特定の権限（例えばスーパーユーザー権限）を持つアカウントが適切に管理されているかなどを確認するためには、まずユーザー一覧を正確に把握する必要があります。

この記事では、PostgreSQLでユーザー一覧を取得するための主な方法である、psqlコマンドラインツールで利用できるメタコマンドと、SQLを使ってシステムカタログを直接参照する方法について、それぞれの詳細な使い方、出力内容、利点・欠点、そしてセキュリティ上の考慮事項を含めて徹底的に解説します。特に、SQLによるシステムカタログ pg_roles の参照については、各列が持つ意味や、それらを組み合わせた応用的なクエリについても深く掘り下げて説明し、約5000語というボリュームで読者の皆様がPostgreSQLのユーザー管理をより深く理解し、実践に役立てられることを目指します。

PostgreSQLの認証・認可の基本：ロールとは何か？

PostgreSQLにおける「ロール（Role）」は、ユーザーとグループの両方の概念を統合したものです。データベースに接続するためのログイン可能なアカウントを「ユーザー」と呼ぶことがありますが、これは実質的にはログイン属性（LOGIN）を持つロールに過ぎません。ロールは、以下のような目的に使用されます。

  1. 認証（Authentication）: データベースに接続しようとするクライアント（アプリケーションやユーザー）が、その主張するアイデンティティ（ロール名）の正当性を証明するプロセスです。通常はパスワード認証、クライアント証明書認証、GSSAPI認証などが行われます。PostgreSQLは、接続要求を受け取ると、その接続元IPアドレスやデータベース名、要求されたロール名などを基に、pg_hba.confという設定ファイルを参照して認証方法を決定します。認証に成功したクライアントは、そのロールとしてデータベースセッションを確立します。
  2. 認可（Authorization）: 認証されたロールに対して、データベース内のオブジェクト（テーブル、ビュー、シーケンス、データベース、スキーマなど）に対する操作権限（SELECT, INSERT, UPDATE, DELETE, CREATE, USAGEなど）や、特定の属性（スーパーユーザー、データベース作成権限など）を与えるプロセスです。認可は、GRANTコマンドによって行われます。

ロールは階層的な構造を持つことも可能です。あるロールを別のロールの「メンバー」にすることができます。あるロールが別のロールのメンバーである場合、メンバーであるロールは、その所属するロール（親ロールまたはグループ）が持つ権限を継承することができます（デフォルトでは継承しますが、NOINHERIT属性で継承しないよう設定することも可能です）。この機能を利用することで、複数のユーザーに共通の権限を与える際に、それらのユーザーを一つのグループロールのメンバーとすることで、権限管理を効率化できます。

ユーザー一覧を取得するということは、システムに登録されているすべてのロールの情報を取得することと同義です。これらのロールには、ログイン可能なユーザーアカウント、特定の権限をまとめたグループロール、あるいはシステム内部で使用される特殊なロールなどが含まれます。

ユーザー一覧を取得する方法1：psqlメタコマンド \du

PostgreSQLの対話型ターミナルであるpsqlを使用している場合、ユーザー一覧を最も手軽に確認できるのはメタコマンド\duです。メタコマンドは、SQLコマンドとは異なり、psqlクライアントが解釈して特定の操作を実行するための特殊なコマンドです。

\du コマンドの使い方

psqlに接続した状態で、単に次のように入力し、Enterキーを押します。

sql
\du

または、より詳細な情報を表示したい場合は、+オプションを付けます。

sql
\du+

\du コマンドの出力内容

\duコマンドを実行すると、以下のような形式で、システムに存在するロール（ユーザー）の一覧が表示されます。

List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
app_user | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
web_role | Can login | {app_user}

各列は以下の情報を示します。

  • Role name: ロールの名前です。これはデータベースに接続する際に使用されるユーザー名でもあります。
  • Attributes: そのロールが持つ特別な属性（権限）の一覧です。主な属性には以下のようなものがあります。
    • Superuser: そのロールがスーパーユーザーであることを示します。スーパーユーザーは、データベース内のあらゆるオブジェクトに対して、権限チェックなしに操作を実行できます。システム設定の変更など、非常に強力な権限を持ちます。
    • Create role: そのロールが新しいロールを作成したり、既存のロールを変更/削除したりできることを示します。
    • Create DB: そのロールが新しいデータベースを作成したり、既存のデータベースを削除したりできることを示します。
    • Replication: そのロールがレプリケーションのストリーミングを開始できることを示します。レプリケーション設定を行う際に必要となる権限です。
    • Bypass RLS: そのロールが行レベルセキュリティ（Row Level Security, RLS）ポリシーを迂回できることを示します。RLSは特定の行に対するアクセスを制限する機能ですが、この属性を持つロールはRLSポリシーの影響を受けません。
    • Can login: そのロールがデータベースに接続してセッションを確立できることを示します。この属性を持たないロールは、主に他のロールのメンバーとして権限を継承させるための「グループロール」として使用されます。
    • Inherit: そのロールが、自分がメンバーとなっているロール（グループ）から権限を継承するかどうかを示します。デフォルトでは継承（Inherit）します。NOINHERIT属性が設定されている場合は継承しません。\duの出力では、Inheritがデフォルトなので表示されず、NOINHERITの場合のみ表示されます。
    • Connection limit: そのロールが確立できる同時接続数の上限です。無制限の場合は表示されません。
    • Password valid until: パスワードの有効期限です。期限が設定されていない場合は表示されません。
    • Password encrypted: パスワードが暗号化されているか（常に暗号化されますが、古いバージョンとの互換性のために属性として表示されることがあります）。
  • Member of: そのロールがメンバーとして所属している他のロール（グループ）の一覧です。例えば、上記の例のweb_roleはapp_userというロールのメンバーとなっています。これにより、web_roleはapp_userロールに付与された権限を継承することができます（web_roleにNOINHERIT属性が付いていない限り）。

\du+ コマンドの出力内容

\du+コマンドは、\duの出力に加えて、より詳細な情報を表示します。出力内容はPostgreSQLのバージョンによって若干異なりますが、一般的には以下の列が追加されます。

List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+------------+-------------
admin | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | Administrator account
app_user | | {} | Application user account
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | Default superuser account
web_role | Can login | {app_user} | Web application access role

追加される可能性のある主な列は以下の通りです。

  • Description: ロールに設定されたコメントや説明文です。COMMENT ON ROLE role_name IS '...' コマンドで設定できます。これは運用管理上、非常に有用な情報です。
  • Config: そのロールに対して設定されている特定の構成パラメータ（例：search_path, timezoneなど）です。ALTER ROLE role_name SET parameter_name = 'value' コマンドで設定できます。
  • Oid: ロールの内部的なオブジェクト識別子（OID）です。システム内部でロールを一意に識別するために使用されます。

\du+は、\duよりも多くの情報を一目で確認できるため、詳細なユーザー管理情報の把握に役立ちます。

psqlメタコマンド \du の利点と欠点

利点:

  • 手軽さ: psqlに接続していれば、コマンド一発で簡単にユーザー一覧と主要な情報を確認できます。
  • 見やすい整形出力: ロール名、属性、所属グループなどが分かりやすく整形されて表示されます。
  • 主要な情報が集約: ロールの名前、基本的な権限属性、および所属グループといった、ユーザー管理において最も頻繁に必要とされる情報がまとめて表示されます。\du+ではさらにコメントや設定も確認できます。

欠点:

  • psql限定: このコマンドはpsqlクライアント専用であり、他のデータベースクライアントツールや、プログラム（Pythonのpsycopg2など）から直接実行することはできません。プログラムからユーザー一覧を取得したい場合は、次に説明するSQLコマンドを使用する必要があります。
  • カスタマイズ性の限界: 表示される列や行は固定されており、特定の条件でフィルタリングしたり、表示形式を変更したりといった柔軟なカスタマイズはできません。例えば、「最後にログインした日時でソートする」といったことはこのコマンドだけではできません（そもそもpg_rolesには最終ログイン日時の情報はデフォルトでは含まれていませんが）。

psqlからインタラクティブにユーザー情報を確認したい場合には\duまたは\du+が非常に便利です。しかし、より詳細な情報を取得したい場合や、プログラムからユーザー情報を取得して処理したい場合には、SQLコマンドを利用する必要があります。

ユーザー一覧を取得する方法2：システムカタログ pg_roles を直接参照する

PostgreSQLは、データベースの構造やシステムの状態に関する情報を、特別なテーブル群として管理しています。これを「システムカタログ」と呼びます。ユーザー（ロール）に関する情報は、pg_rolesというシステムカタログテーブルに格納されています。

SQLを使用してこのpg_rolesテーブルを直接クエリすることで、\duコマンドよりもはるかに詳細かつ柔軟な方法でユーザー一覧を取得できます。プログラムからの利用や、複雑な条件でのフィルタリング、他のシステムカタログとの結合などが可能になります。

pg_roles システムカタログの概要

pg_rolesテーブルは、PostgreSQLインスタンス内に存在するすべてのロールに関する主要な設定情報と属性を格納しています。このテーブルはスーパーユーザーまたは適切な権限を持つロールのみがアクセスできます。

テーブルの構造（列）は以下のセクションで詳しく説明しますが、基本的には各行が一つのロールに対応し、各列がそのロールの様々な属性や設定値に対応します。

基本的なクエリ：SELECT * FROM pg_roles;

pg_rolesテーブルに格納されているすべての情報を取得する最も基本的なクエリは以下の通りです。

sql
SELECT * FROM pg_roles;

このクエリを実行すると、pg_rolesテーブルのすべての列とすべての行が表示されます。出力はSQLの標準的な表形式となります。psqlで実行した場合、以下のような出力になります（列が多いため、実際には横に非常に長い表になります）。

oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolvaliduntil | rolpassword | rolconfig | rolparentid
-------+---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+---------------+-------------+-----------+-------------
10 | postgres | t | t | t | t | t | t | t | | ******** | {} | 0
16384 | admin | t | t | t | t | t | t | t | | ******** | {} | 0
16385 | app_user | f | t | f | f | f | f | f | | | {} | 0
16386 | web_role | f | t | f | f | t | f | f | | ******** | {} | 16385
...

\duと比較すると、出力形式は整形されていませんが、より多くの生データが利用可能です。パスワード情報（rolpassword）はセキュリティ上の理由から通常マスクされて表示されます。

pg_roles の主要な列の詳細解説

pg_rolesテーブルには多くの列がありますが、ユーザー一覧を取得し、その属性を理解する上で特に重要な列について詳しく解説します。

  • oid (oid):

    • 意味: ロールのオブジェクト識別子です。PostgreSQL内部で各データベースオブジェクトを一意に識別するための数値IDです。システムカタログ内の多くのテーブルで他のオブジェクト（テーブル、データベースなど）を参照する際に使用されます。
    • 重要性: このID自体が直接ユーザー名として使われるわけではありませんが、内部処理でロールを一意に特定するために利用されます。pg_auth_membersなど、他のカタログテーブルと結合する際に結合キーとして使用されることがあります。

  • rolname (name):

    • 意味: ロールの名前です。データベースに接続する際に指定するユーザー名として使用されます。ロール作成時に指定する名前です。
    • 重要性: ユーザー一覧を把握する上で最も基本的な情報です。通常はこの列を見て、どのユーザー（ロール）が存在するかを識別します。

  • rolsuper (boolean):

    • 意味: そのロールがスーパーユーザーであるかどうかを示します。t（真）または f（偽）の値をとります。
    • 重要性: セキュリティ上、最も重要な属性の一つです。スーパーユーザーはPostgreSQLのあらゆる権限を持ち、システム全体に影響を与える操作を実行できます。スーパーユーザーアカウントの数は最小限に抑え、厳重に管理する必要があります。この列を見ることで、誰がスーパーユーザー権限を持っているかを確認できます。

  • rolinherit (boolean):

    • 意味: そのロールが、自分がメンバーとなっているロール（グループ）から権限を継承するかどうかを示します。tまたはfの値をとります。デフォルトはtです。
    • 重要性: ロール間の権限継承の挙動を制御します。tであれば、メンバーになっているグループロールの権限を自動的に取得します。f（NOINHERIT属性）の場合、権限継承は行われず、メンバーシップは単にその関係を示すためだけに使用されるか、SET ROLEコマンドなどによって明示的にそのロールの権限を使用する場合にのみ意味を持ちます。

  • rolcreaterole (boolean):

    • 意味: そのロールが新しいロールを作成したり、既存のロールを変更/削除したりできるかどうかを示します。tまたはfの値をとります。
    • 重要性: ユーザーアカウント自体を管理する権限です。この権限を持つロールは、他のユーザーを自由に追加・変更・削除できるため、強力な権限とみなされます。不要なロールにこの権限が付与されていないかを確認することが重要です。

  • rolcreatedb (boolean):

    • 意味: そのロールが新しいデータベースを作成したり、既存のデータベースを削除したりできるかどうかを示します。tまたはfの値をとります。
    • 重要性: 新しいデータベース環境を構築できる権限です。この権限を持つロールは、独自のデータベースを作成して作業を進めることができますが、同時に不要なデータベースを無秩序に作成したり、既存のデータベースを誤って削除したりするリスクも伴います。

  • rolcanlogin (boolean):

    • 意味: そのロールがデータベースに接続してセッションを確立できるかどうかを示します。tまたはfの値をとります。
    • 重要性: この属性がtであるロールが、いわゆる「ユーザーアカウント」として直接ログインできるロールです。fであるロールは、ログインできず、主に他のログイン可能なロールがメンバーとなる「グループロール」として使用されます。ログイン可能なアカウントのみを抽出したい場合に重要なフィルタリング条件となります。

  • rolreplication (boolean):

    • 意味: そのロールがレプリケーションのストリーミングを開始できるかどうかを示します。tまたはfの値をとります。
    • 重要性: 物理レプリケーションを設定する際に、スタンバイサーバーがプライマリサーバーからWAL（Write-Ahead Log）ストリームを受信するために必要となる権限です。通常、レプリケーション用の特定のロールにのみ付与されます。

  • rolbypassrls (boolean):

    • 意味: そのロールが行レベルセキュリティ（RLS）ポリシーを迂回できるかどうかを示します。tまたはfの値をとります。
    • 重要性: RLSはテーブルの個々の行に対するアクセスを細かく制御するための強力なセキュリティ機能ですが、この属性を持つロールはRLSポリシーの影響を受けません。スーパーユーザーはデフォルトでこの属性を持ちますが、スーパーユーザー以外にも付与することが可能です。RLSによるアクセス制限が期待通りに機能しているかを確認する際に、どのロールがそれを迂回できるかを知ることが重要です。

  • rolvaliduntil (timestamp with time zone):

    • 意味: パスワードの有効期限を示します。このタイムスタンプ以降は、そのロールのパスワードを使用してログインできなくなります。有効期限が設定されていない場合は NULL となります。
    • 重要性: アカウントの棚卸しやセキュリティポリシーの適用において重要です。例えば、一時的に発行したアカウントに有効期限を設定しておけば、その期限が過ぎると自動的にログインできなくなります。有効期限が切れた、または切れそうなアカウントを特定するためにこの列を使用します。

  • rolpassword (text):

    • 意味: ロールのパスワードのハッシュ値または暗号化されたパスワードです。
    • 重要性: セキュリティ上の理由から、通常のユーザーがこの列の生の値を見ることはできません。SELECTクエリの結果では通常********のようにマスクされて表示されます。この列は、パスワード自体を取得するためではなく、パスワードが設定されているかどうかの確認（NULLでないか）や、パスワードの有効期限(rolvaliduntil)に関連して存在します。

  • rolconfig (text[]):

    • 意味: そのロールに対して設定されているセッションのデフォルト構成パラメータの配列です。例えば、{'search_path=myschema,"$user"', 'client_encoding=UTF8'} のようになります。
    • 重要性: ロールごとに特定の実行環境を設定するために使用されます。これにより、個別のユーザーに対して特定のスキーマをデフォルトにしたり、タイムゾーンを設定したりといったカスタマイズが可能です。この列を確認することで、各ロールの特別なセッション設定を把握できます。

  • rolparentid (oid):

    • 意味: ロールが論理的にネストされている場合の親ロールのOIDを示します。PostgreSQL 16で導入された機能です。それ以前のバージョンでは存在しないか、異なる方法でロールの階層が管理されていました。
    • 重要性: ロールの階層構造を理解するのに役立ちます。ただし、ロールのメンバーシップは通常pg_auth_membersカタログで管理されるため、この列はロールの概念的な親子関係を示す場合に使用されることが多いです。

これらの列を組み合わせることで、\duコマンドでは得られない非常に詳細なロール情報を取得できます。

pg_roles を利用したフィルタリングと情報の整形

SELECT * FROM pg_roles; はすべての情報を表示しますが、特定の条件を満たすロールだけを表示したい場合や、表示する列を絞り込みたい場合は、WHERE句や表示列の指定、CASE文などを使用します。

1. ログイン可能なユーザーのみを抽出する:

通常、データベースに接続できるのはrolcanlogin属性がtrueのロールです。ログイン可能なユーザーアカウントの一覧を取得するには、以下のようにフィルタリングします。

sql
SELECT
rolname,
rolsuper,
rolcreaterole,
rolcreatedb,
rolreplication,
rolbypassrls,
rolvaliduntil,
rolconfig
FROM
pg_roles
WHERE
rolcanlogin = true
ORDER BY
rolname;

このクエリでは、ログイン可能なロールの名前と主要な属性、有効期限、設定を表示し、ロール名でソートしています。

2. スーパーユーザーアカウントを抽出する:

システム全体に影響を与えるスーパーユーザー権限を持つアカウントを特定するには、rolsuper属性でフィルタリングします。

sql
SELECT
rolname,
rolvaliduntil,
rolconfig
FROM
pg_roles
WHERE
rolsuper = true
ORDER BY
rolname;

スーパーユーザーアカウントは厳重に管理すべきため、この一覧を定期的に確認することが重要です。

3. パスワードに有効期限が設定されているロールを抽出する:

セキュリティポリシーとしてパスワードに有効期限を設定している場合、その設定状況や期限切れが近いアカウントを確認したいことがあります。

“`sql
— 有効期限が設定されているロール
SELECT
rolname,
rolvaliduntil
FROM
pg_roles
WHERE
rolvaliduntil IS NOT NULL
ORDER BY
rolvaliduntil;

— 有効期限が近い（例: 30日以内）ロール (現在のタイムスタンプより30日後を基準)
SELECT
rolname,
rolvaliduntil
FROM
pg_roles
WHERE
rolvaliduntil IS NOT NULL
AND rolvaliduntil < NOW() + INTERVAL ’30 days’
ORDER BY
rolvaliduntil;

— 有効期限が既に切れているロール
SELECT
rolname,
rolvaliduntil
FROM
pg_roles
WHERE
rolvaliduntil IS NOT NULL
AND rolvaliduntil < NOW()
ORDER BY
rolvaliduntil;
“`

これらのクエリは、パスワード期限切れによるログイン障害を防いだり、期限切れアカウントを洗い出したりするのに役立ちます。

4. 権限属性を分かりやすく表示する:

rolsuper, rolcanloginなどの属性はブール値（t/f）で格納されていますが、これをより人間が理解しやすい形式（例: ‘はい’/’いいえ’や属性名自体）で表示したい場合があります。CASE文や、PostgreSQLの内部関数を利用できます。

sql
SELECT
rolname,
CASE WHEN rolsuper THEN 'スーパーユーザー' ELSE '一般ユーザー' END AS user_type,
CASE WHEN rolcanlogin THEN 'ログイン可能' ELSE 'ログイン不可 (グループ)' END AS login_status,
'Attributes: ' ||
CASE WHEN rolsuper THEN 'Superuser, ' ELSE '' END ||
CASE WHEN rolinherit THEN 'Inherit, ' ELSE '' END || -- デフォルトはInheritなので、NOINHERITの場合はfalse
CASE WHEN rolcreaterole THEN 'Create role, ' ELSE '' END ||
CASE WHEN rolcreatedb THEN 'Create DB, ' ELSE '' END ||
CASE WHEN rolcanlogin THEN 'Can login, ' ELSE '' END ||
CASE WHEN rolreplication THEN 'Replication, ' ELSE '' END ||
CASE WHEN rolbypassrls THEN 'Bypass RLS' ELSE '' END AS attributes, -- 最後の項目にカンマを付けない
rolvaliduntil
FROM
pg_roles
ORDER BY
rolname;
※上記のattributes列の生成は、末尾のカンマ処理が少し複雑になります。正確には、空でない属性だけを選んで結合し、最後のカンマを除去する必要がありますが、理解を容易にするために上記のように簡略化しました。実際の運用では、より複雑な文字列操作が必要になる場合があります。あるいは、単に各属性列を個別に表示する方が明快かもしれません。

より洗練された方法として、システムカタログ関数 pg_catalog.pg_roles_view またはシステムカタログ自体を参照して、属性名を直接表示することも可能です（ただし、\duのような完全な整形はSQLだけでは難しいです）。

\duコマンドの出力形式に近い形で属性を表示するには、pg_rolesカタログのデータとPostgreSQL内部の属性情報を組み合わせる必要があります。これは少し複雑ですが、例としてログイン属性を表示するクエリを示します。

sql
SELECT
rolname,
pg_catalog.array_to_string(ARRAY(
SELECT att.attname
FROM pg_catalog.pg_attribute att JOIN pg_catalog.pg_class cls ON att.attrelid = cls.oid
WHERE cls.relname = 'pg_roles' AND att.attnum > 0 AND NOT att.attisdropped
AND att.attname IN ('rolsuper', 'rolinherit', 'rolcreaterole', 'rolcreatedb', 'rolcanlogin', 'rolreplication', 'rolbypassrls')
AND ( -- 各属性が真である場合に表示
(att.attname = 'rolsuper' AND r.rolsuper) OR
(att.attname = 'rolinherit' AND NOT r.rolinherit) OR -- InheritはデフォルトなのでNOINHERIT(false)を表示することが多い
(att.attname = 'rolcreaterole' AND r.rolcreaterole) OR
(att.attname = 'rolcreatedb' AND r.rolcreatedb) OR
(att.attname = 'rolcanlogin' AND r.rolcanlogin) OR
(att.attname = 'rolreplication' AND r.rolreplication) OR
(att.attname = 'rolbypassrls' AND r.rolbypassrls)
)
ORDER BY att.attnum -- 属性の表示順序を固定
), ', ') AS attributes,
rolvaliduntil
FROM
pg_roles r
ORDER BY
rolname;
注: 上記のクエリはpg_attributeカタログを参照して属性名を動的に取得しようとしていますが、属性値と属性名を正確に対応させて表示するのは非常に複雑です。\duの出力は、PostgreSQLのC言語コード内でこれらの属性を文字列に変換して生成しているため、SQLだけで全く同じ形式を再現するのは現実的ではありません。一般的には、最初のCASE文を使った方法や、各属性列を個別に表示する方がシンプルで実用的です。\duに近い見た目がどうしても必要な場合は、psqlを実行して結果をパースするか、アプリケーション側で取得したブール値を文字列に変換する処理を行うのが良いでしょう。

pg_roles を使用する利点と欠点

利点:

  • 柔軟性: SQLクエリを自由に記述できるため、特定の条件でのフィルタリング、ソート、特定の列のみの表示など、要件に合わせて柔軟に情報を取得できます。
  • 詳細な情報: \duでは省略されるか簡単にしか表示されない情報（例：パスワード有効期限、構成パラメータ、OIDなど）も直接参照できます。
  • プログラムからの利用: プログラミング言語のPostgreSQLドライバー（例: Pythonのpsycopg2, JavaのJDBCなど）から標準的なSQLクエリとして実行できるため、アプリケーションやスクリプトに組み込んでユーザー情報を自動的に取得・処理することが容易です。
  • 他のシステムカタログとの結合: pg_auth_membersなどの他のシステムカタログと結合することで、ロールのメンバーシップ関係など、より複雑な情報を取得できます（後述）。

欠点:

  • SQLの知識が必要: システムカタログの構造やSQLの書き方に関する知識が必要です。
  • 出力の整形: デフォルトの出力は生のデータであり、\duのように人間が見やすいように整形されていません。整形はSQLクエリで行うか、クエリ結果を受け取った側で行う必要があります。
  • アクセス権限: pg_rolesカタログへのアクセスには通常スーパーユーザー権限が必要です（または特定の権限が付与されている必要があります）。一般ユーザーはデフォルトではこのカタログを参照できません。

SQLによるpg_rolesの参照は、より高度なユーザー管理や、自動化されたユーザー情報収集において強力な手法です。

pg_roles と pg_auth_members を組み合わせた応用クエリ：ロールのメンバーシップを確認する

PostgreSQLのロールは、他のロールのメンバーになることができます。これにより、権限をグループ化し、管理を簡素化することが可能です。どのロールがどのグループに所属しているか（またはどのロールがどのロールのメンバーとなっているか）を確認するには、pg_rolesカタログと、ロールのメンバーシップ情報が格納されているもう一つのシステムカタログ、pg_auth_membersを使用します。

pg_auth_members システムカタログの概要

pg_auth_membersテーブルは、ロール間のメンバーシップ関係を記録しています。各行は一つのメンバーシップを示し、どのロール（メンバー）がどのロール（親またはグループ）に所属しているかを記録します。

主要な列は以下の通りです。

  • roleid (oid): 親ロール（グループ）のOID。
  • member (oid): 子ロール（メンバー）のOID。
  • grantor (oid): このメンバーシップを付与したロールのOID。
  • admin_option (boolean): このメンバーシップにWITH ADMIN OPTIONが付与されているかどうか。WITH ADMIN OPTIONが付与されているメンバーは、さらにその親ロールのメンバーシップを他のロールに付与する権限を持ちます。

pg_auth_members自体を直接参照すると、OIDの羅列が表示されます。

sql
SELECT * FROM pg_auth_members;

roleid | member | grantor | admin_option
--------+--------+---------+--------------
16385 | 16386 | 16384 | f
(1 row)
この例では、OID 16386 のロールが、OID 16385 のロールのメンバーであり、このメンバーシップは OID 16384 のロールによって付与され、ADMIN OPTION は付与されていません。

ロール名を使用してメンバーシップ関係を一覧表示するクエリ

pg_auth_membersのOIDを、pg_rolesのoid列と結合することで、ロール名を使ってメンバーシップ関係を分かりやすく表示できます。

以下のクエリは、どのロール（メンバー）がどのロール（グループ）のメンバーになっているか、そして誰がそのメンバーシップを付与したかを表示します。

sql
SELECT
mem.rolname AS member_role, -- メンバー（子）ロールの名前
grp.rolname AS group_role, -- グループ（親）ロールの名前
gra.rolname AS grantor_role, -- メンバーシップを付与したロールの名前
am.admin_option AS with_admin_option -- ADMIN OPTIONが付与されているか
FROM
pg_auth_members am
JOIN
pg_roles mem ON am.member = mem.oid -- memberのOIDをpg_rolesのoidと結合
JOIN
pg_roles grp ON am.roleid = grp.oid -- roleid (グループ)のOIDをpg_rolesのoidと結合
JOIN
pg_roles gra ON am.grantor = gra.oid -- grantorのOIDをpg_rolesのoidと結合
ORDER BY
group_role, member_role;

このクエリを実行すると、以下のような出力が得られます。

member_role | group_role | grantor_role | with_admin_option
-------------+------------+--------------+-------------------
web_role | app_user | admin | f

この出力から、「web_roleはapp_userというグループロールのメンバーであり、このメンバーシップはadminロールによって付与されたものだ」ということが明確に分かります。

このクエリは、\duコマンドのMember of列よりも詳細な情報を提供します。例えば、誰がそのメンバーシップを付与したのか（grantor_role）、そしてそのメンバーシップ自体をさらに他のロールに付与する権限（with_admin_option）が与えられているかどうかも確認できます。

ロールに付与された権限の確認（補足）

pg_rolesで確認できるのは、スーパーユーザーやログイン可否などの「ロール属性」です。特定のデータベースオブジェクト（テーブル、ビューなど）に対するSELECT, INSERT, UPDATEなどの権限（Privileges）は、pg_class、pg_namespace、pg_databaseなどの他のシステムカタログや、information_schemaスキーマにあるビュー群（例: information_schema.role_table_grants, information_schema.table_privilegesなど）を参照する必要があります。

ユーザー一覧取得と権限確認は密接に関連しますが、ここではユーザー一覧（ロールとその基本属性・メンバーシップ）の取得に焦点を当てているため、オブジェクト権限の詳細な確認については別の記事で解説するか、簡単な言及に留めます。しかし、ユーザー管理においては、誰がログインできるか（pg_roles）、誰がどのグループに属するか（pg_auth_members）、そしてそのロールやグループが具体的にどのオブジェクトにどのような権限を持っているか（information_schemaなど）の三つを組み合わせて理解することが非常に重要です。

ユーザー一覧取得とセキュリティ

ユーザー一覧を定期的に取得し、内容を確認することは、データベースのセキュリティを維持する上で不可欠な運用の一部です。以下にその重要性と関連するセキュリティの考慮事項を述べます。

なぜ定期的にユーザー一覧を確認する必要があるのか？

  1. 不要なアカウントの発見と削除: プロジェクトの終了、従業員の退職などにより、不要になったユーザーアカウントがシステムに残っていることがあります。これらのアカウントは、不正アクセスのリスクを高める可能性があるため、定期的に棚卸しを行い、不要なアカウントは削除または無効化する必要があります。
  2. 過剰な権限を持つアカウントの特定: 本来必要のない強力な権限（例: スーパーユーザー、ロール作成権限、DB作成権限など）がユーザーに付与されていないかを確認します。最小権限の原則に従い、各ユーザーにはその職務遂行に必要最低限の権限のみを与えるべきです。
  3. 不審なアカウントの検出: 知らない間に新しいアカウントが作成されていないか、通常とは異なる属性を持つアカウントがないかなどを確認します。これは不正アクセスや内部犯行の兆候である可能性があります。
  4. 権限継承の確認: pg_auth_membersを使用してロールのメンバーシップ関係を確認することで、意図しない権限継承が発生していないかをチェックできます。グループロールの構造が複雑になると、予期しない権限が付与されてしまうリスクがあります。
  5. パスワード有効期限の管理: rolvaliduntilを確認することで、パスワード有効期限ポリシーが正しく適用されているか、期限切れ間近または既に期限切れのアカウントがないかを確認できます。

セキュリティベストプラクティスとの関連

  • 最小権限の原則 (Principle of Least Privilege): 各ユーザーには、その職務を遂行するために必要最低限の権限のみを付与するというセキュリティ原則です。ユーザー一覧と権限属性を確認することで、この原則が守られているかを監査できます。スーパーユーザー権限は、データベース管理者など、本当に必要な担当者だけに限定すべきです。
  • アカウントの定期的な棚卸し: 定期的に全ユーザーアカウントを確認し、現在も必要なアカウントかどうか、付与されている権限が適切かどうかをレビューするプロセスを設けるべきです。
  • パスワードポリシー: パスワードの複雑性や有効期限などのポリシーを適用することで、アカウントの安全性を高めます。pg_rolesのrolvaliduntilはその管理に役立ちます。
  • 監査ログとの組み合わせ: ユーザー一覧だけでなく、PostgreSQLの監査ログ（log_connections, log_statementなどの設定）を有効にすることで、誰がいつ接続したか、どのようなSQLを実行したかといった情報を記録できます。ユーザー一覧で特定したアカウントの活動を監査ログで追跡することで、不審な行動がないかを確認できます。

ユーザー一覧の取得は、これらのセキュリティベストプラクティスを実践するための最初のステップとなります。どのようなユーザーが存在し、どのような基本的な権限を持っているのかを正確に把握することから、セキュリティ対策は始まります。

他の関連するユーザー管理コマンド

ユーザー一覧を取得するコマンドそのものではありませんが、ユーザー管理に関連して知っておくと便利なSQLコマンドについても簡単に触れておきます。これらのコマンドは、ユーザー一覧で特定したアカウントに対して、管理操作を行う際に使用します。

  • CREATE ROLE role_name [WITH attribute ...];: 新しいロール（ユーザーまたはグループ）を作成します。WITH句でLOGIN, PASSWORD, SUPERUSER, CREATEDB, CREATEROLEなどの属性を指定できます。CREATE USERはCREATE ROLE WITH LOGINの省略形です。
    sql
    CREATE ROLE new_user WITH LOGIN PASSWORD 'mypassword';
    CREATE ROLE app_group; -- ログイン不可のグループロール
    CREATE ROLE admin WITH LOGIN PASSWORD 'securepass' SUPERUSER;
  • DROP ROLE role_name;: 指定したロールを削除します。削除対象のロールが所有するオブジェクトがある場合、削除に失敗することがあります。その場合は、先にオブジェクトの所有権を別のロールに移譲（ALTER ... OWNER TO ...）するか、CASCADEオプション（非推奨の場合あり）を使用する必要があります。
    sql
    DROP ROLE old_user;
  • ALTER ROLE role_name [WITH attribute ...];: 既存のロールの属性を変更したり、パスワードを設定/変更したりします。
    sql
    ALTER ROLE new_user PASSWORD 'newpassword';
    ALTER ROLE app_group CREATEDB; -- グループにDB作成権限を付与
    ALTER ROLE temp_user VALID UNTIL '2024-12-31 23:59:59+09'; -- 有効期限設定
  • GRANT role_name [, ...] TO role_name [, ...] [WITH ADMIN OPTION];: あるロールを別のロールのメンバーにします。これにより、付与された側のロールは付与した側のロールの権限を継承できるようになります（Inherit属性が有効な場合）。WITH ADMIN OPTIONを付けると、付与された側のロールも、さらにそのロールのメンバーシップを他のロールに付与できるようになります。
    sql
    GRANT app_group TO web_role; -- web_roleをapp_groupのメンバーにする
    GRANT app_group TO another_admin WITH ADMIN OPTION; -- another_adminもapp_groupメンバーシップを付与できるようにする
  • REVOKE role_name [, ...] FROM role_name [, ...];: ロールのメンバーシップを取り消します。
    sql
    REVOKE app_group FROM web_role;
  • GRANT privilege_type [, ...] ON object_name [, ...] TO role_name [, ...] [WITH GRANT OPTION];: 特定のデータベースオブジェクト（テーブル、シーケンス、データベースなど）に対する特定の権限（SELECT, INSERTなど）をロールに付与します。
    sql
    GRANT SELECT, INSERT ON my_table TO app_user;
    GRANT ALL PRIVILEGES ON DATABASE my_db TO admin;
  • REVOKE privilege_type [, ...] ON object_name [, ...] FROM role_name [, ...];: オブジェクトに対する権限を取り消します。
    sql
    REVOKE DELETE ON my_table FROM app_user;

これらのコマンドは、ユーザー一覧で現状を把握した後に、必要な変更を適用するために使用します。ユーザー一覧取得コマンドは「見る」ためのツール、これらのコマンドは「変更する」ためのツールと言えます。どちらも適切なユーザー管理には欠かせません。

まとめ

PostgreSQLにおけるユーザー一覧の取得は、データベースのセキュリティと運用管理において非常に重要なタスクです。この記事では、そのための主要な二つの方法、すなわちpsqlのメタコマンド\du（および\du+）と、SQLによるシステムカタログpg_rolesの参照について、それぞれの詳細な使い方、出力内容、そして利点・欠点を比較しながら解説しました。

\duコマンドは、psql環境で手軽にユーザー一覧と基本的な属性、所属グループを確認するのに最適です。整形された見やすい出力を素早く得られるため、インタラクティブな確認作業に向いています。

一方、SQLでpg_rolesシステムカタログを直接クエリする方法は、非常に高い柔軟性と詳細な情報へのアクセスを提供します。SELECTクエリを自由に記述することで、特定の条件でのフィルタリング、ソート、特定の属性値の抽出など、要件に合わせたカスタマイズが可能です。特に、プログラムからユーザー情報を取得して自動処理する場合や、パスワード有効期限、構成パラメータ、pg_auth_membersカタログとの結合による詳細なメンバーシップ関係の確認など、\duでは得られない情報を取得したい場合に強力な手法となります。

どちらの方法を選択するかは、目的と実行環境によります。手動での簡単な確認なら\du、自動化や詳細な分析が必要ならSQLクエリと使い分けるのが一般的です。

ユーザー一覧の定期的な確認は、不要アカウントの削除、過剰な権限の是正、不審なアカウントの早期発見など、セキュリティリスクを低減するために不可欠です。取得した情報を基に、最小権限の原則に従った適切な権限管理を維持し、必要に応じてユーザーの作成、削除、権限変更を行いましょう。

この記事が、読者の皆様のPostgreSQLにおけるユーザー管理、特にユーザー一覧の正確な把握とそれを通じたセキュリティ強化の一助となれば幸いです。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール