Supabase RLS pitfalls — what we learned in production

Row Level Security is the right choice for Supabase apps. We’re not here to talk you out of it.

What we are here to say: the gap between “RLS enabled” and “actually secure” is wider than the docs suggest, and the failure modes are quiet. A policy calling auth.uid() directly — not (select auth.uid()) — can run 171ms per row on a 100K-row table. A missing WITH CHECK clause silently accepts writes you never intended to allow. The anon role has table access by default, and it won’t be revoked automatically until a planned platform change rolls out.

We tested against Supabase JS v2.97.0, PostgreSQL 15+, and three authentication paths: anon key, service_role, and authenticated JWT. Here are the 7 pitfalls that hit production Supabase apps hardest, with the exact fix for each.

Who this is for

Developers running Supabase in production, or actively evaluating it at scale. Specifically, anyone who has enabled RLS, added policies, and assumed that was enough.

If you’re still deciding between Supabase and Firebase, Supabase vs Firebase covers the structural differences. If you’re evaluating database hosting, Neon vs Supabase is the comparison. This post is for people who have already chosen Supabase and are operating it with real users.

What we tested

Supabase JS v2.97.0
PostgreSQL 15+ (Supabase hosted + local CLI with supabase start)
Authentication paths: anon key, service_role, authenticated JWT
Policy performance: EXPLAIN ANALYZE on a 100K-row documents table, isolated per-policy timing
RLS test suite: supabase-test-helpers v0.0.6, pgTAP, supabase test db CLI ≥1.11.4

Pitfall 1: auth.uid() returns NULL and your policy silently passes

The most common silent failure: a policy that uses auth.uid() to restrict access, but auth.uid() returns NULL at the moment the policy runs. No error. Rows returned or not returned — and you don’t know which outcome is correct.

There are two distinct root causes.

Root cause A: anonymous requests

The anon key is a service key. It doesn’t represent a user. auth.uid() returns NULL for any request authenticated with the anon key. If your policy is:

CREATE POLICY "users can read own rows"
ON documents
FOR SELECT
USING (auth.uid() = user_id);

An anon request evaluates NULL = user_id. PostgreSQL treats NULL = anything as NULL, not false — so no rows match, which looks like correct behavior. It is correct behavior, as long as you also haven’t left the anon role with SELECT privilege on the table. If you have (see Pitfall 6), rows leak through.

Root cause B: JWT algorithm mismatch (GitHub #43066)

A reported issue: GoTrue signs JWTs with ES256, while PostgREST expects HS256. When algorithm expectations diverge, PostgREST silently falls back to the anon role rather than returning a 401 — so requests that carry a valid JWT are treated as unauthenticated, and auth.uid() returns NULL. The thread in GitHub #43066 is unresolved; if you see authenticated requests silently behaving as anonymous, verify your PostgREST and GoTrue versions share the same algorithm expectations.

Root cause C: stale app_metadata in the JWT

A related class of NULL-adjacent failure: role or permission claims cached in the JWT don’t reflect recent database changes until the token refreshes. A user promoted from member to admin in your user_roles table still carries member in their JWT until they log out or you force a refresh. Policies reading auth.jwt() -> 'app_metadata' operate on stale data.

The defensive pattern is to read role data from the database via a security definer function, not from the JWT:

CREATE OR REPLACE FUNCTION public.get_user_role(user_id uuid)
RETURNS text
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT role FROM public.user_roles WHERE id = user_id;
$$;

-- Policy reads from the database, not the JWT
CREATE POLICY "admin access"
ON sensitive_table
FOR SELECT
USING (public.get_user_role((select auth.uid())) = 'admin');

Note the (select auth.uid()) wrap — that’s Pitfall 3’s fix applied here. The function call happens once per query, not per row.

Pitfall 2: service_role bypasses RLS — CVE-2025-48757 hit 10.3% of apps

service_role bypasses RLS entirely. That’s by design. The problem is when RLS is missing entirely, or when the service_role key ends up in a context where it shouldn’t be.

CVE-2025-48757, disclosed May 2025, is the concrete example of the first case. A scan of 1,645 Lovable-platform apps found 303 endpoints across 170 projects (10.3%) had Supabase tables readable by unauthenticated requests using the public anon key. The root cause was missing or incomplete RLS: tables were accessible to the anon role without any restrictive policy in place. Not service_role exposure — plain RLS misconfiguration, with the anon key doing exactly what it’s designed to do on an unprotected table.

A separate, higher-severity antipattern: the service_role key placed in frontend code.

The pattern:

// Wrong: service_role in browser context
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL,
  process.env.NEXT_PUBLIC_SUPABASE_SERVICE_ROLE_KEY // bundled into client JS
);

