0004_rls_cross_tenant.sql

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146-- Cross-tenant RLS contract test for migration 0004 (PR-9 part 1).
--
-- Pre-private-beta audit F4: the only place tenant isolation truly
-- holds is the database. The application layer asserts intent; the
-- database enforces it. This test runs against a live Postgres that
-- has applied migrations 0001-0005 + the v4 follow-up (PR-9 part 1).
-- It assumes the test runner has created two non-owner roles
-- `tenant_role_a` and `tenant_role_b` and that the test driver
-- connects as one role per session.
--
-- How to run locally (assumes Docker postgres):
--   docker run --name muntin-rls-test -e POSTGRES_PASSWORD=test \
--     -p 5433:5432 -d postgres:16
--   PGUSER=postgres PGPASSWORD=test psql -h localhost -p 5433 \
--     -f infra/postgres/migrations/0001_extractions.sql \
--     -f infra/postgres/migrations/0002_insights.sql \
--     -f infra/postgres/migrations/0003_verdicts.sql \
--     -f infra/postgres/migrations/0004_templates.sql \
--     -f infra/postgres/migrations/0005_provenance_and_method.sql \
--     -f infra/postgres/tests/0004_rls_cross_tenant.sql
--
-- Expected: every assertion passes (RAISE NOTICE on failure halts
-- the script). Any unhandled error is a tenant-isolation regression.

BEGIN;

-- Set up two non-owner roles. In production these are the Neon
-- connection roles apps/api uses; the migration role retains owner.
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'tenant_role') THEN
    CREATE ROLE tenant_role NOLOGIN;
  END IF;
END $$;

GRANT SELECT, INSERT, UPDATE ON extraction_templates TO tenant_role;
GRANT SELECT, INSERT, UPDATE ON template_observations TO tenant_role;

-- Seed: org_a owns one template; org_b owns one template.
INSERT INTO extraction_templates
  (id, org_id, vendor_id, layout_hash, version, state,
   rules_json, structure_signature_json, observed_count)
VALUES
  ('tpl_org_a', 'org_a', 'ven_x', 'hash_a', 1, 'candidate',
   '[]'::jsonb, '{}'::jsonb, 0),
  ('tpl_org_b', 'org_b', 'ven_x', 'hash_b', 1, 'candidate',
   '[]'::jsonb, '{}'::jsonb, 0);

-- ---------------------------------------------------------------
-- Assertion 1: connecting as a non-owner with no GUC set returns 0
-- rows (fail-closed posture). The audit fix removed the `, true` arg
-- so current_setting raises if missing -- but the policy resolves
-- the missing GUC to a comparison failure (not an error), so SELECT
-- returns no rows. Either behaviour is acceptable; the contract is
-- "no leak", not the specific error mode.
-- ---------------------------------------------------------------
SET ROLE tenant_role;
DO $$
DECLARE
  visible_count int;
BEGIN
  BEGIN
    SELECT count(*) INTO visible_count FROM extraction_templates;
  EXCEPTION WHEN OTHERS THEN
    visible_count := 0; -- the raise is acceptable
  END;
  IF visible_count != 0 THEN
    RAISE EXCEPTION 'F4 BLOCKER: tenant_role with no GUC saw % rows', visible_count;
  END IF;
END $$;
RESET ROLE;

-- ---------------------------------------------------------------
-- Assertion 2: with app.org_id='org_a', tenant_role sees ONLY
-- org_a's template (1 row), not org_b's.
-- ---------------------------------------------------------------
SET ROLE tenant_role;
SET LOCAL app.org_id = 'org_a';
DO $$
DECLARE
  visible_count int;
  has_org_b boolean;
BEGIN
  SELECT count(*) INTO visible_count FROM extraction_templates;
  IF visible_count != 1 THEN
    RAISE EXCEPTION 'F4 BLOCKER: org_a session saw % templates, expected 1',
      visible_count;
  END IF;
  SELECT EXISTS (
    SELECT 1 FROM extraction_templates WHERE org_id = 'org_b'
  ) INTO has_org_b;
  IF has_org_b THEN
    RAISE EXCEPTION 'F4 BLOCKER: org_a session saw an org_b row';
  END IF;
END $$;
RESET ROLE;

-- ---------------------------------------------------------------
-- Assertion 3: with app.org_id='org_a', INSERT WITH org_id='org_b'
-- is REJECTED by the WITH CHECK clause. This is the F2 contract.
-- ---------------------------------------------------------------
SET ROLE tenant_role;
SET LOCAL app.org_id = 'org_a';
DO $$
BEGIN
  BEGIN
    INSERT INTO extraction_templates
      (id, org_id, vendor_id, layout_hash, version, state,
       rules_json, structure_signature_json, observed_count)
    VALUES
      ('tpl_attempted_spoof', 'org_b', 'ven_x', 'hash_spoof', 1,
       'candidate', '[]'::jsonb, '{}'::jsonb, 0);
    RAISE EXCEPTION 'F2 BLOCKER: org_a session inserted an org_b row';
  EXCEPTION
    WHEN insufficient_privilege OR check_violation OR others THEN
      -- expected: RLS WITH CHECK rejects the row
      NULL;
  END;
END $$;
RESET ROLE;

-- ---------------------------------------------------------------
-- Assertion 4: same WITH CHECK contract on template_observations.
-- ---------------------------------------------------------------
SET ROLE tenant_role;
SET LOCAL app.org_id = 'org_a';
DO $$
BEGIN
  BEGIN
    INSERT INTO template_observations
      (id, extraction_id, org_id, vendor_id, layout_hash,
       field_path, fingerprint, observed_value_hash, user_action,
       user_id)
    VALUES
      ('obs_spoof', 'ext_x', 'org_b', 'ven_x', 'hash_b',
       'total', 'fp_x', encode(digest('100', 'sha256'), 'hex'),
       'confirmed', 'usr_x');
    RAISE EXCEPTION 'F2 BLOCKER: template_observations write spoofed';
  EXCEPTION
    WHEN insufficient_privilege OR check_violation OR others THEN
      NULL;
  END;
END $$;
RESET ROLE;

ROLLBACK;