Enforcing MFA at the database: a restrictive aal2 rail, not the published one

• Status: accepted
• Date: 2026-08-12
• Depends on: 20260201-org-scoped-tenancy.md

Context

A session that has passed a second factor carries aal2 in its JWT; one that has passed only a password carries aal1. The claim is signed by the auth server and PostgREST verifies the signature itself, so a check written in RLS binds a client talking straight to PostgREST with the publishable key — not merely one that goes through a screen or a middleware. That property is why this decision puts the enforcement in the database rather than in a layout.

Supabase publishes an opt-in aal2 policy for exactly this. It is broken, in three directions, and each was reproduced against a running stack rather than reasoned about. The published shape is:

USING (array[(SELECT auth.jwt() ->> 'aal')] <@ (
  SELECT CASE WHEN count(id) > 0 THEN array['aal2'] ELSE array['aal1','aal2'] END
    FROM auth.mfa_factors WHERE (SELECT auth.uid()) = user_id AND status = 'verified'))

1. It raises 42501 for every caller. authenticated holds no privilege on auth.mfa_factors, so the subquery is denied before any row is considered. Loud, and therefore harmless.
2. The obvious fix makes it silently fail open. Add GRANT SELECT ON auth.mfa_factors TO authenticated and the table still has RLS enabled with no policy — so it is default-deny, count(id) is 0 for everyone including enrolled users, the CASE falls through to array['aal1','aal2'], and the policy accepts aal1. Verified: with the published policy and that grant installed, an enrolled user presenting an aal1 token read every row in the org and successfully INSERTed, UPDATEd and DELETEd.
3. A token with no aal claim is refused outright. array[NULL] <@ … is false, so the policy denies a caller who has never enrolled and whose token predates the claim. Found because it broke an unrelated suite — the audit trail's own "a write by a real member succeeds" assertion — which is how a control that fails the wrong way announces itself.

Defect 2 is the one this decision is shaped against. It passes every test anybody would naturally write: an unenrolled user is admitted, an enrolled user at aal2 is admitted, the policy exists, is restrictive, and names the right table. The only case that distinguishes a working rail from a fail-open is an enrolled user who did not use their factor.

Decision

The privileged read is one boolean, behind SECURITY DEFINER

private.mfa_is_required() is SECURITY DEFINER with SET search_path = ''. That is what gives it the read of auth.mfa_factors — no grant on that table is created, and none is needed. The function takes no parameter (identity comes from private.caller_id()), and it returns a boolean, not a set the caller compares against, so there is no empty-result branch that can mean "allow".

private.mfa_satisfied() is SECURITY INVOKER and holds no privilege: it reads the aal claim through private.caller_aal() and combines the two. Keeping the definer surface to the one function that genuinely needs it is deliberate — a definer wrapper around a predicate is a definer wrapper around every future edit to that predicate.

The policy is RESTRICTIVE, with no FOR clause

Two orthogonal axes that read as one, and conflating them is how this went wrong upstream:

• RESTRICTIVE ANDs onto the permissive set instead of ORing into it. The four per-operation policies still decide which rows a member may touch; this one can only subtract, so it cannot be defeated by adding another permissive policy.
• No FOR clause means every command. Splitting it per operation would be four copies of one predicate with four chances to omit one — and Supabase's other documentation page writes this same policy for update, which gates writes while leaving SELECT wide open.

Both USING and WITH CHECK are present: USING alone governs visibility and would let an aal1 session INSERT rows it then cannot see.

Scope is the worked vertical, and that is stated rather than implied

The rail is applied to public.notes — the vertical every new slice is copied from — and not to every table. Which data warrants a second factor is a product decision: a low-sensitivity lookup table blocked at aal1 is a support ticket, not a control. So the harness ships the correct shape, the proof that it binds, and a conformance register that grades accordingly — no row claims "MFA is used to authenticate users of data repositories" on the strength of one table.

What this deliberately does not do

It does not make MFA mandatory, because the platform cannot: GoTrue's MFA configuration carries no required field anywhere, so "every user has enrolled" is an application decision. What the rail does is make enrolment mean something — a user who holds a verified factor can no longer reach the data with a password alone, on any surface, including one nobody has written yet. The gap is recorded as the obligations row e8-mfa-enrolment-surface rather than papered over.

WebAuthn — the only phishing-resistant factor here — stays off. It exists and does produce aal2, but it is undocumented, absent from the Dashboard, experimental in the client library, challengeAndVerify is TOTP-only, and it is a billable add-on the CLI silently downgrades to false when cost is not confirmed. A config line claiming a factor the platform quietly disabled is worse than no line: it is a control that reads as protection and applies nothing. Recorded as e8-phishing-resistant-mfa.

Consequences

• A user who enrols a factor and then authenticates with a password alone reads zero rows from public.notes and can write none. That is the intended behaviour and it is a fail-closed one: an application that offers enrolment must also offer the challenge, or it will lock its own users out. Stated here because the failure is correct and still surprising.
• tools/check-rls-manifest.mjs closes the rail's shape: a GRANT on auth.mfa_factors anywhere in the migrations reds naming it as the fail-open, a permissive or per-operation MFA policy reds, a predicate missing WITH CHECK reds, and helpers defined with no policy using them reds — a rail no policy references enforces nothing while reading, to the next person, as MFA already in place.
• The same gate stopped counting restrictive policies toward the per-operation requirement, and so did supabase/tests/rls_structure.test.sql. A restrictive policy can only subtract, so one covering an operation grants nothing; counting it would have let a table whose permissive SELECT policy was deleted stay green on the strength of a policy that denies. The hole was unreachable until this migration seeded the tree's first restrictive policy.
• supabase/tests/mfa_aal2.test.sql is the runtime proof, and it was written to fail against the published policy. It does: installing the vendor shape plus the naive grant reds the enrolled-at-aal1 assertion with the two rows it should not have seen, and reds the INSERT refusal with caught: no exception.

Sources

• supabase/migrations/20260812000000_mfa_aal2.sql — the migration, with both vendor defects written out at the site that avoids them.
• supabase/tests/mfa_aal2.test.sql — the runtime proof and its vacuity controls.
• tools/auth-posture.json — the ten reviewed [auth.mfa] keys, closed both ways against supabase/config.toml.
• docs/compliance/essential-eight.md — how the rail is graded, and why enrolment being available is not the same as MFA being used.
• PostgreSQL row security: a RESTRICTIVE policy is ANDed with the permissive set [corpus: postgres/rls-force]
• Transaction-local GUCs: identity and claims travel in request.jwt.claims [corpus: postgres/guc-set-local]