導入
PostgreSQLの運用において、アプリケーションごとに適切な「ロール（ユーザー）」を定義することは、セキュリティと保守性の観点から非常に重要です。スーパーユーザー権限でアプリケーションを稼働させることは、SQLインジェクションなどの脆弱性を突かれた際にデータベース全体が破壊されるリスクを伴います。本稿では、CREATE ROLEコマンドを正しく理解し、現場で安全にロールを作成するための実務知識を解説します。
基礎知識
PostgreSQLにおける「ロール」とは、データベースにアクセスできるユーザーやグループを指す概念です。MySQLなどの他データベース製品でいう「ユーザー」と「グループ」が、PostgreSQLでは「ロール」という単一の概念に統合されています。
ロールを作成する際は、そのロールが「ログイン可能か（LOGIN）」「データベースを作成できるか（CREATEDB）」「他のロールを管理できるか（CREATEROLE）」といった属性を慎重に設定する必要があります。特に、最小権限の原則（必要な権限のみを付与する）に従うことが、DBAにとって最も重要な基本姿勢です。
実装/解決策
実務では、単にユーザーを作るだけでなく、アプリケーション専用のロールに対して必要最小限の権限を付与する手順を踏みます。まずは基本的な作成コマンドを確認し、その後、権限を付与する流れを理解しましょう。
1. ロールの作成（ログイン可能、パスワード設定）
2. データベースへのアクセス権限（CONNECT）付与
3. スキーマやテーブルへの操作権限（SELECT, INSERT, UPDATE, DELETE）付与
サンプルプログラム
以下は、アプリケーション用の読み取り・書き込み専用ロールを作成し、特定のデータベースに対する操作権限を付与する一連のSQL例です。
— 1. アプリケーション用ロールの作成（ログイン可能、パスワード設定）
CREATE ROLE app_user WITH LOGIN PASSWORD ‘strong_password_123’;
— 2. 対象データベースへの接続権限を付与
GRANT CONNECT ON DATABASE my_database TO app_user;
— 3. publicスキーマへの利用権限を整理（※必要に応じて制限する）
GRANT USAGE ON SCHEMA public TO app_user;
— 4. テーブルに対するDML（データ操作）権限を付与
— これにより、app_userはテーブルの読み書きが可能になります
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
— 5. シーケンス（ID採番用）の使用権限も忘れずに付与
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
/
解説：
- LOGIN属性を指定しないと、そのロールはログインできません。
- GRANTによる権限付与は、CREATE ROLEで作成した直後には適用されないため、
別途明示的な権限付与が必要です。
/
応用・注意点
現場で陥りやすいトラブルや注意点をまとめます。
・デフォルト権限の落とし穴
上記のサンプルのように「ALL TABLES」に対してGRANTを行っても、将来的に新しく作成されたテーブルには権限が引き継がれません。新しく作成されるテーブルに対しても自動的に権限を付与したい場合は、ALTER DEFAULT PRIVILEGESコマンドを使用する必要があります。
・CREATEROLE権限の危険性
参考資料にもある通り、CREATEROLE権限を持つロールは、自身が持っていない権限を持つロールを作成できるため、実質的にスーパーユーザーと同等の権限を持つことになります。開発者やアプリケーションユーザーには絶対に付与してはいけません。
・パスワードの管理
サンプルコードにパスワードをベタ書きしていますが、実際の運用では環境変数やシークレット管理ツール（AWS Secrets Managerなど）を使用し、履歴に残さない工夫を徹底してください。
これらの基本を押さえ、安全かつ効率的なロール管理を目指しましょう。
コメント