FORCE ROW LEVEL SECURITY forks the audit_log hash chain

The short version

When a tamper-detection hash chain trigger SELECTs “the previous row,” that SELECT is itself subject to FORCE ROW LEVEL SECURITY. audit_log was meant to be a single chain across the whole table regardless of which role wrote to it, but because the trigger function’s own SELECT runs under the tenant context of whichever INSERT fired it, a school_admin’s INSERT only ever saw “the previous row for its own school,” not “the previous row overall.” The chain silently forked per school. No tampering had actually happened, but the verification function couldn’t tell a fork apart from tampering and reported broken anyway.

What it looks like

audit_log is a cross-tenant audit trail table, and FORCE ROW LEVEL SECURITY is set on it specifically to stop even the table owner from tampering with it.

-- 0001_enable_rls.sql (as of the 2026-05-29 introduction)
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log FORCE ROW LEVEL SECURITY;

The read policy only allows “own school, or system_admin”:

-- 0002_rls_policies.sql (same point in time)
DROP POLICY IF EXISTS audit_log_tenant_read ON audit_log;
CREATE POLICY audit_log_tenant_read ON audit_log FOR SELECT
  USING (
    school_id = current_setting('app.current_school_id', true)::uuid
    OR current_setting('app.current_user_role', true) = 'system_admin'
  );

On top of that sits a trigger that chains each INSERT’s row_hash to the previous row’s hash as prev_hash.

-- 0003_audit_trigger.sql (same point in time, audit_log_set_hash())
  -- Fetch the previous row's row_hash (null if this is the first row)
  SELECT row_hash INTO v_prev_hash
    FROM audit_log
   ORDER BY occurred_at DESC, id DESC
   LIMIT 1;

When school A’s school_admin context does an INSERT, this SELECT, bound by the audit_log_tenant_read policy, can only see “school A’s rows.” When school B’s school_admin then does an INSERT, the very same SELECT can only see “school B’s rows.” The result: the first chain link for school B connects not to “school A’s previous row” but to null or to one of school B’s own earlier rows, and what was meant to be one chain forks into as many branches as there are schools. audit_log_verify_chain(), which assumes a single chain across the whole table, reported broken even though no tampering had occurred at all.

Why

FORCE ROW LEVEL SECURITY is a setting that makes policies apply even to the table owner, closing off the path by which an owner could bypass RLS to tamper with data. That part is correct on its own. What got overlooked was that the SELECT the trigger function’s body executes is also evaluated under the tenant context of whichever INSERT called the function. The trigger intended to fetch “the previous row across the whole table” for each INSERT, but from RLS’s point of view this is simply “a role called school_admin ran SELECT ... FROM audit_log,” and the policy filters rows based on that role’s session variables (app.current_school_id), exactly as it would for any other query. There was a gap between the trigger’s implicit assumption that it was “privileged internal processing” and the actual behavior, where the caller’s policy applies to it unmodified.

Fixing it

The fix switches app.current_user_role to system_admin inside the trigger function’s body, just for the moment it reads the previous row, and always switches it back.

-- 0039_audit_log_global_chain.sql (audit_log_set_hash() after the fix)
  -- Read the global previous row as system_admin only while fetching it
  -- (so RLS doesn't scope it to one school). Always restore afterward.
  v_role := COALESCE(current_setting('app.current_user_role', true), '');
  BEGIN
    PERFORM set_config('app.current_user_role', 'system_admin', true);
    SELECT a.row_hash, a.occurred_at INTO v_prev_hash, v_prev_at
      FROM public.audit_log a
     ORDER BY a.occurred_at DESC, a.id DESC
     LIMIT 1;
    PERFORM set_config('app.current_user_role', v_role, true);
  EXCEPTION WHEN OTHERS THEN
    PERFORM set_config('app.current_user_role', v_role, true);
    RAISE;
  END;

The first option considered was a function-level SET clause, attaching SET app.current_user_role = ... to the function definition itself. Review caught a problem: on PostgreSQL 15 and later, only a superuser can specify a custom GUC in a function’s SET clause (permission denied to set parameter), and the production migrator role is not a superuser, so that path didn’t work. Instead, the fix switches the role temporarily inside the function body via set_config, right around the SELECT, and restores it afterward unconditionally, including on exception.

Because the function returns with the caller’s original role restored, the INSERT’s own WITH CHECK (which blocks actor spoofing) still runs after the BEFORE trigger, under the caller’s real role. A test confirms that an INSERT spoofing someone else’s user_id as actor_user_id is still rejected as before.

SECURITY DEFINER was also considered and rejected. It would have made the fix implicitly depend on the function owner’s (migrator’s) BYPASSRLS, and it would have grown the list of SECURITY DEFINER functions — both of which conflicted with the priority of not disturbing the existing invariants around functions like resolve_magic_link.

// audit-log-global-chain.test.ts:148-153
it("the trigger function is neither SECURITY DEFINER nor a function SET clause (it switches temporarily via set_config in its body)", async () => {
  const [fn] = await raw<{ prosecdef: boolean; proconfig: string[] | null }[]>`
    SELECT prosecdef, proconfig FROM pg_proc WHERE proname = 'audit_log_set_hash'
  `;
  expect(fn.prosecdef).toBe(false);
  expect((fn.proconfig ?? []).some((c) => c.startsWith("app."))).toBe(false);
});

The fork is verified to be gone with a test that has two schools write both alternately and concurrently.

// audit-log-global-chain.test.ts:73-86
it("chain does not fork when two schools' admins write alternately (A→B→A gives broken count 0)", async () => {
  const a1 = await insertAs(db, ctxA(), fx.schoolA, fx.userA, 1);
  const b1 = await insertAs(db, ctxB(), fx.schoolB, fx.userB, 2);
  await insertAs(db, ctxA(), fx.schoolA, fx.userA, 3);
  // ...
  // School B's first row links to school A's row (which B itself cannot see)
  expect(rows[1]?.prev_hash).toBe(rows[0]?.row_hash);
  expect(rows[2]?.prev_hash).toBe(rows[1]?.row_hash);
  expect(await brokenCount()).toBe(0);
});

Alongside this, the fix also pins down occurred_at as GREATEST(clock_timestamp(), previous row + 1µs) after acquiring an advisory lock, so concurrent INSERTs don’t scramble the chain’s order. If occurred_at were decided before taking the lock, a row committed while another was waiting on the lock could become visible later, so “lock-acquisition order” and “occurred_at order” could drift apart and produce false positives under concurrency. No existing rows are touched; the cutover is recorded as a single line with diff.event = hash_chain_global_cutover. Production rollout is still waiting on skill apply-migration’s human gate and had not been applied as of this writing.

Preventing a repeat

This bug slipped through because the RLS test suite treated “tenant isolation” and “does the hash chain survive RLS” as separate axes it wasn’t checking together. FORCE ROW LEVEL SECURITY guarantees that the owner cannot bypass policies; it says nothing about whether a SELECT running inside a trigger gets treated as a privileged operation. Because the two look similar at a glance, an RLS-application review alone will read as “correctly applied,” and without the separate step of tracing into the trigger function’s body to ask “under whose role does this SELECT get evaluated,” the gap goes unnoticed. The post-fix tests now pin down six patterns — alternating writes from two schools, 30 concurrent rows from two schools, rejection of actor spoofing, no role leakage, no clobbering of occurred_at, and not using SECURITY DEFINER — so that if anyone later touches this function and removes the context switch around the SELECT, these tests fail.