PostgreSQLにおけるユーザー、ロール、権限を管理するための必須コマンド

データベースのセキュリティは、堅牢なアクセス制御にかかっています。PostgreSQLでは、この制御は主にロールとその関連する権限（または特権）を通じて管理されます。これらのセキュリティエンティティを作成、変更、管理する方法を理解することは、すべてのデータベース管理者や開発者にとって不可欠です。

この包括的なガイドでは、データベースへの安全なアクセス制御ポリシーの定義、およびPostgreSQL環境内での最小権限の原則の実施に必要なSQLコマンドについて探ります。ロールの作成、属性の設定、グループメンバーシップ、およびGRANTとREVOKEを使用した詳細なオブジェクト権限管理を扱います。

PostgreSQLのロールの理解

ユーザーとグループを厳密に区別する他のデータベースシステムとは異なり、PostgreSQLは統一された概念であるロールを使用します。ロールは以下を表すことができます。

データベースユーザー（ログイン可能なエンティティ。通常はLOGIN属性を持ちます）。
ユーザーグループ（特権のグループ化のみに使用されるエンティティ。通常はLOGIN属性を持ちません）。

効果的なセキュリティは、データベースにアクセスする個人やアプリケーションの責任を正確に反映したロールを定義することから始まります。

1. ロール管理：作成、変更、および削除

データベースプリンシパルを管理するための基本的なコマンドは、CREATE ROLE、ALTER ROLE、DROP ROLEを中心としています。

新しいロールの作成

ロールを作成する際には、特にログイン可能かどうか（LOGIN）や認証情報（PASSWORD）など、その属性を指定する必要があります。

基本的なログインユーザーの作成

接続にパスワードを必要とする標準的なユーザーロールを作成するには：

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

特定の属性を持つロールの作成

ロールには、その機能とシステム権限を定義するさまざまな属性を設定できます。

属性	説明
`LOGIN`	ロールがデータベースに接続できるようにします。
`SUPERUSER`	すべてのデータベース特権を付与します（慎重に使用）。
`CREATEROLE`	ロールが他のロールを作成、変更、削除できるようにします。
`CREATEDB`	ロールが新しいデータベースを作成できるようにします。
`REPLICATION`	ロールがストリーミングレプリケーションを開始できるようにします。
`INHERIT` / `NOINHERIT`	ロールがメンバーであるロールから特権を自動的に継承するかどうかを制御します（デフォルトは`INHERIT`）。

例：管理者ロール（スーパーユーザーではない）の作成

CREATE ROLE db_admin WITH
    LOGIN
    PASSWORD 'admin_secret'
    CREATEROLE
    CREATEDB
    VALID UNTIL '2025-01-01'; -- オプションの有効期限

既存のロールの変更

ALTER ROLEを使用して、既存のロールの属性を変更したり、パスワードを更新したりします。

-- 既存ユーザーのパスワードを変更
ALTER ROLE app_user WITH PASSWORD 'new_strong_password';

-- ログイン機能を無効にして（ユーザーをグループに変換）、ログイン不可にする
ALTER ROLE db_admin NOLOGIN;

-- ユーザーをロックアウト状態にする
ALTER ROLE old_employee NOLOGIN;

ロールの削除

ロールを削除する前に、そのロールがデータベースオブジェクト（テーブル、スキーマなど）を所有していないことを確認してください。所有している場合は、まずREASSIGN OWNEDを使用して所有権を移行する必要があります。

-- ロールが所有するすべてのオブジェクトの所有権を 'postgres' に再割り当て
REASSIGN OWNED BY old_employee TO postgres;

-- その後、ロールを削除
DROP ROLE old_employee;

警告: ロールの削除は元に戻せません。特に多くのオブジェクトを所有するロールに対しては注意して使用してください。

2. グループメンバーシップの管理

権限管理を簡素化するために、ロールはグループとして機能することがよくあります。50人の個々のユーザーに権限を付与する代わりに、1つのグループロールに権限を付与し、そのグループにユーザーをメンバーとして追加します。

グループロールの作成

グループは通常、LOGIN属性なしで作成されます。

CREATE ROLE data_analysts NOLOGIN;

グループメンバーシップの付与と取り消し

GRANTコマンドを使用してグループロールにメンバーを追加し、REVOKEを使用してメンバーを削除します。

-- ユーザー 'alice' およびユーザー 'bob' を 'data_analysts' グループに追加
GRANT data_analysts TO alice, bob;

-- 'bob' を 'data_analysts' グループから削除
REVOKE data_analysts FROM bob;

`WITH ADMIN OPTION`

メンバーシップを付与する際にWITH ADMIN OPTIONを含めると、受け取ったロールは、そのグループのメンバーシップを他のユーザーに付与したり、そのグループロールをDROPしたりできるようになります。

GRANT data_analysts TO supervisor WITH ADMIN OPTION;

3. オブジェクト権限（特権）の管理

権限は、ロールがどのデータベースオブジェクト（テーブル、ビュー、関数、スキーマなど）に対してどのようなアクションを実行できるかを定義します。これがデータベースセキュリティの核心です。

`GRANT`コマンドの構文

