Tools

Tools: 76 RLS policies rewritten in one migration: the auth.uid() init-plan trap in Supabase (2026)

2026-05-20 0 views admin
Tools: 76 RLS policies rewritten in one migration: the auth.uid() init-plan trap in Supabase (2026)

What auth.uid() actually does per row

The fix that takes ten characters

Why we missed it for months

How we caught it: the database advisor

The PL/pgSQL footgun nobody mentions

What to do today, in order

ArVaViT / equip

Free, open-source LMS for Bible schools, ministries, and nonprofit educational programs. React + FastAPI + Supabase.

Screenshots

Why this project? TL;DR. If your Supabase RLS policies call auth.uid() directly inside USING(...) or WITH CHECK(...), Postgres re-evaluates the function once per row. Wrap it as (SELECT auth.uid()) and the planner hoists the call to a single init plan that runs once per query. Same logical query, different plan, different cost at scale. We had 76 policies doing this wrong on Equip. Supabase's own auth_rls_initplan advisor lint found every one of them. Equip is an open-source LMS we run on equipbible.com — FastAPI backend, React frontend, Supabase Postgres with RLS on every public table. Migrations live in the repo as plain .sql files. Standard setup, nothing exotic. A while back we ran Supabase's database advisor as part of a pre-deploy sweep and one warning stopped us: It listed 76 of our policies. Same warning every time, same root cause. A typical Equip policy at the time: That auth.uid() looks like a constant. It isn't. It's a Postgres function marked STABLE, which means within one query it returns the same value, but the planner has to be told it can hoist the call out of the per-row loop. By default it doesn't. Every row scanned during the policy check fires the function again, allocates the JWT context, reads the claim, returns it. For a 50-row dev table you'd never notice. For a chapter_progress table with one row per (student × chapter), or a quiz_answers table that grows every time a student submits anything, it adds up fast. Wrapping the call in a subquery isn't stylistic. The planner treats (SELECT auth.uid()) as a subquery returning one row of one value, and pulls it out into an InitPlan that runs exactly once at the start of the query. Every row check then sees a literal, not a function call. You can confirm this on your own database. EXPLAIN ANALYZE on the bare version shows a function call inside the filter. On the wrapped version it shows InitPlan 1 at the top and the filter references $0. Same query, different plan. Supabase covers this under RLS performance recommendations, but it's easy to miss when you're writing policies by hand at the same pace you're shipping features. Honest moment: we shipped most of Equip's policies with the raw auth.uid() pattern. They worked. Every test passed. Production users (students, teachers, admins) couldn't tell the difference. We were nowhere near the row count where the per-row overhead would have shown up as user-visible latency. That's the actual trap. The warning sign isn't "your app is slow." By the time it's slow you've already shipped a pile more policies in the same shape, because everything you wrote between then and now had no reason to fail. The trap is that this pattern works fine in dev and on a small prod, then quietly compounds when one table grows. Two patterns reliably hit the cliff first in our experience: Anything else (small lookup tables, course catalogs, taxonomy) stays fast for a long time even with the bad pattern. So the early "we don't see it yet" reading is correct, but it's not informative. The pattern is structurally wrong, you just haven't hit the row count that exposes it. The Supabase project dashboard has an Advisors tab. Open it on any Postgres project and one of the performance lints is auth_rls_initplan. It scans pg_policies, parses the qual and with_check expressions, and flags any direct auth.<function>() call that isn't wrapped. If you've wired the Supabase MCP server into your dev environment, get_advisors returns the same list as a tool call you can run from an agent. Either way the output is a flat list of policy names. The migration itself is mechanical: Our 20260421015755_rls_perf_cleanup_016_policies.sql did this 76 times in a single migration. Mostly find-replace with care to keep the original semantics intact. While you're already in there, one more thing: if you've abstracted any of this into a helper function (e.g. is_admin() to keep policies DRY), check the language declaration. LANGUAGE sql + STABLE + a single SELECT lets Postgres inline the function body into the policy at plan time. EXPLAIN looks identical to inline EXISTS. Switch to LANGUAGE plpgsql and the planner treats it as an opaque call. Can't push predicates in, can't reorder joins, every row hits the function. We don't use helpers in Equip yet, but multiple people in the Supabase community have hit this when extracting is_member_of(school_id) for multi-tenant setups. The helper looks right, the policy looks DRY, the EXPLAIN looks wrong. The advisor keeps flagging this on every new policy you write, so once you wire it into pre-merge checks (CI step, pre-deploy hook, or a habit on PR review) the trap doesn't come back. What I want to hear back, especially from people running Supabase in production: A free, open-source learning management system built for Bible schools church ministries, and nonprofit educational programs Live demo · Roadmap · Contributing · Support · Changelog Live at equipbible.com. Teacher and admin views (gradebook, course editor, analytics) are behind sign-in — create a free account to explore. Hundreds of small Bible schools, home churches, and missionary training programs around the world still manage courses on paper, WhatsApp, or spreadsheets. Commercial LMS platforms are expensive, overkill, or require technical expertise that volunteer-run organizations simply don't have. Equip is designed to change that: Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to ? It will become hidden in your post, but will still be visible via the comment's permalink. as well , this person and/or

