Supabase RLS: The Copy-Paste Guide for Vibe Coders

-- Users can only see their own rows
CREATE POLICY "Users can view own rows"
ON your_table FOR SELECT
USING (auth.uid() = user_id);

-- Users can only insert rows for themselves
CREATE POLICY "Users can insert own rows"
ON your_table FOR INSERT
WITH CHECK (auth.uid() = user_id);

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

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

USING controls which rows you can read/modify. WITH CHECK controls which rows you can write. For INSERT, there's no existing row to check — only WITH CHECK applies.

-- Required: your table needs a user_id column
ALTER TABLE your_table ADD COLUMN user_id UUID REFERENCES auth.users(id);

-- Anyone (even unauthenticated) can read
CREATE POLICY "Public read access"
ON posts FOR SELECT
USING (true);

-- Only authenticated users can insert
CREATE POLICY "Authenticated users can post"
ON posts FOR INSERT
WITH CHECK (auth.uid() IS NOT NULL);

-- Authors can update their own posts
CREATE POLICY "Authors can edit own posts"
ON posts FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Authors can delete their own posts
CREATE POLICY "Authors can delete own posts"
ON posts FOR DELETE
USING (auth.uid() = user_id);

-- First, create a way to identify admins
CREATE TABLE admins (
  user_id UUID REFERENCES auth.users(id) PRIMARY KEY
);

-- Only admins can read
CREATE POLICY "Admin read only"
ON sensitive_table FOR SELECT
USING (
  auth.uid() IN (SELECT user_id FROM admins)
);

-- Only admins can modify
CREATE POLICY "Admin write only"
ON sensitive_table FOR ALL
USING (
  auth.uid() IN (SELECT user_id FROM admins)
)
WITH CHECK (
  auth.uid() IN (SELECT user_id FROM admins)
);

Performance note: the subquery (SELECT user_id FROM admins) runs per-row. For small admin tables this is fine. For large datasets, use a security definer function instead (see Performance Anti-Patterns).

CREATE TABLE org_members (
  org_id UUID,
  user_id UUID REFERENCES auth.users(id),
  role TEXT DEFAULT 'member',
  PRIMARY KEY (org_id, user_id)
);

-- Members can read their org's data
CREATE POLICY "Org members can read"
ON projects FOR SELECT
USING (
  org_id IN (
    SELECT org_id FROM org_members
    WHERE user_id = auth.uid()
  )
);

-- Members can insert into their org
CREATE POLICY "Org members can create"
ON projects FOR INSERT
WITH CHECK (
  org_id IN (
    SELECT org_id FROM org_members
    WHERE user_id = auth.uid()
  )
);

-- Only org admins can delete
CREATE POLICY "Org admins can delete"
ON projects FOR DELETE
USING (
  org_id IN (
    SELECT org_id FROM org_members
    WHERE user_id = auth.uid() AND role = 'admin'
  )
);

-- The service role bypasses ALL RLS policies automatically.
-- No special policy needed — just use the service role client server-side.

-- Server-side (Node.js / Next.js API route / Edge function):
import { createClient } from '@supabase/supabase-js'

const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!  // Never in NEXT_PUBLIC_*
)

// This query ignores all RLS — sees every row
const { data } = await supabaseAdmin.from('users').select('*')

The service role key has full read/write/delete access to every table. Store it in server-only environment variables (no NEXT_PUBLIC_ prefix). Use it for admin dashboards, cron jobs, webhooks, and data migrations — never in client-side code.

-- To verify the anon role CAN'T bypass RLS, test in SQL editor:
BEGIN;
SET LOCAL role = anon;
SELECT * FROM your_table;  -- Should return only policy-allowed rows
ROLLBACK;

-- The SQL editor normally runs as the postgres role, which bypasses RLS:
SELECT * FROM your_table;  -- Returns everything