GRANT privilege_list ON object_type object_name TO role_name [WITH GRANT OPTION];

一般的な特権と例

テーブル権限

特権	許可されるアクション
`SELECT`	テーブルからデータを読み取る。
`INSERT`	新しい行を追加する。
`UPDATE`	既存の行を変更する。
`DELETE`	既存の行を削除する。
`TRUNCATE`	テーブルの内容をすべて消去する。
`REFERENCES`	外部キー制約を作成する。

例: 特定のテーブルへの読み取り専用アクセスを許可する。

GRANT SELECT ON TABLE production.orders TO data_analysts;

-- テーブルに対するすべてのDML操作を許可する
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE staging.temp_data TO app_user;

データベースおよびスキーマ権限

データベースおよびスキーマの権限は、環境構造を制御するために重要です。

オブジェクト	主要な権限	目的
DATABASE	`CONNECT`	データベースへの接続を許可する。
DATABASE	`CREATE`	新しいスキーマ、テーブルスペースなどを作成できるようにする。
SCHEMA	`USAGE`	スキーマ内のオブジェクトへのアクセスを許可する。
SCHEMA	`CREATE`	スキーマ内に新しいオブジェクトを作成できるようにする。

例：スキーマアクセス権の付与

ユーザーがapp_schema内のテーブルにアクセスする必要がある場合、そのスキーマに対してUSAGE権限を持っている必要があります。

GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_user;

シーケンスおよび関数の権限

シーケンスの使用（自動採番ID用）および関数の実行には、特定の権限が必要です。

-- シーケンスを進める権限をユーザーに付与する（INSERTに必要）
GRANT USAGE, SELECT ON SEQUENCE app_schema.user_id_seq TO app_user;

-- 特定のストアドプロシージャまたは関数の実行を許可する
GRANT EXECUTE ON FUNCTION audit_log_insert(text) TO app_user;

権限の取り消し

REVOKEコマンドを使用して、以前に付与された特定の権限を削除します。構文はGRANTと似ています。

-- orders テーブルへの新しいレコード挿入機能を無効にする
REVOKE INSERT ON TABLE production.orders FROM app_user;

-- 以前に付与されたすべての権限を取り消す（所有権は取り消されません）
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM data_analysts;

取り消しに関する注意: 特権がロールに付与され、そのロールが別のグループのメンバーである場合、特権を取り消しても直接付与された権限のみに影響します。ロールがグループメンバーシップを通じて特権を継承している場合、アクセス権は維持されます。

将来のオブジェクトへの権限の適用

まだ存在しないオブジェクトの権限を管理するには、ALTER DEFAULT PRIVILEGESを使用する必要があります。これは、アプリケーションが頻繁に新しいテーブルを作成するスキーマで不可欠です。

例：app_ownerロールがapp_schema内で作成するすべての将来のテーブルに対してapp_userがSELECTできるよう保証する：

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO app_user;

4. 現在の権限の表示

セキュリティ設定を監査するために、PostgreSQLはいくつかのツールとカタログビューを提供しています。

コマンド/ビュー	説明
`\du` (psql内)	すべてのロールとその属性を一覧表示します。
`\du+ role_name` (psql内)	詳細なロール属性とメンバーシップを表示します。
`\dp table_name` (psql内)	テーブルに付与されている権限（特権）を一覧表示します。
`pg_roles`	ロール定義を含むシステムカタログビュー。
`information_schema.table_privileges`	テーブルに対して付与された特権を示すビュー。

例：psql経由でテーブルの権限を確認する

=> \dp production.orders

ユーザー管理のためのセキュリティのベストプラクティス

最小権限の原則 (PoLP): ロールがその機能を実行するために必要とする最小限の権限のみを付与します。ALL PRIVILEGESやSUPERUSERは、絶対に必要な場合を除き、付与しないでください。
アプリケーションロールの分離: 人間のDBAロールとは別に、アプリケーション専用のロール（例：api_service_role）を使用します。アプリケーションには通常、DML権限（SELECT、INSERT、UPDATE、DELETE）のみが必要であり、DDL権限（CREATE、DROP）は不要です。
グループの広範な使用: 権限のセットを管理するために、ログインしないロール（グループ）を作成します（例：read_only_group、etl_writer_group）。個々のユーザーには、権限を個別に付与するのではなく、これらのグループを割り当てます。
デフォルトロールの使用を避ける: postgresスーパーユーザーロールを一般的なアプリケーションまたは開発タスクに使用しないでください。
認証の保護: 常に強力なパスワードを使用し、可能な限り、パスワードベースの認証の代わりにクライアント証明書認証または集中型ID管理ソリューションを活用します。

結論

ロールと権限の効果的な管理は、PostgreSQLセキュリティの基盤です。データベース管理者は、CREATE ROLE、ALTER ROLE、GRANT、およびREVOKEを習得することにより、きめ細かな制御を実装し、データベースにアクセスするすべてのユーザーまたはアプリケーションが必要なアクセス権だけを持ち、それ以上は持たないことを保証できます。最小権限の原則を一貫して適用し、グループロールを活用することは、長期的なメンテナンスを簡素化し、不正アクセスに対するデータベースの態勢を強化します。