Complete Guide to Row Level Security in Supabase

Row Level Security (RLS) is Supabase's most powerful security feature, yet it's also the most misunderstood. Get it wrong and users can see each other's data. Get it right and you have bank-level security with zero application-layer code.

This guide teaches you everything you need to master RLS, with real-world patterns used by production SaaS apps.

What is Row Level Security?

RLS is a PostgreSQL feature that enforces access control at the database level. Instead of checking permissions in your application code, the database automatically filters rows based on who's making the request.

Without RLS (❌ Insecure):

// Application code filters data
const { data } = await supabase
  .from('tasks')
  .select('*')
  .eq('user_id', currentUser.id) // Easy to forget!

// Attacker modifies request to skip filter
// const { data } = await supabase.from('tasks').select('*')
// ↑ Now they see ALL tasks from ALL users! 🚨

With RLS (✅ Secure):

// Application code (no filtering needed)
const { data } = await supabase
  .from('tasks')
  .select('*')

// Database automatically filters based on auth.uid()
// Attacker can't bypass this - it's enforced by PostgreSQL

Key insight: RLS moves security from application code (can be bypassed) to the database (cannot be bypassed).

Why RLS Matters for SaaS

Multi-tenant SaaS apps have a critical requirement: tenant isolation. User A must never see User B's data.

Common approaches:

• **Application-layer filtering** (❌)

- Check user_id in every query

- Easy to forget one query = security breach

- Can't audit or verify comprehensively

• **Separate databases per tenant** (❌)

- Expensive (1000 customers = 1000 databases)

- Complex to manage and backup

- Difficult to run analytics across tenants

• **RLS with shared database** (✅)

- One database, automatic isolation

- Impossible to accidentally query wrong data

- Cost-effective and scalable

- Industry standard (used by Notion, Linear, etc.)

The 3 Types of RLS Policies

Supabase supports 5 PostgreSQL policy types, but you'll use these 3 most often:

1. SELECT Policies (Reading Data)

Controls which rows users can read.

CREATE POLICY "Users can read own tasks"
ON tasks
FOR SELECT
USING (auth.uid() = user_id);

How it works:

• `auth.uid()` returns the currently authenticated user's ID
• Policy checks if the user_id column matches
• Only matching rows are returned

2. INSERT Policies (Creating Data)

Controls which rows users can create.

CREATE POLICY "Users can create own tasks"
ON tasks
FOR INSERT
WITH CHECK (auth.uid() = user_id);

How it works:

• `WITH CHECK` validates the row being inserted
• If user tries to insert with different user_id, it fails
• Prevents impersonation attacks

3. UPDATE Policies (Modifying Data)

Controls which rows users can modify.

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

How it works:

• `USING` checks which rows can be updated (must own it)
• `WITH CHECK` validates the new values (can't change owner)

Common mistake: Forgetting WITH CHECK allows users to change user_id to someone else's!

Multi-Tenant Patterns

Pattern 1: User-Owned Data (B2C SaaS)

Every row belongs to one user. Todoist, Notion personal, note-taking apps.

Schema:

CREATE TABLE notes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

RLS Policies:

-- Enable RLS
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

-- Users can read own notes
CREATE POLICY "Users can read own notes"
ON notes FOR SELECT
USING (auth.uid() = user_id);

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

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

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

Usage:

// No filtering needed! RLS handles it automatically
const { data: notes } = await supabase
  .from('notes')
  .select('*')
  .order('created_at', { ascending: false })

// Trying to insert with wrong user_id fails automatically
const { error } = await supabase
  .from('notes')
  .insert({ user_id: 'someone-else', title: 'Hack attempt' })
// ❌ Error: new row violates row-level security policy

Pattern 2: Organization-Based (B2B SaaS)

Data belongs to organizations/teams. Users can be members of multiple organizations. Slack, Asana, Linear.

Schema:

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE organization_members (
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  PRIMARY KEY (organization_id, user_id)
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

RLS Policies:

-- Enable RLS
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Users can read organizations they're members of
CREATE POLICY "Users can read own organizations"
ON organizations FOR SELECT
USING (
  id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
  )
);

-- Users can read members of their organizations
CREATE POLICY "Users can read organization members"
ON organization_members FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
  )
);

