Convert Supabase RLS

The problemCopy Anchor Link

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?Copy Anchor Link

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.

QuickstartCopy Anchor Link

# 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.

No CLI to hand? Paste your migrations or a schema dump into the web converter. It runs the same converter in your browser - nothing is uploaded - and produces the same bundle and report. It reads SQL only, so live introspection stays a CLI feature.

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 outCopy Anchor Link

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.

User ids that are not uuids (Clerk)Copy Anchor Link

Supabase's auth.uid() returns a uuid, and by default so does the converted accessor (app.user_id(), or auth.uid() in compat mode). If your identity provider's user ids are not uuids - Clerk's user_2abc..., for one - every policy that calls it fails with invalid input syntax for type uuid. Pass --uid-type text and the accessor returns the id as text, uncast:

capydb migrate rls --source-url "$SUPABASE_DIRECT_URL" --uid-type text

Every column the user id is compared to must then be text too, because Postgres has no implicit text-to-uuid conversion. That mismatch is loud, never silent: applying the bundle stops at the first policy that compares the user id to a uuid column (operator does not exist: text = uuid) or at a uuid column that takes it as a default. The report lists each such column it can see, with the fix - run it before you apply the bundle:

alter table public.todos alter column owner_id type text using owner_id::text;

A foreign key on that column has to change type on both sides. The converter does not cast the column side (owner_id::text = ...) for you: that predicate cannot use the column's index, and a uuid column cannot hold a user_... id anyway, so the policy would silently match nothing. One case is only caught at runtime: a plpgsql function body is type-checked when it is called, not when it is created, so review the helpers listed under "Functions to review" for the same comparison.

Role modelsCopy Anchor Link

• --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' (vanilla) or whose claims carry "role": "service_role" (compat) skips the row filters (seeds, backfills, admin jobs). A permissive policy cannot lift a restrictive one, so the escape is also ORed into every restrictive policy - service_role skipped those on Supabase too. The escape adds convenience, not exposure - an owner could disable RLS anyway. --no-service-escape omits it.
• --role-model split. A runtime role that owns nothing, so policies apply to it, and a separate service path that skips them. A CapyDB database role cannot create roles, so on CapyDB the runtime role is the platform's: enable it for the project (POST /v1/projects/{projectID}/roles/app, Postgres 16 or newer; rolling out, and answering 404 until it is offered - see Runtime role) and the cell gets app_user - a login that owns nothing, cannot bypass row security and can never act as the owner, with its own connection strings next to the owner's. The owner's credential can also switch to it (begin; set local role app_user; ...). The bundle creates no roles: it checks that app_user exists (and stops with how to enable it if not), then grants it the table and sequence privileges it needs, including by default for tables the owner creates later. The owner is the service path: nothing is FORCEd, and owners bypass row security on tables that are not, which is what BYPASSRLS gave service_role - keep the owner's credential for migrations, backfills and admin jobs and send request traffic through app_user. SECURITY DEFINER functions the owner owns keep bypassing row security too, as they did on Supabase. If your source already FORCEd a table, the report names it: FORCE confines the owner as well. For a Postgres where you manage roles yourself, --target postgres builds the classic three-role bundle instead (app_user plus a BYPASSRLS app_service, created by the bundle).

Compat modeCopy Anchor Link

--mode supabase-compat is the lift-and-shift: it emits an auth.* shim backed by the request.jwt.claims setting and ports policies verbatim. Under the single role model their TO authenticated / TO anon clauses become auth.role() checks, so no roles are created. So they do under --role-model split on CapyDB, targeted at app_user: TO anon becomes auth.role() = 'anon' (a caller with no claims reads as anon) and TO authenticated becomes auth.role() = 'authenticated', and service_role-only policies are covered by the owner. Only --target postgres recreates the pseudo-roles as real roles, since CREATE ROLE needs CREATEROLE, which a CapyDB database role does not have.

Under the single role model the service path is claims-based: a transaction whose verified claims carry "role": "service_role" - what a Supabase service key's JWT said - passes the service escape on every table. Set that role only from server code that used the service key, never from a token your users can shape: a role claim copied from user input is a bypass of every policy. --no-service-escape removes the escape, and the report then lists policies that applied only to service_role as access that is now denied.

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.

Living under FORCECopy Anchor Link

