MFA Your UI Can't Bypass: Enforcing TOTP in Postgres with Supabase

In the last post I described how our 12-person company replaced its SaaS stack with one home-grown app, DolphinChat, and promised the technical deep-dives. Here's the first one: adding TOTP two-factor authentication — the authenticator-app kind — with a twist that I think is the actually interesting part: the database enforces it, not the app.

Two decisions shaped everything. First, MFA is per-user and admin-assigned: I flag who must use it from an admin screen, rather than flipping one global switch. Second, enforcement is server-side from day one. Both decisions turned out to have consequences I didn't fully anticipate, which is what makes them worth writing about.

A UI gate is a suggestion

Here's the uncomfortable truth about most MFA implementations in small apps: they're a login-screen decoration. The app shows a 6-digit prompt, the user types it, the app proceeds. But your API is not your app. Anyone holding a valid password and your API URL can skip the app entirely and talk to the backend directly — and with Supabase, the "API" is PostgREST sitting in front of your whole database. If the only thing standing between a stolen password and your data is a Flutter widget, you don't have MFA. You have MFA theater.

So the requirement I set was: **a password-only session of a flagged user must get nothing from the database** — not via the app, not via curl, not via any client anyone writes later.

The building blocks

We were lucky in our starting position. A month earlier we'd migrated the app from a service-key-plus-custom-login arrangement to proper Supabase Auth with row-level security (that migration is its own future post). Two pieces of that groundwork made the MFA build almost easy.

Supabase's native MFA. GoTrue — Supabase's auth server — ships TOTP support out of the box: auth.mfa.enroll() returns a secret and an otpauth:// URI you render as a QR code; challengeAndVerify() checks the 6-digit code. Crucially, the TOTP secret lives in GoTrue's own auth.mfa_factors table and never touches your schema or your client. I wrote zero lines of TOTP cryptography. If you ever find yourself implementing RFC 6238 by hand for an app login, stop and check what your auth provider already does.

The key concept GoTrue gives you is the assurance level: a session that authenticated with just a password carries the JWT claim aal: "aal1"; after verifying a TOTP code it's upgraded to aal2. That claim is in every request's token, signed, unforgeable by the client. That's the hook everything else hangs on.

A custom access token hook. We already had a Postgres function that GoTrue calls at token issue, stamping our app-level identity (a bigint userid, a role) into every JWT. Adding MFA policy to it was three lines: look up the user's mfa_required flag, stamp it into the claims. Now every request arrives carrying both whether this user must use MFA and whether this session actually passed it — and the database can compare the two without a single extra query:

create or replace function public.app_mfa_ok()
returns boolean language sql stable as $$
  select coalesce((auth.jwt() ->> 'mfa_required')::boolean, false) = false
      or coalesce(auth.jwt() ->> 'aal', '') = 'aal2'
$$;

Read it carefully, because the coalesce defaults are load-bearing: an absent claim means allowed. Our database is shared by several internal apps, and existing sessions don't carry the new claim until their next token refresh. Defaulting to "allowed" means deploying this migration changed nothing for anyone — enforcement switches on per-user, the moment the admin flags them. No flag-day, no big-bang cutover.

Restrictive policies: the AND gate

Normal Postgres RLS policies are permissive — they OR together, each one granting access. What we want is the opposite: an extra condition that must also hold, on top of whatever access rules already exist. Postgres has exactly this: CREATE POLICY ... AS RESTRICTIVE, which ANDs with the permissive set. One loop stamps the gate onto every table a chat session touches:

