How to Set Up Supabase Row Level Security Correctly

TL;DR

To set up Supabase Row Level Security correctly, enable RLS on each table, write precise policies using USING and WITH CHECK expressions, always test with the SQL editor, and avoid common mistakes like forgetting to enable RLS or using overly permissive policies. This guide walks you through every step with real code examples.

Why Supabase Row Level Security Matters

Supabase Row Level Security (RLS) is a PostgreSQL feature that restricts which rows a user can see, insert, update, or delete based on a boolean expression. Without RLS, any authenticated user (or even anonymous users) can access all data in your tables. For apps built quickly with AI coding tools, RLS is often overlooked, leading to data leaks. Correctly setting up Supabase Row Level Security ensures that users only access their own data, preventing IDOR (Insecure Direct Object Reference) vulnerabilities.

Step 1: Enable RLS on Your Table

RLS is disabled by default. To enable it, go to the Supabase dashboard, open the SQL editor, and run:

ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

Or use the Table Editor: select the table, go to "RLS Policies", and toggle "Enable RLS".

Step 2: Create Basic Policies

Policies are defined per operation: SELECT, INSERT, UPDATE, DELETE. Each policy has a USING clause (for SELECT, UPDATE, DELETE) and a WITH CHECK clause (for INSERT, UPDATE). The expression must return true for the operation to proceed.

Example: Allow users to view only their own todos

CREATE POLICY "Users can view their own todos"
ON todos FOR SELECT
USING (auth.uid() = user_id);

Example: Allow users to insert todos with their own user_id

CREATE POLICY "Users can insert their own todos"
ON todos FOR INSERT
WITH CHECK (auth.uid() = user_id);

Example: Allow users to update their own todos

CREATE POLICY "Users can update their own todos"
ON todos FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

Example: Allow users to delete their own todos

CREATE POLICY "Users can delete their own todos"
ON todos FOR DELETE
USING (auth.uid() = user_id);

Step 3: Use Helper Functions

Supabase provides helper functions like auth.uid(), auth.role(), and auth.email(). For multi-tenant apps, you might check auth.jwt() ->> 'org_id'.

Important: auth.uid() returns the user's ID from the JWT. If you use a custom user ID column, ensure it matches.

Step 4: Test Your Policies

Use the SQL editor to test as different users. First, get a user's JWT:

SELECT auth.uid();

Then try queries that should succeed or fail:

-- Should work (user_id matches current user)
SELECT * FROM todos WHERE user_id = auth.uid();

-- Should fail (user_id differs)
SELECT * FROM todos WHERE user_id != auth.uid();

You can also use supabase-js client-side with select('*') and check errors.

Step 5: Common Pitfalls & Fixes

Pitfall 1: Forgetting to Enable RLS

If you create policies but RLS is off, they are ignored. Always verify RLS is enabled.

Pitfall 2: Overly Permissive Policies

Avoid USING (true) for operations. For public read data, use a separate policy with USING (true) only for SELECT on specific columns.

Pitfall 3: Not Handling Non-Existent Rows on UPDATE/DELETE

When no rows match the USING clause, the operation silently does nothing. This is correct behavior but can confuse users. Handle it in your app logic.

Pitfall 4: Mixing Auth and Anonymous Access

For anonymous users, auth.uid() is null. Use auth.role() = 'anon' if you allow anonymous inserts.

Example: Complete Setup for a Blog App

Tables: posts, comments.

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

-- Posts: authenticated users can CRUD their own; anyone can read published
CREATE POLICY "Anyone can read published posts"
ON posts FOR SELECT
USING (published = true);

CREATE POLICY "Authors can CRUD their own posts"
ON posts FOR ALL
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

-- Comments: authenticated users can insert; owners can update/delete
CREATE POLICY "Anyone can read comments"
ON comments FOR SELECT
USING (true);

CREATE POLICY "Users can insert comments"
ON comments FOR INSERT
WITH CHECK (auth.role() = 'authenticated');

CREATE POLICY "Comment owners can update/delete"
ON comments FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Comment owners can delete"
ON comments FOR DELETE
USING (auth.uid() = user_id);

Using OverMCP to Scan for RLS Issues

Before deploying, scan your Supabase project with OverMCP. It automatically detects tables without RLS, overly permissive policies, and missing auth.uid() checks. This catches mistakes early and secures your vibe-coded app.

Best Practices

FAQ

What is the difference between USING and WITH CHECK in RLS policies?

USING controls which existing rows are visible or modifiable (for SELECT, UPDATE, DELETE). WITH CHECK controls which new rows can be inserted or updated (for INSERT, UPDATE). For UPDATE, both must pass: the row must satisfy USING before the update, and the new row must satisfy WITH CHECK after.

Why does my RLS policy not work even though I created it?

Most common reasons: RLS is not enabled on the table, the policy is misspelled, or the auth.uid() does not match the column type (e.g., UUID vs text). Check RLS status with SELECT relrowsecurity FROM pg_class WHERE relname = 'your_table';.

Can I use RLS with anonymous users?

Yes. Anonymous users have auth.role() = 'anon' and auth.uid() returns null. You can create policies that check auth.role() = 'anon' or use IS NULL for the user ID column. Ensure you enable RLS and create appropriate policies for anonymous access.

Next Steps

Now that you understand Supabase Row Level Security, apply these patterns to all your tables. Regularly audit policies using OverMCP to catch misconfigurations. Secure data builds user trust and prevents costly breaches.