🔐 Row Level Security in Supabase: The Complete Guide to Multi-Tenant Data Isolation

The Vulnerability You Ship Without RLS

When you create a table in Supabase and start building your app, there is a default behavior that surprises most developers when they discover it in production: without Row Level Security enabled, every row in your table is accessible to every authenticated (and sometimes anonymous) user via the Supabase JavaScript SDK.

Consider a SaaS application with a documents table where each row belongs to a specific user. Without RLS, any client-side call like this returns every document in the table — across all users:

const { data } = await supabase.from('documents').select('*')
// Returns ALL documents from ALL users. 💀

The Supabase anon key (which is safe to expose in the browser) does not provide any data filtering by itself. It identifies your Supabase project, not the user. Data isolation is entirely your responsibility — and with RLS, you can enforce it at the database layer so that no bug in your application code can ever expose another user's data.

Enabling RLS on a Table

RLS must be enabled per table. By default, tables have RLS disabled. Run this SQL in the Supabase SQL editor or in a migration file:

-- Enable RLS (required before creating policies)
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- With RLS enabled but no policies, the table is now completely inaccessible
-- to all non-service-role clients. Start from locked down, add permissions up.

This is important: once you enable RLS with no policies, the table becomes completely inaccessible to the anon key and the authenticated role. You must explicitly create policies to grant access. This "default deny" posture is the correct security model — grant only what you need, and nothing more.

Policy Syntax: USING and WITH CHECK

RLS policies have two clauses that serve different purposes:

• USING — evaluated for rows that already exist. Applies to SELECT, UPDATE, and DELETE. If the USING expression returns false for a row, that row is invisible (for SELECT) or unmodifiable (for UPDATE/DELETE) to the current user.
• WITH CHECK — evaluated for new row data. Applies to INSERT and UPDATE. If the expression returns false, the operation is rejected with a policy violation error.

-- Full CRUD policies for a documents table with user_id FK

CREATE POLICY "select_own_documents"
  ON documents FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "insert_own_documents"
  ON documents FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "update_own_documents"
  ON documents FOR UPDATE
  USING (auth.uid() = user_id)           -- Can only update rows you own
  WITH CHECK (auth.uid() = user_id);      -- Cannot change user_id to someone else's

CREATE POLICY "delete_own_documents"
  ON documents FOR DELETE
  USING (auth.uid() = user_id);

The UPDATE policy needs both clauses: USING prevents you from modifying rows you don't own, while WITH CHECK prevents you from transferring ownership of a row to another user by setting user_id = '<other-uuid>'. Without the WITH CHECK on UPDATE, an attacker who somehow gained access to another user's row could change its user_id to their own, effectively stealing it.

The auth.uid() Function

auth.uid() is a PostgreSQL function provided by Supabase that extracts the user's UUID from the JWT attached to the database request. When the Supabase JS SDK makes a query, it includes the user's session JWT in the request headers. The Supabase PostgREST layer validates the JWT and sets the auth.uid() context for the duration of that query. This happens transparently — you do not need to pass user IDs explicitly in your queries.

This also means RLS policies evaluate in milliseconds — there is no extra round-trip to an auth server. The JWT is verified cryptographically, and auth.uid() reads from the verified payload. No spoofing is possible.

Standard Multi-Tenant Pattern: user_id Foreign Key

The simplest and most common RLS pattern is a direct user_id column on every user-data table, with a foreign key to auth.users(id):

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

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- One policy per operation
CREATE POLICY "user_projects_select" ON projects FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "user_projects_insert" ON projects FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "user_projects_update" ON projects FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "user_projects_delete" ON projects FOR DELETE USING (auth.uid() = user_id);

When inserting, never rely on your application to set user_id correctly. Set it in the database using the DEFAULT approach, or enforce it via the WITH CHECK policy. A common pattern is to use a trigger or the WITH CHECK policy to ensure user_id always equals the authenticated user.

Team-Based Access: Policies with JOIN

Many SaaS products need team-level access: multiple users sharing the same projects. The pattern uses a team_members join table and a subquery in the USING clause:

