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

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:

auth_rls_initplan: Detects RLS policies that re-evaluate
auth functions for each row.

It listed 76 of our policies. Same warning every time, same root cause.

What auth.uid() actually does per row

A typical Equip policy at the time:

-- 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')
  )
);

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.

The fix that takes ten characters

-- 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')
  )
);

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.

Why we missed it for months

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:

1. Per-user write tables like chapter_progress or quiz_attempts. One row per (student × content), grows linearly with engagement.
2. Many-to-many junctions like enrollments. Scanned during nearly every authenticated read.

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.

How we caught it: the database advisor

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.

Two ways to run it:

# Supabase CLI (v2.81.3+)
supabase db advisors

# Or via the dashboard:
# https://supabase.com/dashboard/project/<ref>/advisors/performance

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:

-- 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())>);

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.

The PL/pgSQL footgun nobody mentions

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.

-- 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'
  );
$$;

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.

What to do today, in order

1. Open your project's Advisors → Performance tab. Look for auth_rls_initplan. If the list is non-empty, you have the trap.
2. 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.
3. 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.
4. 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.

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:

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

ArVaViT / equip

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

Equip

A free, open-source learning management system built for Bible schools church ministries, and nonprofit educational programs

Live demo · Roadmap · Contributing · Support · Changelog

---

Screenshots

Sign in (light)	Sign in (dark)
Account creation — student / teacher role picker	Mobile (390px)

Live at equipbible.com. Teacher and admin views (gradebook, course editor, analytics) are behind sign-in — create a free account to explore.

---

Why this project?

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:

• 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…

View on GitHub