RLS Policy SQL

The actual row-level-security policies the whole isolation claim defers to (10, 11, 20, 43, 60). RLS is defense-in-depth (the per-tenant database is the primary boundary) but it is what holds when application code forgets a filter, and it enforces the in-tenant boundaries that matter: classification and wargame cell.

Session context (set per short tx by the api middleware — 60, 40)

SET LOCAL app.tenant_id  = '…';   -- from JWT tid
SET LOCAL app.user_id    = '…';   -- from JWT sub
SET LOCAL app.role       = '…';   -- from JWT role
SET LOCAL app.clearance  = '…';   -- from JWT clearance: highest classification this user may read
SET LOCAL app.cell       = '…';   -- from JWT cell (nullable; wargame ONLY)
SET LOCAL app.is_faculty = 'f';   -- 't' only for the wargame umpire role

SET LOCAL scopes to the transaction so a pooled connection can't leak context between requests.

Classification and cell are two INDEPENDENT dimensions. The earlier draft conflated them (a missing cell implied "see everything"), which is the exact hole RLS exists to catch. They are now gated separately:

• Classification is gated on app.clearance (an ordered level the user holds), NOT on "is OPEN". A non-OPEN row is visible only if the user's clearance ≥ the row's classification.
• Cell is gated so that "no cell" never means "see everything": a row with no cell_id is shared within the tenant; a row with a cell_id is visible only to that cell (or to faculty).

-- classification ranking helper (immutable; OPEN < RESTRICTED < SECRET < TOPSECRET)
CREATE FUNCTION class_rank(c text) RETURNS int IMMUTABLE LANGUAGE sql AS $$
  SELECT CASE c WHEN 'OPEN' THEN 0 WHEN 'RESTRICTED' THEN 1
                WHEN 'SECRET' THEN 2 WHEN 'TOPSECRET' THEN 3 ELSE 99 END $$;

Enable + policies

ALTER TABLE observations ENABLE ROW LEVEL SECURITY;
ALTER TABLE observations FORCE ROW LEVEL SECURITY;   -- applies even to the table owner

-- ONE combined policy that AND-s three INDEPENDENT predicates:
--   (1) tenant   (2) clearance ≥ classification   (3) cell isolation
CREATE POLICY obs_access ON observations USING (
  tenant_id = current_setting('app.tenant_id')::uuid
  AND
  -- (2) CLEARANCE: user's clearance must dominate the row's classification.
  --     No fall-through to "OPEN only"; an unset clearance ranks as OPEN(0),
  --     so a no-clearance user sees ONLY OPEN rows — fail-closed.
  class_rank(classification) <= class_rank(coalesce(current_setting('app.clearance', true), 'OPEN'))
  AND
  -- (3) CELL: "no cell" is NOT "see everything". A NULL-cell row is tenant-shared;
  --     a cell-tagged row needs a matching cell, UNLESS the user is faculty (umpire).
  ( cell_id IS NULL
    OR cell_id = nullif(current_setting('app.cell', true), '')::uuid
    OR coalesce(current_setting('app.is_faculty', true), 'f') = 't' )
);

Why each branch is safe:

• Clearance fails closed: if app.clearance is unset, coalesce(…, 'OPEN') ranks it at 0, so the user sees only OPEN rows. A regular analyst with no clearance can never see a classified row at the data layer — the app engine (41) is no longer the only thing protecting classification.
• Cell fails closed: a missing app.cell matches only cell_id IS NULL rows (tenant-shared), never cell-tagged rows. Cell isolation no longer depends on guaranteeing every analyst has a cell.
• Faculty is an explicit, separate flag for the umpire — and note (decide per deployment) whether faculty clearance also dominates classified rows within an exercise; clearance still applies to faculty unless you set their app.clearance accordingly.

Apply the same tenant_id (+ classification/cell where the column exists) pattern to every table:

ALTER TABLE entities      ENABLE ROW LEVEL SECURITY; ALTER TABLE entities FORCE ROW LEVEL SECURITY;
CREATE POLICY ent_access  ON entities      USING (tenant_id = current_setting('app.tenant_id')::uuid);
ALTER TABLE events        ENABLE ROW LEVEL SECURITY; ALTER TABLE events FORCE ROW LEVEL SECURITY;
CREATE POLICY evt_access  ON events        USING (tenant_id = current_setting('app.tenant_id')::uuid);
ALTER TABLE cases         ENABLE ROW LEVEL SECURITY; ALTER TABLE cases FORCE ROW LEVEL SECURITY;
CREATE POLICY case_access ON cases         USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- … reports, sources, case_bindings, audit, etc.

Faculty bypass — visibility ONLY, never approval

The wargame umpire (faculty) sees every cell — handled by the is_faculty = 't' branch inside the single obs_access policy above (no separate, partly-redundant obs_faculty_view policy; one policy keeps the AND-semantics intact). Faculty's cell bypass does not grant an approval/commit bypass — that is enforced in the POLICY ENGINE (41), where the faculty role does not carry proposal:decide. Visibility (RLS) and approval (engine) are independent on purpose (the real bug class in product §7). Faculty cell-visibility still respects clearance unless app.clearance is set for them.

Writes

RLS USING governs read/update/delete visibility; add WITH CHECK so a row can't be written outside the tenant:

CREATE POLICY obs_insert ON observations FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

The app DB role (hard requirement)

RLS is silently void if the connection role can bypass it. The api connects as a dedicated, non-owner role created without BYPASSRLS and without SUPERUSER:

CREATE ROLE insigz_app LOGIN PASSWORD '…' NOBYPASSRLS NOSUPERUSER;
-- own the schema with a SEPARATE migration/owner role; insigz_app only gets table DML grants.

Do not run the app as the Cloud SQL default high-privilege user — it would defeat every policy above. FORCE ROW LEVEL SECURITY also covers the owner case, but the app must still use insigz_app.

Verification (CI — 43, 52)

• A test sets app.tenant_id to tenant A and asserts a query for tenant B's known row returns 0 rows.
• Clearance test: with app.clearance='OPEN' (or unset), a query for a SECRET row returns 0 rows; with app.clearance='SECRET' it returns the row.
• Cell test: with app.cell unset, a query for a cell-tagged row returns 0 rows (proves "no cell" ≠ "see everything"); with the matching cell it returns the row; with app.is_faculty='t' it returns rows from every cell.
• A migration test asserts every table with a tenant_id column has RLS enabled + forced and an access policy (fail CI otherwise).
• A startup assertion verifies the connection role has neither rolsuper nor rolbypassrls.

Gaps to close

• Generate the per-table policy migrations from a table manifest (avoid hand-drift).
• Finalize the classification level vocabulary + the JWT clearance claim mapping (which IdP attribute → which level).
• Decide whether the wargame umpire is cleared for classified rows within an exercise (set their app.clearance accordingly).