-- Users can read projects in their organizations
CREATE POLICY "Users can read organization projects"
ON projects FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
  )
);

-- Only admins/owners can create projects
CREATE POLICY "Admins can create projects"
ON projects FOR INSERT
WITH CHECK (
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  )
);

Usage:

// Get all projects across all user's organizations
const { data: projects } = await supabase
  .from('projects')
  .select(`
    *,
    organization:organizations(name)
  `)

// RLS automatically filters to only organizations user is member of
// No need to pass organization_id manually

Pattern 3: Hierarchical Permissions (Advanced)

Different roles have different permissions. Editors can modify, viewers can only read.

RLS Policy:

-- Users can read if they're viewer, editor, or owner
CREATE POLICY "Users can read based on role"
ON projects FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('viewer', 'editor', 'admin', 'owner')
  )
);

-- Only editors, admins, and owners can update
CREATE POLICY "Editors can update projects"
ON projects FOR UPDATE
USING (
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('editor', 'admin', 'owner')
  )
);

-- Only owners can delete
CREATE POLICY "Owners can delete projects"
ON projects FOR DELETE
USING (
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role = 'owner'
  )
);

Common RLS Mistakes

Mistake 1: Forgetting to Enable RLS

-- ❌ RLS not enabled - anyone can read everything!
CREATE TABLE sensitive_data (
  user_id UUID,
  secret TEXT
);

-- ✅ Always enable RLS
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

By default, tables with RLS enabled but no policies deny all access. This is safe - you explicitly grant access via policies.

Mistake 2: Missing WITH CHECK on INSERT/UPDATE

-- ❌ User can insert rows owned by other users
CREATE POLICY "Bad insert policy"
ON tasks FOR INSERT
USING (auth.uid() = user_id); -- Wrong! USING is for SELECT

-- ✅ Use WITH CHECK for INSERT
CREATE POLICY "Good insert policy"
ON tasks FOR INSERT
WITH CHECK (auth.uid() = user_id);

Mistake 3: Overly Permissive Policies

-- ❌ Everyone can read all organizations
CREATE POLICY "Bad policy"
ON organizations FOR SELECT
USING (true);

-- ✅ Only show organizations user is member of
CREATE POLICY "Good policy"
ON organizations FOR SELECT
USING (
  id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
  )
);

Mistake 4: Not Testing RLS as Different Users

-- Testing RLS in SQL editor
SET ROLE authenticated;
SET request.jwt.claim.sub = 'user-uuid-1';

SELECT * FROM tasks; -- Only sees user-uuid-1's tasks

SET request.jwt.claim.sub = 'user-uuid-2';

SELECT * FROM tasks; -- Only sees user-uuid-2's tasks

RESET ROLE;

Performance Optimization

RLS policies run on every query. Poorly written policies can slow down your app.

Problem: Slow Policies with Subqueries

-- ❌ Subquery runs for EVERY row
CREATE POLICY "Slow policy"
ON tasks FOR SELECT
USING (
  project_id IN (
    SELECT id FROM projects
    WHERE organization_id IN (
      SELECT organization_id
      FROM organization_members
      WHERE user_id = auth.uid()
    )
  )
);

Solution: Denormalize organization_id

-- Add organization_id to tasks table
ALTER TABLE tasks ADD COLUMN organization_id UUID REFERENCES organizations(id);

-- Create index
CREATE INDEX idx_tasks_organization_id ON tasks(organization_id);

-- ✅ Fast policy using direct column
CREATE POLICY "Fast policy"
ON tasks FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
  )
);

Trade-off: Denormalization adds complexity (need to keep organization_id in sync) but dramatically improves query speed.

Use EXPLAIN ANALYZE to Profile

EXPLAIN ANALYZE
SELECT * FROM tasks WHERE status = 'todo';

-- Look for:
-- - Sequential scans (bad) vs Index scans (good)
-- - Execution time
-- - Rows filtered by RLS policy

RLS with Server-Side Code

RLS works automatically with Supabase client, but requires special setup for server-side code:

