1. 導入
PostgreSQLを運用する際、最も疎かになりがちなのが「ロール（ユーザー）管理」です。開発中、とりあえずスーパーユーザーで全ての作業を行っていませんか？本稿では、セキュリティリスクを最小限に抑えつつ、実務で必須となる「最小権限の原則」に基づいたロール設計と、その実装方法を解説します。適切な権限分離は、誤操作によるデータ消失や不正アクセスを防ぐための最も重要な防衛線です。
2. 基礎知識
PostgreSQLにおける「ロール」とは、データベースにアクセスし、オブジェクト（テーブルやスキーマなど）を操作するための主体を指します。MySQLなどの他RDBMSでは「ユーザー」と「グループ」が分かれていますが、PostgreSQLではこれらを統合して「ロール」として管理します。
ロールのポイント:
・LOGIN権限: データベースに接続できるロール。
・グループロール: 複数のロールをまとめた「権限の受け皿」となるロール。
・権限の継承: メンバーであるロールが、グループロールの権限を引き継ぐ仕組み。
3. 実装/解決策
実務では、アプリケーション専用のロールを作成し、必要なスキーマに対してのみ権限を付与するのが鉄則です。
具体的なステップは以下の通りです。
1. アプリケーション専用のロールを作成する。
2. 特定のスキーマに対するアクセス権を付与する。
3. 権限を継承させる設定を行う。
4. サンプルプログラム
以下は、アプリケーション用ユーザーを作成し、必要な権限を最小限に制限する一連のSQLスクリプトです。psql等で実行可能です。
— 1. アプリケーション用ロールの作成
CREATE ROLE app_user WITH LOGIN PASSWORD ‘strong_password’;
— 2. 権限管理用グループロールの作成
CREATE ROLE app_group;
— 3. グループにユーザーを所属させる
GRANT app_group TO app_user;
— 4. 特定スキーマ(public)へのアクセス権を付与
— 基本的にデフォルトではpublicスキーマに作成権限があるため、これを制限するのが安全です
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_group;
— 5. テーブルに対する操作権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_group;
— 6. シーケンス（自動採番）の権限付与も忘れずに
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_group;
— 注意: 今後作成されるテーブルにも自動的に権限を付与する設定
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_group;
5. 応用・注意点
現場で陥りやすい罠:
・デフォルト権限の落とし穴: 上記のサンプルにある「ALTER DEFAULT PRIVILEGES」を忘れると、後から作成したテーブルに対してアプリケーションが「権限不足エラー（Permission Denied）」を吐くことになります。新しいテーブルを作成する際は、必ず権限付与をセットで考える必要があります。
・スキーマ設計: 運用が複雑化するシステムでは、publicスキーマに全てのテーブルを置くのではなく、機能単位でスキーマを分割し、個別にGRANTを行うとセキュリティと保守性が飛躍的に向上します。
・DROP OWNEDの活用: ユーザーを削除する際は、そのユーザーが所有しているオブジェクトを事前に整理する必要があります。その際に「DROP OWNED BY ユーザー名」を使うと、残存オブジェクトの削除漏れを防ぐことができます。
これらの設定を初期段階で行うことで、堅牢なデータベース環境を構築してください。
コメント