やあ、お疲れ様。データベースの運用、順調かな？
今日はPostgreSQLの「裏側」を覗き見る話、`pg_roles`について話そうと思うんだ。
PostgreSQLを触り始めると、まずは `CREATE ROLE` や `GRANT` を使ってユーザーを作ったり権限を付与したりするよね。でも、いざ運用が長くなってくると、「あれ、このユーザーって具体的にどんな権限を持ってたっけ？」「スーパーユーザー権限が付与されているユーザーは誰だ？」と確認したくなる場面が必ず来る。
そんな時、僕らが頼りにするのが `pg_roles` というシステムビューだ。マニュアルをそのまま読むと退屈かもしれないけど、実務でどう使うかという視点で深掘りしてみよう。
—
pg_roles って結局なんなの？
一言で言えば、「データベース内の『誰』を知るための住所録」だ。
PostgreSQLでは、ユーザーもグループもすべて「ロール」という概念で統一されている。`pg_roles` は、そのロールに関するすべての属性（ログイン可能か、スーパーユーザーか、パスワードの期限はあるか等）が詰まった宝箱なんだ。
ちなみに、`pg_user` というビューもあるんだけど、あれは「ログイン可能なロール」に絞ったもの。`pg_roles` はログインできないロール（単なる権限のグループとして使っているものなど）も含めてすべて網羅しているから、管理目的でクエリを叩くなら断然 `pg_roles` の方がおすすめだよ。
—
現場でよく使う「pg_roles」活用術
じゃあ、具体的にどんな場面で使うのか、いくつかコード例を挙げてみるね。
1. スーパーユーザー権限を持つユーザーを洗い出す
セキュリティ監査の基本だね。誰が最強の権限を持っているか、定期的にチェックするのはエンジニアの嗜みだよ。
SELECT rolname, rolsuper, rolinherit
FROM pg_roles
WHERE rolsuper = true;
これだけで、誰が「何でもできる人」なのかが一発でわかる。不要なアカウントがスーパーユーザーになっていないか、棚卸しするときによく使うよ。
2. パスワードの期限を確認する
これも地味だけど重要。セキュリティポリシーでパスワード変更を義務付けているなら、こんなクエリで期限切れ間近のユーザーを探せる。
SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolvaliduntil IS NOT NULL;
`rolvaliduntil` が過去の日付になっていれば、そのユーザーはもうログインできないはずだ。トラブルシューティングで「ログインできない！」と相談されたら、まずここを見てみるのも手だね。
3. 「ログインできないロール」の全容を把握する
例えば、Webアプリの権限管理で、特定のテーブルにアクセスできるグループを作ったとするよね。それらの「グループ用ロール」がどれくらいあるかを確認したい時はこうする。
SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolcanlogin = false;
—
先輩からのアドバイス：ここだけは注意して
`pg_roles` を使う上で、一つだけ気をつけてほしいことがある。
それは、「パスワードそのものは見えない」ということだ。当たり前だけど、ハッシュ化されたパスワードすら直接は見えないようになっている（`rolpassword` カラムもあるけど、中身は暗号化された文字列だ）。
あと、もう一つ。`pg_roles` には、そのユーザーが「どの権限セット（ロール）を継承しているか」という情報は直接は載っていないんだ。もし「このユーザーが実質的にどんな権限を持っているか」を細かく追跡したいなら、`pg_auth_members` というテーブルとJOINして調べる必要がある。
これは少し複雑なクエリになるから、また別の機会にじっくり解説するよ。
—
まとめ：怖がらずにクエリを叩こう
`pg_roles` は、PostgreSQLのセキュリティとアクセスコントロールを理解するための「地図」みたいなものだ。
「権限が足りない」「誰がこの操作をしたんだ？」という混乱が起きたとき、`SELECT FROM pg_roles;` を叩いて全体像を眺めるだけで、解決の糸口が見えることは本当に多い。
最初は難しく感じるかもしれないけど、ぜひ自分の環境で `SELECT` してみてほしい。システムビューを使いこなせるようになると、データベース運用がグッと「自分の手の中にある」感覚になるはずだよ。
何か詰まったら、いつでも聞いてくれ。応援しているよ！
コメント