Supabase RLS in production: four traps that silence your queries

« Your enrollments — how many? Because I see zero »

One Tuesday morning, I had just enabled RLS on eighteen tables of Rembrandt, L'Atelier Palissy's ERP. Policies written, tested in direct SQL, everything passing. Prod deploy, coffee. Françoise calls from the next office — she doesn't come over, she shouts from her chair. « Bon. Tes inscriptions sur le site de Maisons-Laffitte, il y en a combien, dis-moi ? Moi j'en vois zéro. » — Right. Your enrollments on the Maisons-Laffitte site, how many are there? Because I see zero. I open the same page on my machine. Zero too. No exception, no 500, no Sentry error log. Just zero rows, which is precisely what makes the bug dangerous: Françoise sees nothing to fix, she sees an empty school.

Row Level Security is one of the rare Postgres/Supabase features that can break your application silently. A misconfiguration doesn't return an error. It returns an empty set, or worse, a partial set that passes through code without alerting it. I spent four weeks running into four distinct traps, naming them, documenting them. This article gathers them.

---

If you have 30 seconds. Well-configured RLS is the best data guardrail you can put on a Supabase database. Misconfigured RLS is the worst bug because it never screams. The four traps: wrong Supabase client in Server Components, RPC SECURITY DEFINER open to anon, write policies without role check, forgotten public Storage bucket. Each has a silent symptom — empty query, public endpoint, open write, exposed file — and a five-minute fix once the cause is found. The article gives the four symptoms and the four fixes.

Trap 1 — The wrong client in a Server Component

This is the trap that put Françoise in front of an empty school. Supabase exposes three distinct clients, and their difference isn't obvious at first glance.

• createSupabaseBrowser() with the anon key, client-side in the browser
• createSupabaseServer() with the anon key plus the auth cookie, server-side in a Server Component
• createSupabaseAdmin() with the service_role key, server-side, bypasses RLS

The trap: if you use createSupabaseServer() in a Server Component but the auth cookie doesn't transit correctly — misconfigured middleware, expired refresh token, proxy route reshaping the request — the JWT falls back to anon. No policy matches for an anon user. The query returns zero rows. No error, because technically the query is valid; Postgres simply found nothing that matched.

The rule I eventually wrote in my CLAUDE.md and in an auto-invoked agent skill: in a Server Component, use createSupabaseAdmin(), never createSupabaseServer(). Authentication is already verified upstream by the route-guarding middleware, the service_role never reaches the browser, and queries return what they're supposed to.

// ❌ Silently empty if auth doesn't pass
import { createSupabaseServer } from '@/lib/supabase-server'
const supabase = createSupabaseServer()
const { data } = await supabase.from('inscriptions').select('*')
// data = [] with no error

// ✅ Auth already verified by middleware, RLS bypassed
import { createSupabaseAdmin } from '@/lib/supabase-admin'
const admin = createSupabaseAdmin()
const { data } = await admin.from('inscriptions').select('*')

Trap 2 — RPC functions open to anon

Second trap, more insidious because it hurts in the opposite direction: you don't have too few readers, you have too many.

Supabase generates REST endpoints for every Postgres function declared SECURITY DEFINER, and by default PUBLIC has execution rights. And PUBLIC in Postgres includes the anon role, which is the role used when someone hits your endpoint with curl without a token. In other words, your calculation functions — pay_echeance_tx, publier_planning_tx, convertir_sd_tx — are exposed by default to anyone on the internet.

I discovered this by auditing the public surface with the following control query:

-- List functions executable by anon (dangerous by default)
SELECT p.proname, n.nspname
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
  AND has_function_privilege('anon', p.oid, 'EXECUTE');

It returned fifteen functions I had never wanted to expose. Bulk fix, plus ALTER DEFAULT PRIVILEGES so future functions inherit the right permissions:

-- Close all existing functions to anon
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;
GRANT  EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO authenticated, service_role;

-- So future functions inherit the rule
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT  EXECUTE ON FUNCTIONS TO authenticated, service_role;

Legitimate public flows — enrollment form, QR-code attendance signing — all go through Next.js API routes that use the service_role. Revoking anon broke nothing. What should have been the default behavior, and isn't.

Trap 3 — Write policies without a role check

