Postgres 行レベルセキュリティの地雷

-- title に索引があってもフルテーブルスキャンになる
CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND title ILIKE '%search%');

-- LEAKPROOF 関数を使うか、複雑なロジックをポリシーの外に移す
CREATE OR REPLACE FUNCTION safe_ilike(text, text)
RETURNS boolean
LANGUAGE sql
LEAKPROOF
AS $$ SELECT $1 ILIKE $2 $$;

CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND safe_ilike(title, '%search%'));

CREATE POLICY complex_access ON orders
USING (
  EXISTS (
    SELECT 1 FROM user_permissions up
    JOIN departments d ON up.dept_id = d.id
    WHERE up.user_id = current_user_id()
    AND d.region = orders.region
  )
);

-- 複雑さを LEAKPROOF 関数に移す
CREATE OR REPLACE FUNCTION user_has_region_access(region_name text)
RETURNS boolean
LANGUAGE sql
LEAKPROOF
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_permissions up
    JOIN departments d ON up.dept_id = d.id
    WHERE up.user_id = current_user_id()
    AND d.region = region_name
  );
$$;

CREATE POLICY simple_access ON orders
USING (user_has_region_access(region));

-- ポリシーで使うカラムには必ず索引を張る
CREATE INDEX ON orders(tenant_id);
CREATE INDEX ON orders(owner_id);
CREATE INDEX ON orders(tenant_id, owner_id); -- AND 条件には複合索引

-- テーブルオーナーに対しても RLS を強制
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE sensitive_data FORCE ROW LEVEL SECURITY;

-- アプリケーションロールに BYPASSRLS を絶対に付与しない
-- 専用の非スーパーユーザーアカウントでテストする

-- 適切なテストユーザーを作る
CREATE ROLE test_user;
GRANT app_user TO test_user;

-- 実アプリのユーザーとしてテスト
SET ROLE test_user;
SET app.tenant_id = 'test-tenant-id';
-- ここでテストを実行
RESET ROLE;

-- スーパーユーザーが作成 — すべての RLS ポリシーを迂回！
CREATE VIEW all_patient_data AS
SELECT * FROM patients;

-- Postgres 15+: SECURITY INVOKER を使う
CREATE VIEW patient_data
WITH (security_invoker = true)
AS SELECT * FROM patients;

-- 古いバージョン: SECURITY DEFINER 内で明示的に RLS を確認
CREATE OR REPLACE FUNCTION get_patient_data()
RETURNS TABLE(id uuid, name text, doctor_id uuid)
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
BEGIN
  -- SECURITY DEFINER 関数内で RLS を明示的にチェック
  IF NOT row_security_active('patients') THEN
    RAISE EXCEPTION 'Row security must be active';
  END IF;

  RETURN QUERY SELECT p.id, p.name, p.doctor_id FROM patients p;
END;
$$ LANGUAGE plpgsql;

-- タイミングが、禁じられた患者の存在を漏らす
SELECT COUNT(*) FROM patients
WHERE condition = 'rare_disease'
AND tenant_id = current_setting('app.tenant_id')::uuid;

-- テーブルオーナーは依然としてすべてを見られる！
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- オーナーを含めすべてに RLS を強制
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- ユーザーが見えないデータを INSERT できてしまう！
CREATE POLICY tenant_data ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- PgBouncer 下では役に立たない — すべての接続が同一ユーザー
CREATE POLICY user_data ON orders
USING (owner_id = current_user);

-- アプリ制御のセッション変数を使う
-- アプリがトランザクションごとに設定:
SET app.user_id = 'user-uuid';
SET app.tenant_id = 'tenant-uuid';

-- ポリシーはセッションから読む:
CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- クライアントが直接 app.* を設定するのを防ぐ
REVOKE ALL ON SCHEMA pg_catalog FROM app_user;
-- 信頼できるサーバーコードだけが設定できるようにする

-- 顧客が存在しても、この INSERT は失敗する
INSERT INTO orders (customer_id, tenant_id)
VALUES ('existing-customer-id', 'my-tenant');
-- ERROR: insert or update on table "orders" violates foreign key constraint

-- 親テーブルに FK チェック用の SELECT ポリシーが必要
CREATE POLICY customer_fk_visibility ON customers
FOR SELECT
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- email がどのテナントにあっても存在を露呈する
CREATE UNIQUE INDEX users_email_unique ON users(email);
-- 他テナントでも「duplicate key」で INSERT が失敗する

-- ユニーク性をテナントにスコープする
CREATE UNIQUE INDEX users_email_per_tenant
ON users(tenant_id, lower(email));

-- 一時的に RLS を無効化してテスト
SET row_security = off;
-- クエリを実行して RLS が原因か確かめる
SET row_security = on;

-- またはポリシーが有効か確認
SELECT row_security_active('table_name');

-- ユーザーは自分のレコードの SSN を見られる
SELECT * FROM users WHERE tenant_id = current_setting('app.tenant_id')::uuid;

-- 選択肢 1: カラム権限
REVOKE SELECT (ssn, salary) ON users FROM app_user;

-- 選択肢 2: セキュアなビュー
CREATE VIEW users_safe AS
SELECT id, name, email,
       CASE WHEN has_role('hr_role')
            THEN ssn
            ELSE 'XXX-XX-' || right(ssn, 4)
       END as ssn_masked
FROM users;

-- マテリアライズドビューが RLS を迂回する
CREATE MATERIALIZED VIEW order_summary AS
SELECT * FROM orders; -- 全テナントのデータを含む！

-- バックグラウンドジョブが全部エクスポートする
COPY (SELECT * FROM orders) TO '/tmp/backup.csv';

-- マテリアライズドビュー内でフィルタする
CREATE MATERIALIZED VIEW tenant_order_summary AS
SELECT tenant_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY tenant_id;

-- RLS を意識したエクスポートを使う
COPY (
  SELECT * FROM orders
  WHERE tenant_id = 'specific-tenant'
) TO '/tmp/tenant_backup.csv';

-- これらは OR で合成 — どちらかが真ならアクセスが通る
CREATE POLICY user_own_data ON orders
USING (owner_id = current_user_id());

CREATE POLICY admin_all_data ON orders
USING (has_role('admin')); -- まずい、広すぎ！

-- 選択肢 1: restrictive ポリシーを使う (AND で合成)
CREATE POLICY tenant_restriction ON orders
AS RESTRICTIVE
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- 選択肢 2: ロジックを 1 つのポリシーに統合
CREATE POLICY combined_access ON orders
USING (
  tenant_id = current_setting('app.tenant_id')::uuid
  AND (
    owner_id = current_user_id()
    OR has_role('tenant_admin')
  )
);