0015_rls_data_plane.sql

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106-- Postgres schema: Row-Level Security for the Neon data plane.
--
-- Neon data-plane Sprint 4. DEFENCE-IN-DEPTH ONLY: tenant isolation
-- is ALREADY enforced today by an explicit `org_id = $1` predicate on
-- every query in NeonExtractionsStore (Sprint 1), NeonDocumentsStore
-- (Sprint 2, org-scoped methods) and NeonVerdictsStore (Sprint 3) —
-- all three GREEN-audited. This migration adds RLS as a second wall
-- so a future query that forgets the predicate still cannot cross
-- tenants.
--
-- Pattern is copied verbatim from the proven, applied
-- 0004_templates.sql (extraction_templates / template_observations):
-- ENABLE + FORCE ROW LEVEL SECURITY, and a policy that is
-- **fail-closed** — `current_setting('app.org_id')` WITHOUT the
-- second `true` arg, so a session that never set the GUC ERRORS
-- rather than silently matching nothing (or everything). The
-- companion WITH CHECK clause stops a write landing under a
-- different org_id than the session.
--
-- Numbering: 0007 was skipped during v5; 0014 is RESERVED for the
-- planned workspace_grants migration (STATUS-v6.md "Migrations
-- forward-only"). This is 0015. Forward-only; no down-migration.
--
-- Re-run note: ENABLE/FORCE and CREATE POLICY are NOT idempotent
-- (no IF NOT EXISTS). Applying twice errors inside the BEGIN and
-- rolls back cleanly (no partial state) — apply exactly once, as a
-- forward-only migration. Same posture as 0004_templates.sql.
--
-- Apply with:
--   psql $DATABASE_URL -f infra/postgres/migrations/0015_rls_data_plane.sql
--
-- ============================================================
-- SCOPE — why these four tables and not documents / line_item_keys
-- ============================================================
-- INCLUDED (every access path goes through a set_config('app.org_id',
-- $1, true)-first sql.transaction, so fail-closed RLS is satisfied):
--   * extractions            — NeonExtractionsStore.listForOrg /
--                              getOwnershipForOrg (Sprint 1)
--   * verdicts               — NeonVerdictsStore.topForOrg +
--                              markExpected CTE (Sprint 3)
--   * anomaly_overrides      — written by the markExpected CTE
--                              (Sprint 3) within the scoped txn
--   * line_item_observations — read by the markExpected median CTE
--                              (Sprint 3) within the scoped txn
--
-- DEFERRED (intentionally NOT in this migration):
--   * documents — the retention reaper (listExpiredForReaper /
--     markR2Purged, Sprint 2) is a GLOBAL cross-org sweep that
--     deliberately sets NO GUC. Fail-closed FORCE RLS would make the
--     reaper ERROR and stop purging — a privacy/GDPR regression.
--     Enabling documents RLS requires the reaper to connect under a
--     BYPASSRLS Postgres role (an infra/role decision the operator
--     makes when provisioning DATABASE_URL). Tracked in
--     runbooks/neon-data-plane.md as an operator follow-up; the
--     explicit org predicate remains the documents boundary mean-
--     while.
--   * line_item_keys — no wired accessor in Sprints 1–3 (the
--     verdict-compute WRITER that populates it is unbuilt). Enable
--     RLS on it in the same sprint that wires that writer, so the
--     writer's GUC contract is introduced together with the policy
--     (avoids a fail-closed table with an un-GUC'd writer).

BEGIN;

ALTER TABLE extractions ENABLE ROW LEVEL SECURITY;
ALTER TABLE extractions FORCE ROW LEVEL SECURITY;
ALTER TABLE verdicts ENABLE ROW LEVEL SECURITY;
ALTER TABLE verdicts FORCE ROW LEVEL SECURITY;
ALTER TABLE anomaly_overrides ENABLE ROW LEVEL SECURITY;
ALTER TABLE anomaly_overrides FORCE ROW LEVEL SECURITY;
ALTER TABLE line_item_observations ENABLE ROW LEVEL SECURITY;
ALTER TABLE line_item_observations FORCE ROW LEVEL SECURITY;

CREATE POLICY extractions_org_isolation ON extractions
  USING (org_id = current_setting('app.org_id'))
  WITH CHECK (org_id = current_setting('app.org_id'));

CREATE POLICY verdicts_org_isolation ON verdicts
  USING (org_id = current_setting('app.org_id'))
  WITH CHECK (org_id = current_setting('app.org_id'));

CREATE POLICY anomaly_overrides_org_isolation ON anomaly_overrides
  USING (org_id = current_setting('app.org_id'))
  WITH CHECK (org_id = current_setting('app.org_id'));

-- line_item_observations has no org_id column (it hangs off
-- line_item_keys). Scope it transitively: a row is visible iff its
-- parent key belongs to the session org. Fail-closed via the same
-- bare current_setting(); the EXISTS subquery is org-filtered.
CREATE POLICY line_item_observations_org_isolation ON line_item_observations
  USING (
    EXISTS (
      SELECT 1 FROM line_item_keys k
      WHERE k.id = line_item_observations.line_item_key_id
        AND k.org_id = current_setting('app.org_id')
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM line_item_keys k
      WHERE k.id = line_item_observations.line_item_key_id
        AND k.org_id = current_setting('app.org_id')
    )
  );

COMMIT;