DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

高度な PostgreSQL ロールベース アクセス制御: 行レベル セキュリティとポリシー管理

PostgreSQLの行レベルセキュリティをRBACポリシーと組み合わせて、テナント分離、所有権チェック、安全なアプリケーションアクセスを実現します。

高度なPostgreSQLロールベースアクセス制御：行レベルセキュリティとポリシー管理

PostgreSQLの行レベルセキュリティ（RLS）を使用すると、現在のロールとセッションコンテキストに基づいてデータベースが行をフィルタリングできます。アプリが複数のテナントを提供する場合や、管理者、マネージャー、一般ユーザーのアクセスが混在する場合、RLSはWHERE tenant_id = ...の見落としによるデータ漏洩を防ぐことができます。

RLSはアプリケーションの認可を置き換えるものではありません。接続が読み取りまたは書き込みできる行という最も機密性の高い境界に対して、データベースレベルのセーフティネットを提供します。

なぜ行レベルセキュリティなのか？

従来のアプローチの問題点

アプリケーション層でのフィルタリング:

# すべてのクエリでフィルタを忘れずに記述する必要がある
query = "SELECT * FROM documents WHERE tenant_id = %s"
results = db.execute(query, [current_user_tenant_id])

問題点:

  • フィルタを忘れやすい（セキュリティ脆弱性）
  • アプリケーション全体でコードが重複する
  • データベースへの直接アクセスを保護できない
  • 複雑な監査要件に対応できない

行レベルセキュリティの利点

  • 自動適用: ポリシーが透過的に適用される
  • 集中管理されたルール: セキュリティロジックを一箇所に集約
  • マルチテナント分離: SaaSアプリケーションに最適
  • 監査コンプライアンス: 組み込みのセキュリティ保証
  • パフォーマンス: 効率的なクエリ計画

基本的なRLS設定

ステップ1: テーブルでRLSを有効化

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    owner_id INTEGER NOT NULL,
    department VARCHAR(50),
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 行レベルセキュリティを有効化
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

ステップ2: ロールを作成

-- 異なるユーザーロールを作成
CREATE ROLE regular_user;
CREATE ROLE department_manager;
CREATE ROLE admin;

-- テーブルアクセス権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO regular_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO department_manager;
GRANT ALL ON documents TO admin;

ステップ3: ポリシーを作成

-- ポリシー: ユーザーは自分のドキュメントのみ表示可能
CREATE POLICY user_own_documents ON documents
    FOR SELECT
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- ポリシー: ユーザーは自分が所有するドキュメントのみ挿入可能
CREATE POLICY user_insert_own ON documents
    FOR INSERT
    TO regular_user
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- ポリシー: ユーザーは自分のドキュメントのみ更新可能
CREATE POLICY user_update_own ON documents
    FOR UPDATE
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER)
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

ステップ4: セッション変数を設定

アプリケーションコード内:

import psycopg2

# ロールとして接続
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="regular_user",
    password="password"
)

# ユーザーコンテキストを設定
cursor = conn.cursor()
cursor.execute("SELECT set_config('app.user_id', %s, false)", [str(current_user_id)])

# これでクエリは自動的にフィルタリングされる
cursor.execute("SELECT * FROM documents")
# owner_id = current_user_id のドキュメントのみ返される

マルチテナントSaaSアプリケーション

完全なマルチテナント設定

-- テナントテーブルを作成
CREATE TABLE tenants (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ユーザーテーブルを作成
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    tenant_id INTEGER REFERENCES tenants(id),
    role VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- アプリケーションデータテーブルを作成
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    customer_name VARCHAR(100),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

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

-- ポリシー: ユーザーは自分のテナントの注文のみ表示可能
CREATE POLICY tenant_isolation ON orders
    FOR ALL
    TO public
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::INTEGER);

アプリケーション統合

Djangoの例:

from django.db import connection

class TenantMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        if request.user.is_authenticated:
            with connection.cursor() as cursor:
                cursor.execute(
                    "SELECT set_config('app.tenant_id', %s, false)",
                    [str(request.user.tenant_id)]
                )
        
        response = self.get_response(request)
        return response

Node.jsの例:

const { Pool } = require('pg');
const pool = new Pool();

async function setTenantContext(tenantId) {
    const client = await pool.connect();
    try {
        await client.query('SELECT set_config($1, $2, false)', ['app.tenant_id', String(tenantId)]);
        return client;
    } catch (err) {
        client.release();
        throw err;
    }
}