FORCE ROW LEVEL SECURITY is what makes the single-role model work: without it, your app connects as the table owner and every policy you just converted is inert. With it, three things are true that surprise people, and all of them are worth knowing before a cutover rather than during one.

You no longer have a privileged view of your own dataCopy Anchor Link

As the owner under FORCE, a count(*) returns what your policies admit, not what the table holds. A count taken through capydb sql, Studio or psql is a policy-filtered number - and with no context set it is usually zero, because unset context fails closed.

The service escape is the way to see every row. Count inside a transaction that raises it:

begin;
select set_config('app.role', 'service', true);  -- vanilla
-- compat: select set_config('request.jwt.claims', '{"role":"service_role"}', true);
select count(*) from public.todos;
rollback;

If you converted with --no-service-escape there is no such path: lift FORCE on that table inside a transaction and roll it back instead.

This is the most common way people conclude a table is empty or a migration dropped rows. It usually didn't.

Adding a foreign key later needs one extra stepCopy Anchor Link

Postgres is asymmetric here, and it is not something the converter does. Verified on PostgreSQL 17.11:

So adding a foreign key whose parent is FORCEd fails:

ERROR:  insert or update on table "child" violates foreign key constraint "child_parent_fk"
DETAIL:  Key is not present in table "parent".  -- SQLSTATE 23503

The key is present. Your policy is hiding it from the validation scan. Nothing is wrong with your data, and referential integrity is not affected - existing keys hold and every write is still checked. It is only the two DDL paths above that stop, which also means drizzle-kit push adding a foreign key to an existing table hits this.

To add or validate one, drop FORCE on the parent only, for the length of the statement. The child stays FORCEd and the window is owner-only:

do $$
declare was_forced boolean;
begin
  select relforcerowsecurity into was_forced
    from pg_class where oid = 'public.parent'::regclass;

  alter table public.parent no force row level security;
  alter table public.child add constraint child_parent_fk
    foreign key (parent_id) references public.parent(id);

  if was_forced then
    alter table public.parent force row level security;
  end if;
exception when others then
  if was_forced then
    alter table public.parent force row level security;
  end if;
  raise;
end $$;

capydb migrate scan --source-url reports any foreign key that is already NOT VALID in your source, because those are the ones that still owe a validation scan the destination will refuse to run. Validate them before you cut over and this never comes up.

SECURITY DEFINER functions stop bypassing your policiesCopy Anchor Link

A SECURITY DEFINER function runs as the owner of your tables. On Supabase that owner bypassed row security, which is usually why the function is a definer at all: it writes a row the caller's own policies would not admit - accepting an invitation on someone else's behalf, bumping a counter on a shared row. Under FORCE the owner is filtered like anyone else, so that write now fails with 42501 new row violates row-level security policy, or disappears into an exception when others handler.

The report lists every definer whose body writes a table the bundle FORCEs, under "SECURITY DEFINER functions under FORCE". The fix is to raise the service escape for the length of the body and restore it before every RETURN. That needs plpgsql - rewrite a language sql function first. In vanilla mode:

create or replace function public.share_todo(target uuid, t text) returns void
language plpgsql security definer as $$
declare
  prev text := current_setting('app.role', true);
begin
  perform set_config('app.role', 'service', true);
  insert into public.todos (owner_id, title) values (target, t);
  perform set_config('app.role', coalesce(prev, ''), true);
end $$;

In compat mode, merge the role into the claims instead of replacing them, so auth.uid() still names the caller inside the body:

declare
  prev text := current_setting('request.jwt.claims', true);
begin
  perform set_config('request.jwt.claims',
    (auth.jwt() || '{"role": "service_role"}')::text, true);
  -- the original body
  perform set_config('request.jwt.claims', coalesce(prev, ''), true);
end

If the body raises, the rollback restores the setting with it. alter function ... set app.role = 'service' looks tidier and does not work: setting a custom parameter on a function needs a privilege a CapyDB database role does not have, and Postgres answers 42501 permission denied to set parameter. Detection reads each function's own statements - a write through EXECUTE or through another function is not seen, so check definers that build SQL dynamically by hand.

What needs a humanCopy Anchor Link

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.
• SECURITY DEFINER functions that write to FORCEd tables - listed with the fix for your mode, which you apply by hand (above).

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 sourceCopy Anchor Link

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.

The same converter also runs in the browser at capydb.dev/tools/rls-converter: paste SQL, get the bundle and the report. The conversion happens in the page; nothing is uploaded.