導入
データベース運用において、セキュリティ管理や権限設計は極めて重要なタスクです。特に、現在どのロール（ユーザー）が存在し、それぞれどのような権限を持っているかを正確に把握しておくことは、不要な特権の放置を防ぎ、最小権限の原則を維持するために不可欠です。本記事では、PostgreSQL環境でロール一覧を効率的に確認する方法と、システムカタログを活用した詳細な管理手法について解説します。
基礎知識
PostgreSQLにおける「ロール」とは、データベースへのアクセス権限を持つエンティティの総称です。以前は「ユーザー」と「グループ」が区別されていましたが、現在はすべて「ロール」として統一的に管理されています。
ロール一覧を確認する主な方法は2つあります。
1. psqlメタコマンド：対話型インターフェースで即座に確認したい場合に適しています。
2. システムカタログ（pg_roles）：クエリを使って結果をフィルタリングしたり、アプリケーションから動的に取得したい場合に適しています。
実装/解決策
最も手軽なのは、psql内で \du コマンドを実行することです。これにより、ロール名、属性、所属グループを一目で確認できます。
しかし、本番環境ではデフォルトロール（pg_で始まるシステム用ロール）が多数存在し、管理対象のロールが埋もれてしまうことがよくあります。そのため、システムカタログ pg_roles を直接参照し、自分たちで作成したロールのみを抽出するクエリを構築するのが実務的です。
サンプルプログラム
以下のSQLは、システム管理上のノイズとなるデフォルトロールを除外し、実務で作成したカスタムロールのみを一覧表示するものです。そのままコピーして実行可能です。
— カスタムロールのみを抽出して、主要な権限情報を取得するSQL
SELECT
rolname AS “ロール名”,
rolsuper AS “スーパーユーザ権限”,
rolcreaterole AS “ロール作成権限”,
rolcreatedb AS “DB作成権限”,
rolcanlogin AS “ログイン可否”
FROM
pg_roles
WHERE
— pg_で始まるシステム定義のロールを除外する
rolname NOT LIKE ‘pg_%’
— データベースの内部管理用ロールを除外する
AND rolname NOT LIKE ‘rds_%’
ORDER BY
rolname;
応用・注意点
実務でpg_rolesを扱う際の注意点をいくつか挙げます。
・デフォルトロールの扱い：pg_read_all_dataなどのロールは、PostgreSQL 10以降で導入された便利な権限管理用のロールです。これらを一律に除外すると見落としが発生するため、必要に応じてWHERE句を調整してください。
・セキュリティ：pg_rolesにはパスワード情報（暗号化済みですが）や接続制限などの設定も含まれています。スクリプトでこれらの情報を取得する場合は、適切なアクセス権限を持つユーザーで実行するようにしてください。
・レプリケーション環境：レプリケーション専用のロール（rolreplicationがtrueのもの）は、通常のユーザーとは異なる管理が求められます。運用監視ツールに組み込む際は、rolreplicationカラムでフィルタリングを行い、レプリケーションユーザーの死活監視を行うことも推奨します。
コメント