Supabase Multi-Tenancy & Row-Level Security

Building secure multi-tenant SaaS platforms requires absolute isolation of customer data. Traditional application-level security relies on backend developers remembering to append WHERE tenant_id = ? to every SQL query across hundreds of API endpoints. A single forgotten filter condition results in critical data leaks between tenants.

Supabase—built directly on top of PostgreSQL—solves this fundamental vulnerability using Row-Level Security (RLS). RLS enforces access control policies inside the database engine itself. Regardless of whether a query originates from a frontend client via Supabase-JS or an external API gateway, PostgreSQL filters rows before returning data. In this guide, we dive deep into architecting multi-tenant schemas with custom JWT claims, optimizing policy performance, and avoiding security definer pitfalls.

1. Enabling RLS & Multi-Tenant Schema Design

In a pooled multi-tenant database, every table storing tenant-specific data must include a tenant_id column (typically a UUID referencing a tenants table). By default, PostgreSQL tables allow unrestricted access until RLS is explicitly enabled.

-- Create tenant organization structure
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_name TEXT NOT NULL,
    plan_tier TEXT NOT NULL DEFAULT 'standard',
    created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

-- Create tenant-scoped resource table
CREATE TABLE organization_projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    project_name TEXT NOT NULL,
    budget_usd NUMERIC(12,2) DEFAULT 0.00,
    created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

-- CRITICAL: Explicitly enable Row-Level Security on all tables
ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_projects ENABLE ROW LEVEL SECURITY;

2. Custom JWT Claims & Policy Definitions

When users authenticate via Supabase Auth, Supabase signs a JSON Web Token (JWT) containing the user's UUID (auth.uid()) and optional custom claims. During request execution, PostgreSQL parses the JWT into the request.jwt.claims session setting.

Instead of executing subqueries against a user_tenants table on every single row check, we extract tenant_id directly from the JWT claims for high performance:

-- Helper function to extract tenant_id from Supabase JWT claims safely
CREATE OR REPLACE FUNCTION auth.jwt_tenant_id() 
RETURNS UUID 
LANGUAGE sql 
STABLE 
AS $$
  SELECT COALESCE(
    (NULLIF(current_setting('request.jwt.claims', true), ''))::jsonb -> 'app_metadata' ->> 'tenant_id',
    '00000000-0000-0000-0000-000000000000'
  )::uuid;
$$;

-- RLS Policy: Grant READ access only to users belonging to the matching tenant
CREATE POLICY "Tenant Read Access Policy"
ON organization_projects
FOR SELECT
TO authenticated
USING (tenant_id = auth.jwt_tenant_id());

-- RLS Policy: Grant WRITE/INSERT access enforcing tenant ownership
CREATE POLICY "Tenant Write Access Policy"
ON organization_projects
FOR INSERT
TO authenticated
WITH CHECK (tenant_id = auth.jwt_tenant_id());

3. Performance Tuning: Avoiding Subquery Bottlenecks

A common mistake when defining RLS policies is joining permission tables inside the USING clause without index optimization. If PostgreSQL executes a subquery like WHERE user_id IN (SELECT user_id FROM team_members...) for every evaluated row in a 500,000-row table, query execution times shoot from 5ms up to 4,000ms.

To eliminate RLS performance degradation:

• Index foreign keys: Always create B-Tree indexes on tenant_id columns across all tables.
• Use STABLE helper functions: Mark custom policy functions as STABLE or IMMUTABLE so PostgreSQL caches function results per statement rather than re-evaluating per row.
• Avoid unindexed joins in policies: Pre-calculate user permissions into custom JWT claims or indexed lookup tables.

-- High-performance B-Tree index supporting RLS policy evaluation
CREATE INDEX idx_organization_projects_tenant 
ON organization_projects (tenant_id);

4. Bypassing RLS Safely: Security Definer Functions

In specific administrative scenarios (such as global reporting or payment webhook processing), backend services need elevated privileges. Rather than exposing the database superuser connection string to client applications, wrap sensitive actions inside PostgreSQL SECURITY DEFINER functions:

-- SECURITY DEFINER executes with the permissions of the function creator (admin)
CREATE OR REPLACE FUNCTION aggregate_tenant_metrics(target_tenant UUID)
RETURNS TABLE (total_projects INT, aggregate_budget NUMERIC)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    -- Verify caller matches target_tenant or is a super-admin
    IF auth.jwt_tenant_id() <> target_tenant AND (auth.jwt()->>'role') <> 'service_role' THEN
        RAISE EXCEPTION 'Unauthorized metrics access attempt';
    END IF;

    RETURN QUERY
    SELECT COUNT(*)::INT, COALESCE(SUM(budget_usd), 0.00)
    FROM organization_projects
    WHERE tenant_id = target_tenant;
END;
$$;

5. Testing RLS Compliance with pgTAP

To verify that security policies function as expected, write automated unit tests using the pgTAP testing framework:

-- Sample pgTAP unit test script
BEGIN;
SELECT plan(2);

-- Impersonate an authenticated tenant user
SET LOCAL ROLE authenticated;
SET LOCAL "request.jwt.claims" = '{"sub": "user_123", "app_metadata": {"tenant_id": "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"}}';

-- Assert user can view their own tenant projects
SELECT results_eq(
    'SELECT COUNT(*)::INT FROM organization_projects',
    ARRAY[3],
    'Tenant user should only see their 3 assigned projects'
);

SELECT finish();
ROLLBACK;

Conclusion

Supabase Row-Level Security converts PostgreSQL into a self-defending data platform. By pairing custom JWT claims with indexed tenant_id columns, STABLE security functions, and automated pgTAP validation, development teams can build bulletproof multi-tenant applications that scale securely.