概要
大規模なエンタープライズシステムにおいて、データベースのセキュリティ管理は複雑を極めます。特にPostgreSQLのようなRDBMSでは、ロール（ユーザーやグループ）が別のロールを継承する「ロールの入れ子構造」を構築できるため、特定のユーザーが最終的にどのような権限を保持しているのかを即座に把握することが困難になるケースが多々あります。
本稿では、PostgreSQLのシステムカタログである`pg_auth_members`を活用し、ロールの階層構造を再帰的にトレースすることで、「どのロールが、どの親ロールのメンバーとして属しているのか」を一覧化する手法を徹底解説します。これは、監査対応や権限の棚卸しを行うDBAにとって必須のテクニックです。
詳細解説
PostgreSQLにおけるロールの継承関係は、`pg_authid`（ロール定義テーブル）と`pg_roles`（ロールのビュー）、そして`pg_auth_members`（メンバシップ関係テーブル）によって管理されています。
`pg_auth_members`テーブルには、以下の重要なカラムが存在します。
– `roleid`: メンバーが属している「親」ロールのOID
– `member`: メンバーである「子」ロールのOID
このテーブルを再帰的に検索することで、直接的な親子関係だけでなく、孫、ひ孫といった間接的な継承関係までを網羅的にリストアップすることが可能になります。
再帰的クエリには、SQLの共通テーブル式（CTE）である`WITH RECURSIVE`構文を使用します。再帰の初期セット（アンカーメンバー）として、最上位のロールから順に探索を開始するか、あるいは全ての関係性をボトムアップで洗い出すことで、複雑なツリー構造をフラットなリストとして出力できます。
サンプルコード
以下のクエリは、ロール間の継承関係を再帰的に辿り、親子関係のパスを文字列として表示するものです。
WITH RECURSIVE role_hierarchy AS (
-- 1. アンカーメンバー: 直接の親子関係を取得
SELECT
m.roleid AS parent_id,
m.member AS member_id,
r_parent.rolname AS parent_name,
r_member.rolname AS member_name,
1 AS depth,
r_parent.rolname::text AS path
FROM pg_auth_members m
JOIN pg_roles r_parent ON m.roleid = r_parent.oid
JOIN pg_roles r_member ON m.member = r_member.oid
UNION ALL
-- 2. 再帰ステップ: 親の親を辿る
SELECT
m.roleid,
m.member,
r_parent.rolname,
r_member.rolname,
rh.depth + 1,
rh.path || ' -> ' || r_parent.rolname
FROM pg_auth_members m
JOIN pg_roles r_parent ON m.roleid = r_parent.oid
JOIN pg_roles r_member ON m.member = r_member.oid
JOIN role_hierarchy rh ON m.member = rh.parent_id
)
SELECT
member_name AS "メンバーロール",
parent_name AS "属している親ロール",
depth AS "階層深度",
path AS "継承パス"
FROM role_hierarchy
ORDER BY member_name, depth;
このコードを実行することで、例えば「開発者ロール」が「読み取り専用ロール」を継承し、さらに「読み取り専用ロール」が「監査用ロール」を継承しているといった、多層的な権限構造を視覚化できます。
実務アドバイス
実務においてこのクエリを活用する際、以下のポイントに留意してください。
1. 循環参照の回避:
ロールの設定ミスにより、AがBを継承し、BがAを継承するような循環参照が発生する可能性があります。PostgreSQLの`pg_auth_members`は通常このような設定を許容しませんが、万が一異常なデータ構造が存在する場合、再帰クエリが無限ループに陥るリスクがあります。本番環境で実行する際は、必ず`depth`制限を設けるか、循環チェックをロジックに組み込んでください。
2. セキュリティと可読性:
出力された一覧は、必ずしも「現在のセッションで有効な権限」と一致するわけではありません。`INHERIT`属性の有無により、実際に権限が継承されるかどうかが決まるためです。このクエリの結果をベースに、`pg_roles`の`rolinherit`カラムを確認し、実際に権限が行使可能な状態にあるかをマッピングすると、より精度の高い権限監査レポートとなります。
3. 自動化と監視:
このクエリを定期的に実行し、結果をCSVなどでエクスポートしておくことを推奨します。特にセキュリティ監査の際は、「いつ時点でどのような権限構造であったか」のログが求められることが多いため、構成管理の一環として組み込んでください。
4. パフォーマンスへの配慮:
数百〜数千のロールを抱える大規模環境では、再帰クエリの実行コストが無視できません。基本的には読み取り専用のスタンバイサーバーに対して実行し、プライマリサーバーへの負荷を考慮した運用を行ってください。
まとめ
ロールのメンバシップ関係を把握することは、データベースセキュリティの根幹です。PostgreSQLの強力なCTE機能を活用すれば、複雑な階層構造もわずかな行数のSQLで完全に可視化できます。
今回紹介した再帰的クエリは、単なる技術的な興味を満たすだけでなく、企業のセキュリティポリシーに準拠した権限管理を実現するための強力な武器となります。
「誰がどの権限を持っているのか」という問いに対し、勘や経験に頼るのではなく、システムカタログに基づく客観的なエビデンスを提示できること。これこそが、プロフェッショナルなDBAに求められる品質です。ぜひ日々の運用業務にこの手法を取り入れ、より堅牢なデータベース運用を目指してください。
コメント