PostgreSQLの接続ユーザーをpg_rolesで棚卸しする方法｜ログイン権限と有効期限・所属グループの確認

この記事のポイント

・ pg_roles で全ロールを一覧表示し、rolcanlogin でログイン可否を確認できる
・ rolvaliduntil でパスワード有効期限の切れそうなアカウントを事前に検知できる
・ pg_auth_members を JOIN すれば所属グループもまとめて確認できる
・ 棚卸し用の総合クエリ1本で「誰が・いつまで・どのグループか」を把握できる

# PostgreSQLに接続（postgresユーザーに切り替えてから実行）
$ sudo -u postgres psql

# または別の管理ユーザーで直接接続
$ psql -h localhost -U dbadmin -d mydb

SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole,
       rolconnlimit, rolvaliduntil
FROM pg_roles
ORDER BY rolcanlogin DESC, rolname;

        rolname         | rolcanlogin | rolsuper | rolcreatedb | rolcreaterole | rolconnlimit |    rolvaliduntil     
------------------------+-------------+----------+-------------+---------------+--------------+----------------------
 appuser                | t           | f        | f           | f             |           10 | 2026-12-31 23:59:59+09
 dbadmin                | t           | f        | t           | t             |           -1 | 
 postgres               | t           | t        | t           | t             |           -1 | 
 replicator             | t           | f        | f           | f             |            5 | 
 testuser               | t           | f        | f           | f             |           -1 | 2025-03-01 00:00:00+09
 pg_checkpoint          | f           | f        | f           | f             |           -1 | 
 pg_database_owner      | f           | f        | f           | f             |           -1 | 
 pg_monitor             | f           | f        | f           | f             |           -1 | 
 pg_read_all_data       | f           | f        | f           | f             |           -1 | 
 pg_signal_backend      | f           | f        | f           | f             |           -1 | 
(10 rows)

SELECT rolname, rolsuper, rolcreatedb, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolcanlogin = true
  AND rolname NOT LIKE 'pg\_%'
ORDER BY rolname;

  rolname   | rolsuper | rolcreatedb | rolconnlimit |    rolvaliduntil     
------------+----------+-------------+--------------+----------------------
 appuser    | f        | f           |           10 | 2026-12-31 23:59:59+09
 dbadmin    | f        | t           |           -1 | 
 postgres   | t        | t           |           -1 | 
 replicator | f        | f           |            5 | 
 testuser   | f        | f           |           -1 | 2025-03-01 00:00:00+09
(5 rows)

SELECT rolname, rolvaliduntil,
       rolvaliduntil - NOW() AS days_remaining
FROM pg_roles
WHERE rolcanlogin = true
  AND rolvaliduntil IS NOT NULL
  AND rolvaliduntil < NOW() + INTERVAL '30 days'
ORDER BY rolvaliduntil;

  rolname  |    rolvaliduntil     |      days_remaining       
-----------+----------------------+---------------------------
 testuser  | 2025-03-01 00:00:00+09 | -554 days -09:23:15.291
 appuser   | 2026-12-31 23:59:59+09 | 115 days 14:36:44.709
(2 rows)

SELECT 
  r.rolname AS member,
  g.rolname AS group_role,
  m.admin_option
FROM pg_auth_members m
JOIN pg_roles r ON m.member = r.oid
JOIN pg_roles g ON m.roleid = g.oid
ORDER BY r.rolname, g.rolname;

   member   |    group_role    | admin_option 
------------+------------------+--------------
 appuser    | readonly_group   | f
 dbadmin    | readonly_group   | f
 dbadmin    | readwrite_group  | t
 replicator | pg_monitor       | f
(4 rows)

SELECT
  r.rolname,
  CASE WHEN r.rolsuper    THEN 'SUPER' ELSE '' END AS super,
  CASE WHEN r.rolcanlogin THEN 'YES'   ELSE 'NO'  END AS can_login,
  r.rolconnlimit,
  COALESCE(r.rolvaliduntil::text, '無期限') AS valid_until,
  COALESCE(
    STRING_AGG(g.rolname, ', ' ORDER BY g.rolname),
    '（なし）'
  ) AS member_of
FROM pg_roles r
LEFT JOIN pg_auth_members m ON r.oid = m.member
LEFT JOIN pg_roles g ON m.roleid = g.oid
WHERE r.rolname NOT LIKE 'pg\_%'
GROUP BY r.rolname, r.rolsuper, r.rolcanlogin, r.rolconnlimit, r.rolvaliduntil
ORDER BY r.rolcanlogin DESC, r.rolname;

  rolname   | super | can_login | rolconnlimit |         valid_until          |       member_of       
------------+-------+-----------+--------------+------------------------------+-----------------------
 appuser    |       | YES       |           10 | 2026-12-31 23:59:59+09       | readonly_group
 dbadmin    |       | YES       |           -1 | 無期限                        | readonly_group, readwrite_group
 postgres   | SUPER | YES       |           -1 | 無期限                        | （なし）
 replicator |       | YES       |            5 | 無期限                        | pg_monitor
 testuser   |       | YES       |           -1 | 2025-03-01 00:00:00+09       | （なし）
 readonly_group |   | NO        |           -1 | 無期限                        | （なし）
 readwrite_group |  | NO        |           -1 | 無期限                        | （なし）
(7 rows)

-- NG: 一般ユーザーには permission denied
SELECT * FROM pg_authid;

-- OK: pg_roles は一般ユーザーも参照可能
SELECT * FROM pg_roles;