Any NEXT_PUBLIC_ variable gets embedded in the client bundle. Anyone who opens DevTools has your service_role key. With that key, RLS on every table is bypassed.

The correct split:

// Browser: always the anon key
const browserClient = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY);

// Server only: service_role for admin operations
const adminClient = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY);

Supabase has announced a split between publishable keys (safe to expose) and secret keys (never expose) — see the Supabase Security Retrospective 2025 for background. If you’re on an older project, review your dashboard; check that your server-only key is not in any client-exposed env var.

Audit steps: check git history for accidental commits of .env files, run your bundle analyzer and search for service_role in the output, and verify that your server-side API routes — not your client components — are the only callers of adminClient.

Pitfall 3: Policy performance — 171ms becomes 0.1ms with one change

The single highest-impact performance fix for RLS: wrap auth.uid() in a subquery.

The cause: auth.uid() is marked VOLATILE by default. PostgreSQL evaluates a VOLATILE function once per row during a scan. On a 100K-row table, that’s 100,000 calls per SELECT.

Measured on a 100K-row documents table:

-- Unoptimized
EXPLAIN ANALYZE
SELECT * FROM documents WHERE user_id = auth.uid();
-- Planning time: 0.3ms
-- Execution time: 171ms

-- Optimized
EXPLAIN ANALYZE
SELECT * FROM documents WHERE user_id = (select auth.uid());
-- Planning time: 0.4ms
-- Execution time: 0.1ms

The (select auth.uid()) pattern wraps the call in an initPlan. PostgreSQL evaluates it once per query and substitutes the result into the scan predicate. Semantics are identical; execution is 1,710× faster on this workload.

Apply the fix in the policy definition itself, not just in ad-hoc queries:

-- Slow: auth.uid() called once per row
CREATE POLICY "user owns row"
ON documents
FOR SELECT
USING (auth.uid() = user_id);

-- Fast: auth.uid() called once per query
CREATE POLICY "user owns row"
ON documents
FOR SELECT
USING ((select auth.uid()) = user_id);

The same (select ...) wrap applies to security definer function calls inside policies (the pattern shown in Pitfall 1). Those functions are also VOLATILE by default; per-row evaluation applies the same way, and the same fix resolves it.

Running EXPLAIN ANALYZE on production requires dedicated Postgres access. On the Supabase Pro plan, you get a dedicated Postgres instance and query log retention — which means you can run EXPLAIN ANALYZE against your actual production workload rather than a synthetic replica. On the free tier, you’re measuring against local data that may not match your production row counts or access patterns.

Pitfall 4: Infinite recursion in self-referencing policies

This one throws an error rather than failing silently, but the error message is unhelpful:

ERROR: infinite recursion detected in policy for relation "organization_memberships"

The pattern: you have an organization_memberships table and a policy that restricts access to members of the same organization. The policy checks organization_memberships to determine membership — which triggers the policy — which checks organization_memberships again.

-- This recurses infinitely
CREATE POLICY "members can see members"
ON organization_memberships
FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id FROM organization_memberships
    WHERE user_id = (select auth.uid())
  )
);