// 使用例
app.use(async (req, res, next) => {
    if (req.user) {
        req.dbClient = await setTenantContext(req.user.tenantId);
    }
    next();
});

高度なポリシーパターン

1. 階層的アクセス制御

-- ユーザーは自分の部署のドキュメントと公開ドキュメントを表示可能
CREATE POLICY department_access ON documents
    FOR SELECT
    TO regular_user
    USING (
        department = current_setting('app.user_department', true)
        OR is_public = true
        OR owner_id = current_setting('app.user_id')::INTEGER
    );

2. 時間ベースのアクセス

CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    content TEXT,
    valid_from TIMESTAMP NOT NULL,
    valid_until TIMESTAMP NOT NULL
);

ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

-- ポリシー: ユーザーは有効なサブスクリプションのみ表示可能
CREATE POLICY active_subscriptions ON subscriptions
    FOR SELECT
    TO public
    USING (
        user_id = current_setting('app.user_id')::INTEGER
        AND NOW() BETWEEN valid_from AND valid_until
    );

3. 複雑なロジックを持つロールベースアクセス

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    owner_id INTEGER,
    team_id INTEGER,
    visibility VARCHAR(20) -- 'private', 'team', 'public'
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- 複雑な可視性ポリシー
CREATE POLICY project_visibility ON projects
    FOR SELECT
    TO public
    USING (
        CASE current_setting('app.user_role', true)
            WHEN 'admin' THEN true
            WHEN 'manager' THEN (
                team_id = current_setting('app.user_team_id')::INTEGER
                OR visibility = 'public'
            )
            ELSE (
                owner_id = current_setting('app.user_id')::INTEGER
                OR visibility = 'public'
            )
        END
    );

4. 共有アクセスリスト

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    owner_id INTEGER NOT NULL
);

CREATE TABLE document_shares (
    document_id INTEGER REFERENCES documents(id),
    shared_with_user_id INTEGER,
    permission VARCHAR(20), -- 'read', 'write'
    PRIMARY KEY (document_id, shared_with_user_id)
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- ポリシー: ユーザーは所有または共有されたドキュメントを表示可能
CREATE POLICY document_access ON documents
    FOR SELECT
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
        )
    );

-- ポリシー: ユーザーは所有または書き込み権限で共有されたドキュメントを変更可能
CREATE POLICY document_modify ON documents
    FOR UPDATE
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
              AND permission = 'write'
        )
    );

管理アクセスとRLS

テーブル所有者とスーパーユーザーは、強制しない限りRLSをバイパスできます。管理パスは後付けではなく、独立した設計上の決定として扱ってください。

オプション1: BYPASSRLSロール属性

CREATE ROLE admin_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE admin_user BYPASSRLS;

これは控えめに使用してください。BYPASSRLSは強力であり、通常のアプリケーションロールに付与すべきではありません。

オプション2: 管理者向けの許容ポリシー

-- 管理者ロール用の許容ポリシーを作成
CREATE POLICY admin_all_access ON documents
    FOR ALL
    TO admin
    USING (true)
    WITH CHECK (true);

オプション3: テーブル所有者にRLSを強制

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITYにより、テーブル所有者もポリシーに従うようになります。これはアプリケーションが所有するテーブルにとって通常より安全です。メンテナンス作業には、アドホックスクリプト内でRLSを無効にする代わりに、制御された別のロールを使用してください。

ポリシータイプ: USING vs WITH CHECK

USING句

既存の行の表示/変更可能性を制御します:

CREATE POLICY select_own ON documents
    FOR SELECT
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- ユーザーはowner_idが一致する行のみSELECT可能

WITH CHECK句

新しい行や更新された行の許可を制御します:

CREATE POLICY insert_own ON documents
    FOR INSERT
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- ユーザーはowner_idが一致する行のみINSERT可能

組み合わせポリシー

CREATE POLICY update_own ON documents
    FOR UPDATE
    USING (owner_id = current_setting('app.user_id')::INTEGER)
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

パフォーマンスに関する考慮事項

1. ポリシーカラムにインデックスを作成

-- ポリシーで使用されるカラムにインデックスを作成
CREATE INDEX idx_documents_owner ON documents(owner_id);
CREATE INDEX idx_documents_tenant ON documents(tenant_id);
CREATE INDEX idx_documents_department ON documents(department);