Code Block

Copy

auth_rls_initplan: Detects RLS policies that re-evaluate auth functions for each row. CODE_BLOCK: auth_rls_initplan: Detects RLS policies that re-evaluate auth functions for each row. CODE_BLOCK: auth_rls_initplan: Detects RLS policies that re-evaluate auth functions for each row. CODE_BLOCK: -- BEFORE: auth.uid() called for every row scanned CREATE POLICY "assignments_update_teacher" ON public.assignments FOR UPDATE TO authenticated USING ( EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = auth.uid() AND p.role IN ('teacher', 'admin') ) ); CODE_BLOCK: -- BEFORE: auth.uid() called for every row scanned CREATE POLICY "assignments_update_teacher" ON public.assignments FOR UPDATE TO authenticated USING ( EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = auth.uid() AND p.role IN ('teacher', 'admin') ) ); CODE_BLOCK: -- BEFORE: auth.uid() called for every row scanned CREATE POLICY "assignments_update_teacher" ON public.assignments FOR UPDATE TO authenticated USING ( EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = auth.uid() AND p.role IN ('teacher', 'admin') ) ); CODE_BLOCK: -- AFTER: auth.uid() runs once, planner caches the result CREATE POLICY "assignments_update_teacher" ON public.assignments FOR UPDATE TO authenticated USING ( EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = (SELECT auth.uid()) AND p.role IN ('teacher', 'admin') ) ); CODE_BLOCK: -- AFTER: auth.uid() runs once, planner caches the result CREATE POLICY "assignments_update_teacher" ON public.assignments FOR UPDATE TO authenticated USING ( EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = (SELECT auth.uid()) AND p.role IN ('teacher', 'admin') ) ); CODE_BLOCK: -- AFTER: auth.uid() runs once, planner caches the result CREATE POLICY "assignments_update_teacher" ON public.assignments FOR UPDATE TO authenticated USING ( EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = (SELECT auth.uid()) AND p.role IN ('teacher', 'admin') ) ); COMMAND_BLOCK: # Supabase CLI (v2.81.3+) supabase db advisors # Or via the dashboard: # https://supabase.com/dashboard/project/<ref>/advisors/performance COMMAND_BLOCK: # Supabase CLI (v2.81.3+) supabase db advisors # Or via the dashboard: # https://supabase.com/dashboard/project/<ref>/advisors/performance COMMAND_BLOCK: # Supabase CLI (v2.81.3+) supabase db advisors # Or via the dashboard: # https://supabase.com/dashboard/project/<ref>/advisors/performance COMMAND_BLOCK: -- For each flagged policy: DROP + recreate with the wrapped call DROP POLICY "<name>" ON public.<table>; CREATE POLICY "<name>" ON public.<table> FOR <command> TO <roles> USING (<original expression with (SELECT auth.uid())>); COMMAND_BLOCK: -- For each flagged policy: DROP + recreate with the wrapped call DROP POLICY "<name>" ON public.<table>; CREATE POLICY "<name>" ON public.<table> FOR <command> TO <roles> USING (<original expression with (SELECT auth.uid())>); COMMAND_BLOCK: -- For each flagged policy: DROP + recreate with the wrapped call DROP POLICY "<name>" ON public.<table>; CREATE POLICY "<name>" ON public.<table> FOR <command> TO <roles> USING (<original expression with (SELECT auth.uid())>); CODE_BLOCK: -- BAD: planner can't inline a plpgsql function, every call is opaque CREATE FUNCTION public.is_admin() RETURNS boolean LANGUAGE plpgsql STABLE AS $$ BEGIN RETURN EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = (SELECT auth.uid()) AND p.role = 'admin' ); END $$; -- GOOD: SQL functions get inlined into the policy at plan time CREATE FUNCTION public.is_admin() RETURNS boolean LANGUAGE sql STABLE AS $$ SELECT EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = (SELECT auth.uid()) AND p.role = 'admin' ); $$; CODE_BLOCK: -- BAD: planner can't inline a plpgsql function, every call is opaque CREATE FUNCTION public.is_admin() RETURNS boolean LANGUAGE plpgsql STABLE AS $$ BEGIN RETURN EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = (SELECT auth.uid()) AND p.role = 'admin' ); END $$; -- GOOD: SQL functions get inlined into the policy at plan time CREATE FUNCTION public.is_admin() RETURNS boolean LANGUAGE sql STABLE AS $$ SELECT EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = (SELECT auth.uid()) AND p.role = 'admin' ); $$; CODE_BLOCK: -- BAD: planner can't inline a plpgsql function, every call is opaque CREATE FUNCTION public.is_admin() RETURNS boolean LANGUAGE plpgsql STABLE AS $$ BEGIN RETURN EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = (SELECT auth.uid()) AND p.role = 'admin' ); END $$; -- GOOD: SQL functions get inlined into the policy at plan time CREATE FUNCTION public.is_admin() RETURNS boolean LANGUAGE sql STABLE AS $$ SELECT EXISTS ( SELECT 1 FROM public.profiles p WHERE p.id = (SELECT auth.uid()) AND p.role = 'admin' ); $$; - Per-user write tables like chapter_progress or quiz_attempts. One row per (student × content), grows linearly with engagement. - Many-to-many junctions like enrollments. Scanned during nearly every authenticated read. - Open your project's Advisors → Performance tab. Look for auth_rls_initplan. If the list is non-empty, you have the trap. - Write a single migration that DROPs and recreates the flagged policies with (SELECT auth.uid()) everywhere auth.uid() appears bare. Don't try to refactor the policies' logic at the same time. Pure mechanical change. - While you're in the advisor, glance at multiple_permissive_policies and policy_exists_rls_disabled. Both compound the same per-row cost. Multiple permissive policies on the same role + action each run separately, so two bad policies double the trap. - If you have helper functions in policies, verify each is LANGUAGE sql STABLE with a single SELECT body. Convert any plpgsql ones if you can keep the logic in pure SQL. - Has this lint caught anything for you besides auth.uid()? auth.jwt() ->> 'role' should hit the same code path but I haven't traced it directly. - If you've written is_member_of(...) helpers for multi-tenant RLS, did you keep them in SQL or move to plpgsql? Curious about the tradeoff at scale. - For anyone who left these unwrapped on purpose — what does that constraint look like? I can imagine a few cases where you'd want the per-row eval, but I can't think of any in policy code specifically. - Free forever — MIT-licensed, no paywalls, no "premium" tiers. - Simple to deploy — one-click Vercel deploy with a free Supabase database. No Docker, no servers to manage. - Built for small scale — optimized for 20-100 students, not…

