TECH BLOG
技術ブログ

ARTICLE

  • 2024-10-10 PostgreSQLPostgreSQL ナレッジ

    PostgreSQLでのロールとユーザの確認方法

前回の記事では、ロールとユーザの違いについて説明しました。
今回は、PostgreSQL におけるロールとユーザの確認方法について説明します。

環境

本記事で検証を行った環境は以下となります。

  • RockeyLinux 9.4
  • PostgreSQL 16.4

ロールの確認

作成されているロールは pg_roles ビューで確認できます。pg_roles ビューは、pg_authid テーブルをもとにしたビューとなります。
pg_roles からは下記のようにロール名やスーパーユーザかどうか、ログイン可能かどうか(前回のブログ記事でお話しましたように、ログイン不可であればロール、ログイン可能であればユーザ)などを確認できます。

postgres=# SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;
           rolname           | rolsuper | rolcanlogin 
-----------------------------+----------+-------------
 postgres                    | t        | t
 pg_database_owner           | f        | f
 pg_read_all_data            | f        | f
 pg_write_all_data           | f        | f
 pg_monitor                  | f        | f
 pg_read_all_settings        | f        | f
 pg_read_all_stats           | f        | f
 pg_stat_scan_tables         | f        | f
 pg_read_server_files        | f        | f
 pg_write_server_files       | f        | f
 pg_execute_server_program   | f        | f
 pg_signal_backend           | f        | f
 pg_checkpoint               | f        | f
 pg_use_reserved_connections | f        | f
 pg_create_subscription      | f        | f
(15 rows)

その他、pg_roles から確認できる情報については PostgreSQL 文書を確認してください。

【 PostgreSQL 16.4文書 - 54.20. pg_roles - 】
https://www.postgresql.jp/document/16/html/view-pg-roles.html

ユーザの確認

作成されているユーザは pg_user ビューで確認できます。pg_user ビューは、pg_shadow ビューをもとにしたビューとなります。pg_shadow ビューも pg_authid テーブルをもとにしたビューですので、pg_user ビューも結局のところ pg_authid テーブルをもとにしたビューとなります。
pg_user からは pg_roles とほぼ同様の内容を確認できます。

postgres=# SELECT usename, usesuper FROM pg_user;
 usename  | usesuper 
----------+----------
 postgres | t
(1 row)

その他、pg_user から確認できる情報については PostgreSQL 文書を確認してください。

【 PostgreSQL 16.4文書 - 54.33. pg_user - 】
https://www.postgresql.jp/document/16/html/view-pg-user.html

pg_roles ビューと pg_user ビューの違い

pg_roles と pg_user の違いとしては、出力結果として表示されるロール(ユーザ)に違いがあります。pg_roles にはユーザを含めたロールが出力されますが、pg_user ではユーザのみが結果として出力されます。
以下で、ロールとユーザをそれぞれ作成し、その後、pg_user を SELECT して挙動を確認します。

-- ロールを作成
postgres=# CREATE ROLE test_role WITH NOLOGIN;
CREATE ROLE

-- ユーザを作成
postgres=# CREATE ROLE test_user WITH LOGIN;
CREATE ROLE

postgres=# SELECT usename, usesuper FROM pg_user;
  usename  | usesuper 
-----------+----------
 postgres  | t
 test_user | f
(2 rows)

上記のとおり、pg_user にはロールとして作成した test_role は出力されず、ユーザとして作成した test_user のみが出力されます。
繰り返しとなりますが、ロールとユーザの違いはログインできるかどうかということになりますので、pg_roles の rolcanlogin が true か false かでロールかユーザかを判断できます。
ということは、pg_user を参照せずとも pg_roles だけを参照することで、ロールのみを確認またはユーザのみを確認ということも可能です。
具体的な SELECT 文は下記のようなものとなります。

-- pg_roles からロールのみを取得
postgres=# SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolcanlogin = false;
           rolname           | rolsuper | rolcanlogin 
-----------------------------+----------+-------------
 pg_database_owner           | f        | f
 pg_read_all_data            | f        | f
 pg_write_all_data           | f        | f
 pg_monitor                  | f        | f
 pg_read_all_settings        | f        | f
 pg_read_all_stats           | f        | f
 pg_stat_scan_tables         | f        | f
 pg_read_server_files        | f        | f
 pg_write_server_files       | f        | f
 pg_execute_server_program   | f        | f
 pg_signal_backend           | f        | f
 pg_checkpoint               | f        | f
 pg_use_reserved_connections | f        | f
 pg_create_subscription      | f        | f
 test_role                   | f        | f
(15 rows)

-- pg_roles からユーザのみを取得
postgres=# SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolcanlogin = true;
  rolname  | rolsuper | rolcanlogin 
-----------+----------+-------------
 postgres  | t        | t
 test_user | f        | t
(2 rows)
ロールに付与されているアクセス権限を確認する

次に、ロールに付与されているアクセス権限を確認します。アクセス権限(テーブルレベルの権限)は information_schema.role_table_grants ビューで確認できます。
test_user ユーザに pgbench_accounts テーブルを SELECT できる権限を GRANT コマンドで付与して、それを確認します。information_schema.role_table_grants の grantor が権限を付与したロール(ユーザ)、grantee が権限を付与されたロール(ユーザ)となります。

