Supabase RLS Errors in Production: Debug Empty Results

When Supabase Row Level Security blocks a query, reads usually return an empty array with no error, while inserts and updates fail with new row violates row-level security policy (Postgres code 42501); debug it by checking who the request is authenticated as and which policy applies.

This is one of the most confusing production bugs for teams new to Supabase, because "no rows" looks identical to "no data".

The Two Symptoms

An empty list on a table you know has rows is the signature of a read blocked by RLS.

Debug It in 4 Steps

1. Confirm RLS is on and list the policies.

select tablename, rowsecurity from pg_tables where schemaname = 'public';
select * from pg_policies where tablename = 'orders';

RLS enabled with no policy denies everything. That is the most common first-day mistake.

2. Check who the request is. In the SQL editor you run as a privileged role, so your query "works" there. Your app runs as anon or authenticated. Reproduce as the user:

set local role authenticated;
select set_config('request.jwt.claims', '{"sub":"<user-uuid>"}', true);
select * from orders;

3. Verify auth.uid() is not null. A policy like using (auth.uid() = user_id) returns nothing when no session reaches the database.

4. Read the policy against the data. A policy checking user_id fails if rows were inserted with a different column, a null, or by another tenant.

Why It Works Locally but Not in Production

The usual cause is that server code has no session. With Next.js, create the Supabase client with @supabase/ssr so it reads the auth cookies. A client created with only the anon key makes every request anonymous.

import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export async function supabaseServer() {
  const store = await cookies()
  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    { cookies: { getAll: () => store.getAll(), setAll: () => {} } }
  )
}

If the session is lost after login only in production, check cookie settings the same way you would for NextAuth sessions returning null, and confirm your environment variables exist in production (env vars undefined).

The Dangerous "Fix": Using the Service Role Key

The service role key bypasses RLS, so it makes the empty results vanish. It also removes the security boundary. Never ship it to the browser, never use it in a request path that handles user input without your own authorization checks, and keep it out of NEXT_PUBLIC_ variables. Use it only in trusted server code such as webhooks and background jobs, and filter by user explicitly.

Performance Note

Policies run per row. Index every column used in a policy (user_id, org_id), and wrap auth.uid() as (select auth.uid()) so Postgres evaluates it once per query rather than per row.

Handle Errors in Code

const { data, error } = await supabase.from('orders').select('*')
if (error) throw error
if (!data?.length) console.warn('empty result: check RLS and session', { userId })

Log the user ID on empty reads of tables that should never be empty for that user. It turns an invisible bug into a signal.

Let Relia Surface It

An RLS block never throws, so error trackers stay quiet while the page shows "No orders yet". Relia records the user's session and the response, links the empty state to the failing request, and writes the incident up in plain words: what happened, why it thinks so (with a confidence level), and one thing to do next. On request it writes a fix plan, such as the corrected policy or client setup, with a "Copy for your AI assistant" button; Relia doesn't change code itself.

FAQ

Why does Supabase return an empty array instead of an error?

RLS filters rows out of the result instead of raising an error, so a blocked read looks like an empty table.

What is Postgres error 42501 in Supabase?

It is an insufficient privilege error. On inserts it means no policy allows the row you tried to write.

Is it safe to disable RLS to fix this?

No. With RLS off, anyone holding your public anon key can read the table. Write the correct policy instead.