Retrofitting Row-Level Security on a Live App, Without a Flag Day

Time for a confession that makes security people wince. When DolphinChat — the home-grown app my company runs on — first shipped, every install carried the Supabase service-role key. The key that bypasses every access rule. Every phone and laptop in the company was, from the database's point of view, the database administrator.

Why? The reasons will sound familiar to anyone who's built an internal tool under time pressure. We had an existing users table with bigint IDs, plaintext-adjacent passwords, and years of data referencing it — fifty-five foreign keys' worth. Supabase Auth wanted UUIDs and its own auth.users table. Bridging the two looked like work; shipping looked like value. "It's an internal app, everyone here is trusted" carried the argument, the way it always does.

It's still a loaded gun. Any curious employee — or anyone who extracts one string from an installer — has unrestricted read-write on everything: chats, tasks, HR data, the works. This post is about fixing that on a live system: real users mid-workday, a database shared with other internal apps, and client updates that roll out over weeks, not minutes. No flag day allowed.

The constraint that shapes everything

The defining feature of this migration is that old and new had to coexist. You cannot atomically swap the security model of a system whose clients update on their own schedule. Windows users update when prompted; iPhone users when TestFlight and inertia allow. For weeks, some devices would run the old god-mode build while others ran the new locked-down one — against the same database.

That constraint dictated the strategy: make every server-side change additive and inert. Enable RLS, write the policies, wire up auth — such that the old builds (whose service-role key bypasses RLS entirely) don't notice anything, and the new builds get the full enforcement. The service-role key's RLS bypass, the very thing we were trying to retire, became the transition mechanism. There's a certain irony in that.

The identity bridge

The first real problem: our entire schema speaks bigint userid; Supabase Auth speaks UUID. Rewriting 55 foreign keys was never on the table.

The answer is a bridge, not a rewrite. One nullable column — users.auth_user_id uuid references auth.users(id) — links each app user to an auth account. The schema keeps its bigints; auth keeps its UUIDs; one join connects the worlds.

But you don't want that join running inside every policy on every row. Supabase has a mechanism that turns out to be the cornerstone of the whole design: the custom access token hook — a Postgres function GoTrue calls whenever it issues or refreshes a JWT. Ours looks up the app identity once and stamps it into the token's claims:

select u.userid, u.userroleid, (u.userseparationdate is not null)
  into v_userid, v_roleid, v_separated
from public.users u
where u.auth_user_id = (event ->> 'user_id')::uuid;

claims := jsonb_set(claims, '{userid}',       to_jsonb(v_userid));
claims := jsonb_set(claims, '{userroleid}',   to_jsonb(coalesce(v_roleid, 2)));
claims := jsonb_set(claims, '{is_separated}', to_jsonb(coalesce(v_separated, false)));

Every request now arrives carrying userid, role, and employment status as signed, unforgeable claims. Policies read them through tiny helpers (app_userid(), app_is_admin(), app_is_active()) that Postgres treats as near-constants — no per-row joins against a shared users table, no trusting anything the client sends. When we later added MFA enforcement (previous post), it was three more lines in this same hook. The hook is the pattern; everything else is application.

The password import nobody noticed

The migration's best trick was invisible. Our users had passwords in the legacy table; making everyone "sign up again" or run a reset gauntlet was the kind of friction that kills internal-tool migrations. But GoTrue stores bcrypt hashes — and Postgres can make bcrypt hashes:

update auth.users a
set encrypted_password = extensions.crypt(u.userpassword, extensions.gen_salt('bf'))
from public.users u
where u.auth_user_id = a.id;

One statement, and every existing password worked against the new auth system. On update day, people opened the app, logged in with the password they'd always had, and had no idea the entire authentication machinery had been replaced beneath them. The plaintext column's deletion is scheduled for the end of the cutover; the point is that users experienced nothing.

Policies for a shared house

Writing the chat-table policies was the straightforward part — members see their channels' messages, senders edit their own, and a SECURITY DEFINER membership helper avoids the classic recursion trap where the members table's policy consults the members table.

The interesting policies were forced by our situation: this Postgres instance is shared by several internal apps, and only DolphinChat was migrating. Locking down tasks or clients with chat-user-only rules would have broken the other apps' sessions overnight. The escape valve is the same absent-claim trick that later powered the MFA rollout:

using (public.app_is_active() OR (auth.jwt() ->> 'userid') IS NULL)

