vibecheck

Friday, October 3, 2025

Stop Leaking Data: The 5-Minute Supabase RLS Lockdown Guide

Jeremy B

If you are using Supabase, Row Level Security (RLS) is your database firewall. Without it, your data is exposed to every client that knows your anon key. The catch? RLS is written in PostgreSQL and can be notoriously tricky to get right on the first try.

This guide is designed to clarify the three core principles of RLS and provide copy-paste policies for 90% of the common use cases, so you can stop guessing and ship securely.

1. The Core Principle: DENY EVERYTHING FIRST

When you enable RLS on a table, the first thing it does is deny all access to that table, even for authenticated users.

RLS Status and Effect

  • RLS OFF: Anyone can read/write everything. (Insecure)
  • RLS ON: Nobody can read/write anything. (Secure, but Useless)
  • RLS ON + POLICIES: Only data matching a policy is allowed. (Secure & Usable)

The Vibe Coder Mistake: Forgetting to enable RLS. You must run this command on every sensitive table:

ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

2. The Four Policy Types and When to Use Them

A Policy is a single rule that grants access. Every policy must specify which SQL command it applies to.

A. READ Policy (FOR SELECT)

This is the rule for what data a user can see.

  • Command: SELECT
  • Clause Used: USING (condition)
  • Use Case: Viewing lists of items, reading public profiles, etc.

B. CREATE Policy (FOR INSERT)

This is the rule for what data a user is allowed to create.

  • Command: INSERT
  • Clause Used: WITH CHECK (condition)
  • Use Case: Submitting a form, creating a new post, etc.

C. UPDATE Policy (FOR UPDATE)

This is the rule for what data a user can change.

  • Command: UPDATE
  • Clauses Used: USING (old_row_condition) AND WITH CHECK (new_row_condition)
  • Use Case: Editing an existing record, changing a profile picture, etc.

D. DELETE Policy (FOR DELETE)

This is the rule for what data a user can destroy.

  • Command: DELETE
  • Clause Used: USING (condition)
  • Use Case: Deleting a record, closing an account, etc.

3. The Big Trap: USING vs. WITH CHECK

This is where RLS gets confusing. The clauses are about when the rule applies:

1. USING Clause (For SELECT, UPDATE, DELETE)

  • Checks the existing row: Should the user be allowed to see/modify this row?
  • Best for: Looking up existing data. For SELECT, it filters rows. For UPDATE/DELETE, it determines which rows the user has permission to target.

2. WITH CHECK Clause (For INSERT, UPDATE)

  • Checks the resulting row: Does the new data the user is trying to create or update meet the security rules?
  • Best for: Preventing a user from creating a row for someone else (INSERT), or preventing a user from updating a row to violate ownership (UPDATE).

Crucial Insight: For INSERT, you only need WITH CHECK. For UPDATE, you need both a USING (to find the row) and a WITH CHECK (to ensure the updated data is still valid).

4. Copy-Paste Policies for Common Scenarios

Assume your table has a column named user_id which stores the ID of the owner, matching the ID from auth.uid().

Scenario A: Fully Owned Private Data (e.g., A User's Private Notes)

Action: Read (User can see their own data)

  • SQL Policy: FOR SELECT USING (auth.uid() = user_id)

Action: Create (User can create their own data)

  • SQL Policy: FOR INSERT WITH CHECK (auth.uid() = user_id)

Action: Update (User can update their own data)

  • SQL Policy: FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id)

Action: Delete (User can delete their own data)

  • SQL Policy: FOR DELETE USING (auth.uid() = user_id)

Scenario B: Publicly Viewable Data, Owned by User (e.g., Blog Posts)

This is slightly more complex as unauthenticated users need read access, but only the owner can modify it.

Action: Read (Public)

  • Policy Name: Anyone can view all data
  • SQL Policy: FOR SELECT USING (true)

Action: Create (Authenticated)

  • Policy Name: Auth user can create their own
  • SQL Policy: FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id)

Action: Update (Owner)

  • Policy Name: Owner can update their row
  • SQL Policy: FOR UPDATE USING (auth.uid() = user_id)

Action: Delete (Owner)

  • Policy Name: Owner can delete their row
  • SQL Policy: FOR DELETE USING (auth.uid() = user_id)

Scenario C: Admin-Only Creation (No Client Access)

If a table is populated only by a secure backend (e.g., your Next.js API Routes using the service_role key), you don't need policies, as the service_role bypasses RLS.

If you must restrict access to a specific role:

Action: Create (Admin)

  • Policy Name: Only members of the 'admin' role can insert
  • SQL Policy: FOR INSERT TO service_role WITH CHECK (auth.role() = 'admin')

Back to blog