I've shipped three multi-tenant SaaS apps on Supabase in the past two years. The first one was a disaster. Not because Supabase failed me -- it didn't -- but because I fundamentally misunderstood how Row Level Security (RLS) interacts with query planning at scale. The second was better. The third one handles 2,000+ tenants with sub-50ms query times across tables with millions of rows.

This article is everything I wish someone had told me before that first project. We're going to build a real multi-tenant schema from scratch, wire up RLS policies that actually perform, and cover the edge cases that only show up when you've got real traffic hitting your database.

Table of Contents

Supabase RLS Multi-Tenant Production Schema Design Guide

Multi-Tenancy Approaches in Supabase

Before we write a single line of SQL, let's get clear on the three approaches to multi-tenancy and why one of them wins for most Supabase projects.

Approach Isolation Level Complexity Cost per Tenant Best For
Database-per-tenant Highest Very High $25+/mo per tenant Enterprise, compliance-heavy
Schema-per-tenant High High $5-15/mo per tenant Mid-market SaaS
Shared schema + RLS Medium Medium Pennies per tenant Most SaaS apps

Database-per-tenant is what you'd use if you're selling to banks or healthcare companies who literally require separate infrastructure. Supabase doesn't make this easy -- you'd be managing multiple Supabase projects.

Schema-per-tenant (using PostgreSQL schemas like tenant_123.projects) sounds appealing but turns into a maintenance nightmare. Every migration runs against every schema. I tried this once. With 400 tenants, a simple ALTER TABLE migration took 45 minutes.

Shared schema with RLS is the sweet spot for 90% of SaaS applications. One set of tables, one set of migrations, and PostgreSQL's RLS policies handle the isolation. That's what we're building here.

The Foundation: Tenant and User Schema

Let's start with the core tables. I'm going to show you the schema I use in production, not a tutorial toy.

-- Enable UUID generation
create extension if not exists "uuid-ossp";

