【PostgreSQL】ロールベースの権限管理をCREATE ROLEでゼロから設計する

PostgreSQL
2023.01.27

こんにちは、かつコーチです。

「アプリからは全部postgresユーザーで接続している」というプロジェクト、実は少なくありません。

これは動作こそしますが、セキュリティの観点では避けたい設計です。

PostgreSQLにはロール（ユーザーとグループの両方の性質を兼ね備えた権限管理の単位）という仕組みがあり、これを使うことで「誰に何を許可するか」を細かく制御できます。

この記事では、CREATE ROLEを使った権限設計の基本から、実務で使うパターンまでを解説します。

ロールとは何か

ユーザーとロールの関係

PostgreSQLでは、実は「ユーザー」という独立した概念はなく、すべてロールとして管理されています。

-- CREATE USER は CREATE ROLE のショートカット（LOGIN権限が自動で付く）
CREATE USER app_user WITH PASSWORD 'change_this_password';

-- 上記は、実質的に次と同じ
CREATE ROLE app_user WITH LOGIN PASSWORD 'change_this_password';

つまりCREATE USERはCREATE ROLE ... LOGINの糖衣構文であり、ロールにLOGIN属性が付いているかどうかが「ログインできるユーザーか」「権限グループか」の違いになります。

ロールに付与できる主な属性

ロール作成時には、用途に応じて次のような属性を指定できます。

属性説明
LOGINデータベースへの接続を許可する
SUPERUSERすべての制限を無視できる最強権限（原則付与しない）
CREATEDB新しいデータベースを作成できる
CREATEROLE新しいロールを作成できる
PASSWORDパスワード認証の値を設定する
VALID UNTILロールの有効期限を設定する
-- アプリケーション接続用のロール（最小権限を意識した設定）
CREATE ROLE app_user WITH
    LOGIN
    PASSWORD 'change_this_password'
    VALID UNTIL '2027-09-02';

SUPERUSER属性は、管理用の限られたロールにのみ付与し、アプリケーション接続用のロールには絶対に付けないのが鉄則です。

GRANT・REVOKEで権限を細かく制御する

テーブル単位の権限を付与する

ロールを作成しただけでは、テーブルへのアクセス権限はありません。

GRANT文で、どの操作を許可するかを明示的に指定します。

-- app_user に orders テーブルへのSELECT・INSERT・UPDATEのみ許可（DELETEは許可しない）
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;

-- 逆に、権限を取り消す場合は REVOKE を使う
REVOKE UPDATE ON orders TO app_user;

「読み取り専用のレポート用ロール」と「読み書き両方できるアプリ用ロール」を分けておくと、万が一アプリにバグがあっても被害範囲を限定できます。

-- レポート・分析用の読み取り専用ロール
CREATE ROLE report_user WITH LOGIN PASSWORD 'change_this_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;

ロールをグループとして使う（ロールの継承）

PostgreSQLでは、ロールに別のロールを所属させることで、権限をグループ単位でまとめて管理できます。

-- グループロール（LOGIN属性なし＝直接ログインはできない）
CREATE ROLE app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

-- 個々のユーザーロールをグループに所属させる
CREATE ROLE analyst_tanaka WITH LOGIN PASSWORD 'change_this_password';
GRANT app_readonly TO analyst_tanaka;

こうしておくと、新しいテーブルへの権限を追加したいときはapp_readonlyロールに対して1回GRANTすれば、所属する全ユーザーに反映されます。

個別ユーザーごとにGRANTを繰り返す必要がなくなり、権限管理の見通しが格段によくなります

デフォルト権限を設定する

GRANTは既存のテーブルにしか効果がなく、新しく作られたテーブルには権限が引き継がれません。

これを解決するのがALTER DEFAULT PRIVILEGESです。

-- 今後 admin_user が作成するテーブルには、自動で app_readonly にSELECT権限を付与する
ALTER DEFAULT PRIVILEGES FOR ROLE admin_user IN SCHEMA public
    GRANT SELECT ON TABLES TO app_readonly;