The fix is a SECURITY DEFINER function that queries the table outside of RLS:

CREATE OR REPLACE FUNCTION public.get_user_organizations()
RETURNS SETOF uuid
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT organization_id
  FROM organization_memberships
  WHERE user_id = auth.uid();
$$;

-- Policy calls the function, which bypasses the policy check
CREATE POLICY "members can see members"
ON organization_memberships
FOR SELECT
USING (organization_id IN (SELECT public.get_user_organizations()));

SECURITY DEFINER makes the function execute with the privileges of its definer (typically a superuser in Supabase), bypassing the policy on the table itself. This breaks the recursion.

The same pattern appears anywhere you have a self-referencing access control table: team_members, project_collaborators, user_follows. If your policy references the same table it’s protecting, you need this fix. The Supabase community has an extended discussion of variations at GitHub discussion #3328.

Pitfall 5: Missing WITH CHECK allows privilege escalation

USING and WITH CHECK are different clauses with different semantics:

USING — filters which rows are visible for SELECT, and which rows can be targeted for UPDATE and DELETE
WITH CHECK — validates the resulting row state after INSERT or UPDATE

A common mistake on UPDATE policies: you write a USING clause only. PostgreSQL’s default for a missing WITH CHECK is to use the USING expression for both filtering and result validation — but the validation applies to the row as it existed before the update, not after.

The privilege escalation: a user who owns a row (passes USING) can update that row’s user_id field to a different user’s UUID. USING checks ownership at the start. WITH CHECK (absent) would have verified ownership of the resulting row. Without it, the update goes through and the target user now owns a row they never created.

-- Vulnerable: no WITH CHECK, UPDATE can change user_id to anything
CREATE POLICY "user can update own rows"
ON documents
FOR UPDATE
USING ((select auth.uid()) = user_id);

-- Fixed: WITH CHECK matches USING — row must belong to the user both before and after
CREATE POLICY "user can update own rows"
ON documents
FOR UPDATE
USING ((select auth.uid()) = user_id)
WITH CHECK ((select auth.uid()) = user_id);

For sensitive columns like user_id, role, and plan, add column-level REVOKE as a second layer:

-- Column permission check happens before RLS — this is a hard block
REVOKE UPDATE (user_id, role) ON documents FROM authenticated;

REVOKE at the column level is enforced before RLS evaluation. Even if a policy would permit the update, PostgreSQL rejects it first. Use both the WITH CHECK clause and column-level revokes for columns that control ownership or access level.

Pitfall 6: Anon role has table access by default

New tables in Supabase projects grant SELECT, INSERT, UPDATE, DELETE privileges to the anon role by default — a behavior Supabase has announced will change in a future platform update, but which has not yet rolled out at the time of writing. RLS enabled on the table doesn’t remove these privileges — it adds policy checks on top of them. If your RLS policies don’t explicitly restrict the anon role, anonymous users can interact with your tables through whatever policies apply (including policies with no role filter that default to all roles).

A concrete example: Supabase’s password reset flow stores temporary tokens in a table accessible to the anon role. If you replicate that pattern without the right policy guards, anonymous users can read or insert into what you expected to be an authenticated-only resource.

Audit your tables now — don’t wait for the platform change:

SELECT
  schemaname,
  tablename,
  has_table_privilege('anon', schemaname || '.' || tablename, 'SELECT') AS anon_select,
  has_table_privilege('anon', schemaname || '.' || tablename, 'INSERT') AS anon_insert,
  has_table_privilege('anon', schemaname || '.' || tablename, 'UPDATE') AS anon_update,
  rowsecurity AS rls_enabled
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

Tables with anon_select = true and rls_enabled = true are not necessarily exposed — verify that you have a restrictive policy for the anon role on each one. Tables with anon_select = true and rls_enabled = false are fully open to anonymous requests.