2. クエリ計画を分析

EXPLAIN ANALYZE
SELECT * FROM documents
WHERE title LIKE '%report%';

-- ポリシーフィルタが効率的に適用されているか確認

3. ポリシー内のサブクエリに注意

サブクエリは共有アクセスに適したモデルである場合がありますが、インデックスとテストが必要です。候補行ごとに別のテーブルを調査するポリシーは、コストが高くなる可能性があります。

インデックスがない場合のリスク:

CREATE POLICY slow_policy ON documents
    USING (
        id IN (
            SELECT document_id
            FROM shares
            WHERE user_id = current_setting('app.user_id')::INTEGER
        )
    );

サポートインデックスを使用するとより明確になることが多い:

CREATE POLICY faster_policy ON documents
    USING (
        EXISTS (
            SELECT 1 FROM shares 
            WHERE document_id = documents.id 
              AND user_id = current_setting('app.user_id')::INTEGER
        )
    );

shares(user_id, document_id)などのインデックスを作成し、EXPLAIN (ANALYZE, BUFFERS)で計画を確認してください。

監視と監査

アクティブなポリシーを表示

-- すべてのRLSポリシーを一覧表示
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
ORDER BY tablename, policyname;

ポリシー変更の監査

ポリシーDDLをマイグレーションとデータベース監査ログで追跡します。環境でイベントトリガーを使用する場合は、PostgreSQLのバージョンに対して慎重にテストし、CREATE POLICY、ALTER POLICY、DROP POLICYイベントを含めてください。テストされていないトリガースニペットをコンプライアンスの証拠として信頼しないでください。

ポリシーの有効性をテスト

-- テスト関数を作成
CREATE OR REPLACE FUNCTION test_user_access(
    test_user_id INTEGER,
    test_tenant_id INTEGER
)
RETURNS TABLE(document_id INTEGER, title VARCHAR) AS $$
BEGIN
    -- セッション変数を設定
    PERFORM set_config('app.user_id', test_user_id::text, false);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, false);
    
    -- アクセス可能なドキュメントを返す
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;

-- アクセスをテスト
SELECT * FROM test_user_access(123, 5);

トラブルシューティング

問題1: ポリシーが適用されない

RLSが有効か確認:

SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' AND tablename = 'documents';

無効な場合に有効化:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

問題2: セッション変数が設定されていない

現在の設定をデバッグ:

SHOW app.user_id;
SHOW app.tenant_id;

-- またはcurrent_settingを使用
SELECT current_setting('app.user_id', true);

問題3: パフォーマンスの低下

遅いポリシーを特定:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM documents WHERE title = 'test';

-- ポリシー条件を含む「Filter」操作を確認

解決策: ポリシーを簡素化するか、インデックスを追加する

問題4: 予期しないアクセス拒否

特定のユーザーとしてテスト:

SET ROLE regular_user;
SELECT set_config('app.user_id', '123', false);

SELECT * FROM documents;
-- そのユーザーが実際に表示できるものを表示

RESET ROLE;

ベストプラクティス

  1. 常にセッション変数を設定: current_setting()の値が確実に設定されるようにする
  2. ポリシーカラムにインデックスを作成: USING/WITH CHECKで使用されるすべてのカラムにインデックスを作成
  3. 徹底的にテスト: 異なるロールとシナリオでポリシーを検証
  4. ポリシーをシンプルに保つ: 複雑なロジックはパフォーマンスに影響
  5. EXPLAINを使用: RLSを有効にしてクエリ計画を分析
  6. ポリシーを文書化: 保守性のためにポリシーロジックにコメントを付ける
  7. 関心事を分離: SELECT、INSERT、UPDATE、DELETEに異なるポリシーを使用
  8. パフォーマンスを監視: 遅いクエリのアラートを設定

まとめ

最もリスクの高いテーブルに対するテナント分離という、1つのシンプルなポリシーから始めてください。set_configでセッションコンテキストを設定し、ポリシーで使用されるすべてのカラムにインデックスを作成し、実際のアプリケーションロールとして読み取りと書き込みをテストしてください。

基本的な境界が機能したら、製品が必要とする場合にのみ、所有権、マネージャー、または共有アクセスポリシーを追加してください。小さくテストされたポリシーは、権限モデル全体をエンコードしようとする1つの巧妙なルールよりも理解しやすいです。