Two permissive UPDATE policies, both individually verified correct, but the combined UPDATE still throws 42501

[jwhicks727]

I've got a table (points) with two permissive UPDATE policies:

sql
create policy "points_soft_delete" on points
for update to authenticated
using (auth.uid() = staff_id)
with check (auth.uid() = staff_id);

create policy "admins_update_any_point" on points
for update to authenticated
using (
exists (select 1 from profiles where profiles.id = auth.uid() and profiles.is_admin = true)
)
with check (
exists (select 1 from profiles where profiles.id = auth.uid() and profiles.is_admin = true)
);

Goal: an admin (is_admin = true) should be able to update any row, not just their own (staff_id). Non-admins should still be limited to their own rows via the first policy.

Testing as an admin trying to update a row where staff_id belongs to someone else — I keep hitting:

ERROR: 42501: new row violates row-level security policy for table "points"

Here's what I've independently confirmed, using a simulated session (set local role authenticated; set local request.jwt.claims = '{"sub": "", "role": "authenticated"}'):

1. The admin check itself evaluates true, run standalone against the target row:

sql
select
id, staff_id,
(staff_id = auth.uid()) as owns_point,
exists (select 1 from profiles where profiles.id = auth.uid() and profiles.is_admin = true) as admin_check
from points where id = '';
-- owns_point: false, admin_check: true

2. With admins_update_any_point temporarily dropped (inside a transaction, rolled back after), the same UPDATE against points_soft_delete alone returns 0 rows, no error — confirming that policy behaves exactly as documented on its own (silently filters non-owned rows rather than throwing).

3. With both policies present, both given explicit matching WITH CHECK clauses (shown above — no implicit-fallback ambiguity), the identical UPDATE still throws 42501, across multiple fresh sessions/connections.

Also ruled out: RLS is enabled (relrowsecurity = true), no other UPDATE policies on the table, no triggers, full column-level UPDATE grants to authenticated on every column being written, and profiles itself has a permissive select true policy so the subquery isn't blocked.

Given #1 proves the check is logically true and #2 proves the other policy works correctly alone, I'd expect the two permissive policies to OR together and let this through. Anyone run into something like this, or see something I'm missing? Happy to share more (schema, full policy list, etc.) if useful.

localhosthar.txt

[GaryAustin1]

Quick read thru and not looking at details but for update you must also meet select policy on the current row and the new row.

[satiricalguru]

The reason you are hitting 42501: new row violates row-level security policy for table "points" during the UPDATE—despite the standalone SELECT evaluating to true—is almost certainly due to subquery evaluation context and RLS recursion on the profiles table during the WITH CHECK phase.

---

Root Cause

When PostgreSQL evaluates the subquery:

exists (select 1 from profiles where profiles.id = auth.uid() and profiles.is_admin = true)

inside the WITH CHECK expression of an UPDATE executed by the authenticated role:

1. Subquery RLS Enforcement: The subquery against profiles runs under the permissions of the current session (authenticated). If profiles has any RLS policies or if auth.uid() evaluation behaves differently during the row check phase, the subquery can evaluate to NULL / empty, failing the WITH CHECK expression.
2. Subquery Plan Invalidation: Direct subqueries inside RLS policies are re-evaluated per modified row without execution caching and can encounter permission boundaries.

---

The Canonical Fix: Wrap the Check in a SECURITY DEFINER Function

In Supabase / PostgreSQL, the recommended best practice for role/admin checks inside RLS policies is to encapsulate the query in a security definer function with stable volatility and an explicit search_path:

-- 1. Create a secure, cached admin check function
create or replace function public.is_admin()
returns boolean
language sql
security definer
set search_path = public
stable
as $$
  select exists (
    select 1 from profiles
    where id = auth.uid()
      and is_admin = true
  );
$$;

-- 2. Update your admin policy to use the function
drop policy if exists "admins_update_any_point" on points;

create policy "admins_update_any_point" on points
  for update to authenticated
  using (is_admin())
  with check (is_admin());

Why this fixes the issue:

1. Bypasses Recursive RLS: security definer executes the profiles check with elevated privileges, guaranteeing that RLS on profiles never interferes with the WITH CHECK validation on points.
2. Query Plan Caching (stable): stable volatility allows PostgreSQL to evaluate is_admin() once per query rather than executing a subquery for every single updated row, improving UPDATE performance significantly.

