Supabase RLS policy not working: why the query returns all rows

A Supabase query that returns all rows means that the caller bypasses RLS, or that a policy allows every row. The most common causes are RLS turned off, a second permissive policy with using (true), a view without security_invoker, a service_role key in the client, and a test run as the postgres role. Find which cause applies, fix it, and test as the real role.

This guide gives a short test for each cause, in the order that finds most problems first. It also shows how to check the damage and how to prevent the problem. For a full RLS tutorial, read the Supabase RLS guide. This page stays on the one problem.

What does “RLS returns all rows” mean?

It means that a user sees rows that the policy should hide. You wrote a policy such as using (auth.uid() = user_id). A test shows rows for other users. The policy looks correct, but it has no effect on the query.

Row Level Security works in a fixed way. The Supabase RLS guide states that when RLS is on and no policy exists, the API returns no data to a publishable-key caller. Each policy then adds access. The guide also states that a secret key uses the service_role Postgres role, which has the bypassrls attribute. Such a request skips RLS.

The Postgres documentation adds the rule for policy combination. The CREATE POLICY page states that permissive policies for one query combine with the Boolean OR operator. If one policy allows a row, the row is visible. A strict policy cannot block a row that a different policy allows.

Five causes explain nearly all cases:

A sixth cause is a missing session. The policy uses auth.uid(), and the request has no user. That case usually returns no rows, not all rows. It is still part of a correct test, so this guide covers it.

How do I find which cause applies?

Run the checks in this order. Each check takes less than a minute.

1. Check the RLS flag. Run this query in the SQL Editor:

select c.relname,
       c.relrowsecurity as rls_on,
       c.relforcerowsecurity as rls_forced,
       c.relowner::regrole as owner
from pg_class c
where c.oid = 'public.notes'::regclass;

Replace public.notes with your table. If rls_on is false, RLS is off. That is cause 1.

2. List all policies on the table. Run this query:

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

Read the qual column for each policy with cmd set to SELECT or ALL. If any qual is true, that policy shows every row. If permissive is PERMISSIVE for that policy, it overrides the strict policies. That is cause 2.

3. Check whether the query goes through a view. Look in your code for the object name in from("..."). Run this query to list views:

select table_name from information_schema.views where table_schema = 'public';

If the name is a view, test the table behind it separately. That is cause 3.

4. Check which key the client uses. Search your code and your deployed bundle for service_role and for a key that starts with sb_secret_. Also check which key the failing code path uses on the server. That is cause 4. Read the service_role key guide.
5. Check how you tested. If you saw the result in the SQL Editor, the Dashboard ran the query as postgres. The Supabase roles guide states this. Table owners normally bypass row security, according to the Postgres RLS documentation. If owner in step 1 is postgres, that is cause 5.

Note: More than one cause can apply. Fix each one, and test again after each fix.

How do I test a policy as the real role?

Do not trust a test from the SQL Editor or from a server script with the secret key. Test as the role that your users have. The RLS guide shows how to switch the role and the identity with set local role and set local request.jwt.claim.sub.

Run this test in the SQL Editor:

begin;
set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';

select count(*) from public.notes;
select count(*) from public.notes where user_id <> '11111111-1111-1111-1111-111111111111';

rollback;

Replace the UUID with the ID of a real test user. The first count shows the rows that the user can read. The second count must be zero. If the second count is above zero, the policy lets the user read the rows of other users.

Run the same test for the anonymous caller:

begin;
set local role anon;
select count(*) from public.notes;
rollback;

For a table that holds private data, the count must be zero.

Then test the real API from outside. This test uses only the public key, as a visitor would:

curl -s "https://<project_ref>.supabase.co/rest/v1/notes?select=*&limit=5" \
  -H "apikey: <ANON_KEY>" \
  -H "Authorization: Bearer <ANON_KEY>"

An empty array is the correct result for a private table. Repeat the call with the session token of a test user. The result must contain only the rows of that user.

What do I do first? Fix it now, in order of urgency

Fix the open access first. Then fix the policy design. Use the cause that your checks found.

Caution: A fix can hide rows that your app needs. Test the main pages of your app after each change. A hidden page costs less than a leak.

1. Turn on RLS if it is off. Run:

alter table public.notes enable row level security;

With no policy, the API returns no rows. Add the owner policies from step 3 at once.

2. Remove the open policy. Delete each policy with qual set to true, unless the table holds only public data:

drop policy "your open policy name" on public.notes;

Use the exact name from the pg_policies output. Names are case sensitive and can contain spaces and dots.

3. Write an owner policy. A signed-in user reads and writes only their own rows:

create policy "owner reads own notes"
on public.notes
for select
to authenticated
using ((select auth.uid()) = user_id);

create policy "owner inserts own notes"
on public.notes
for insert
to authenticated
with check ((select auth.uid()) = user_id);