To remove anon access on a table entirely:

REVOKE ALL ON your_table FROM anon;

The planned platform change applies only to new tables. It won’t retroactively fix existing tables. Run the audit query.

Pitfall 7: RLS tests have a silent failure gap

SELECT policies that block access return 0 rows — not an error. A test that only checks “the query didn’t throw” will pass even when the policy is blocking everything. INSERT policies are different: a blocked INSERT throws a 42501 permission error, which your test sees.

This asymmetry means SELECT policy tests need to assert on row count, not just on absence of exceptions:

BEGIN;
SELECT plan(3);

-- Create a user and authenticate as them
SELECT tests.create_supabase_user('[email protected]');
SELECT tests.authenticate_as('[email protected]');

-- Assert the authenticated user sees exactly their own rows
SELECT results_eq(
  'SELECT count(*)::int FROM documents WHERE user_id = auth.uid()',
  ARRAY[2],
  'authenticated user sees exactly their 2 documents'
);

-- Assert the authenticated user sees no rows belonging to others
SELECT results_eq(
  'SELECT count(*)::int FROM documents WHERE user_id != auth.uid()',
  ARRAY[0],
  'authenticated user sees no documents belonging to others'
);

-- Assert anon cannot insert
SELECT throws_ok(
  $$ INSERT INTO documents (title, user_id) VALUES ('test', gen_random_uuid()) $$,
  '42501',
  NULL,
  'anon insert is rejected'
);

SELECT * FROM finish();
ROLLBACK;

This uses supabase-test-helpers v0.0.6 with pgTAP. Run with:

# Requires supabase CLI ≥1.11.4
supabase test db

The limitation: supabase test db runs against the local development database from supabase start. If you’ve applied migrations in production that aren’t in your local migration directory, your tests are testing a different schema than what’s running in production. Fix this in CI:

supabase db pull  # sync remote schema before running tests
supabase test db

On Supabase Pro, you get query log retention and point-in-time recovery, which helps when a production-specific policy failure surfaces and you need to reconstruct what state the database was in when it happened. On free-tier projects, logs roll off fast.

Verdict: RLS vs application-level auth vs hybrid

Use RLS when your data is multi-tenant, your frontend talks directly to Supabase without a backend proxy, or you need security guarantees that survive a compromised application layer. RLS runs inside Postgres — a bug in your API layer can’t bypass it.

Use application-level auth when your access rules are too complex for SQL expressions: time-windowed access, computed permissions from multiple tables, rules that depend on external state (a third-party entitlement system, a feature flag service), or access patterns that change frequently enough that migrating SQL policies is too slow.

Hybrid is the most common pattern at scale: RLS as a safety net with application-level checks as the primary enforcement layer. The database rejects anything the application layer misses. This covers the “compromised middleware” threat model without requiring all access logic to live in SQL.

If you’re building a new project and want to remove the JWT dependency from your access control entirely, Neon with Clerk or Auth.js as the auth layer separates the auth and data planes more cleanly. Tradeoff: you lose Supabase’s built-in auth integration and have to wire user context manually into every data access path. Worth considering if your access model is complex or your team finds RLS policy debugging expensive. See Clerk vs Supabase Auth for the cost crossover at 50K MAU.

Caveats

The JWT algorithm mismatch described in root cause B (Pitfall 1, GitHub #43066) has an unconfirmed resolution status — check the linked issue for updates. The anon and stale app_metadata root causes apply regardless of client version.

The performance numbers (171ms vs 0.1ms) are from a 100K-row table with a sequential scan. Your numbers depend on table size, index coverage, and whether user_id is indexed. Measure on your own data before reporting results.

The planned anon-privilege platform change had not rolled out at test time. Verify current behavior for your specific project version.

toolchew has affiliate relationships with Supabase and Neon. Affiliate status didn’t change the findings — if RLS had been broken enough to recommend against it, we’d say so.