[VivensB]

I built your setup on a local PostgreSQL 16.15 with an auth.uid() shim reading request.jwt.claims, and the two policies exactly as you posted them do work. The admin update succeeds:

=== admin updates a row owned by someone else ===
UPDATE 1

The one requirement is a SELECT policy that lets the admin see the row. With no SELECT policy, or an owner-only one, the same UPDATE returns UPDATE 0 silently. That is what @GaryAustin1 was pointing at.

Both explanations so far predict the wrong symptom. I tested them:

scenario	result
no SELECT policy on points	UPDATE 0
owner-only SELECT policy	UPDATE 0
SELECT policy admins can satisfy	UPDATE 1
profiles has RLS enabled, no policy	UPDATE 0
profiles has RLS with a self-select policy	UPDATE 1
admin also reassigns staff_id	UPDATE 1

RLS on profiles blocking the subquery does not throw 42501. It makes the USING clause false, so you get zero rows and no error. None of those paths produce your message.

The error text itself narrows it, and the detail is easy to miss. I reproduced two different forms:

-- a RESTRICTIVE policy failed: postgres names it
ERROR:  new row violates row-level security policy "points_guard" for table "points"

-- every permissive WITH CHECK was false: no name
ERROR:  new row violates row-level security policy for table "points"

Yours has no policy name, which is the second form. That means the row passed USING (so it was found) and then every permissive WITH CHECK evaluated false. If your admin policy's WITH CHECK really were the exists (...) is_admin expression, it would be true and the update would go through, which is the UPDATE 1 above.

So the thing to check is what is actually stored, rather than what was intended:

select policyname, permissive, cmd, qual as using_expr, with_check as with_check_expr
from pg_policies
where tablename = 'points' and cmd in ('UPDATE', 'ALL')
order by policyname;

The failure mode looks like this, and it is the shape that reproduces your exact error:

       policyname        | permissive |  cmd   |     with_check_expr
-------------------------+------------+--------+-------------------------
 admins_update_any_point | PERMISSIVE | UPDATE | (auth.uid() = staff_id)
 points_soft_delete      | PERMISSIVE | UPDATE | (auth.uid() = staff_id)

Both WITH CHECKs carrying the ownership expression, so neither can ever be true for an admin editing someone else's row. A CREATE POLICY that was edited and re-run, or a later migration replacing the policy, produces this without any error at creation time.

Two smaller things worth knowing. Leaving WITH CHECK off an UPDATE policy entirely is fine, it falls back to USING, and that configuration passed for me at UPDATE 1. And if the error ever does name a policy, look for as restrictive, since restrictive policies AND with everything else rather than OR.

Caveat on method: this was local PostgreSQL with an auth.uid() shim, not Supabase itself, so anything specific to how PostgREST issues the statement is outside what I tested.

[paramjeetn]

In PostgreSQL Row Level Security, when multiple permissive FOR UPDATE policies are defined:

1. The USING clause is combined with logical OR (if either policy's USING is true, the existing row can be selected for update).
2. However, the WITH CHECK clause applies to the new state of the row after the update. If an admin updates a row where staff_id is someone else, the first policy's WITH CHECK (auth.uid() = staff_id) evaluates to false.
3. In PostgreSQL RLS, multiple permissive WITH CHECK conditions are evaluated such that at least one policy must satisfy BOTH the USING and WITH CHECK expression simultaneously for that user.

Why the error occurs:

If the second policy's WITH CHECK query evaluates inside a subquery referencing profiles, PostgreSQL subplan execution under simulated roles often fails if profiles table itself has RLS enabled without a SELECT policy for authenticated users, causing the subquery to return empty (false).

The Fix:

Use a SECURITY DEFINER helper function to check admin status to prevent recursive RLS evaluation on profiles:

-- 1. Create secure helper:
create or replace function public.is_admin()
returns boolean
language sql
security definer
set search_path = public
stable
as $$
  select exists (
    select 1 from profiles
    where id = auth.uid() and is_admin = true
  );
$$;

-- 2. Consolidate into a single clean policy:
drop policy if exists "points_soft_delete" on points;
drop policy if exists "admins_update_any_point" on points;

create policy "points_update_policy" on points
  for update to authenticated
  using (
    auth.uid() = staff_id or public.is_admin()
  )
  with check (
    auth.uid() = staff_id or public.is_admin()
  );

Using a SECURITY DEFINER function eliminates subquery RLS interference and cleanly evaluates true for administrators on any target row.

[VivensB]

@paramjeetn that middle claim is testable, and it does not hold. Two permissive policies, deliberately split so that neither satisfies both halves:

-- passes USING, fails WITH CHECK
create policy a on t for update to authenticated using (true)  with check (false);
-- fails USING, passes WITH CHECK
create policy b on t for update to authenticated using (false) with check (true);

=== can USING from policy A combine with WITH CHECK from policy B? ===
UPDATE 1

The update succeeds. Neither policy satisfies both expressions on its own, so the two sides are OR'd independently across permissive policies rather than needing to come from the same one.

The control confirms which half raises the error. Change B's with check to false, leaving A's using (true) untouched:

ERROR:  new row violates row-level security policy for table "t"

Restore B's with check (true) and it is UPDATE 1 again.

So 42501 here means every permissive WITH CHECK evaluated false while some USING passed. That is consistent with what I measured before: on @jwhicks727's two policies as posted, the admin branch's WITH CHECK is true, so the update goes through, and it did in my run.

Which is why I would still go and read what is actually stored rather than what was intended:

select policyname, permissive, cmd, qual as using_expr, with_check as with_check_expr
from pg_policies where tablename = 'points' and cmd in ('UPDATE','ALL') order by policyname;

If both rows come back with (auth.uid() = staff_id) in with_check_expr, that is the whole error, and it is a one line fix rather than anything to do with subquery evaluation contexts.

PostgreSQL 16.15, same throwaway container as before.

[cekuu35]

Your two permissive UPDATE policies DO OR together for both USING and WITH CHECK ? that part of the model is correct, and I think it is a red herring. The real trap is that an UPDATE is silently two policy checks combined with AND, not just the UPDATE policies you are looking at.

The part everyone misses: SELECT policies apply to UPDATE too

When PostgreSQL runs an UPDATE, the new row version must also satisfy the SELECT policies on the table (CREATE POLICY docs, "Application of Multiple Policies"): policy expressions of different command types are combined with AND. Two things make this fire in your exact situation:

1. UPDATE ... RETURNING ? PostgREST / supabase-js .update().select() adds RETURNING, and a row coming back through RETURNING that fails the table's SELECT policy raises 42501, exactly this message.
2. A USING (deleted IS NOT TRUE) / deleted = false SELECT policy with a soft-delete write ? your policy is even named points_soft_delete. If the row you flip to soft-deleted (or staff_id to anyone else's) no longer matches your SELECT policy as it exists after the update, the new row image is rejected ? independently of your UPDATE WITH CHECK.

So "the check evaluates true standalone" is expected: you are testing the two UPDATE predicates in isolation, but PostgreSQL is also running the SELECT predicates against BOTH the old and the new row and ANDing them with your UPDATE checks.

What to check (in order, fast)

-- 1. Does your client write use RETURNING / .select()?
--    (this is THE most common cause ? the UPDATE works, the read-back is rejected)

-- 2. List the SELECT policies that also apply on UPDATE:
select polname, cmd, polqual, polwithcheck
from pg_policy
where polrelid = 'points'::regclass;

• If you have FOR SELECT ... USING (deleted IS NOT TRUE) (or any staff_id-based SELECT filter), and your update sets deleted = true or changes staff_id, that SELECT policy rejects the new row ? 42501, regardless of your UPDATE WITH CHECK (true).
• Fix paradigm: make the SELECT policy transition-safe (e.g. a deleted_at timestamp with (deleted_at IS NULL OR deleted_at > now() - interval '5 seconds') guard) or drive soft-delete through a SECURITY DEFINER function, or write a FOR DELETE policy and actually DELETE. The "timestamp + short grace window on the SELECT policy" is the smallest change that keeps both UPDATE and read-back working.

The giveaway that it is this and not your UPDATE policies: your own test in #2 ? dropping admins_update_any_point made the identical UPDATE return 0 rows with no error. A failed USING is silent (row invisible). A failed WITH CHECK is the 42501. Since your USING already passes for the admin row, the 42501 is coming from a check on the new row image ? and the only check running on new rows that you are not modeling is the SELECT policy (via RETURNING) plus your UPDATE WITH CHECK.

If you can paste pg_policy output for the table and confirm whether the failing statement uses .select()/RETURNING, I will point at the exact line. (I audit Postgres/Supabase authorization boundaries for a living ? this "permission denied survives a correct WITH CHECK" signature is almost always the SELECT-policy-on-UPDATE && RETURNING interaction.)

[cekuu35]

jayblast's restrictive-policy check is the right next filter. To rule it in/out in one shot:

select polname, polpermissive, polcmd,
       pg_get_expr(polqual, polrelid) as qual,
       pg_get_expr(polwithcheck, polrelid) as with_check
from pg_policy
where polrelid = 'public.points'::regclass;

If no RESTRICTIVE policy appears, the two permissive UPDATE policies do OR together (USING and WITH CHECK both) — your test #2 proves it. The remaining cause for 42501 with both policies present is typically the SELECT policy gate triggered by RETURNING (PostgREST/supabase-js .update(...).select() generates UPDATE ... RETURNING *, which requires SELECT permission on the new row version).

Quick verification: run the bare UPDATE without RETURNING (e.g. update points set ... where id = '<target>' in SQL Editor). If that succeeds but the app call fails, it's the RETURNING→SELECT policy interaction (same root as #49106). If the bare UPDATE also fails, we're back to a missing/incorrect policy.

Post the pg_policy output above if you want a definitive call.

[cekuu35]

This is permissive policy OR-behavior — when multiple permissive policies exist for the same command, Postgres ORs their USING/WITH CHECK expressions together.

What happens: Policy A allows update if auth.uid() = user_id. Policy B allows update if auth.uid() = admin_id. The effective check becomes: (auth.uid() = user_id) OR (auth.uid() = admin_id). If a user matches neither, the combined check fails → 42501.

Root cause: One of your policies likely has a USING condition that evaluates to false for the current user, and because it's permissive, it doesn't block — but the combined expression becomes restrictive in a way that blocks valid updates.

Fix options:

1. Consolidate into one FOR UPDATE policy with all conditions properly grouped
2. Use RESTRICTIVE policies for fine-grained control (they AND together)
3. Add explicit USING (true) to the broader policy so it doesn't restrict

Debug query:

SELECT policyname, cmd, permissive, roles, qual, with_check
FROM pg_policies WHERE tablename = 'your_table' AND cmd IN ('UPDATE', 'ALL');
-- Check: are both PERMISSIVE? Do their qual expressions OR to false?

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

[soarautomation]

Hey all — really appreciate the depth everyone's put into this, especially @VivensB's actual repro work and @cekuu35's SELECT-policy theory. Wanted to close the loop with what I found after digging further, because it rules out a lot of ground and I think it's worth having on record here.

Applied the SECURITY DEFINER fix. Wrapped the check in is_admin() security definer stable, confirmed the policy now reads using (is_admin()) with check (is_admin()). Retested the exact same failing case — identical 42501, no change at all.

So the admin check was never the problem. I went further and tested every remaining piece directly, in the same session, right before the failing statement.

Both UPDATE policies confirmed permissive:

polname	polcmd	polpermissive	qual	with_check
admins_update_any_point	w	true	is_admin()	is_admin()
points_soft_delete	w	true	auth.uid() = staff_id	auth.uid() = staff_id

is_admin() is true. The OR is true. Both policies are permissive, not restrictive. And the identical UPDATE on the identical row, same session, still throws 42501.

Also tested @cekuu35's RETURNING/SELECT-policy theory directly — ran the update with no RETURNING/.select() at all. Same error, unchanged. Ruled that out too.

At this point I've disproven every specific mechanism proposed here, empirically, one at a time, in the exact failing session. Per Postgres's documented behavior, an OR of two permissive conditions where one is true should pass — full stop. I'm out of theories that fit the documented model.

I'm on the free tier so a real support ticket isn't in the cards — does anyone know of a Supabase-specific quirk in how permissive UPDATE policies actually combine, something that wouldn't show up in pg_policy/pg_policies? Connection pooling holding a stale plan across set local changes, some PostgREST-specific evaluation order, anything like that? Genuinely stumped, and grateful for any pointers.

[GaryAustin1]

Have you tried with a select policy. I'm pretty sure select policy is required Postgres and PostgREST. This is not just for returning data...

[GaryAustin1]

Also from the PostgREST team early on in their repository..

PostgREST/postgrest#2156 (comment)

Also from Postgres docs...