postgres=# GRANT SELECT ON pgbench_accounts TO test_user;
GRANT

postgres=# SELECT grantor, grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'test_user';
 grantor  |  grantee  | table_schema |    table_name    | privilege_type 
----------+-----------+--------------+------------------+----------------
 postgres | test_user | public       | pgbench_accounts | SELECT
(1 row)

上記のとおり、postgres ユーザによって test_user に対して pgbench_accounts テーブルに対する SELECT 権限が付与されていることを確認できました。
WHERE 句の grantee = 'test_user' の部分を確認したいユーザ名に置き換えて SELECT 文を実行することで、任意のユーザのアクセス権限を確認できます。

information_schema.role_table_grants の詳細については、PostgreSQL 文書をご確認ください。

【 PostgreSQL 16.4文書 - 37.37. role_table_grants - 】
https://www.postgresql.jp/document/16/html/infoschema-role-table-grants.html

システムレベルの権限は確認できない

ただし、同じ権限でもシステムレベルの権限(※)は information_schema.role_table_grants などで確認することはできません。

※ PostgreSQL 文書ではシステムレベルの権限という表現はでてきませんが、GRANT の説明で「テーブルレベルの権限」という表現があるため、それと比較する形で「CHECKPOINT を行える権限」などを「システムレベルの権限」と表現しています。

例えば、CHECKPOINT を行えるのはスーパーユーザから pg_checkpoint ロール(および pg_checkpoint ロールを継承しているロール)のみとなりますが、この情報は
information_schema.role_table_grants では確認できません。

postgres=# SELECT * FROM information_schema.role_table_grants WHERE grantee = 'pg_checkpoint';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)

システムレベルの権限は PostgreSQL が内部的に付与・管理しているもののようで、PostgreSQL文書などで確認するしかなさそうです(SELECT 文を利用して確認できる方法を見つけることはできませんでした)。

ロールおよびユーザに付与されているロールを確認する

ロールが付与されているかどうかは pg_auth_members を確認します。実際に test_role を test_user に付与し、pg_auth_members の内容を確認してみます。

-- test_role を test_user に付与
postgres=# GRANT test_role TO test_user;
GRANT

-- pg_auth_members を確認
postgres=# SELECT * FROM pg_auth_members;
  oid  | roleid | member | grantor | admin_option | inherit_option | set_option 
-------+--------+--------+---------+--------------+----------------+------------
 10226 |   3374 |   3373 |      10 | f            | t              | t
 10227 |   3375 |   3373 |      10 | f            | t              | t
 10228 |   3377 |   3373 |      10 | f            | t              | t
 17310 |  17308 |  17309 |      10 | f            | t              | t
(4 rows)

oid = 17310 のレコードが該当しますが、oid で表示されていてもよく分かりませんので pg_roles と結合して再度確認します。

postgres=# SELECT am.oid, am.roleid, r1.rolname AS rol_name, am.member, r2.rolname AS member_name, am.grantor, r3.rolname AS grantor_name FROM pg_auth_members am INNER JOIN pg_roles r1 ON am.roleid = r1.oid INNER JOIN pg_roles r2 ON am.member = r2.oid INNER JOIN pg_roles r3 ON am.grantor = r3.oid;
  oid  | roleid |       rol_name       | member | member_name | grantor | grantor_name 
-------+--------+----------------------+--------+-------------+---------+--------------
 10226 |   3374 | pg_read_all_settings |   3373 | pg_monitor  |      10 | postgres
 10227 |   3375 | pg_read_all_stats    |   3373 | pg_monitor  |      10 | postgres
 10228 |   3377 | pg_stat_scan_tables  |   3373 | pg_monitor  |      10 | postgres
 17310 |  17308 | test_role            |  17309 | test_user   |      10 | postgres
(4 rows)

releid が付与する側のロール、member が付与される側のロール、grantor が付与(GRANT コマンド)を実行したロールとなります。上記の SELECT の結果より test_role が test_user に付与されていることを確認できました。
その他の pg_auth_members についての詳細は PostgreSQL 文書を参照してください。

【 PostgreSQL 16.4文書 - 53.9 pg_auth_members - 】
https://www.postgresql.jp/document/16/html/catalog-pg-auth-members.html

ロールの付与が循環するような付与は不可

test_role を test_user に付与した状態で、test_user を test_role に付与することはできません。test_role → test_user → test_role → ... といった形で循環してしまうためです。
実際に test_user を test_role に付与しようとすると、下記のようなエラーとなります。

postgres=# GRANT test_user TO test_role;
ERROR:  role "test_user" is a member of role "test_role"

まとめ

以上がロールとユーザの確認方法となります。
システム運用を続けていく上で、不要になったロールや役割が変更されたロールなども発生するかと思います。定期的に pg_roles や pg_auth_members を確認して、想定通りのロール管理ができているかを確認することが必要ではないかと考えておりますので、本記事でロールとユーザの確認方法を説明しました。
テーブルレベルの権限やシステムレベルの権限など、今回の記事で説明できていない内容が多々ありますので、次回以降で説明できればと思います。

CATEGORY

ARCHIVE

PostgreSQLの
ハイパフォーマンスチューニングのご相談は
株式会社インサイトまで
お気軽にお問い合わせください。

CONTACT