Supabase select returns empty array: RLS, not a bug

When a Supabase select returns [] with no error but the table editor shows rows, Row Level Security is filtering the query. RLS is enabled and either no policy exists for that role and command, or the request didn't satisfy the one that does. Postgres doesn't raise an error for rows it hides — it just returns fewer. Find out which role the request ran as and which policy it missed. Don't disable RLS to test it, even temporarily.

The table editor isn't lying and neither is your query. The dashboard runs as the postgres role, which owns your tables and isn't subject to their policies. Your app runs as anon or authenticated, which is.

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

Because RLS on a read is a filter, not a permission check. A policy is a where clause Postgres adds to your query; a row that fails it is invisible, not forbidden. Zero matching rows is a successful query with an empty result.

Writes behave differently — a blocked insert raises new row violates row-level security policy — which is why an empty read feels like a bug by comparison.

Supabase's own troubleshooting page gets the cause right:

"Usually this means you have RLS (row level security) enabled and no policy, or do not meet the policy. It can also mean you have a filter and have no rows matching that."

Then it gives this as the diagnostic:

"If you have RLS enabled you can test by disabling RLS on the table."

It goes on to suggest temporarily setting a policy to anon with true if your policy requires authenticated users. Both work as diagnostics. Both are also the two most common real-world RLS vulnerabilities, and "temporarily" is how they end up in migrations. There's a way to get the same answer without opening the table.

Diagnose it without turning anything off

1. Is RLS on, and what policies exist?

select c.relrowsecurity as rls_enabled
from pg_class c
where c.oid = 'public.orders'::regclass;

select policyname, cmd, roles, qual
from pg_policies
where schemaname = 'public' and tablename = 'orders';

• RLS on, zero policies → every read returns []. With no policy, the default is deny. This is the single most common cause.
• Policies exist, but none with cmd of SELECT or ALL → you can insert but not read back.
• A SELECT policy whose roles don't include the role your request uses → e.g. {authenticated} while the request runs as anon.

2. Which role did the request actually use?

If the client has no session, the request runs as anon, and a policy written to authenticated doesn't apply. On the server this is often a client created without the user's cookies. Check it where the query runs:

const { data: { user } } = await supabase.auth.getUser();
console.log(user?.id ?? "no user: this query runs as anon");

If there is a user but auth.uid() still doesn't match, you're in the auth.uid() returns null family of problems.

3. Reproduce the request's view, safely.

This runs the query exactly as a specific signed-in user would see it, inside a transaction that's rolled back:

begin;
set local role authenticated;
select set_config(
  'request.jwt.claims',
  json_build_object('sub', '<user-uuid>', 'role', 'authenticated')::text,
  true
);

select * from public.orders;   -- what the app sees
select auth.uid();             -- what the policy sees

rollback;

If this returns rows, the policy is fine and the problem is the session in your app. If it returns [], the policy doesn't match that user — read its qual against the row's actual values. Testing RLS policies properly turns this into a repeatable test.

4. Rule out the boring cause. The doc's second sentence matters: a filter that matches nothing also returns []. A user_id stored as text compared with a UUID, a stale ID from a different environment, a trailing space — check the .eq() values before blaming RLS.

If you're calling .single(), the same empty result surfaces as an error instead: PGRST116.

The diagnostic that ships

Here's what "test by disabling RLS" looks like once it's saved as a migration so the fix can be "tried in staging":

Two findings, one per piece of advice. Disabling RLS makes orders readable and writable by anyone holding your publishable key — which ships in your JavaScript bundle by design. The anon + using (true) policy is subtler and arguably worse: it keeps reading open even after someone remembers to re-enable RLS, because the policy still grants every row to every anonymous visitor.

Neither one looks alarming in a diff titled "debug empty orders query". That's the problem. Once it's committed it gets applied to every environment the migration runs in, including production. Disabled RLS is among the most frequent findings in real Supabase repos for exactly this reason.

The fix is almost always a policy

For the common case — users read their own rows:

alter table public.orders enable row level security;

create policy "users read their own orders"
  on public.orders for select
  to authenticated
  using ((select auth.uid()) = user_id);

Wrapping auth.uid() in select lets Postgres evaluate it once per query instead of once per row. For data that genuinely is public, write that deliberately, for select only, with a name that says so — and never on a table that also holds private rows.

Quick self-check

-- Tables that will silently return [] (RLS on, no policies) and tables
-- that will return everything to anyone (RLS off).
select c.relname as table_name,
       c.relrowsecurity as rls_enabled,
       count(p.policyname) as policy_count
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policies p
  on p.schemaname = n.nspname and p.tablename = c.relname
where n.nspname = 'public' and c.relkind = 'r'
group by c.relname, c.relrowsecurity
order by c.relrowsecurity, policy_count;

Then make sure no debugging made it into version control:

grep -rniE "disable row level security|using \(true\)" supabase/migrations

FAQ

Why does the query work in the SQL editor but return [] in my app? The SQL editor runs as postgres, which isn't bound by the table's policies. Your app runs as anon or authenticated, which is.

RLS is enabled and I wrote a policy. Why is it still empty? Check the policy's command and roles. A policy for insert doesn't allow reads, and a policy to authenticated doesn't apply to a request without a session.

Does using the service role key fix it? It bypasses RLS, so yes, you'll get rows — and so will anyone who gets that key. Fine on the server for admin tasks; never as a fix for a client query.

Is disabling RLS for five minutes really a problem? On a local database, no. On a shared or hosted one, the table is open for those five minutes, and "temporary" changes that get committed have a long half-life.

Why doesn't Supabase just return a permission error? Because Postgres RLS filters reads by design. An error would also leak that rows exist that you can't see.