Skip to main content

Solved: "New row violates row-level security policy" on Supabase Inserts

 You have set up your Supabase table. You have enabled Row Level Security (RLS). You even wrote an INSERT policy that explicitly allows authenticated users to create rows. Yet, when your frontend tries to save data, the database throws the dreaded error:

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

This is one of the most frustrating errors in PostgreSQL development because it feels like a lie. You permitted the insert, but the database says you didn't.

The problem usually isn't your INSERT policy. The problem is that Supabase (via PostgREST) attempts to read the row immediately after writing it, and your policies don't allow that read operation.

Here is the technical breakdown of why this happens and how to fix it properly.

The Root Cause: The Hidden "Select"

To understand the error, you must understand what happens under the hood of the Supabase JavaScript client.

When you run a standard insert command in a modern React or Node.js application, the goal is usually to update the UI state immediately with the new data (including autogenerated IDs or timestamps).

const { data, error } = await supabase
  .from('todos')
  .insert({ title: 'Fix RLS policies', user_id: user.id })
  .select(); // <--- The culprit

By chaining .select(), or by using the older v1 syntax that returned data by default, the client sends a request to PostgREST that looks roughly like this SQL:

INSERT INTO todos (title, user_id)
VALUES ('Fix RLS policies', 'uuid-here')
RETURNING *;

PostgreSQL executes this as a single transaction:

  1. The Insert: It attempts to write the row. Your INSERT policy runs. If valid, the row is written.
  2. The Return: It attempts to return the data (RETURNING *). This triggers your SELECT policy.

If you have an INSERT policy but no matching SELECT policy, PostgreSQL successfully writes the row but is forbidden from showing it back to you. Because the transaction cannot fulfill the RETURNING clause due to security constraints, the entire transaction fails, and the row is rolled back.

The Fix: Aligning Policies

To resolve this, you must ensure the user has permission to view the row they just created. You have two primary implementation paths depending on your security model.

Solution A: The "My Data" Pattern (Recommended)

In 99% of application scenarios (To-Do lists, User Profiles, Dashboards), if a user creates a piece of data, they should be allowed to see it.

You need two distinct policies (or one combined policy) to handle this lifecycle.

1. The Insert Policy

This checks if the user is allowed to write the new row.

-- Allow users to insert rows, but ONLY if the user_id matches their auth token
CREATE POLICY "Users can insert their own todos"
ON public.todos
FOR INSERT
WITH CHECK (
  auth.uid() = user_id
);

2. The Select Policy

This checks if the user is allowed to see the row. This is the missing piece causing your error.

-- Allow users to view rows that belong to them
CREATE POLICY "Users can view their own todos"
ON public.todos
FOR SELECT
USING (
  auth.uid() = user_id
);

Once you apply the SELECT policy, the RETURNING * clause in the background succeeds, and the error vanishes.

Solution B: The "Blind Insert" (Log/Audit Pattern)

Sometimes you don't want users to see what they just inserted. Examples include:

  • Anonymous feedback forms.
  • Audit logging.
  • Voting systems (where you can't see who voted).

If the user strictly cannot have SELECT permissions, you must modify your client-side code to stop asking for the data back.

The JavaScript Fix:

Remove the .select() call. This tells Supabase to send a header Prefer: return=minimal to PostgREST.

// Do not chain .select()
const { error } = await supabase
  .from('feedback')
  .insert({ message: 'Great app!', user_id: user.id });

if (error) console.error(error);

// Manually handle UI updates since you won't get the ID back

This prevents the implicit RETURNING * SQL generation. The INSERT policy will pass, and since no read is requested, the missing SELECT policy won't trigger an error.

Deep Dive: WITH CHECK vs USING

To master RLS debugging, you must understand the distinction between the two clauses used in PostgreSQL policies. Mixing these up is a frequent source of "silent failures" or security holes.

WITH CHECK (For New Data)

This clause runs against new rows being added to the table. It ensures the data incoming meets your criteria.

  • Used in: INSERT and UPDATE policies.
  • Context: "Does the new row look correct?"

USING (For Existing Data)

This clause runs against rows that already exist in the table. It filters the scope of rows the command can affect.

  • Used in: SELECT, UPDATE, and DELETE policies.
  • Context: "Can I see/touch this existing row?"

The UPDATE Paradox

Updates are tricky because they use both. You need permission to find the row (USING) and permission to save the new state of the row (WITH CHECK).

If you have a rigorous RLS setup, your UPDATE policy might look like this:

CREATE POLICY "Users can update their own todos"
ON public.todos
FOR UPDATE
USING ( auth.uid() = user_id )      -- Can I find the row? (Pre-update)
WITH CHECK ( auth.uid() = user_id ); -- Is the resulting row still mine? (Post-update)

If you omit the WITH CHECK, a user could technically update a row and reassign the user_id to someone else, effectively "giving away" the row (and losing access to it).

Common Edge Cases

1. The service_role Bypass

If you are running a server-side script (e.g., in a Next.js API Route or Supabase Edge Function) and getting RLS errors, you likely forgot to use the service role key.

RLS policies apply to the anon and authenticated roles. The service_role bypasses RLS entirely.

// BAD: Uses the standard client (subject to RLS) in a backend script
import { createClient } from '@supabase/supabase-js';

// GOOD: Uses the Service Role Key for backend admin tasks
const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

// This insert works regardless of policies
await supabaseAdmin.from('logs').insert({ event: 'system_reboot' });

2. Triggers and RLS

If you have a Database Trigger that runs on insert (e.g., creating a public.users profile row when a new user signs up), that trigger runs with the privileges of the user executing the query.

If the trigger tries to insert into a table that the user doesn't have access to, it will fail with an RLS error.

Solution: Define the function with SECURITY DEFINER. This forces the function to execute with the privileges of the function creator (usually the superuser/postgres), bypassing the invoking user's RLS limits.

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name)
  VALUES (new.id, new.raw_user_meta_data->>'full_name');
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER; -- <--- Critical flag

Summary

When you see new row violates row-level security policy during an insert, do not just stare at your INSERT policy.

  1. Check if your JavaScript client is requesting the inserted data back (using .select()).
  2. If it is, ensure you have a corresponding SELECT policy in Postgres.
  3. Ensure your WITH CHECK (write) logic matches your USING (read) logic.

RLS is a powerful feature that moves security from your fragile middleware into the database kernel. Once you understand the read-after-write lifecycle, it becomes the most robust way to secure your application.

Labels: