PostgreSQLユーザー・権限管理 - セキュアなアクセス制御の実装

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 基本構文
CREATE ROLE ロール名 [ WITH オプション ];

-- ログイン可能なユーザーを作成
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password_123';

-- グループロールを作成（ログイン不可）
CREATE ROLE developers;

-- 複数の属性を持つロールを作成
CREATE ROLE admin_user WITH 
    LOGIN 
    PASSWORD 'admin_secure_456'
    CREATEDB 
    CREATEROLE
    VALID UNTIL '2027-12-31';

1
2
3

-- 以下の2つは同じ意味
CREATE USER webapp WITH PASSWORD 'password123';
CREATE ROLE webapp WITH LOGIN PASSWORD 'password123';

1
2
3
4
5
6
7
8

-- psqlでロール一覧を確認
\du

-- システムカタログから確認
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;

1
2
3
4
5
6

      rolname      | rolsuper | rolcreatedb | rolcreaterole | rolcanlogin
-------------------+----------+-------------+---------------+-------------
 admin_user        | f        | t           | t             | t
 app_user          | f        | f           | f             | t
 developers        | f        | f           | f             | f
 postgres          | t        | t           | t             | t

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- パスワードを変更
ALTER ROLE app_user WITH PASSWORD 'new_secure_password';

-- 属性を追加
ALTER ROLE app_user WITH CREATEDB;

-- 属性を削除
ALTER ROLE app_user WITH NOCREATEDB;

-- 接続数制限を設定
ALTER ROLE app_user WITH CONNECTION LIMIT 10;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- グループロールを作成
CREATE ROLE readonly_group;
CREATE ROLE readwrite_group;

-- ユーザーをグループに追加
GRANT readonly_group TO app_user;
GRANT readwrite_group TO admin_user;

-- グループに別のグループを追加（階層構造）
GRANT readonly_group TO readwrite_group;

1
2
3
4
5
6
7
8

-- ADMIN OPTION付きで付与（メンバーシップを他者に付与可能）
GRANT developers TO senior_dev WITH ADMIN OPTION;

-- INHERIT オプションの制御
GRANT developers TO junior_dev WITH INHERIT FALSE;

-- SET オプションの制御（SET ROLEで切り替え可能かどうか）
GRANT developers TO contractor WITH SET FALSE;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 特定のテーブルにSELECT権限を付与
GRANT SELECT ON employees TO app_user;

-- 複数の権限を同時に付与
GRANT SELECT, INSERT, UPDATE ON employees TO app_user;

-- すべての権限を付与
GRANT ALL PRIVILEGES ON employees TO admin_user;

-- 全ユーザー（PUBLIC）に権限を付与
GRANT SELECT ON public_notices TO PUBLIC;

-- スキーマ内の全テーブルに権限を付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 従業員テーブルを作成
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    salary NUMERIC(10, 2),
    department_id INTEGER
);

-- 給与カラムを除いてSELECT権限を付与
GRANT SELECT (id, name, email, department_id) ON employees TO general_user;

-- 特定のカラムのみUPDATE権限を付与
GRANT UPDATE (email) ON employees TO general_user;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 特定の権限を剥奪
REVOKE INSERT ON employees FROM app_user;

-- すべての権限を剥奪
REVOKE ALL PRIVILEGES ON employees FROM app_user;

-- PUBLICからの権限を剥奪
REVOKE ALL ON employees FROM PUBLIC;

-- スキーマ内の全テーブルから権限を剥奪
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM readonly_group;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- psqlで権限を確認
\dp employees

-- システムカタログから確認
SELECT 
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.role_table_grants
WHERE table_name = 'employees'
ORDER BY grantee, privilege_type;

1
2
3
4
5
6

                            Access privileges
 Schema |   Name    | Type  |     Access privileges     
--------+-----------+-------+---------------------------
 public | employees | table | postgres=arwdDxtm/postgres+
        |           |       | app_user=arw/postgres     +
        |           |       | readonly_group=r/postgres

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 特定のスキーマで作成される全テーブルにデフォルト権限を設定
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_group;

-- 特定のロールが作成するオブジェクトに対してデフォルト権限を設定
ALTER DEFAULT PRIVILEGES FOR ROLE admin_user IN SCHEMA public
GRANT SELECT, INSERT, UPDATE ON TABLES TO readwrite_group;

-- シーケンスに対するデフォルト権限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_user;

-- 関数に対するデフォルト権限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT EXECUTE ON FUNCTIONS TO app_user;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- スキーマを作成
CREATE SCHEMA sales;
CREATE SCHEMA hr;
CREATE SCHEMA analytics;

