Fix: 'RLS policy violates row security' in Supabase (2025)

You’re setting up a new table in Supabase. You want to make sure users can only see their own data, so you write a simple Row Level Security (RLS) policy. You click "Save," try to insert a row from your app, and... "Error: RLS policy violates row security." Or even worse: "infinite recursion detected in policy."

This is the "Brick Wall" of backend development. Row Level Security is Supabase's superpower—it's what allows you to query your database directly from the frontend without a custom API. But it’s also one of the most intellectually difficult parts of the "Vibe Stack" to master.

AI tools like Cursor or ChatGPT are notoriously bad at debugging RLS. They can write the basic auth.uid() = user_id check, but they consistently fail when your security logic requires "joining" tables or checking roles. They’ll often suggest policies that lead directly into Infinite Recursion, leaving you with a broken database and no clear path forward.

In this guide, we’ll explain how Supabase RLS works under the hood, identify the "Infinite Recursion" trap, and provide the proven patterns for writing secure, recursion-free policies.

How Supabase RLS Works: The PostGrest Security Layer

Supabase uses PostgreSQL's native Row Level Security. When a request comes in from your frontend, Supabase's API layer (PostGrest) identifies the user using their JWT.

Postgres then looks at your RLS policies. It treats each policy as a hidden WHERE clause that is automatically appended to every query. If your policy is auth.uid() = user_id, and you run SELECT * FROM posts, Postgres actually runs SELECT * FROM posts WHERE auth.uid() = user_id.

If the result of that "hidden check" is false, or if the logic inside the check causes an error, Postgres returns the "violates row security" error.

The 'Infinite Recursion' Trap Explained

Infinite recursion is the most common cause of RLS failures. It happens when a policy for a table refers to that same table to validate the user.

Example of a BROKEN Policy (Recursion):

Imagine you have a profiles table. You want to allow users to see other profiles only if they are an "admin."

-- ❌ THE DANGER ZONE: This will cause infinite recursion
CREATE POLICY "Admins can view all profiles" 
ON profiles 
FOR SELECT 
USING (
  (SELECT role FROM profiles WHERE id = auth.uid()) = 'admin'
);

Why it fails:

1. User tries to SELECT from profiles.
2. Postgres triggers the RLS policy.
3. The policy runs a subquery: SELECT role FROM profiles...
4. To run that subquery, Postgres must check the RLS policy for profiles again.
5. Infinite Loop.

Debugging 'violates row level security' in the SQL Editor

If your policy isn't working, don't just keep refreshing your app. Use the Supabase SQL Editor to "impersonate" a user and see the real error.

-- Impersonate a specific user
BEGIN;
  SET local role authenticated;
  SET local "request.jwt.claims" = '{"sub": "USER_UUID_HERE"}';
  
  -- Run your query and see the error
  SELECT * FROM your_table;
COMMIT;

This will reveal the raw Postgres error message, which is often much more descriptive than the one sent back to your frontend.

Pattern 1: The 'auth.uid()' Check for User Profiles

The safest and most performant policy is the direct auth.uid() check. This doesn't require any subqueries and is immune to recursion.

-- ✅ THE SAFE ZONE: Direct ID check
CREATE POLICY "Users can view their own data"
ON profiles
FOR SELECT
USING ( auth.uid() = id );

Pattern 2: Joining Tables without Recursion

If you need to check a user's role or membership in a different table, ensure you are querying a different table than the one the policy is on.

If you are on the posts table and you want to check the profiles table, that is usually safe.

-- ✅ THE SAFE ZONE: Cross-table check
CREATE POLICY "Admins can delete posts"
ON posts
FOR DELETE
USING (
  (SELECT role FROM profiles WHERE id = auth.uid()) = 'admin'
);

Note: This is safe because the subquery is on profiles, but the policy is on posts. There is no loop.

Testing Policies with the Supabase CLI

For complex projects, don't rely on the dashboard UI. Use the Supabase CLI to write unit tests for your RLS policies.

By using pg_tap or simple test scripts, you can verify that "User A cannot see User B's data" automatically every time you change your schema. This is the "Professional" way to manage backend security. For a guide on setting up the local environment for testing, see our Supabase connection refused guide.

Security vs. Performance: The Hidden Cost of Complex Policies

Every subquery in an RLS policy is executed for every row in your result set. If you have a policy with 3 joins and you try to fetch 1,000 rows, your database is effectively doing 3,000 extra lookups.

Optimization Tip: If your RLS policies are slowing down your app, consider using Postgres Functions with the security definer tag to handle complex logic, or cache roles in the user's JWT metadata.

FlowQL: Hardening Your Supabase Backend

RLS is the "last 20%" of Supabase development where small logic errors lead to massive security vulnerabilities or performance meltdowns. AI assistants are great at building the "Happy Path," but they consistently fail to account for the recursive nature of Postgres security.

At FlowQL, we provide the senior security oversight to audit your Supabase RLS policies. We help you untangle recursion, optimize for performance, and ensure that your user data is truly private.

If your "violates row security" error has turned into a multi-day debugging session, it's time for a human-in-the-loop audit.

Conclusion

The "RLS policy violates row security" error is Postgres's way of telling you that your security logic is flawed or circular. By avoiding Infinite Recursion and using direct auth.uid() checks whenever possible, you can build a backend that is both secure and lightning-fast.

Your Action Plan:

1. Check for "circular references" (Table A policy queries Table A).
2. Use the SQL Editor impersonation script to see the raw error.
3. Simplify your checks to direct ID comparisons wherever possible.

Don't let a security wall stop your build. [Book a session with FlowQL] and let’s secure your Supabase backend today.

---

FAQ: Supabase RLS and Row Security

Q: Can I disable RLS entirely? A: You can, but you shouldn't for any table containing user data. Disabling RLS makes your database public to anyone with your Anon Key.

Q: What is the difference between USING and WITH CHECK? A: USING applies to rows being read (SELECT, UPDATE, DELETE). WITH CHECK applies to rows being created or modified (INSERT, UPDATE).

Q: How do I handle "Admin" roles in RLS? A: The best way is to have a role column in your profiles table and use a subquery in your other tables' policies to check that role based on auth.uid().