RLS DELETE policy matches auth.uid() correctly but affects 0 rows (authenticated role); superuser delete with identical filter succeeds

[ErkTeker]

Project ref: qotymdmeqfbwminykkxu
Postgres version: PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit

Table: public.push_tokens (columns: id uuid, user_id uuid not null, token text not null unique, created_at/updated_at timestamptz)

RLS: enabled, single policy:

create policy "can remove own push token" on public.push_tokens
  for delete to authenticated using (user_id = auth.uid());

Repro (via PostgREST, anon key, real anonymous auth session — reproduced with a freshly created auth user each time):

1. Sign in anonymously -> get JWT with sub = <uid>, role = authenticated.
2. Insert a row via a SECURITY DEFINER function using auth.uid() internally (confirms user_id is stored correctly).
3. DELETE FROM push_tokens WHERE user_id = <uid> as the authenticated role — reproduced three independent ways:
   • Via PostgREST client: .delete().eq('user_id', uid).count(exact)
   • Via raw SQL simulation: SET LOCAL ROLE authenticated; SET LOCAL request.jwt.claims to the same sub/role; DELETE ...; GET DIAGNOSTICS row_count
   • Via a completely fresh anonymous test user (never touched by the app/browser), confirming this isn't specific to one session/account
4. Result: 0 rows affected, every time, across all three methods. No error is raised.
5. The exact same DELETE, with the exact same literal user_id, run as a superuser (RLS bypassed), succeeds immediately and removes the matching row(s).
6. Also confirmed: filtering by a different column (token instead of user_id) under the same authenticated RLS context produces the identical silent 0-rows-affected result — this doesn't appear to be specific to the user_id column or its policy expression.

Already ruled out:

• auth.uid() resolves to the correct value in the same session/transaction where the DELETE fails (confirmed via RAISE NOTICE / SELECT auth.uid() immediately before the DELETE, and via a single atomic DO $$ ... $$ block to eliminate any statement-boundary/session ambiguity).
• JWT role claim is genuinely authenticated, not anon (checked directly, decoded from the actual Authorization header sent in the HTTP request).
• Session/token is fresh, not expired; refreshSession() before the delete made no difference.
• Policy text reconfirmed via pg_policies; dropped and recreated identically — no change in behavior.
• user_id column type is plain uuid, no cast/whitespace mismatch (matches_explicit_cast=true checked explicitly against every candidate row).
• No triggers on the table (pg_trigger checked, empty).
• GET DIAGNOSTICS ... row_count used as the affected-row measure (independent of RETURNING/SELECT-policy visibility, since this table intentionally has no SELECT policy — ruling out a RETURNING-visibility red herring).

Workaround in place: routing the delete through a SECURITY DEFINER function that resolves auth.uid() internally and bypasses RLS entirely, mirroring the existing register_push_token() pattern already used for INSERT in this project. This works reliably and has been verified independently against the real project multiple times.

Filing this because the underlying RLS DELETE-under-authenticated-role behavior itself appears broken for this project/table, not because we lack a workaround. Happy to provide any additional diagnostic queries if useful.

[GaryAustin1]

By default supabase-js needs the select policy to delete if you are filtering which you almost certainly are.

[cekuu35]

This is a DELETE command type mismatch — the policy likely has cmd = 'SELECT' or cmd = 'UPDATE' but not DELETE.

Check (run in SQL Editor):

SELECT policyname, cmd, roles, qual, with_check
FROM pg_policies WHERE tablename = 'your_table';

Fix: Recreate with FOR ALL (covers SELECT, INSERT, UPDATE, DELETE):

DROP POLICY IF EXISTS "your_policy" ON your_table;
CREATE POLICY "owner_all" ON your_table
  FOR ALL TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

Why SQL works but API fails: SET ROLE authenticated bypasses command-type checks; the client library hits the real RLS path where cmd matters.

Free audit script: https://github.com/cekuu35/supabase-rls-leak-demo

[cekuu35]

This is a DELETE command type mismatch — the policy likely has cmd = 'SELECT' or cmd = 'UPDATE' but not DELETE.

Check (run in SQL Editor):

SELECT policyname, cmd, roles, qual, with_check
FROM pg_policies WHERE tablename = 'your_table';

Fix: Recreate with FOR ALL (covers SELECT, INSERT, UPDATE, DELETE):

DROP POLICY IF EXISTS "your_policy" ON your_table;
CREATE POLICY "owner_all" ON your_table
  FOR ALL TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

Why SQL works but API fails: SET ROLE authenticated bypasses command-type checks; the client library hits the real RLS path where cmd matters.

Free audit script: https://github.com/cekuu35/supabase-rls-leak-demo

[zoubingwu]

PostgreSQL’s CREATE POLICY docs explicitly mention this behavior for DELETE:

“the appropriate SELECT or ALL policies will be applied in addition to the DELETE policies.”

and:

“the user must have access to the row(s) being deleted through a SELECT or ALL policy”

So having a valid DELETE policy alone may not be sufficient if the row is not also visible through a SELECT/ALL policy.

[alpsten]

One thing worth flagging before chasing the SELECT-policy angle: testing as superuser doesn't tell you much here — superuser bypasses RLS entirely, so "it works as superuser" just confirms the row exists and your filter matches, not that your policy's auth.uid() logic is correct.

To isolate this properly, use Supabase Studio's SQL Editor role impersonation (supabase.com/features/user-impersonation) and run the delete directly as that specific user:

DELETE FROM public.push_tokens
WHERE user_id = auth.uid()
returning *;

That tells you definitively whether the policy itself is the problem, independent of anything supabase-js/PostgREST is doing on top. While you're in there, check what policies actually exist on the table:

SELECT policyname, cmd, roles, qual, with_check
FROM pg_policies
WHERE tablename = 'push_tokens’;

If you only see the one delete row, that confirms there's no select policy at all, which lines up with the most common cause of this exact symptom: PostgREST needs a matching SELECT policy to correctly report what a delete affected, not just a DELETE policy.

Fix: add a select policy scoped the same way, rather than switching to for all unless you actually want authenticated users to insert/update this table too:

CREATE POLICY "can view own push token"
ON public.push_tokens
for SELECT
to authenticated
using (user_id = auth.uid());

Then re-run your original delete, it should now report the correct row count.

[patelved3313]

Your DELETE policy looks correct. The issue is most likely that you don’t have a SELECT policy.

With Supabase RLS, a filtered DELETE can only delete rows that are also visible through a SELECT policy.

Try adding:

create policy "can view own push token"
on public.push_tokens
for select
to authenticated
using (auth.uid() = user_id);

Keep your existing DELETE policy, then try the delete again.

So basically:

SELECT policy = lets Supabase find the row
DELETE policy = lets the user delete it