-- スキーマの所有者を指定して作成
CREATE SCHEMA marketing AUTHORIZATION marketing_admin;

-- スキーマへのUSAGE権限を付与（スキーマ内のオブジェクトにアクセスするために必要）
GRANT USAGE ON SCHEMA sales TO sales_team;

-- スキーマ内でオブジェクトを作成する権限
GRANT CREATE ON SCHEMA sales TO sales_admin;

-- スキーマ内の全テーブルへの権限
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_team;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 環境分離パターン
CREATE SCHEMA production;
CREATE SCHEMA staging;
CREATE SCHEMA development;

-- ロールごとにアクセス可能なスキーマを制御
GRANT USAGE ON SCHEMA production TO production_app;
GRANT USAGE ON SCHEMA staging TO staging_app, developers;
GRANT USAGE ON SCHEMA development TO developers;

-- 機能別分離パターン
CREATE SCHEMA core;      -- コアビジネスロジック
CREATE SCHEMA reporting; -- レポーティング用
CREATE SCHEMA audit;     -- 監査ログ用

-- レポーティングチームには読み取り専用
GRANT USAGE ON SCHEMA reporting TO reporting_team;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO reporting_team;

-- 監査ログは特定の管理者のみ
GRANT USAGE ON SCHEMA audit TO audit_admin;
GRANT SELECT ON ALL TABLES IN SCHEMA audit TO audit_admin;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 現在のsearch_pathを確認
SHOW search_path;

-- セッション単位で設定
SET search_path TO myschema, public;

-- ロールのデフォルトsearch_pathを設定
ALTER ROLE app_user SET search_path TO app_schema, public;

-- データベースのデフォルトsearch_pathを設定
ALTER DATABASE mydb SET search_path TO app_schema, public;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- サンプルテーブルを作成
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    owner_name TEXT NOT NULL,
    department TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- テストデータを挿入
INSERT INTO documents (title, content, owner_name, department) VALUES
    ('営業レポート', '2026年1月の営業成績...', 'tanaka', 'sales'),
    ('技術仕様書', 'システム設計について...', 'suzuki', 'engineering'),
    ('人事評価', '従業員評価基準...', 'yamada', 'hr'),
    ('予算計画', '来期の予算配分...', 'tanaka', 'sales');

-- RLSを有効化
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- ポリシーがない状態では、一般ユーザーは何も見えない（デフォルト拒否）
-- テーブル所有者とスーパーユーザーはRLSをバイパスする

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 自分が作成したドキュメントのみ参照可能
CREATE POLICY own_documents_select
ON documents
FOR SELECT
USING (owner_name = current_user);

-- 自分が作成したドキュメントのみ更新可能
CREATE POLICY own_documents_update
ON documents
FOR UPDATE
USING (owner_name = current_user)
WITH CHECK (owner_name = current_user);

-- 自分の部署のドキュメントを参照可能
CREATE POLICY department_documents_select
ON documents
FOR SELECT
TO sales_team
USING (department = 'sales');

-- 管理者はすべてのドキュメントにアクセス可能
CREATE POLICY admin_all_documents
ON documents
FOR ALL
TO admin_role
USING (true)
WITH CHECK (true);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 許可型ポリシー（デフォルト）
-- 複数のポリシーがORで結合される
CREATE POLICY permissive_policy
ON documents
AS PERMISSIVE
FOR SELECT
USING (owner_name = current_user);

-- 制限型ポリシー
-- 許可型ポリシーに加えて、この条件もANDで満たす必要がある
CREATE POLICY restrictive_policy
ON documents
AS RESTRICTIVE
FOR SELECT
USING (department != 'confidential');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

-- テナント管理テーブル
CREATE TABLE tenants (
    tenant_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_name VARCHAR(255) NOT NULL
);

-- ユーザーとテナントの紐付け
CREATE TABLE user_tenants (
    user_name TEXT NOT NULL,
    tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
    PRIMARY KEY (user_name, tenant_id)
);

-- テナントごとのデータテーブル
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
    customer_name VARCHAR(255) NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE
);

-- RLSを有効化
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 現在のユーザーが所属するテナントのデータのみアクセス可能
CREATE POLICY tenant_isolation_policy
ON orders
FOR ALL
USING (
    tenant_id IN (
        SELECT tenant_id 
        FROM user_tenants 
        WHERE user_name = current_user
    )
)
WITH CHECK (
    tenant_id IN (
        SELECT tenant_id 
        FROM user_tenants 
        WHERE user_name = current_user
    )
);

