PostgreSQL 本格入門

-- 検証用テーブルの作成
CREATE TABLE saas_users (
    id SERIAL PRIMARY KEY,
    tenant varchar,
    name TEXT,
    email TEXT,
    org varchar,
    date TEXT
);

-- データの大量投入（テナント10種、組織10種の計10万件）
\copy saas_users FROM 'sample_data.csv' with (FORMAT csv, HEADER);

-- rls 有効化 (テーブル所有者を含める）
ALTER TABLE saas_users enable row level security;
ALTER TABLE saas_users force row level security;

-- index: 単一バージョン
CREATE INDEX idx_org ON saas_users (org);
CREATE INDEX idx_tenant ON saas_users (tenant);
-- index: 複合バージョン
CREATE INDEX idx_tenant_org ON saas_users (tenant, org);
CREATE INDEX idx_org_tenant ON saas_users (org, tenant); -- 最も利用された

-- tenant policy
CREATE POLICY  tenant_isolation_policy ON saas_users
    USING (
        (tenant = current_setting('app.current_tenant_id'::text))
    );

-- 1. orgの型がvarchar, text 型の場合
CREATE POLICY org_access_policy ON saas_users  AS RESTRICTIVE 
    USING (
        org = ANY(string_to_array(current_setting('app.allowed_orgs'), ','))
    );

-- 2. orgの型がbpchar 型の場合
CREATE POLICY org_access_policy ON saas_users AS RESTRICTIVE 
    USING (
        org = ANY(cast(string_to_array(current_setting('app.allowed_orgs'), ',') as bpchar[]))
    );

-- 3. サブクエリ (Claude3の初期案)
CREATE POLICY org_access_policy ON saas_users AS RESTRICTIVE 
    USING (
        EXISTS (
            SELECT 1
            FROM unnest(string_to_array(current_setting('app.allowed_orgs'), ',')) allowed_org
            WHERE saas_users.org = allowed_org
        )
    );

-- セッション変数
SET app.current_tenant_id = 'current_tenant';
SET app.allowed_orgs = 'org1,org3';

-- bypass権限のないユーザで実行
EXPLAIN ANALYZE select * from saas_users;

-- べき等性担保
DROP POLICY IF EXISTS org_access_policy ON saas_users;
DROP POLICY IF EXISTS tenant_isolation_policy ON saas_users;
DROP INDEX IF EXISTS idx_org;
DROP INDEX IF EXISTS idx_tenant;
DROP INDEX IF EXISTS idx_tenant_org;
DROP INDEX IF EXISTS idx_org_tenant;
DROP TABLE IF EXISTS saas_users;

-- **************************************
-- 0. 事前準備
-- **************************************
\echo '■ 0. テスト準備開始'
-- テスト用のテーブルを作成
CREATE TABLE test_table (
    id serial PRIMARY KEY,
    title text,
    content text,
    org_codes text[]
);

-- 'テーブルにデータを挿入';
INSERT INTO test_table (title, content, org_codes) VALUES 
    ('パターン0', '単一組織', ARRAY['org_0']),
    ('パターン1', '単一組織', ARRAY['org_1']),
    ('パターン2', '単一組織', ARRAY['org_2']),
    ('パターン3', '複数組織(2)', ARRAY['org_0', 'org_1']),
    ('パターン4', '複数組織(2)', ARRAY['org_0', 'org_2']),
    ('パターン5', '複数組織(2)', ARRAY['org_1', 'org_2']),
    ('パターン6', '複数組織(3)', ARRAY['org_0', 'org_1', 'org_2']);

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

-- ポリシーを設定
-- Policy: レコードが複数の組織を持つテーブル （単一ポリシーのため、RESTRICTIVEは除去）
CREATE POLICY test_policy on test_table
    FOR ALL 
    USING ( 
        string_to_array(current_setting('app.session_org_code'), ',') @> org_codes
    );

-- テスト用のロールを作成
CREATE ROLE test_user;
GRANT SELECT ON test_table TO test_user;
\echo ''

-- **************************************
-- 1. テスト開始
-- **************************************
\echo '■ 1. テスト開始'
-- スーパーユーザーとして全データを確認
\echo '⇒ [DB 確認]: スーパーユーザでのテーブルフルスキャン';
SELECT * FROM test_table;

-- test_userロールに切り替え
\echo '⇒ [ロール切り替え]: セッション変数未定義のため error';
SET ROLE test_user;
SELECT * FROM test_table;   -- error になる
\echo ''

-- case 1: 単一権限ユーザ
\echo '⇒ [case1: 単一権限] org_0 を持つ場合';
SET app.session_org_code = 'org_0';
SELECT * FROM test_table;

-- case 2: 複数権限ユーザ
\echo '⇒ [case2: 複数権限] org_0,org_1 を持つ場合';
SET app.session_org_code = 'org_0,org_1';
SELECT * FROM test_table;

-- case3: 全権限ユーザ
\echo '⇒ [case3: 全権限] org_0,org_1,org_2 を持つ場合';
SET app.session_org_code = 'org_0,org_1,org_2';
SELECT * FROM test_table;
\echo ''

-- **************************************
-- 2. 事後処理
-- **************************************
\echo '■ 2. 事後処理'
RESET ROLE; -- スーパーユーザに戻す

DROP TABLE test_table;
DROP ROLE test_user;

cat test-compare.sql | docker exec -i postgres_test psql -U testuser -d testdb