Row Level Security (RLS)
Database-level data isolation for multi-tenant applications.
When to Use This Skill
- Building multi-tenant SaaS applications
- Ensuring users can only access their own data
- Implementing organization-based data isolation
- Adding defense-in-depth security layer
Why RLS?
Application-level filtering can be bypassed. RLS enforces access at the database level:
❌ Application Filter: SELECT * FROM posts WHERE user_id = ?
(Bug in code = data leak)
✅ RLS Policy: User can ONLY see rows where user_id matches
(Database enforces, impossible to bypass)Basic Setup
Enable RLS on Tables
-- Enable RLS (required first step)
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
-- Force RLS for table owners too (important!)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;User-Based Policies
-- Users can only see their own posts
CREATE POLICY "Users can view own posts"
ON posts FOR SELECT
USING (user_id = auth.uid());
-- Users can insert posts as themselves
CREATE POLICY "Users can create own posts"
ON posts FOR INSERT
WITH CHECK (user_id = auth.uid());
-- Users can update their own posts
CREATE POLICY "Users can update own posts"
ON posts FOR UPDATE
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());
-- Users can delete their own posts
CREATE POLICY "Users can delete own posts"
ON posts FOR DELETE
USING (user_id = auth.uid());Organization-Based Multi-Tenancy
Schema Setup
-- Organizations table
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Organization memberships
CREATE TABLE organization_members (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
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',
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(organization_id, user_id)
);
-- Projects belong to organizations
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Enable RLS
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;Organization Policies
-- Helper function: Get user's organizations
CREATE OR REPLACE FUNCTION get_user_organizations()
RETURNS SETOF UUID AS $$
SELECT organization_id
FROM organization_members
WHERE user_id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER STABLE;
-- Users can see organizations they belong to
CREATE POLICY "Members can view organization"
ON organizations FOR SELECT
USING (id IN (SELECT get_user_organizations()));
-- Users can see projects in their organizations
CREATE POLICY "Members can view org projects"
ON projects FOR SELECT
USING (organization_id IN (SELECT get_user_organizations()));
-- Only admins 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 ('admin', 'owner')
)
);Role-Based Policies
-- Define roles
CREATE TYPE user_role AS ENUM ('viewer', 'editor', 'admin', 'owner');
-- Role hierarchy helper
CREATE OR REPLACE FUNCTION has_role(
required_role user_role,
org_id UUID
) RETURNS BOOLEAN AS $$
SELECT EXISTS (
SELECT 1 FROM organization_members
WHERE user_id = auth.uid()
AND organization_id = org_id
AND role::user_role >= required_role
)
$$ LANGUAGE sql SECURITY DEFINER STABLE;
-- Viewers can read
CREATE POLICY "Viewers can read"
ON projects FOR SELECT
USING (has_role('viewer', organization_id));
-- Editors can update
CREATE POLICY "Editors can update"
ON projects FOR UPDATE
USING (has_role('editor', organization_id))
WITH CHECK (has_role('editor', organization_id));
-- Admins can delete
CREATE POLICY "Admins can delete"
ON projects FOR DELETE
USING (has_role('admin', organization_id));Supabase-Specific Setup
Auth Helper Functions
-- Get current user ID (Supabase)
CREATE OR REPLACE FUNCTION auth.uid()
RETURNS UUID AS $$
SELECT COALESCE(
current_setting('request.jwt.claims', true)::json->>'sub',
(current_setting('request.jwt.claims', true)::json->>'user_id')
)::UUID
$$ LANGUAGE sql STABLE;
-- Get current user's email
CREATE OR REPLACE FUNCTION auth.email()
RETURNS TEXT AS $$
SELECT current_setting('request.jwt.claims', true)::json->>'email'
$$ LANGUAGE sql STABLE;Service Role Bypass
-- Allow service role to bypass RLS (for admin operations)
CREATE POLICY "Service role bypass"
ON projects FOR ALL
USING (auth.role() = 'service_role');TypeScript Integration
Supabase Client Setup
// lib/supabase.ts
import { createClient } from '@supabase/supabase-js';
// Client-side (respects RLS)
export const supabase = createClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);
// Server-side with service role (bypasses RLS)
export const supabaseAdmin = createClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.SUPABASE_SERVICE_ROLE_KEY!
);Querying with RLS
// This automatically filters by RLS policies
async function getUserProjects() {
const { data, error } = await supabase
.from('projects')
.select('*');
// Only returns projects user has access to
return data;
}
// Admin operation (bypasses RLS)
async function getAllProjects() {
const { data, error } = await supabaseAdmin
.from('projects')
.select('*');
// Returns ALL projects
return data;
}Testing RLS Policies
-- Test as specific user
SET request.jwt.claims = '{"sub": "user-uuid-here"}';
-- Run query (should be filtered)
SELECT * FROM projects;
-- Reset
RESET request.jwt.claims;Automated Tests
// __tests__/rls.test.ts
describe('RLS Policies', () => {
it('user can only see own projects', async () => {
// Create two users
const user1 = await createTestUser();
const user2 = await createTestUser();
// User1 creates a project
const project = await createProject(user1.id, 'Secret Project');
// User2 tries to access
const client = createClientAsUser(user2);
const { data } = await client.from('projects').select('*');
// Should not see user1's project
expect(data).not.toContainEqual(
expect.objectContaining({ id: project.id })
);
});
});Performance Considerations
Index for RLS Columns
-- Always index columns used in RLS policies
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_projects_org_id ON projects(organization_id);
CREATE INDEX idx_org_members_user_org ON organization_members(user_id, organization_id);Avoid Expensive Functions
-- ❌ Bad: Subquery in every row check
CREATE POLICY "slow_policy"
ON posts FOR SELECT
USING (user_id IN (SELECT user_id FROM complex_view));
-- ✅ Good: Use SECURITY DEFINER function with caching
CREATE OR REPLACE FUNCTION get_accessible_user_ids()
RETURNS SETOF UUID AS $$
SELECT user_id FROM simple_lookup WHERE condition
$$ LANGUAGE sql SECURITY DEFINER STABLE;
CREATE POLICY "fast_policy"
ON posts FOR SELECT
USING (user_id IN (SELECT get_accessible_user_ids()));Best Practices
- Enable RLS on ALL tables with user data: Don't forget any table
- Use FORCE ROW LEVEL SECURITY: Applies to table owners too
- Create helper functions: Reuse logic across policies
- Index RLS columns: Critical for performance
- Test policies thoroughly: Verify isolation works
Common Mistakes
- Forgetting to enable RLS (table is wide open)
- Not using FORCE (table owner bypasses policies)
- Complex subqueries in policies (performance killer)
- Not indexing policy columns
- Trusting application-level filtering alone
Security Checklist
- RLS enabled on all user-data tables
- FORCE ROW LEVEL SECURITY set
- Policies cover SELECT, INSERT, UPDATE, DELETE
- Service role key only used server-side
- Helper functions use SECURITY DEFINER
- Policies tested with multiple users
- Indexes on all RLS columns