Supabase Row Level Security: Policies That Actually Work

Row Level Security is the most important security feature in Supabase — and the most commonly misconfigured. Enabled but with no policies? Everyone is blocked. Wrong policy logic? Users see each other's data. Here's how to write policies that are both secure and fast.

Prerequisites

Supabase project with a PostgreSQL database
Tables with data you want to protect
Authentication configured (users must exist to write meaningful RLS)

Enable RLS and Understand the Default

-- Enable RLS on a table (disabled by default)
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
 
-- With RLS enabled and NO policies: all access is denied (for non-superusers)
-- With RLS enabled and policies: only rows matching a policy are accessible

The anon role (unauthenticated) and authenticated role (logged-in user) are what your policies target. The service role bypasses RLS entirely.

Basic User Isolation Pattern

The most common pattern — users can only access their own rows:

-- Allow users to read only their own rows
CREATE POLICY "users_read_own" ON posts
  FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);
 
-- Allow users to insert only rows with their own user_id
CREATE POLICY "users_insert_own" ON posts
  FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = user_id);
 
-- Allow users to update only their own rows
CREATE POLICY "users_update_own" ON posts
  FOR UPDATE
  TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);
 
-- Allow users to delete only their own rows
CREATE POLICY "users_delete_own" ON posts
  FOR DELETE
  TO authenticated
  USING (auth.uid() = user_id);

USING controls which rows are visible (SELECT, UPDATE, DELETE). WITH CHECK controls which rows can be written (INSERT, UPDATE).

Public Read, Authenticated Write

Blog posts or products that are public to read but protected to write:

-- Anyone (including unauthenticated) can read published posts
CREATE POLICY "public_read_published" ON posts
  FOR SELECT
  TO anon, authenticated
  USING (published = true);
 
-- Only the author can read their unpublished drafts
CREATE POLICY "author_read_drafts" ON posts
  FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id AND published = false);
 
-- Only authenticated users can insert
CREATE POLICY "authenticated_insert" ON posts
  FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = user_id);

Multi-Tenant Pattern with Organizations

When users belong to organizations and can see all data within their org:

-- Helper function for cleaner policies
CREATE OR REPLACE FUNCTION auth.user_org_id()
RETURNS uuid AS $$
  SELECT org_id FROM user_profiles WHERE user_id = auth.uid()
$$ LANGUAGE sql STABLE SECURITY DEFINER;
 
-- Users can see all rows belonging to their organization
CREATE POLICY "org_members_read" ON projects
  FOR SELECT
  TO authenticated
  USING (org_id = auth.user_org_id());
 
-- Only owners can delete
CREATE POLICY "org_owners_delete" ON projects
  FOR DELETE
  TO authenticated
  USING (
    org_id = auth.user_org_id() AND
    EXISTS (
      SELECT 1 FROM org_members
      WHERE org_id = projects.org_id
      AND user_id = auth.uid()
      AND role = 'owner'
    )
  );

Admin Override with Custom Claims

Sometimes you need an admin user to see all rows. Use JWT custom claims:

-- Check if user has admin claim in their JWT
CREATE POLICY "admins_read_all" ON posts
  FOR SELECT
  TO authenticated
  USING (
    auth.uid() = user_id  -- user's own posts
    OR
    (auth.jwt() ->> 'user_role') = 'admin'  -- OR is admin
  );

Set the custom claim in a Supabase Function or Edge Function after user creation:

// supabase/functions/set-user-role/index.ts
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';
 
Deno.serve(async (req) => {
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  );
 
  const { userId, role } = await req.json();
 
  await supabase.auth.admin.updateUserById(userId, {
    app_metadata: { user_role: role }
  });
 
  return new Response(JSON.stringify({ success: true }));
});

Performance: Index the Columns in USING Clauses

RLS policies run on every row. If your USING clause filters on user_id, that column must be indexed:

-- Without this index, every SELECT scans the full table for RLS
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_projects_org_id ON projects(org_id);

For complex policies with subqueries, test with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM posts WHERE true;
-- The WHERE true triggers RLS evaluation — check for Seq Scans on large tables

Bypass RLS from Server (Service Role)

Your Next.js server-side code can bypass RLS using the service role key:

// Use service role only in server-side code (API routes, server actions)
import { createClient } from '@supabase/supabase-js';
 
const adminClient = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // NOT NEXT_PUBLIC — server only
);
 
// This bypasses RLS — use only for admin operations
const { data } = await adminClient.from('posts').select('*');

Debugging Policies

-- Test as a specific user
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO '{"sub": "user-uuid-here"}';
SELECT * FROM posts; -- Should return only that user's rows
 
-- See all policies on a table
SELECT * FROM pg_policies WHERE tablename = 'posts';
 
-- Check which rows the current user can see
SELECT * FROM posts; -- Run as authenticated user in Supabase dashboard

Common Pitfalls

RLS enabled with no policies: blocks ALL access including your app — always create at least a SELECT policy before enabling
Not indexing filtered columns: USING (user_id = auth.uid()) on a 1M-row table without an index is a full table scan on every query
Using anon key on the server: the anon key is subject to RLS — your server code should use service role for admin operations
Forgetting WITH CHECK on INSERT: USING only applies to existing rows — INSERT needs WITH CHECK to prevent users from inserting rows they don't own