🏷️ Tags

toolsutilitiessecurity toolspoliciesrewrittenmigrationsupabaseactuallyrce

More from Tools

Tools: RHEL End-of-Life Dates — Official EOL Schedule for Every Version

Tools: RHEL End-of-Life Dates — Official EOL Schedule for Every Version

2026-05-21 0
Tools: The Complete EOL Calendar for 2026 — Every Major Software End-of-Life Date

Tools: The Complete EOL Calendar for 2026 — Every Major Software End-of-Life Date

2026-05-21 0
Tools: Report: How to Deploy a Virtual Machine in Azure

Tools: Report: How to Deploy a Virtual Machine in Azure

2026-05-21 0
Tools: CI/CD with AWS CodePipeline and CodeBuild (2026)

Tools: CI/CD with AWS CodePipeline and CodeBuild (2026)

2026-05-21 0

Trending

1

CVE-2025-61481: Critical Remote Code Execution Vulnerability in MikroTik RouterOS & SwitchOS

2025-10-27 • 189 views
2

CVE-2025-43939: Dell Unity OS Command Injection (High)

2025-10-30 • 148 views
3

Google disputes false claims of massive Gmail data breach

2025-10-30 • 130 views
4

Microsoft: DNS outage impacts Azure and Microsoft 365 services

2025-10-30 • 88 views
5

3.5B Accounts, 1 Critical Flaw: Meta Closes WhatsApp Data-Harvesting

2025-11-25 • 81 views