Convert Supabase RLS

The problem

Supabase RLS is standard CREATE POLICY plus a platform-provided context: auth.uid(), auth.jwt(), the anon/authenticated/service_role pseudo-roles, and PostgREST injecting a verified JWT into a session setting on every request. None of that exists on plain Postgres - an unconverted policy either aborts the restore or silently never matches a row. That context layer, not the policies themselves, is what keeps projects stuck.

capydb migrate rls converts the whole layer. Your policies stay in the database, doing the same authorization work, with no Supabase left in them.

Which path: keep RLS or app-layer guards?

Converting is not always the right call - sometimes the policies were PostgREST plumbing you never relied on, and a handful of explicit guards in your data layer is the better home for the rules. capydb migrate scan --source-url recommends a path, and the rule it applies is simple:

• Keep the policies (convert them) when your server code builds anon-key Supabase clients: authorization was delegated to RLS, so the policies are your authorization model, and rewriting them as app code means re-deriving that model by hand across every call site. A large live corpus (≥50 policies) points the same way on volume alone.
• Rewrite as app-layer guards when the corpus is small and your server code uses the service-role key with explicit filters: RLS never made an authorization decision for you, so give each policy a named guard in the module that owns that table's queries - easier to unit-test, and nothing to convert.

The primary discriminator is code style, not policy count. In a real migration we assessed, the scan found 483 live policies - 471 resolving through a single custom helper wrapping auth.jwt() - with anon-key server clients across roughly 224 files. Rewriting that as app guards would have been a rewrite of the security model itself; converting kept it enforcing through the cutover.

Quickstart

# most faithful: introspect the live database directly (read-only)
capydb migrate rls --source-url "postgres://...direct-or-session-pooler..."

# or point it at your repo - it finds supabase/migrations on its own
capydb migrate rls

# a schema dump file works too
capydb migrate rls schema.sql

Prefer --source-url: migration folders drift from what is actually deployed - policies dropped and recreated, SQL-editor hotfixes that never became migrations - and the live catalog's server-normalized policy expressions are the ground truth the converter does its best work on.

It writes an ordered SQL bundle plus a report:

capyrls/
  capyrls_01_prelude.sql     # the new auth context (accessor functions over GUCs)
  capyrls_02_force_rls.sql   # row-security enforcement for your setup
  capyrls_03_policies.sql    # your policies, converted
  capyrls_report.md          # the contract your app now fulfils + anything needing a human

Apply the files in order (they are plain SQL - psql, your migration tool, or capydb sql all work), read the report, and wire the context into your app.

What comes out

The default output is the idiomatic plain-Postgres convention: a small app.* schema of accessor functions over transaction-local settings. The database stops knowing JWTs exist - your app verifies the caller at the edge and states typed facts per transaction:

Your app sets the context inside each transaction. set_config(..., true) is SET LOCAL semantics - safe through the connection pooler, resets at commit/rollback, and an unset value reads as NULL so every policy fails closed:

begin;
select set_config('app.user_id', '5f4d...', true);
-- queries run under RLS here
commit;

With drizzle, @capydb/drizzle does this for you:

import { withAuthContext } from '@capydb/drizzle'

const todos = await withAuthContext(db, { userId: session.userId }, (tx) =>
  tx.select().from(schema.todos)
)

The report lists every setting your app must provide - that table is your integration checklist.

Role models

• --role-model single (the default here). Your CapyDB project connects as the credential that owns its tables, and owners bypass RLS unless the table is FORCEd - so the bundle emits FORCE ROW LEVEL SECURITY plus a service escape: a transaction that sets app.role = 'service' skips the row filters (seeds, backfills, admin jobs). The escape adds convenience, not exposure - an owner could disable RLS anyway.
• --role-model split. The classic three-role convention: a runtime role that owns nothing (app_user, policies apply) and a BYPASSRLS service role (app_service, replaces service_role). Choose this when you manage your own roles or plan to.

Compat mode

--mode supabase-compat is the zero-risk lift-and-shift: it emits an auth.* shim backed by the request.jwt.claims setting and ports policies verbatim, including their TO authenticated clauses (the pseudo-roles are recreated as membership roles). Your app sets the whole verified-JWT claims object per transaction - withSupabaseJwtClaims in @capydb/drizzle does exactly that. Ship the compat shim first, adopt the vanilla convention when things are calm.

What needs a human

The converter refuses to guess. The report calls out, and the bundle comments out rather than mistranslates:

• Policies referencing auth.users - that table stays behind on Supabase. Port the columns you need into your own users table, then rewrite the policy against it.
• Policies on Supabase-managed schemas (storage.objects, realtime) - they protect platform features that do not migrate.
• Function bodies calling auth.* - listed for manual review, and linked back to the policies that depend on them: a converted policy that authorizes via such a helper is annotated, each entry in the "Functions to review" list shows how many policies reference it, and the report's summary warns that the conversion is incomplete until the helper bodies are ported. A corpus where most policies resolve the caller through one custom helper looks fully converted at the policy level while the actual decision still reads auth.jwt() - the linkage exists so that cannot slip through.
• Deep auth.jwt() paths the converter could not promote - they fall back to a full-claims setting (app.claims) and are flagged.

capydb doctor also warns (supabase_rls_unconverted) when SQL in your repo still calls auth.uid()/auth.jwt(), so an unconverted policy cannot sneak toward an import unnoticed.

Standalone and open source

The converter is capyrls - MIT, no CapyDB account needed, works for any Postgres destination:

go install github.com/capydatabase/capyrls/cmd/capyrls@latest
capyrls convert --db "$SUPABASE_URL"   # live introspection, works standalone

Live introspection is the same engine capydb migrate rls --source-url uses; the standalone binary defaults to the split role model since it does not know your platform.