このコマンドを設定しておかないと、マイグレーションで新しいテーブルを追加するたびにGRANTをし忘れる、という事故につながります。

つまずきやすいポイント・トラブル対処

❌Before：postgresユーザーでアプリを運用する

冒頭で触れた「アプリからpostgresユーザーで接続」は、次のような実際のリスクにつながります。

  • SQLインジェクションが成立した場合、DROP TABLEやDROP DATABASEまで実行されてしまう
  • 開発者の設定ミスで、意図せず他のスキーマ・テーブルを操作してしまう
-- ❌ postgresロールは全権限を持つため、アプリ用途には過剰
-- DB_USER=postgres として接続している状態

✅After：最小権限のロールを用途別に分ける

アプリの用途ごとに、必要最小限の権限を持つロールを分けて設計します。

-- Webアプリのバックエンド用：CRUD操作のみ許可
CREATE ROLE app_backend WITH LOGIN PASSWORD 'change_this_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_backend;

-- バッチ処理用：特定テーブルへの書き込みのみ許可
CREATE ROLE batch_worker WITH LOGIN PASSWORD 'change_this_password';
GRANT SELECT, INSERT ON import_logs TO batch_worker;

-- マイグレーション実行用：DDL権限が必要な場合のみ分離
CREATE ROLE migration_user WITH LOGIN CREATEDB PASSWORD 'change_this_password';

こうしておくと、万が一いずれかのロールの認証情報が漏えいしても、被害範囲を特定の操作・テーブルに限定できます。

❌Before：権限を確認せずGRANTし続けて設定が把握できなくなる

ロールと権限の数が増えてくると、「結局このユーザーは何ができるんだっけ」と把握できなくなりがちです。

✅After：情報スキーマで現在の権限を可視化する

information_schemaを使うと、特定ロールに付与されている権限を一覧で確認できます。

-- app_backend ロールが持つテーブル権限の一覧を確認
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'app_backend'
ORDER BY table_name;

筆者は権限まわりのトラブル対応で、まずこのクエリを実行して「想定外の権限が付いていないか」を確認するところから調査を始めるようにしています。

実際に、退職済みメンバーのロールにCREATEDB権限が残ったままになっていたのをこのクエリで発見し、慌ててREVOKEした経験があります。

応用：ロール一覧と権限のたな卸し

\duコマンドでロール一覧を確認する

psqlの\duメタコマンドで、サーバー上に存在するロールとその属性を一覧確認できます。

postgres=# \du
                                    List of roles
   Role name    |                         Attributes
-----------------+------------------------------------------------------------
 app_backend     |
 batch_worker    |
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS
 report_user     |

定期的にこのコマンドで棚卸しを行い、不要になったロールをDROP ROLEで削除する運用を組み込んでおくと、権限が野放しになるリスクを防げます。

次のステップ：行単位のアクセス制御

ここまではテーブル単位の権限管理でしたが、「同じテーブルでもテナントごとに見えるデータを制限したい」といったより細かい制御が必要な場面もあります。

そのようなケースでは、次回解説するRow-Level Security（行単位でアクセス可能なデータを制御する仕組み）が有効です。

まとめ

この記事のポイント

  • PostgreSQLの権限管理は「ロール」という単一の仕組みで、LOGIN属性の有無でユーザーとグループを使い分ける
  • GRANT/REVOKEでテーブル単位の操作権限を制御し、用途別にロールを分けることで被害範囲を限定できる
  • ALTER DEFAULT PRIVILEGESを設定しておかないと、新規テーブルへの権限付与漏れが発生しやすい
  • information_schemaや\duで定期的に権限を棚卸しし、不要な権限が残っていないか確認する

次に読むべき記事

  • Row-Level Securityでマルチテナントのデータを守る
  • pg_dumpでバックアップ・リストアする方法
  • よくあるPostgreSQLエラーまとめ

タグ: PostgreSQL, 中級者向け, セキュリティ

タイトルとURLをコピーしました