Translated: you're welcome if you're an active chat user — or if you're some other app's authenticated session that carries no chat claims at all. Each app can migrate to its own claim set on its own timeline. Absent claims defaulting to permissive is what makes incremental migration of a shared database possible at all; you tighten the default only at the very end.

The last policy phase covered a subtler problem: our singleton settings table mixes harmless app config with genuine secrets — third-party API keys, mail credentials. Row-level security is the wrong tool there, because the unit of protection is a column, not a row. Postgres has had the right tool since forever:

revoke all on public.settings from authenticated;
grant select (settingsid, chat_app_version, chat_announcements_channel_id /* ... */)
  on public.settings to authenticated;

Column-level grants: clients can read the config columns and physically cannot select the secret ones. One side effect worth knowing: select * on that table now throws a permission error, so every client query names its columns explicitly through a shared whitelist constant. Mildly annoying, safely annoying.

The museum of things RLS broke

Turning on RLS over a live app is archaeology in reverse — you find out, one bug at a time, which of your code paths silently depended on god-mode. Three exhibits, each a general lesson:

Exhibit one: INSERT ... RETURNING obeys the SELECT policy. Creating a channel started failing with new row violates row-level security — but the insert policy was satisfied. The catch: our Flutter code inserts and reads the row back in one call, and that read-back is filtered by the SELECT policy, which said "members only." The creator isn't a member yet at that instant — membership is written a statement later. Fix: the select policy also admits created_by = app_userid(). If your insert-then-return breaks under RLS, look at your SELECT policy first.

Exhibit two: the bug the old build was hiding. Message deletion is a soft delete — set is_deleted = true. Under RLS this always fails: the updated row must still satisfy the SELECT policy, and the SELECT policy excludes deleted messages. The kicker is that we didn't catch it in testing, because deletions from legacy service-role builds sailed through — the old clients were masking the regression for the new ones. Mixed fleets don't just complicate rollouts; they hide bugs. The fix was a small SECURITY DEFINER RPC that authorizes explicitly (sender or admin) and performs the delete with owner rights.

Exhibit three: RPCs that trusted the client. Several database functions took a p_user_id parameter — "whose channels shall I fetch?" — and trusted it, because in the god-mode era the parameter was merely informational. Under real auth it's an impersonation vector: any authenticated user could pass anyone's ID. The hardening: make them SECURITY DEFINER, derive the acting user from the JWT claim, and ignore the parameter for authenticated callers — keeping the parameter honored only for the service-role path, so legacy builds stayed alive through the transition. (That fallback is scheduled to die with the old key. Write the removal down, or it becomes permanent.)

The last ten percent is the security

Here's the uncomfortable honesty that most migration posts skip: everything above, shipped and working, secured nothing by itself — because the old service-role key still existed, still worked, and still bypassed every policy. RLS with a live bypass key is a fence with the gate propped open.

Actually closing the gate is a logistics exercise, not a technical one. Force-update the stragglers (our app has a minimum-version mechanism for exactly this). Inventory every holder of the old keys — ours turned up in cron job headers, CI secrets, a sibling app, and two scheduled AI agents. Move them all to Supabase's newer key system (publishable keys for clients, secret keys for servers), which crucially lets you disable the legacy JWT-based keys without rotating the JWT signing secret — meaning the old keys die everywhere at once while nobody's login session even blinks. Then, finally, delete the plaintext password column and the RPC fallbacks that existed only for the old builds.

We're mid-way through that sequence as I write this — versions forced, holders inventoried, cutover scheduled. I'm including the unfinished state deliberately: a security migration isn't done when the policies ship. It's done when the old key is dead.

The checklist

If you're staring at your own internal app with a service key in its pocket:

1. Bridge identity, don't rewrite it. One UUID column and an access-token hook beat a schema migration of every foreign key.
2. Stamp app identity into JWT claims via the hook; write policies against claims, never against client-supplied parameters.
3. Import passwords with crypt() so users never notice the auth swap.
4. Make every change additive and inert — the old build must keep working against the new schema until its key dies.
5. In a shared database, default absent claims to permissive, and let each app migrate on its own clock.
6. Use column grants for secrets; RLS protects rows, not columns.
7. Expect RLS to surface hidden dependencies: INSERT...RETURNING, soft deletes, trusting RPCs. Each failure is god-mode debt being repaid.
8. Plan the key's funeral before you start. The migration ends when the old key stops working, not when the policies merge.

The groundwork this migration laid — the hook, the claims, the helpers — is what made the database-enforced MFA build a one-day job instead of a rewrite. Security work compounds the same way features do, and on the same foundation: one database, one identity, one place to enforce the truth.

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