// ❌ Service role bypasses RLS (use carefully!)
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // Bypasses RLS!
)

const { data } = await supabase.from('tasks').select('*')
// Returns ALL tasks from ALL users! 🚨

// ✅ Server-side client that respects RLS
import { createServerClient } from '@supabase/ssr'

const supabase = createServerClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!, // Respects RLS
  {
    cookies: {
      get: (name) => cookieStore.get(name)?.value,
    },
  }
)

const { data } = await supabase.from('tasks').select('*')
// Only returns current user's tasks ✅

When to use service role:

• Admin operations (deleting all data for a user)
• Background jobs (no user context)
• Migrations and bulk operations

Always audit service role usage - it's the nuclear option.

Testing RLS Policies

ClaudeBoyz's database-rls skill generates tests, but here's how to test manually:

-- Create test users
INSERT INTO auth.users (id, email)
VALUES
  ('user-1', 'alice@example.com'),
  ('user-2', 'bob@example.com');

-- Insert test data
INSERT INTO tasks (user_id, title)
VALUES
  ('user-1', 'Alice task 1'),
  ('user-1', 'Alice task 2'),
  ('user-2', 'Bob task 1');

-- Test as Alice
SET ROLE authenticated;
SET request.jwt.claim.sub = 'user-1';

SELECT * FROM tasks;
-- Should return only Alice's 2 tasks

-- Test as Bob
SET request.jwt.claim.sub = 'user-2';

SELECT * FROM tasks;
-- Should return only Bob's 1 task

-- Test unauthorized access
SELECT * FROM tasks WHERE user_id = 'user-1';
-- Should return 0 rows (Bob can't see Alice's data)

RESET ROLE;

Debugging RLS Issues

Issue: User can't see their own data

Symptoms: Query returns empty even though data exists.

Diagnosis:

-- Check if RLS is enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public';

-- List all policies
SELECT * FROM pg_policies WHERE tablename = 'tasks';

-- Check user's auth.uid()
SELECT auth.uid();

Solutions:

• RLS enabled but no policies → Add policies
• Policy using wrong column → Fix USING clause
• auth.uid() returns null → User not authenticated

Issue: Policies conflict or overlap

Symptoms: Unexpected access granted/denied.

Diagnosis:

-- Policies are combined with OR
-- If ANY policy allows access, the user gets access

-- This is permissive:
CREATE POLICY "Owners can read" ON tasks FOR SELECT USING (user_id = auth.uid());
CREATE POLICY "Everyone can read" ON tasks FOR SELECT USING (true);
-- ↑ Everyone can read ALL tasks because of second policy!

Solution: Use RESTRICTIVE policies for AND logic (advanced).

ClaudeBoyz RLS Skills

Setting up RLS manually is tedious and error-prone. ClaudeBoyz automates it:

User: Set up Row Level Security for my tasks table with user isolation

Claude (via database-rls skill):
1. Analyzing table schema...
2. Generating RLS policies for user-owned pattern...
3. Creating indexes for performance...
4. Adding test cases...
5. Done! RLS enabled with 4 policies.

The skill:

• Detects your multi-tenant pattern (user-owned vs org-based)
• Generates optimized policies
• Creates necessary indexes
• Provides test queries
• Includes performance analysis

Try it: Use database-rls skill in ClaudeBoyz after creating your schema.

Conclusion

Row Level Security is the foundation of secure SaaS applications. It's:

✅ More secure than application-layer filtering

✅ More auditable (policies are in database, not scattered across code)

✅ More performant (PostgreSQL optimizes at query level)

✅ Industry standard (used by billion-dollar SaaS companies)

Master these patterns:

• User-owned data (B2C)
• Organization-based (B2B)
• Role-based permissions (RBAC)

Avoid these mistakes:

• Forgetting to enable RLS
• Missing WITH CHECK clauses
• Not testing as different users
• Slow policies without indexes

Use ClaudeBoyz skills:

• `database-schema` - Design multi-tenant schema
• `database-rls` - Generate RLS policies
• `auth-supabase` - Set up authentication
• `database-migrations` - Deploy safely to production

Secure your SaaS the right way. Use RLS.