-- Tenants (organizations, workspaces, whatever you call them)
create table public.tenants (
  id uuid primary key default uuid_generate_v4(),
  name text not null,
  slug text unique not null,
  plan text not null default 'free' check (plan in ('free', 'pro', 'enterprise')),
  settings jsonb not null default '{}',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- Membership junction: connects auth.users to tenants
create table public.tenant_memberships (
  id uuid primary key default uuid_generate_v4(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role text not null default 'member' check (role in ('owner', 'admin', 'member', 'viewer')),
  created_at timestamptz not null default now(),
  
  unique(tenant_id, user_id)
);

-- Critical: indexes that RLS policies will lean on
create index idx_tenant_memberships_user_id on public.tenant_memberships(user_id);
create index idx_tenant_memberships_tenant_id on public.tenant_memberships(tenant_id);
create index idx_tenant_memberships_user_tenant on public.tenant_memberships(user_id, tenant_id);

Two things to notice. First, I'm using a junction table (tenant_memberships) instead of putting a tenant_id directly on the user profile. Users can belong to multiple tenants -- that's a real-world requirement for almost every SaaS app. Second, those indexes aren't optional. Without them, every RLS check does a sequential scan on the memberships table. I've seen this add 200ms+ to queries once you have a few thousand memberships.

Designing RLS Policies That Don't Kill Performance

Here's where most tutorials fail you. They show you a simple policy like:

-- DON'T DO THIS IN PRODUCTION
create policy "Users can view their tenant's data"
  on public.projects for select
  using (
    tenant_id in (
      select tenant_id from public.tenant_memberships
      where user_id = auth.uid()
    )
  );

This works. It'll pass your tests. And then you'll deploy it, get 500 users, and wonder why your dashboard takes 4 seconds to load.

The problem is that PostgreSQL evaluates this subquery for every single row. The query planner can sometimes optimize it, but it often doesn't -- especially with JOINs involved.

The Security Definer Function Pattern

Here's what actually works in production:

-- Create a function that returns the user's tenant IDs
-- SECURITY DEFINER means it runs with the function creator's permissions
-- This is critical: it bypasses RLS on the memberships table itself
create or replace function public.get_user_tenant_ids()
returns setof uuid
language sql
security definer
stable
set search_path = public
as $$
  select tenant_id 
  from public.tenant_memberships 
  where user_id = auth.uid();
$$;

-- Now use it in policies
create policy "tenant_isolation_select"
  on public.projects for select
  using (tenant_id in (select public.get_user_tenant_ids()));

create policy "tenant_isolation_insert"
  on public.projects for insert
  with check (tenant_id in (select public.get_user_tenant_ids()));

create policy "tenant_isolation_update"
  on public.projects for update
  using (tenant_id in (select public.get_user_tenant_ids()))
  with check (tenant_id in (select public.get_user_tenant_ids()));

create policy "tenant_isolation_delete"
  on public.projects for delete
  using (tenant_id in (select public.get_user_tenant_ids()));

Why is this faster? The STABLE keyword tells PostgreSQL the function returns the same result within a single statement. The planner can call it once and reuse the result. In my benchmarks, this cuts RLS overhead by 60-80% on queries that touch multiple rows.

The search_path Trap

See that set search_path = public on the function? That's not optional. Without it, a malicious user could potentially create a function in another schema that shadows auth.uid() and bypass your security. The Supabase team has written about this, but it's easy to miss.

Supabase RLS Multi-Tenant Production Schema Design Guide - architecture

The tenant_id Pattern: Getting It Right

Every table that holds tenant-specific data needs a tenant_id column. No exceptions. Even if the table has a foreign key to another tenant-scoped table. Here's why:

-- Your projects table
create table public.projects (
  id uuid primary key default uuid_generate_v4(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  name text not null,
  created_at timestamptz not null default now()
);

-- Tasks belong to projects. You might think tenant_id is redundant here.
create table public.tasks (
  id uuid primary key default uuid_generate_v4(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  project_id uuid not null references public.projects(id) on delete cascade,
  title text not null,
  status text not null default 'todo',
  created_at timestamptz not null default now()
);

-- Index for RLS + common query patterns
create index idx_projects_tenant on public.projects(tenant_id);
create index idx_tasks_tenant on public.tasks(tenant_id);
create index idx_tasks_project on public.tasks(project_id);
create index idx_tasks_tenant_project on public.tasks(tenant_id, project_id);

Yes, tenant_id on tasks is denormalized. Yes, it's the right call. Without it, the RLS policy on tasks would need to JOIN to projects to verify the tenant -- and that JOIN happens on every query. With the denormalized tenant_id, the RLS check is a simple index lookup.

I enforce consistency with a trigger:

create or replace function public.verify_tenant_consistency()
returns trigger
language plpgsql
as $$
begin
  if NEW.tenant_id != (
    select tenant_id from public.projects where id = NEW.project_id
  ) then
    raise exception 'tenant_id mismatch: task tenant does not match project tenant';
  end if;
  return NEW;
end;
$$;

create trigger check_task_tenant
  before insert or update on public.tasks
  for each row execute function public.verify_tenant_consistency();

JWT Claims vs Database Lookups

Supabase lets you embed custom claims in the JWT token. Some people use this to store the current tenant ID:

-- Reading from JWT (fast, but has caveats)
auth.jwt() -> 'app_metadata' ->> 'current_tenant_id'

Versus looking it up from the database every time:

-- Database lookup (slower, but always current)
select tenant_id from tenant_memberships where user_id = auth.uid()
Aspect JWT Claims Database Lookup
Speed ~0.1ms ~1-5ms
Freshness Stale until token refresh Always current
Multi-tenant switching Requires token refresh Immediate
Security on revocation Delay until JWT expires Immediate
Implementation complexity Higher (need Edge Function) Lower

My recommendation: use database lookups with the security definer function pattern for most apps. The performance difference is negligible when you have proper indexes, and you avoid an entire class of bugs around stale tokens.

If you're serving 10,000+ concurrent users and shaving milliseconds matters, then yes, move the active tenant ID into the JWT. You'll need a Supabase Edge Function (or a hook) to set the claim when users switch tenants, and you'll need to handle the token refresh flow on the client.

Handling Cross-Tenant Operations

Some operations legitimately need to cross tenant boundaries. Admin dashboards, billing systems, analytics aggregation. Here's how to handle them safely.

Service Role Key (Use Sparingly)

The Supabase service role key bypasses RLS entirely. Use it only in server-side code -- never expose it to the client.

// Server-side only (Next.js API route, Edge Function, etc.)
import { createClient } from '@supabase/supabase-js';

const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // NEVER expose this
);

// This bypasses RLS - use with extreme caution
const { data } = await supabaseAdmin
  .from('tenants')
  .select('id, name, plan')
  .eq('plan', 'enterprise');

If you're building on Next.js (we do a lot of this at Social Animal), your API routes and Server Components are the right place for service role operations.

Database Functions for Controlled Cross-Tenant Access

For more granular control, create specific functions:

create or replace function public.admin_get_tenant_stats(target_tenant_id uuid)
returns json
language plpgsql
security definer
set search_path = public
as $$
declare
  result json;
  caller_role text;
begin
  -- Verify the caller is an admin of the target tenant
  select role into caller_role
  from tenant_memberships
  where user_id = auth.uid() and tenant_id = target_tenant_id;
  
  if caller_role not in ('owner', 'admin') then
    raise exception 'Unauthorized: requires admin role';
  end if;
  
  select json_build_object(
    'project_count', (select count(*) from projects where tenant_id = target_tenant_id),
    'task_count', (select count(*) from tasks where tenant_id = target_tenant_id),
    'member_count', (select count(*) from tenant_memberships where tenant_id = target_tenant_id)
  ) into result;
  
  return result;
end;
$$;

Migration Strategy and Schema Evolution

Adding tenant_id to existing tables is the migration everyone dreads. Here's the approach that minimizes downtime:

-- Step 1: Add nullable column
alter table public.some_existing_table 
  add column tenant_id uuid references public.tenants(id);

-- Step 2: Backfill (do this in batches for large tables)
update public.some_existing_table set tenant_id = (
  select tenant_id from public.projects 
  where projects.id = some_existing_table.project_id
)
where tenant_id is null;

-- Step 3: Add NOT NULL constraint
alter table public.some_existing_table 
  alter column tenant_id set not null;

-- Step 4: Add index
create index concurrently idx_some_table_tenant 
  on public.some_existing_table(tenant_id);

-- Step 5: Enable RLS and add policies
alter table public.some_existing_table enable row level security;

create policy "tenant_isolation" on public.some_existing_table
  for all using (tenant_id in (select public.get_user_tenant_ids()));

The concurrently keyword on the index creation is crucial -- without it, you'll lock the table for the entire index build. On a table with a million rows, that could be minutes of downtime.

Performance Benchmarks and Optimization

I ran benchmarks on a Supabase Pro plan ($25/month, as of early 2025) with 500,000 rows in the tasks table spread across 1,000 tenants.

Query Pattern Without RLS Naive RLS Optimized RLS Overhead
Select 50 rows (single tenant) 2.1ms 18.4ms 3.8ms +81%
Insert single row 1.2ms 4.1ms 1.9ms +58%
Count with filter 3.4ms 22.1ms 5.2ms +53%
Join across 2 tables 4.8ms 45.2ms 8.1ms +69%

"Optimized RLS" means: security definer functions, proper composite indexes, tenant_id denormalized on child tables, and STABLE function volatility.

The naive approach (inline subqueries, missing indexes) makes RLS feel slow. Optimized, the overhead is totally acceptable for production workloads.

Additional Optimization Tips

  1. Use composite indexes with tenant_id as the leading column: create index on tasks(tenant_id, status, created_at)
  2. Partition large tables by tenant_id if any single tenant has millions of rows
  3. Use pg_stat_statements to find slow queries -- Supabase exposes this in the dashboard under Database > Query Performance
  4. Consider materialized views for cross-tenant analytics instead of running expensive aggregations in real-time

Security Hardening for Production

RLS is your primary defense, but it shouldn't be your only one.

1. Default Deny

Always enable RLS with no default policy -- this means if you forget to add a policy to a new table, it's locked down by default rather than wide open.

alter table public.new_table enable row level security;
-- Don't add a permissive policy until you've thought through access patterns

2. Audit Logging

create table public.audit_log (
  id bigint generated always as identity primary key,
  tenant_id uuid not null,
  user_id uuid,
  action text not null,
  table_name text not null,
  record_id uuid,
  old_data jsonb,
  new_data jsonb,
  created_at timestamptz not null default now()
);

-- Generic audit trigger
create or replace function public.audit_trigger_func()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if TG_OP = 'DELETE' then
    insert into audit_log(tenant_id, user_id, action, table_name, record_id, old_data)
    values (OLD.tenant_id, auth.uid(), TG_OP, TG_TABLE_NAME, OLD.id, to_jsonb(OLD));
    return OLD;
  else
    insert into audit_log(tenant_id, user_id, action, table_name, record_id, new_data, old_data)
    values (
      NEW.tenant_id, auth.uid(), TG_OP, TG_TABLE_NAME, NEW.id, 
      to_jsonb(NEW),
      case when TG_OP = 'UPDATE' then to_jsonb(OLD) else null end
    );
    return NEW;
  end if;
end;
$$;

3. Rate Limiting at the Edge

RLS prevents data leakage, but it doesn't prevent abuse. Use Supabase Edge Functions or your Next.js middleware for rate limiting. If you're building with Astro, you can handle this in your server endpoints.

4. Test Your Policies

Write actual tests. Use Supabase's local development environment (supabase start) and test that:

  • User A cannot see User B's tenant data
  • Removing a membership immediately revokes access
  • Service role correctly bypasses RLS
  • Insert/update policies prevent tenant_id tampering
// Example test with Vitest
import { describe, it, expect } from 'vitest';
import { createClient } from '@supabase/supabase-js';

describe('RLS Policies', () => {
  it('should prevent cross-tenant data access', async () => {
    const userA = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
      global: { headers: { Authorization: `Bearer ${tokenA}` } }
    });
    
    const { data, error } = await userA
      .from('projects')
      .select('*')
      .eq('tenant_id', TENANT_B_ID);
    
    expect(data).toHaveLength(0); // RLS should filter these out
  });
});

Real-World Schema Example

Here's a condensed but complete schema for a project management SaaS. This is close to what I've deployed in production.

-- Enable RLS on all tables
alter table public.tenants enable row level security;
alter table public.tenant_memberships enable row level security;
alter table public.projects enable row level security;
alter table public.tasks enable row level security;

-- Tenants: users can see tenants they belong to
create policy "view_own_tenants" on public.tenants for select
  using (id in (select public.get_user_tenant_ids()));

-- Only owners can update tenant settings
create policy "owners_update_tenants" on public.tenants for update
  using (
    id in (
      select tenant_id from public.tenant_memberships
      where user_id = auth.uid() and role = 'owner'
    )
  );

-- Memberships: members can see other members in their tenants
create policy "view_tenant_members" on public.tenant_memberships for select
  using (tenant_id in (select public.get_user_tenant_ids()));

-- Only admins+ can manage memberships
create policy "admins_manage_members" on public.tenant_memberships for insert
  with check (
    tenant_id in (
      select tenant_id from public.tenant_memberships
      where user_id = auth.uid() and role in ('owner', 'admin')
    )
  );

-- Projects & Tasks: standard tenant isolation
create policy "tenant_projects" on public.projects for all
  using (tenant_id in (select public.get_user_tenant_ids()))
  with check (tenant_id in (select public.get_user_tenant_ids()));

create policy "tenant_tasks" on public.tasks for all
  using (tenant_id in (select public.get_user_tenant_ids()))
  with check (tenant_id in (select public.get_user_tenant_ids()));

If you're building something like this and want a team that's done it before, check out our headless CMS development work -- we've paired Supabase backends with headless frontends for several clients.

FAQ

Does Supabase RLS add significant latency to queries?

With optimized policies (security definer functions, proper indexes, denormalized tenant_id), the overhead is typically 50-80% on top of the raw query time. For a query that takes 3ms without RLS, expect 5-6ms with it. This is perfectly acceptable for production use. The naive approach can add 10-20x overhead, which is why optimization matters so much.

Can I use RLS with Supabase Realtime subscriptions?

Yes. Supabase Realtime respects RLS policies. When a client subscribes to changes on a table, they'll only receive events for rows they're authorized to see. This works automatically -- you don't need to add any extra filtering logic on the client. Just make sure your RLS policies are efficient, because they're evaluated for every broadcast.

How do I handle tenant switching in the UI?

Store the active tenant ID in your app's state (React context, Zustand store, etc.) and pass it as a filter in your queries. Since RLS already limits results to tenants the user belongs to, switching is just a matter of changing which tenant_id you filter by. No token refresh needed if you're using the database lookup approach.

Should I use Supabase's built-in auth or an external provider like Clerk?

Supabase Auth integrates naturally with RLS through auth.uid(). If you use an external provider, you'll need to sync users into Supabase and use custom JWT claims or a mapping table. I'd stick with Supabase Auth unless you have a specific reason not to -- it saves significant integration work.

How many tenants can a single Supabase project handle?

With shared-schema multi-tenancy, I've personally run 2,000+ tenants on a Supabase Pro plan ($25/month). The practical limit depends on total data volume and query patterns, not tenant count. A Supabase Pro instance with 2 vCPUs and 1GB RAM can handle tables with tens of millions of rows if your indexes are right.

What happens if I forget to enable RLS on a new table?

If you're using the Supabase client with the anon key, any table without RLS enabled is accessible to anyone with that key. This is the biggest footgun in Supabase. Set up a CI check that verifies all tables in the public schema have RLS enabled. You can query pg_tables joined with pg_class to automate this check.

Can I use RLS with Supabase Edge Functions?

Yes. Edge Functions can create a Supabase client using either the anon key (RLS applies) or the service role key (RLS bypassed). Use the anon key with the user's JWT for user-facing operations, and the service role key for administrative tasks that need cross-tenant access.

How do I migrate from a single-tenant app to multi-tenant?

Add tenant_id as a nullable column, backfill it for all existing data (all rows get the same tenant ID since it was single-tenant), then add the NOT NULL constraint, indexes, and RLS policies. Test extensively with your existing data before enabling RLS -- one wrong policy can lock out all your users. Use Supabase's local dev environment to rehearse the migration.