foreach t in array tbls loop
  execute format(
    'create policy mfa_aal2_enforce on public.%I
     as restrictive for all to authenticated
     using (public.app_mfa_ok())
     with check (public.app_mfa_ok())', t);
end loop;

Twenty-two tables, plus a scoped policy on the storage bucket that holds chat files. After this, a flagged user's password-only session gets empty result sets everywhere — PostgREST, realtime subscriptions, file downloads, all of it.

Two deliberate exemptions: the users and settings tables stay readable at aal1. The login flow must be able to load the user's profile and app config before the second factor, or it can't even route the user to the "enter your code" screen. Deciding what your pre-MFA session legitimately needs — and keeping that list tiny — is the design work.

The trap: SECURITY DEFINER functions don't care about your policies

Here's the part that would have silently gutted the whole feature if we'd missed it.

Like most apps of any age, ours has RPCs — Postgres functions the client calls for things a plain table query can't express (the channel list with unread counts, presence updates). Six of them are SECURITY DEFINER: they run with the function owner's privileges and bypass RLS entirely, by design. My shiny restrictive policies meant nothing to them. A flagged user at aal1 would see no chat messages… and a fully populated channel list, courtesy of an RPC.

We got lucky on the fix: during an earlier hardening pass, all six had been rewritten to resolve the caller's identity through one shared helper. That gave us a single choke point:

create or replace function public.app_effective_userid(p_param bigint)
returns bigint language sql stable as $$
  select case
    when public.app_is_service() then p_param
    when not public.app_mfa_ok() then null   -- MFA gate, added here
    else public.app_userid()
  end
$$;

Identity resolves to null for a gated session, and every RPC built on it becomes a no-op. One more function (a message soft-delete with its own authorization) needed the check added to its guard clause explicitly.

The transferable lesson: when you add a database-level gate, grep your SECURITY DEFINER functions before you celebrate. They are exactly the code your new policies don't apply to, and every one either needs the check or needs a documented reason it's safe without it.

The client is just choreography

With the database enforcing, the Flutter side stops being security and becomes UX — a small state machine deciding which screen you land on:

• Password OK, verified factor exists, session still aal1 → challenge screen (6 digits).
• Password OK, flagged, no factor yet → forced enrollment (QR + confirm code).
• Otherwise → straight in.

A few implementation notes that cost me real minutes and might save you some: GoTrue requires an issuer when enrolling TOTP or it throws; abandoned half-enrollments leave unverified factors behind that cause a name-conflict error on retry, so clean them up before enrolling (deleting unverified factors is allowed pre-MFA); and create a fresh challenge per verify attempt rather than reusing one, which sidesteps challenge expiry entirely.

The subtle case is mid-session enforcement: what happens when I flag a user who's currently logged in? Their JWT refreshes within the hour, picks up mfa_required, and the database starts returning empty results — which, from inside the app, looks like the company's data vanished. So the app listens for token refresh, re-checks the claims, and routes to the enrollment screen instead of leaving the user staring at a ghost town. Graceful degradation needs designing on both sides of the wire.

Production scars

Three things bit us that only bite on a live system.

The migration deadlocked. CREATE POLICY takes a brief exclusive lock on its table. My single migration altered the users table and then looped policies onto busy chat tables — so it held the users lock while queueing behind live traffic on chat_messages, and a concurrent query held chat_messages while waiting on users. Classic deadlock, transaction rolled back, nothing applied. The fix: split into three migrations with small lock footprints, add set local lock_timeout = '10s', and make the policy loop idempotent (skip policies that already exist) so a timed-out run can simply be re-run.

CREATE OR REPLACE silently drops function settings. An earlier hardening pass had pinned search_path on that choke-point function via ALTER FUNCTION ... SET search_path. Replacing the function body reset that setting without a whisper — the security linter caught it, I didn't. If you re-create a function, re-apply its proconfig.

The web build lied. The day after release, I logged in on the web, got no MFA prompt, and saw no chats. Panic — until it decoded cleanly: Flutter web's service worker was still serving the previous build (no MFA screens), while the freshly-flagged me was correctly blocked by the new RLS. The database doing its job looked exactly like a data-loss bug. One hard refresh fixed me; a "Refresh Now" button that unregisters the service worker and clears caches fixed it for everyone after.

Lost phones

The unglamorous feature that makes admin-assigned MFA livable: reset. When someone loses their authenticator, an admin taps Reset; a small edge function calls GoTrue's admin API to delete the user's factors, and their next login routes back through enrollment. One honest subtlety: deleting factors doesn't revoke existing sessions — and that's fine, because the lost phone holds only the TOTP seed, not a logged-in session. Reason about what the attacker actually possesses before you build revocation machinery you don't need.

The scoreboard

From "we should have MFA" to enforced-in-production on iOS, Windows, macOS, and web: roughly a day, working with Claude Code — the exploration of our own RLS migrations and the SECURITY DEFINER audit is exactly the kind of tedious-but-critical sweep AI assistance is good at. The checklist version, if you're doing this on Supabase:

1. Use native MFA; never hand-roll TOTP or store secrets in your tables.
2. Put the policy (mfa_required) in a JWT claim via an access token hook; compare it to the fact (aal) in one SQL helper.
3. Enforce with AS RESTRICTIVE policies; default absent claims to "allowed" so rollout is gradual, not a flag day.
4. Audit every SECURITY DEFINER function — they're immune to your policies.
5. Decide the minimal tables a pre-MFA session may read, and write the list down.
6. Handle mid-session flagging, stale enrollments, and lost devices — the edges are the feature.
7. Apply migrations to a live database in small, idempotent, lock-timeout-guarded pieces.

The RLS migration that made all this possible — moving a legacy custom-login app onto Supabase Auth without breaking a company mid-workday — is the next post.

I write about building software and a software company at the same time. Subscribe if you want the rest of the series.