CREATE TABLE teams (
  id   UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE team_members (
  team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role    TEXT NOT NULL DEFAULT 'member', -- 'owner', 'admin', 'member'
  PRIMARY KEY (team_id, user_id)
);

CREATE TABLE team_projects (
  id      UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  name    TEXT NOT NULL
);

ALTER TABLE team_projects ENABLE ROW LEVEL SECURITY;

-- Allow any team member to SELECT team projects
CREATE POLICY "team_members_can_select_projects"
  ON team_projects FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM team_members
      WHERE team_members.team_id = team_projects.team_id
        AND team_members.user_id = auth.uid()
    )
  );

-- Only team admins and owners can INSERT projects
CREATE POLICY "team_admins_can_insert_projects"
  ON team_projects FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM team_members
      WHERE team_members.team_id = team_projects.team_id
        AND team_members.user_id = auth.uid()
        AND team_members.role IN ('admin', 'owner')
    )
  );

The EXISTS subquery is evaluated per row, but PostgreSQL optimizes this efficiently. Make sure team_members(team_id, user_id) has an index — and the PRIMARY KEY on that pair ensures one exists.

The Service Role: RLS Bypass for Admin Code

Supabase provides a service_role key in addition to the anon key. The service role bypasses all RLS policies entirely — it sees all rows in all tables. This is necessary for administrative tasks like: sending emails to all users, running database migrations, or processing background jobs that need to access data across multiple users.

Never expose the service role key to the browser. It must only ever be used in server-side code (Node.js scripts, Next.js Route Handlers, Supabase Edge Functions). Store it as a server-only environment variable (no NEXT_PUBLIC_ prefix).

// lib/supabase/admin.ts — server-side only, never imported in "use client" files
import { createClient } from '@supabase/supabase-js'

export const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // Never NEXT_PUBLIC_!
  { auth: { autoRefreshToken: false, persistSession: false } }
)

Permissive vs Restrictive Policies

By default, all Supabase RLS policies are permissive — they are OR'd together. If any permissive policy grants access to a row, the row is accessible. This is the expected behavior for most use cases.

Restrictive policies are AND'd with the permissive results. A row must pass ALL restrictive policies AND at least one permissive policy to be accessible. Restrictive policies are declared with AS RESTRICTIVE:

-- Example: users can only access documents in their subscription tier
CREATE POLICY "subscription_tier_restriction"
  ON documents FOR SELECT
  AS RESTRICTIVE
  USING (
    EXISTS (
      SELECT 1 FROM user_subscriptions
      WHERE user_subscriptions.user_id = auth.uid()
        AND user_subscriptions.tier = 'pro'
    )
  );

Use restrictive policies for hard constraints that must never be overridden by other permissive policies — like subscription gating or compliance requirements.

Testing That Your RLS Policies Actually Work

Never trust that your policies work without testing them. The testing protocol:

1. Create two test accounts in your Supabase Auth.
2. Insert a row as User A (via your app or the Supabase dashboard).
3. Log in as User B, open your browser's developer tools, and use the Supabase JS client to query the table directly.
4. Verify that the response is empty or returns a policy violation error — not User A's data.

You can also test via SQL in the Supabase editor by temporarily setting the role:

-- In Supabase SQL editor: simulate a user's query context
SET LOCAL role = authenticated;
SET LOCAL "request.jwt.claims" = '{"sub": "<user-b-uuid>", "role": "authenticated"}';

SELECT * FROM documents;
-- Should return 0 rows if only User A has documents

Automated integration tests using a dedicated test Supabase project are even better — create users programmatically, insert data, then verify isolation. This gives you regression coverage as your schema evolves.

Common RLS Mistakes

• Forgetting to enable RLS on new tables. Every time you run a migration that creates a new table, add ENABLE ROW LEVEL SECURITY immediately. Make it part of your migration template.
• Missing the WITH CHECK on UPDATE. Without it, users can change the user_id column to reassign rows.
• Using the service role key in client-facing code. A single leak exposes your entire database.
• Writing policies that are too broad. USING (true) makes every row visible to every user — only appropriate for truly public data.
• Not indexing the join columns used in subquery policies. Team-based policies with unindexed team_id columns will cause full table scans on every query.