RLS Returned Zero Rows, but the Data May Still Exist

Your write succeeded, but the list is empty and the logs show no failed request. If you lead a web app, this article helps you separate missing data from row-level security filtering. You will compare the same direct-table query under two roles on a non-production copy. You do not need to change a live policy.

Every platform claim below was checked against vendor documentation on 2026-09-23. App Rescue Desk is independent and is not affiliated with Supabase.

TL;DR

• RLS can return [] or (0 rows) without an error when no policy exposes the row.
• An owner or privileged SQL Editor can see rows that the app role cannot see.
• Compare the same direct-table query as an inspection role and app role on a quiet non-production copy.

What you see

The symptom is [] in an API client or (0 rows) in psql. This path may produce no RLS error.

[]
(0 rows)

A failed WITH CHECK expression behaves differently. PostgreSQL documents this in CREATE POLICY. PostgreSQL can return:

new row violates row-level security policy for table "<table>"

A USING or SELECT policy that evaluates false or null usually hides the row instead of returning an error. The empty result does not prove that the row is missing. On a page that renders no fallback, the same empty payload can look like a blank screen in production.

Why owner checks can mislead you

PostgreSQL’s row-security documentation says table owners normally bypass RLS unless the table uses FORCE ROW LEVEL SECURITY. Superusers and roles with BYPASSRLS also bypass it. If the owner sees the row, you have only confirmed that the owner can see it.

Supabase’s Dashboard execution documentation says Dashboard SQL Editor queries run through the postgres role. Supabase Studio can also select another role and impersonate a user, as documented in its role-selector announcement. This is Supabase Studio behavior, not a rule for every SQL editor.

Do not disable RLS on a live table to test this theory. That changes the condition you are trying to inspect and can expose rows that the live policy normally filters.

A frontend filter can also return an empty result. The comparison below therefore uses the same direct-table filter under both roles.

How RLS hides rows without an error

RLS is the PostgreSQL policy layer that decides which rows an authorized role can read or modify. Table and column grants are checked before RLS. A missing grant produces a permission error instead of an empty RLS result. Supabase explains this distinction in its RLS guide.

For this empty-result case:

1. RLS is enabled.
2. No permissive policy matches the role and claims.
3. The row fails the applicable USING or SELECT expression.
4. PostgreSQL hides the row from that query.

request role + claims
        ↓
table/column grants
        ↓
RLS policy match
        ↓
visible rows

owner / superuser / BYPASSRLS
        └──────────────→ may bypass the app role's RLS check

When RLS is enabled and no policy applies, PostgreSQL uses default-deny. If a policy depends on auth.uid() and the request has no authenticated user, Supabase documents that auth.uid() returns null. An equality check then does not match.

A write can succeed while a read stays empty

Write and read policies answer different questions. An insert can satisfy its WITH CHECK condition while a later SELECT exposes no row to the app role.

PostgreSQL documents an important boundary. INSERT ... RETURNING requires SELECT privilege. If the new row does not satisfy the table’s SELECT policies, PostgreSQL returns an error instead of silently omitting it. See CREATE POLICY and INSERT.

This article treats “write succeeded, later read is empty” as documented behavior. It does not claim a locally observed demo result.

If the failed write is a file upload and the error names objects, check the Lovable file upload 403 and Storage RLS diagnosis. That case compares the upload path with the Storage policy; the direct-table test below does not exercise the Storage API.

New tables can start in default-deny

Supabase’s rls_auto_enable() and ensure_rls example enables RLS for new public tables after the trigger is installed. The example does not create policies. A new table can therefore start in default-deny until its policies exist. What rls_auto_enable does, and whether to keep it covers the trigger, the logs and the keep-or-drop decision.

Supabase documents that secret keys authorize through the service_role Postgres role, which has BYPASSRLS. A secret key or legacy service_role key must not appear in browser code.

Confirm it on a non-production copy

Do not run this as an experiment on live production. Use a non-production copy with only data you are authorized to inspect.

Keep the copy quiet while you run both transactions. End each transaction with ROLLBACK.

If the live incident is still active, stop before changing the live policy. Finish the copy comparison first.

This procedure tests a direct-table query. A view can use the view owner’s rights unless it uses security_invoker. Check the view mode before using this procedure for a view.

