導入
PostgreSQLの権限管理において、ロール（ユーザーやグループ）の階層構造を把握することはセキュリティ管理の基本です。しかし、システムが複雑化すると「どのユーザーがどのグループに所属しているのか」「誰が権限を付与したのか」が不明瞭になりがちです。本記事では、psqlのメタコマンドを用いた簡易確認方法と、システムカタログを直接参照して詳細な関係性を抽出するSQLテクニックを解説します。
基礎知識
PostgreSQLにおけるロールのメンバ資格とは、あるロール（親）に対して、別のロール（子）をメンバとして登録する仕組みです。これにより、子ロールは親ロールの権限を継承できます。この関係情報はシステムカタログである「pg_auth_members」に保存されています。また、ロールそのものの情報は「pg_authid」に格納されており、これらをJOINすることで、IDではなく意味のあるロール名として情報を取得することが可能です。
実装/解決策
現場での調査時には、まず手軽な「\du」コマンドで全体像を確認し、詳細な分析が必要な場合は「pg_auth_members」をJOINしたクエリを実行するのが定石です。特に、誰が権限を付与したか（grantor）や、ADMIN OPTIONが付与されているかを確認することは、監査や権限管理の観点から非常に重要です。
サンプルプログラム
以下のSQLは、ロール名とメンバ名、付与者、およびADMIN OPTIONの有無を見やすく一覧化するものです。そのままコピーして実行可能です。
— ロールの階層関係を可視化するクエリ
SELECT
r.rolname AS group_role, — 親となるロール（グループ）
m.rolname AS member_role, — メンバとなっているロール
g.rolname AS grantor_role, — 権限を付与したロール
am.admin_option — 管理権限の委譲可否
FROM pg_auth_members am
JOIN pg_authid r ON am.roleid = r.oid
JOIN pg_authid m ON am.member = m.oid
JOIN pg_authid g ON am.grantor = g.oid
ORDER BY group_role; — グループごとに並び替えて表示
応用・注意点
注意点： pg_auth_membersには、ロールが「属している」関係のみが記録されています。循環参照が発生していないかを確認したり、特定のロールが持つ「継承権限（INHERIT）」の状態と混同しないよう注意してください。
現場でのTips： 大規模な環境では、再帰的なクエリ（WITH RECURSIVE）を使用することで、ネストされたグループの全階層を一括で抽出できます。また、本番環境でpg_authidを直接参照する際は、読み取り権限に注意し、必要に応じて参照用のビューを作成しておく運用をおすすめします。
コメント