-- テナントデータを挿入する際、tenant_idを強制的に設定
CREATE POLICY insert_with_tenant
ON orders
FOR INSERT
WITH CHECK (
    tenant_id IN (
        SELECT tenant_id 
        FROM user_tenants 
        WHERE user_name = current_user
    )
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- テーブル所有者にもRLSを強制する
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

-- BYPASSRLS属性を持つロールを作成（監査用など）
CREATE ROLE auditor WITH LOGIN PASSWORD 'audit_pass' BYPASSRLS;

-- セッションレベルでRLS違反時にエラーを発生させる
SET row_security = off;
-- RLSが有効なテーブルにアクセスするとエラーになる
-- （バックアップ時にデータが欠落しないようにするため）

1
2
3

# pg_hba.confの場所を確認
psql -c "SHOW hba_file;"
# 通常は /var/lib/pgsql/data/pg_hba.conf または /etc/postgresql/<version>/main/pg_hba.conf

1
2
3
4

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# PostgreSQL pg_hba.conf 設定例

# ローカル接続: OSユーザー認証（postgresユーザー用）
local   all             postgres                                peer

# ローカル接続: アプリケーション用
local   myapp_db        myapp_user                              scram-sha-256

# ローカルホストからのTCP接続
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

# 特定のIPアドレスからの接続を拒否
host    all             all             192.168.1.100/32        reject

# 開発チームからの接続（特定のネットワーク）
host    development     developers      192.168.10.0/24         scram-sha-256

# アプリケーションサーバーからの接続
host    production      webapp          10.0.1.0/24             scram-sha-256

# レプリケーション接続
host    replication     replicator      192.168.1.20/32         scram-sha-256

# SSL必須の接続（外部からのアクセス）
hostssl all             all             0.0.0.0/0               scram-sha-256

1
2

-- psqlから設定をリロード
SELECT pg_reload_conf();

1
2
3
4
5

# systemdを使用している場合
sudo systemctl reload postgresql

# pg_ctlを使用する場合
pg_ctl reload -D /var/lib/pgsql/data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT 
    line_number,
    type,
    database,
    user_name,
    address,
    auth_method,
    error
FROM pg_hba_file_rules
ORDER BY line_number;

1
2
3
4
5
6
7

-- 悪い例: すべての権限を付与
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

-- 良い例: 必要な権限のみを付与
GRANT SELECT ON customers TO app_user;
GRANT SELECT, INSERT ON orders TO app_user;
GRANT SELECT, UPDATE (status) ON orders TO app_user;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 基本ロールを作成
CREATE ROLE db_read;
CREATE ROLE db_write;
CREATE ROLE db_admin;

-- 権限を付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_write;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;

-- 継承関係を設定
GRANT db_read TO db_write;
GRANT db_write TO db_admin;

-- ユーザーにロールを割り当て
GRANT db_read TO report_user;
GRANT db_write TO app_user;
GRANT db_admin TO admin_user;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- アプリケーション用の接続ロール
CREATE ROLE webapp_connection WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE webapp_connection SET statement_timeout = '30s';
ALTER ROLE webapp_connection SET lock_timeout = '10s';
ALTER ROLE webapp_connection CONNECTION LIMIT 100;

-- 読み取り専用のレポーティング用ロール
CREATE ROLE report_connection WITH LOGIN PASSWORD 'report_password';
ALTER ROLE report_connection SET default_transaction_read_only = on;
ALTER ROLE report_connection SET statement_timeout = '5min';

-- マイグレーション用ロール（DDL権限を持つ）
CREATE ROLE migration_user WITH LOGIN PASSWORD 'migration_password';
GRANT CREATE ON SCHEMA public TO migration_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO migration_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO migration_user;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

-- スーパーユーザーの一覧
SELECT rolname FROM pg_roles WHERE rolsuper = true;

-- CREATEROLE権限を持つユーザー
SELECT rolname FROM pg_roles WHERE rolcreaterole = true;

-- パスワードが設定されていないログイン可能なロール
SELECT rolname 
FROM pg_roles 
WHERE rolcanlogin = true 
  AND rolpassword IS NULL;

-- 各テーブルの権限を確認
SELECT 
    schemaname,
    tablename,
    tableowner,
    array_agg(DISTINCT privilege_type) as privileges,
    array_agg(DISTINCT grantee) as grantees
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
GROUP BY schemaname, tablename, tableowner
ORDER BY tablename;

-- RLSが有効なテーブルの確認
SELECT 
    schemaname,
    tablename,
    rowsecurity,
    forcerowsecurity
FROM pg_tables
WHERE rowsecurity = true;

-- ポリシーの一覧
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
ORDER BY schemaname, tablename, policyname;