Third trap. You enable RLS on a table, you write a SELECT policy saying any authenticated user can read. You forget to write the INSERT / UPDATE / DELETE policy, and Supabase makes the worst possible choice: it allows it, because in Postgres, without an explicit write policy, the table is open to any role with basic Postgres rights.

In other words, any authenticated user can write to any table where you only set the read policy. A student with an account can insert a row into contrats_formateurs. They won't, but they could, and the day an account gets compromised, the attack surface is your whole database.

The pattern I now apply to every new table: a SELECT policy for staff+, an INSERT / UPDATE / DELETE policy for admin+ only, with explicit role check against user_roles.

-- Read for staff and above
CREATE POLICY "select_staff" ON contrats_formateurs
  FOR SELECT TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM user_roles
      WHERE email = auth.email()
        AND role IN ('staff', 'admin', 'super_admin')
    )
  );

-- Write for admin only
CREATE POLICY "write_admin" ON contrats_formateurs
  FOR ALL TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM user_roles
      WHERE email = auth.email()
        AND role IN ('admin', 'super_admin')
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM user_roles
      WHERE email = auth.email()
        AND role IN ('admin', 'super_admin')
    )
  );

The WITH CHECK is the half that's always forgotten. Without it, a user allowed to write can write a row they would not be allowed to read back. It's a classic in RLS audits: read policy and write policy must converge, or the system becomes inconsistent.

Trap 4 — The forgotten public Storage bucket

Last trap, the one that makes headlines when it leaks. You create a Supabase Storage bucket to store handwritten signatures, supporting documents, ID photos — GDPR-sensitive data. By default, the bucket is public. You've probably set RLS on your tables, you're proud, you forget the files live alongside, with their own rules.

Concretely: anyone who knows a file's URL can download it, and the URL is sometimes traceable, guessable, or exposed as a plain-text path in a database column. It took me three weeks to notice. The fix is two steps.

Step 1: make the bucket private via the Supabase dashboard, or by migration:

UPDATE storage.buckets
SET public = false
WHERE name = 'signatures';

Step 2: in code, stop using getPublicUrl(). Store the path instead and serve the file through an authenticated API route that checks permission and returns a signed URL expiring in five minutes.

// ❌ Public URL, valid forever, indexable
const { data } = supabase.storage
  .from('signatures')
  .getPublicUrl(path)

// ✅ Expiring signed URL, after permission check
const { data } = await supabaseAdmin.storage
  .from('signatures')
  .createSignedUrl(path, 60 * 5)  // 5 minutes

The fifth trap, as a bonus, the one you don't see coming

There's another, rarer but spectacular when it fires: the infinite recursion on user_roles policies. If your policy on user_roles itself uses an EXISTS (SELECT 1 FROM user_roles…) to verify the role, you've created a loop: reading user_roles calls the policy that reads user_roles that calls the policy. Postgres returns an infinite recursion detected in policy error, and every query that goes through that table fails.

The fix: the user_roles policy can't reference user_roles. It has to be formulated on auth.email() directly, or routed through a SECURITY DEFINER, or — what I did for several weeks before finding better — leave the table readable to any authenticated user and protect writes elsewhere.

What you can copy into your project

Four directly applicable reflexes:

1. Audit of the anon surface — the SQL query above returns, in thirty seconds, the list of exposed functions. If you've never run this audit, do it today
2. createSupabaseAdmin() by default in Server Components — auth is already verified upstream by your middleware. The SSR client with anon key is a silent-empty-query factory
3. A USING + WITH CHECK pair on every write policy. No write policy without a check. No read policy without a write policy
4. A diff script that lists tables with RLS enabled but no policies — it's a classic trap when creating a new table, and the best time to fix it is immediately

And a broader discipline: a permission system that doesn't scream when it fails is a dangerous system. RLS is powerful because it's invisible, and that's also why it will cost you. Instrument it: audit the anon surface monthly, log queries that come back empty on pages that should be populated, alert when a bucket changes visibility.

And you — your last query that returned zero rows in production, was it really zero rows, or RLS filtering silently? I read the comments.

---

Companion code: rembrandt-samples/rls-supabase/ — the anon-surface audit, the SELECT + WRITE policy pair with WITH CHECK, the recursion-safe user_roles pattern, the storage privatization migration, the client selection guide, and the RLS-without-policies detector. MIT, copy-pastable.