Use the failing query’s exact table and filter. Replace the neutral UUIDs with test values you control.

First, check the inspection role

Run this block as the owner only when rls_forced is false. Otherwise, use a documented inspection role that bypasses RLS on the copy.

-- non-production copy only
BEGIN;

SELECT current_user, session_user;

SELECT c.relrowsecurity AS rls_enabled,
       c.relforcerowsecurity AS rls_forced,
       pg_get_userbyid(c.relowner) AS table_owner
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname = 'documents';

SELECT policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
  AND tablename = 'documents'
ORDER BY policyname;

SELECT count(*) AS inspection_count
FROM public.documents
WHERE tenant_id = '00000000-0000-0000-0000-000000000001';

ROLLBACK;

Record the role, RLS state, policy definitions, and inspection_count.

Then, check the app role

Use a separate transaction. Use a test user you control.

The session user must be allowed to SET ROLE authenticated. A superuser can select any role.

Supabase’s testing overview documents this local pattern for policies that depend on auth.uid().

If the policy reads other auth.jwt() fields, this block is not sufficient. Use a test harness that provides every claim the policy reads.

-- non-production copy only
BEGIN;

SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claim.sub = '00000000-0000-0000-0000-000000000002';

SELECT current_user,
       current_setting('request.jwt.claim.sub', true) AS test_user_sub,
       has_table_privilege(current_user, 'public.documents', 'SELECT') AS has_select;

SELECT count(*) AS application_count
FROM public.documents
WHERE tenant_id = '00000000-0000-0000-0000-000000000001';

ROLLBACK;

Confirm that has_select is true. If it is false, fix or confirm the grant before you interpret RLS behavior.

These blocks show a documented test pattern, not observed incident output. Record the actual role, test UUID, grant result, and counts from your own copy.

Read the two counts together

PostgreSQL documents that row_security = off does not bypass RLS. It can make the query fail instead of hiding rows. PostgreSQL can return:

query would be affected by row-level security policy for table "<table>"

This setting does not identify the hidden row or policy. Use the two-role comparison first.

Fix the policy on the copy

Save the current policy definition before you edit anything. That definition is your rollback point for the copy.

Change the narrowest policy that should match the intended role and tenant or user condition.

Run the same two-role comparison again.

Test one user who must see the row. Then test another user who must not see it.

Test read, insert, update, and delete only when the application requires those actions. A successful read does not prove write isolation.

Promote the reviewed policy as a migration with a named rollback step. A policy change can affect every request whose role and command match it.

This test does not cover untested roles, commands, tables, claims, views, joins, RPCs, or application paths.

Never route a browser request through a Supabase secret key or legacy service_role key. If a secret reached browser code, remove it from the client configuration first. Rotate it using Supabase’s documented key procedure. Suspected misuse needs a security-incident specialist; live breach response is outside this desk’s scope.

Prevent the same failure

Ship policies in the same reviewed migration that enables RLS. If ensure_rls is installed, add policies for every new public table because the documented trigger creates none.

Add a rolled-back role test for each sensitive table and role. Include one permitted row and one forbidden row.

Use FORCE ROW LEVEL SECURITY only when you have tested the owner and maintenance paths that it changes. Superusers and BYPASSRLS roles still bypass it.

When the direct-table test is not enough

The test becomes a policy-review problem when several tables depend on one another or one policy change can widen access across tenants.

For an RLS & Data Isolation Review, the desk checks policies and role memberships on a copy. The review maps what each intended role and tenant can read, insert, change, and delete before any live policy edit.

This is a policy review, not a penetration test. If the failing path uses a view, join, RPC, custom claims, or a suspected live compromise, this direct-table test is not sufficient by itself. Suspected live compromise needs a security-incident specialist.

What this test does not prove

An empty result means the app role received no visible row for the direct-table query you tested. It does not tell you whether the row is absent, filtered by a view, or hidden by different claims.

The comparison can show that RLS filtered rows for the tested role, claims, filter, and table. It does not prove that the policy is correct. A policy that exposes the wrong tenant’s rows can still return a non-zero result.

The web app bug fixing service covers this kind of previously working production failure. If the comparison still points to RLS and you need help restoring the live workflow, send the bug fix request — scope and price agreed before anything in production changes.