create policy "owner updates own notes"
on public.notes
for update
to authenticated
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "owner deletes own notes"
on public.notes
for delete
to authenticated
using ((select auth.uid()) = user_id);

The RLS guide recommends the select wrapper around auth.uid() for speed. The using clause decides which existing rows a user can reach. The with check clause validates the rows that a write creates.

4. Fix each view. Make the view obey the policies of the table below it. This setting works on Postgres 15 and later:

alter view public.my_notes_view set (security_invoker = true);

The RLS guide states that views bypass RLS by default, because the postgres user usually creates them. Test the view again after the change.

5. Remove the service_role key from the client. Delete the variable from the browser code and from any public variable such as VITE_ or NEXT_PUBLIC_. Create a new key and keep it on the server only. The old key is public, so rotate it. Use the service_role guide for the steps.
6. Test again with the real roles. Use the tests from the previous section. Do not use the SQL Editor result as proof.
7. Check the damage. Use the section below.

Why does a second policy break the first one?

This case surprises most people. You write a strict policy and test it. The strict policy is correct. Another policy on the same table still shows all rows.

The OR rule causes this. Suppose the table has two select policies:

A row is visible when A is true or B is true. B is always true, so every row is visible. Policy A has no effect.

Tools that write SQL for you create this state. A first prompt creates a wide policy so that the page works. A later prompt adds a strict policy and does not remove the first one. The table then has both.

The fix is to delete the wide policy. If you need a rule that no other policy can undo, use a restrictive policy. The Postgres documentation states that restrictive policies combine with AND. It also states that at least one permissive policy must exist before a restrictive one is useful. Use restrictive policies only after you understand this rule. A clean set of permissive policies is simpler.

Also check the to clause. A policy with to anon applies to signed-out callers. A policy with no to clause applies to all roles. An omitted to clause on a wide policy is a common cause of a public read.

Why does the filter in my frontend not protect the data?

A filter such as .eq("user_id", user.id) in your React code limits the rows that your page asks for. It does not limit the rows that the server allows. A caller can send the same request without the filter.

Only the policy limits the rows. Treat the frontend filter as a convenience and the policy as the rule. If the policy is wrong, the filter hides the problem on your screen. This is the reason why you can test your app by hand and see only your own rows, while the API returns all rows.

Use the curl test from this guide, because it does not use your frontend code.

How do I check the damage?

You need to know which rows were readable, by which role, and for how long. Not every read leaves a record.

1. Find the start date. Look in your migrations and your commit history for the change that added the open policy or turned RLS off. If you used the Dashboard, use the creation date of the table as the latest start.
2. Name the exposed role. Your tests show who could read the rows. The roles are anon, authenticated, or both. An exposure to anon is worse, because it needs no account.
3. Read the API logs. The Supabase logs guide names API Gateway and PostgREST as log sources. Open Logs in the Dashboard. Search for requests to /rest/v1/notes.
4. Look for unusual reads. Look for requests without a filter, with select=*, or with a large limit. A normal page asks for the rows of one user.
5. Check for writes. If the policy was open for insert, update, or delete, check the table for rows that you did not expect. Compare the row counts with an old backup.
6. Check the retention. The logs guide states that retention depends on your pricing plan. If the exposure is older than the logs, you cannot prove that no one read the table.
7. Check the key. If the service_role key was in the client, assume that an attacker had full access to the project data. Rotate the key and review all tables, not only the one you found.

If the table held personal data, ask a legal adviser whether you must tell the affected users.

How do I stop this from happening again?

Use these controls in each project.

1. Enable RLS in the migration that creates the table. Do not leave it for later.
2. Write policies for each operation. Use separate select, insert, update, and delete policies. Name each policy with the table and the action.
3. Keep one policy per operation and role when you can. This avoids the OR rule problem.
4. Review each using (true). Allow it only for tables that hold public data.
5. Add the policy list query to your release check. Run the pg_policies query and read each qual value.
6. Test with roles, not with the SQL Editor. Keep the set local role test in a file in your repository. Run it after each migration. The RLS guide shows how to write database tests.
7. Keep the secret key on the server. Use the anon or publishable key in the browser. See the anon key guide for what that key can reach.
8. Scan the live app. A scan with the public key finds tables that return rows to a caller who must not see them.

When should I get help?

Get help in these cases:

• The table holds personal, financial, or health data, and the exposure lasted more than a short time.
• The logs show large reads from addresses that you do not know.
• The service_role key was in a public bundle. Rotate the key and ask a security engineer to review the project.
• You have many policies and views, and you cannot tell which one allows a row. Send the pg_policies output to an experienced reviewer.
• Your app uses database functions that run with the rights of their owner, and these functions return rows. Such a function can return rows to any caller who may run it, no matter what the table policies say. Have an expert review each function.

After the fix, run the three tests again: the anon role test, the authenticated test with a second user, and the curl test with the public key. Each test must return only the rows that the caller may see.