Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Supabase: new row violates row-level security policy (42501)

RLS is default-deny per command. Add the insert policy, default the owner column to auth.uid(), and give the server client a real session.

~5 min readSQL
#Supabase#PostgreSQL#Row Level Security#Authentication
A row block stopped by a policy gate with an owner key slot, headline RLS 42501

Introduction & Quick Summary

Symptom. A client insert returns nothing useful — supabase-js does not throw, it hands back an error object:

{ "code": "42501", "message": "new row violates row-level security policy for table \"notes\"", "details": null, "hint": null }

The table exists, the key is correct, reads from the same table work. Only writes fail, and they fail with HTTP 403.

Cause. Row-level security is default-deny, per command. A table with RLS enabled and a SELECT policy allows reads and nothing else. An INSERT is checked against a WITH CHECK expression, and that check evaluated to false or null for your row — most often because there is no INSERT policy at all, because the row has no user_id, or because the request reached Postgres as anon with auth.uid() null.

Fix. Add the policy for the command you are running, and let the database fill the owner column.

alter table public.notes enable row level security; create policy "owner inserts own notes" on public.notes for insert to authenticated with check ((select auth.uid()) = user_id); alter table public.notes alter column user_id set default auth.uid();
ts
const { data, error } = await supabase .from("notes") .insert({ title: "Ship it" }) // user_id comes from the column default .select() .single(); if (error) console.error(error.code, error.message); // 42501 means the policy blocked it

Core Mechanics, Signatures & Reproducible Setup

PostgREST connects to Postgres as either anon (no session) or authenticated (a signed-in user). auth.uid() reads the sub claim of the JWT that arrived with the request, so every policy that compares a column to auth.uid() depends on a real session. That is why the same code works in a page that has a session and fails in a cron job.

Two clauses do different jobs, and mixing them up is the most common structural mistake:

-- USING filters rows you can see, update or delete -- WITH CHECK validates the row you are writing create policy "read own" on public.notes for select to authenticated using ((select auth.uid()) = user_id); create policy "write own" on public.notes for insert to authenticated with check ((select auth.uid()) = user_id); create policy "update own" on public.notes for update to authenticated using ((select auth.uid()) = user_id) with check ((select auth.uid()) = user_id);

Reproduce and inspect from the database rather than from the client, because the policy list explains the failure faster than any log:

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

If no row has cmd of INSERT or ALL, that is the whole answer.

Key Comparison Table

CauseWhat it looks likeFix
No INSERT policyreads work, writes return 42501add a for insert policy
user_id null in the payloadpayload has no owner columnset a column default of auth.uid()
Request is anonauth.uid() is null on the serveruse a @supabase/ssr client with cookies
.insert().select() without a SELECT policyinsert allowed but the whole statement failsadd for select or drop .select()
Policy uses using on an insertpolicy exists and never passesuse with check
Trigger inserts into another tablesecondary table denies the writesecurity definer on the trigger function
RoleBypasses RLSWhere it may be used
anonnobrowser, unauthenticated requests
authenticatednoany request with a user session
service_roleyesserver only, never in a client bundle
table owneryes unless force row level securitymigrations and admin tasks

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. USING on an INSERT policy does nothing

Postgres rejects using on an insert policy at creation time in most cases, and a policy copied from a select statement therefore fails or silently lets nothing through:

-- wrong for insert: there is no existing row to filter create policy "bad" on public.notes for insert to authenticated using ((select auth.uid()) = user_id); -- right create policy "good" on public.notes for insert to authenticated with check ((select auth.uid()) = user_id);

An INSERT ... RETURNING is checked twice: WITH CHECK for the new row and USING of a SELECT policy for returning it. That single fact explains why an insert with .select() fails while the same insert without it succeeds.

2. The server client that has no session

In Next.js, a Server Action that calls createClient(url, anonKey) runs as anon, so auth.uid() is null and every owner check fails. The fix is the SSR client wired to the request cookies:

ts
import { createServerClient } from "@supabase/ssr"; import { cookies } from "next/headers"; export async function createClientForRequest() { const store = await cookies(); return createServerClient( process.env.NEXT_PUBLIC_SUPABASE_URL!, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!, { cookies: { getAll: () => store.getAll(), setAll: (list) => list.forEach(({ name, value, options }) => store.set(name, value, options)), }, }, ); }

Verify the session before the write instead of after the 403:

ts
const { data: { user } } = await supabase.auth.getUser(); if (!user) throw new Error("sign in required");

Middleware that refreshes the session is part of the same fix; a cookie that expired silently is indistinguishable from no session.

3. Sign-up flows and the profile row

An insert immediately after signUp() usually has no session yet, because email confirmation is pending. Client-side inserts then fail permanently for that user, and users who confirm by link see a missing profile. Create the row from a trigger on auth.users with security definer, which runs regardless of the caller's session:

create function public.handle_new_user() returns trigger language plpgsql security definer set search_path = public as $$ begin insert into public.profiles (id, email) values (new.id, new.email) on conflict (id) do nothing; return new; end; $$; create trigger on_auth_user_created after insert on auth.users for each row execute function public.handle_new_user();

set search_path = public is not optional on a security definer function; without it the function runs with whatever path the caller had.

4. Multi-tenant policies and the membership table

A policy that checks a membership table must not query the same table being protected, or it recurses. The documented shape uses a security definer helper function that bypasses RLS for the lookup:

create function public.is_member(org uuid) returns boolean language sql security definer stable set search_path = public as $$ select exists (select 1 from public.memberships m where m.org_id = org and m.user_id = auth.uid()); $$; create policy "members read projects" on public.projects for select to authenticated using (public.is_member(org_id));

Mark the function stable so the planner can cache it per statement.

5. Performance of auth.uid() in a policy

Wrapping the call in a subquery is the pattern Supabase recommends, because it is evaluated once per statement instead of once per row:

-- per row using (user_id = auth.uid()) -- per statement using (user_id = (select auth.uid()))

On a table with a million rows the difference is the difference between an index scan that finishes and a policy check that dominates the query. Pair it with an index on (user_id), which is what makes the policy cheap.

6. Testing policies without the client

Test in SQL with the JWT claims set, so a failing test names the policy rather than a 403:

begin; set local role authenticated; set local request.jwt.claims = '{"sub":"11111111-1111-1111-1111-111111111111"}'; insert into public.notes (title) values ('from test'); select count(*) from public.notes; rollback;

set local keeps the change inside the transaction, so the test leaves nothing behind. When a policy fails only through the client, the difference is almost always the claims: the client sends a fresh token, and your test used a stale or absent one.

Common Compilation/Runtime Mistakes & Version Gotchas

  • Checking error as a thrown exception. supabase-js returns { data, error }; an unchecked error looks like a successful empty insert.
  • Using the service_role key in a browser bundle. It bypasses every policy and is a full database compromise once shipped.
  • Assuming a select policy covers writes. It does not; RLS is per command.
  • Sending user_id from the client. A default plus WITH CHECK is the only version a client cannot get wrong.
  • Forgetting enable row level security after creating policies. Policies on a table with RLS off are not applied at all.
  • Reading 42501 as "permission denied" on the table. It is a policy check, not a GRANT problem.
  • Adding a for all policy to fix one command. It grants delete and update as well; be explicit.

Inspect the JSON error body you get back with the JSON formatter, and decode the JWT the client is sending with the JWT decoder to confirm which role and sub reached Postgres.

Reference: Supabase Row Level Security and Postgres CREATE POLICY.

Official references

primary documentation

The SQL documentation that defines the behaviour this guide explains.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.