はじめに
データベース管理者（DBA）として日々の運用を行っていると、特定の環境で「誰がどのデータベースにアクセスできるのか」「どのような権限を持っているのか」を即座に把握しなければならない場面に多々遭遇します。特に、開発環境から本番環境へ昇格する際の監査や、退職者が出た際の権限削除など、セキュリティ管理の観点からユーザー情報の正確な把握は必須です。本記事では、PostgreSQLを対象に、システムカタログを駆使してユーザー一覧や権限情報を効率的に取得する方法を、実務的な視点で解説します。
システムカタログの重要性
PostgreSQLにおいて、ユーザーやロールの情報はシステムカタログと呼ばれる特別なテーブルに格納されています。具体的には、pg_user、pg_roles、pg_authidなどが該当します。多くの開発者がpg_userビューを単に参照するだけで満足してしまいますが、実務ではより深い情報を引き出すために、これらのカタログをJOINしてクエリを構築するスキルが求められます。
ユーザー一覧を取得する基本クエリ
まず、基本的なユーザー一覧を取得する方法から見ていきましょう。単純にpsqlで \du コマンドを打つのも一つの手ですが、自動化スクリプトに組み込んだり、特定の条件でフィルタリングしたりする場合にはSQLクエリが不可欠です。
以下のクエリは、ユーザー名、ログイン権限、スーパーユーザー権限、および作成可能なデータベース数などを一覧化するものです。
SELECT
usename AS user_name,
usesuper AS is_superuser,
usecreatedb AS can_create_db,
valuntil AS valid_until
FROM pg_user;
このクエリにより、誰がスーパーユーザー特権を持っているか、またアカウントの有効期限が設定されているかを一目で確認できます。実務では、特に「意図しないスーパーユーザーが存在しないか」を定期的にチェックすることがセキュリティの第一歩となります。
権限情報の深掘り：ロールとメンバシップ
PostgreSQLの権限管理は「ロール」ベースで行われます。あるユーザーがどのグループロールに属しているかを確認することは、アクセス制御の妥当性を評価する上で重要です。これを実現するには、pg_auth_membersとpg_rolesを結合する必要があります。
以下のクエリは、各ユーザーが所属しているグループロールを確認するためのものです。
SELECT
roleid::regrole AS group_role,
member::regrole AS member_user
FROM pg_auth_members;
このクエリを実行することで、誰がどの権限グループのメンバーであるかが明確になります。大規模な環境では、このクエリにWHERE句を追加して、特定のユーザーに絞り込んで調査を行うのが一般的です。
データベースおよびスキーマに対する権限の確認
ユーザー情報だけでなく、そのユーザーがどのデータベースやスキーマに対してSELECTやINSERTといった権限を持っているかを調査するのは、さらに複雑な手順を要します。これには、information_schema.table_privileges を活用するのが最も効率的です。
特定のユーザー（例: ‘app_user’）がどのテーブルに対して権限を持っているかを確認するクエリ例を以下に示します。
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.table_privileges
WHERE grantee = ‘app_user’;
このクエリは、アプリケーションユーザーが意図しないテーブルにアクセスできていないか、あるいは必要なテーブルへの権限が漏れていないかを検証する際に非常に役立ちます。
実務で直面する課題と解決策
実務では、単にクエリを発行するだけでは解決できない問題も存在します。例えば、権限の「継承（INHERIT）」設定です。PostgreSQLのロールには権限を継承する設定があり、これが有効になっているか否かで、ユーザーが実際に発揮できる権限が変わります。
以下のクエリで、ユーザーの権限継承設定を確認しましょう。
SELECT
rolname,
rolinherit
FROM pg_roles
WHERE rolcanlogin = true;
もし、本来権限を継承すべきではないユーザーが継承設定になっていれば、即座に修正が必要です。このように、カタログ情報を定期的に抽出して異常値を見つける仕組み（モニタリング）を作っておくことが、DBAの重要なミッションとなります。
セキュリティ監査のためのスクリプト化
DBAとして推奨したいのは、これらの一連の調査用クエリを一つのビューやスクリプトとしてまとめておくことです。以下は、ユーザー名と、そのユーザーが持つロールを一行にまとめるための高度なクエリです。
SELECT
r.rolname,
array_agg(m.rolname) AS member_of
FROM pg_roles r
LEFT JOIN pg_auth_members am ON r.oid = am.member
LEFT JOIN pg_roles m ON am.roleid = m.oid
WHERE r.rolcanlogin = true
GROUP BY r.rolname;
このクエリを使えば、各ユーザーがどのようなロールグループに所属しているかが一目瞭然です。これをcronで定期実行し、結果をCSVとして出力してGitで差分管理するだけでも、立派なセキュリティ監査体制となります。
権限管理における注意点：PUBLIC権限
忘れがちなのが、PostgreSQLのデフォルト設定である「PUBLIC」ロールへの権限付与です。デフォルトでは、PUBLICロールにpublicスキーマへのCREATE権限が付与されていることが多く、これがセキュリティホールになるケースが後を絶ちません。
以下のクエリで、publicスキーマに対する権限設定をチェックしてください。
SELECT
nspname,
nspacl
FROM pg_namespace
WHERE nspname = ‘public’;
もしnspaclにPUBLICが含まれており、CREATE権限が付与されている場合は、速やかにREVOKEコマンドを実行して制限をかけることを強く推奨します。
まとめ：DBAとしてのマインドセット
ユーザー管理と権限管理は、データベースの堅牢性を支える土台です。今回紹介したようなシステムカタログを直接参照するクエリは、GUIツールでは見えない「データベースの裏側」を教えてくれます。
実務においては、以下の3点をルーチン化することをお勧めします。
1. 定期的なユーザー一覧の棚卸しと、不要なアカウントの削除。
2. 権限継承設定（INHERIT）の妥当性確認。
3. PUBLICスキーマへの不要な権限付与の排除。
これらの作業は一見地味ですが、万が一のインシデントが発生した際、あるいは監査が入った際に、DBAとしての信頼性を大きく左右します。ぜひ、本記事のコードを自身の環境で実行し、現在のデータベースの権限状況を可視化してみてください。
データベースは「作って終わり」ではありません。常に誰が何をしているのか、何ができるのかを把握し続けることこそが、プロフェッショナルなDBAの務めです。この記事が、皆さんの日々の運用業務の一助となれば幸いです。もし特定の環境（例えばAWS RDSやCloud SQLなど）特有の制約や、より高度なクエリの最適化について興味があれば、ぜひ今後の記事でも深掘りしていきましょう。
コメント