Supabase Row Level Security: Beginner's Guide 2026
Learn RLS in Supabase with production patterns. Real errors, exact code, and security best practices for indie hackers.
TL;DR
Supabase Row Level Security (RLS) lets you enforce database-level access control without application logic. Enable it on tables, write policies using PostgreSQL, and test thoroughly. Common mistakes: forgetting USING vs WITH CHECK clauses, not handling auth.uid(), and missing SELECT policies on joined tables.
---
What is Row Level Security?
Row Level Security is a PostgreSQL feature that Supabase exposes to limit which rows users can read, insert, update, or delete. Instead of trusting your application to enforce permissions, the database itself rejects unauthorized queries.
Without RLS: ```sql -- Your app must check: "is user 123 the owner?" SELECT * FROM posts WHERE user_id = $1; ```
With RLS: ```sql -- Database automatically filters SELECT * FROM posts; -- Only returns rows the user owns ```
---
Enable RLS on a Table
First, create a table and enable RLS:
```sql CREATE TABLE posts ( id BIGSERIAL PRIMARY KEY, user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE, title TEXT NOT NULL, content TEXT, created_at TIMESTAMP DEFAULT NOW() );
-- Enable RLS ALTER TABLE posts ENABLE ROW LEVEL SECURITY; ```
Critical: Once enabled, RLS denies *all* access by default. You must create policies.
---
Write Your First Policy
Create a policy allowing users to read only their own posts:
```sql CREATE POLICY "Users can read own posts" ON posts FOR SELECT USING (auth.uid() = user_id); ```
Breakdown:
FOR SELECT: This policy applies to read operationsUSING: The condition that must be true for the row to be visibleauth.uid(): Built-in function returning the logged-in user's UUID---
CRUD Policies Pattern
Here's a production-ready set of policies for a typical posts table:
```sql -- SELECT: Users read only their own posts CREATE POLICY "Users can read own posts" ON posts FOR SELECT USING (auth.uid() = user_id);
-- INSERT: Users can create posts CREATE POLICY "Users can create posts" ON posts FOR INSERT WITH CHECK (auth.uid() = user_id);
-- UPDATE: Users modify only their own posts CREATE POLICY "Users can update own posts" ON posts FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
-- DELETE: Users delete only their own posts CREATE POLICY "Users can delete own posts" ON posts FOR DELETE USING (auth.uid() = user_id);
-- BONUS: Allow admins to do anything CREATE POLICY "Admins manage all posts" ON posts FOR ALL USING ( EXISTS ( SELECT 1 FROM auth.users WHERE auth.users.id = auth.uid() AND auth.users.raw_user_meta_data->>'role' = 'admin' ) ); ```
Key distinction:
USING: Applied to existing rows (SELECT, UPDATE, DELETE)WITH CHECK: Applied to new/modified rows (INSERT, UPDATE)---
Real Console Errors You'll Hit
Error 1: No Policy Defined
``` PG Error: new row violates row-level security policy for table "posts" ``` You forgot to create an INSERT policy. The database blocks all inserts until you add one.Error 2: Policy Condition Error
``` PG Error: column "user_id" must appear in the USING clause or be unavailable due to an outer join ``` You referenced a column inUSING that doesn't exist in the table, or joined from another table incorrectly.Error 3: Null auth.uid()
``` PG Error: null value in column "user_id" violates not-null constraint ``` You tried inserting without an authenticated user. Always verifyauth.uid() is not null:```sql CREATE POLICY "Authenticated users create posts" ON posts FOR INSERT WITH CHECK (auth.uid() IS NOT NULL AND auth.uid() = user_id); ```
---
Public/Shared Data Pattern
Not all data is private. For a comments table where everyone reads but only the author edits:
```sql ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
-- Everyone can read (anonymous or logged in) CREATE POLICY "Comments are readable by anyone" ON comments FOR SELECT USING (true);
-- Only author can update/delete CREATE POLICY "Users can edit own comments" ON comments FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete own comments" ON comments FOR DELETE USING (auth.uid() = user_id); ```
---
Testing RLS Locally
Use the Supabase CLI (verify version in [official docs](https://supabase.com/docs/guides/local-development)):
```bash
Start local Supabase (v1.97.0+)
supabase startOpen SQL editor at http://localhost:54323
```Test policies by switching user context:
```sql -- Simulate user 1 SET SESSION auth.uid = '550e8400-e29b-41d4-a716-446655440000'; SELECT * FROM posts; -- Returns only user 1's posts
-- Switch to user 2 SET SESSION auth.uid = '550e8400-e29b-41d4-a716-446655440001'; SELECT * FROM posts; -- Returns only user 2's posts ```
---
Common Mistakes
1. Forgetting WITH CHECK on UPDATE
```sql
-- ❌ Wrong: Only checks existing rows, not new values
CREATE POLICY "users_update" ON posts FOR UPDATE USING (auth.uid() = user_id);
-- ✅ Correct: Prevents privilege escalation
CREATE POLICY "users_update" ON posts FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
```
2. Not testing with anonymous users RLS still applies. Verify public endpoints return 403, not empty arrays silently.
3. Complex joins in policies
Keep policy logic simple. If you need to check related tables, use EXISTS with subqueries:
```sql
CREATE POLICY "read_org_posts" ON posts FOR SELECT
USING (
EXISTS (
SELECT 1 FROM org_members
WHERE org_members.user_id = auth.uid()
AND org_members.org_id = posts.org_id
)
);
```
---
Next Steps
---
What am I missing?
This guide covers fundamentals, but RLS has deep corners:
EXISTS subqueries?Comment below with your RLS patterns, gotchas, or questions. Corrections on version info always welcome.