REF / WRITING · SOFTWARE

Supabase RLS Patterns for Multi-Tenant SaaS: The Complete Playbook

Advanced Supabase RLS patterns for multi-tenant SaaS - org isolation, role hierarchies, shared data, performance optimisation, and testing.

DomainSoftware
Formattutorial
Published6 Aug 2024
Tagssupabase · postgres · rls

Row Level Security (RLS) is Postgres's mechanism for enforcing data access rules at the database level. In Supabase, it's the primary security boundary between your application and your data. When implemented correctly, RLS makes it structurally impossible for one tenant to read another's data. even if your application code has a bug.

This is a deep dive for teams building multi-tenant SaaS on Supabase. We cover organisation-based isolation, role hierarchies, shared/public data, cross-tenant permissions, testing RLS policies, and performance optimisation.

Why RLS for Multi-Tenant SaaS?

Multi-tenant isolation is one of the most critical security requirements in SaaS. A data leak. where tenant A can read tenant B's data. is a catastrophic incident. Traditional approaches implement tenant isolation in the application layer: every query includes a WHERE organization_id = $currentOrg clause. This works but has a fatal flaw: a single missed filter in one query exposes data.

RLS moves the isolation guarantee to the database. Policies apply to every query, regardless of how the query was constructed. Missing a filter in your application code doesn't create a vulnerability. the database enforces it.

Setting Up the Multi-Tenant Schema

Every multi-tenant Supabase application needs this foundation:

-- Organizations (tenants)
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Membership: links users to orgs with a role
CREATE TYPE org_role AS ENUM ('owner', 'admin', 'member', 'viewer');

CREATE TABLE org_memberships (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role org_role NOT NULL DEFAULT 'member',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(org_id, user_id)
);

CREATE INDEX ON org_memberships(user_id);
CREATE INDEX ON org_memberships(org_id);

-- Your tenant-scoped data table
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  created_by UUID NOT NULL REFERENCES auth.users(id),
  name TEXT NOT NULL,
  data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX ON projects(org_id);

The Core Multi-Tenant Policy Pattern

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE org_memberships ENABLE ROW LEVEL SECURITY;
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;

-- Helper function: get the current user's orgs and roles
-- Defined once, used by all policies
CREATE OR REPLACE FUNCTION get_my_org_ids()
RETURNS SETOF UUID
LANGUAGE sql
SECURITY DEFINER
STABLE  -- Important for query planner optimization
AS $$
  SELECT org_id FROM org_memberships WHERE user_id = auth.uid()
$$;

-- Projects: members of the org can read
CREATE POLICY "Org members can read projects"
  ON projects FOR SELECT
  USING (org_id IN (SELECT get_my_org_ids()));

-- Projects: members with write access can insert
CREATE POLICY "Org members can create projects"
  ON projects FOR INSERT
  WITH CHECK (
    org_id IN (SELECT get_my_org_ids())
    AND created_by = auth.uid()
  );

-- Projects: admins/owners can update
CREATE POLICY "Org admins can update projects"
  ON projects FOR UPDATE
  USING (
    org_id IN (
      SELECT org_id FROM org_memberships
      WHERE user_id = auth.uid()
      AND role IN ('owner', 'admin')
    )
  );

-- Projects: only owners can delete
CREATE POLICY "Org owners can delete projects"
  ON projects FOR DELETE
  USING (
    org_id IN (
      SELECT org_id FROM org_memberships
      WHERE user_id = auth.uid()
      AND role = 'owner'
    )
  );

Role Hierarchy with Helper Functions

For more complex role logic, extract role-checking into reusable SQL functions:

-- Returns the current user's role in a specific org
CREATE OR REPLACE FUNCTION get_my_role(p_org_id UUID)
RETURNS org_role
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT role
  FROM org_memberships
  WHERE user_id = auth.uid()
  AND org_id = p_org_id
  LIMIT 1
$$;

-- Returns true if user has at least the specified role level
CREATE OR REPLACE FUNCTION has_org_role(p_org_id UUID, required_role org_role)
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM org_memberships
    WHERE user_id = auth.uid()
    AND org_id = p_org_id
    AND CASE required_role
      WHEN 'viewer' THEN role IN ('viewer', 'member', 'admin', 'owner')
      WHEN 'member' THEN role IN ('member', 'admin', 'owner')
      WHEN 'admin' THEN role IN ('admin', 'owner')
      WHEN 'owner' THEN role = 'owner'
    END
  )
$$;

-- Use in policies:
CREATE POLICY "Members can read, admins can write"
  ON projects FOR ALL
  USING (has_org_role(org_id, 'viewer'))
  WITH CHECK (has_org_role(org_id, 'member'));

Handling Public and Private Data

Many SaaS apps mix org-private and publicly shareable data:

-- Add a visibility field
ALTER TABLE projects ADD COLUMN visibility TEXT DEFAULT 'private' 
  CHECK (visibility IN ('private', 'org', 'public'));

-- Drop existing policy and recreate with visibility logic
DROP POLICY IF EXISTS "Org members can read projects" ON projects;

CREATE POLICY "Read projects by visibility"
  ON projects FOR SELECT
  USING (
    visibility = 'public'  -- Anyone can read public
    OR (visibility = 'org' AND org_id IN (SELECT get_my_org_ids()))  -- Org members
    OR (visibility = 'private' AND org_id IN (SELECT get_my_org_ids()))  -- Org members only
  );

Sharing Resources Across Orgs

Some scenarios require sharing specific resources between organisations. shared templates, marketplace items, etc.:

-- Sharing table: explicit cross-org grants
CREATE TABLE project_shares (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  target_org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  permission TEXT DEFAULT 'read' CHECK (permission IN ('read', 'edit')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(project_id, target_org_id)
);

ALTER TABLE project_shares ENABLE ROW LEVEL SECURITY;

-- Updated read policy to include shared projects
CREATE POLICY "Read own org projects plus shared"
  ON projects FOR SELECT
  USING (
    org_id IN (SELECT get_my_org_ids())  -- Own org
    OR id IN (                            -- Explicitly shared
      SELECT project_id FROM project_shares
      WHERE target_org_id IN (SELECT get_my_org_ids())
    )
  );

Personal Resources Within an Org

User-private resources (drafts, personal notes) within a multi-tenant context:

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

ALTER TABLE user_notes ENABLE ROW LEVEL SECURITY;

-- Notes are strictly personal: only the owner can access them
CREATE POLICY "Users can only access own notes"
  ON user_notes FOR ALL
  USING (user_id = auth.uid())
  WITH CHECK (
    user_id = auth.uid()
    AND org_id IN (SELECT get_my_org_ids())  -- Must still be org member
  );

RLS Performance Optimisation

Poorly designed RLS policies can make queries slow. The key is ensuring your RLS subqueries use indexes.

Problem: Full table scan on each row

-- BAD: Evaluates this subquery for every row in the result
USING (
  org_id IN (
    SELECT org_id FROM org_memberships WHERE user_id = auth.uid()
  )
)

Fix 1: Use a STABLE function with SECURITY DEFINER

The STABLE marker tells Postgres the function returns the same result within a transaction. The planner can call it once and cache the result:

CREATE OR REPLACE FUNCTION get_my_org_ids()
RETURNS SETOF UUID
LANGUAGE sql
SECURITY DEFINER
STABLE  -- <-- crucial
AS $$
  SELECT org_id FROM org_memberships WHERE user_id = auth.uid()
$$;

Fix 2: Use EXISTS instead of IN for large result sets

-- Better for large tables
USING (
  EXISTS (
    SELECT 1 FROM org_memberships
    WHERE org_id = projects.org_id
    AND user_id = auth.uid()
  )
)

Fix 3: Ensure index coverage

-- This index is essential for the policy lookup
CREATE INDEX ON org_memberships(user_id, org_id);

-- For role-based policies
CREATE INDEX ON org_memberships(user_id, org_id, role);

Measure with EXPLAIN

Always measure RLS performance with EXPLAIN ANALYZE:

SET role = 'authenticated';
SET request.jwt.claims = '{"sub": "user-uuid-here"}';

EXPLAIN ANALYZE
SELECT * FROM projects WHERE org_id = 'org-uuid-here';

Look for sequential scans on org_memberships: if you see one, add or adjust indexes.

Testing RLS Policies

Testing RLS requires simulating different users. Use the Supabase SQL editor or psql:

-- Test as user A
SET request.jwt.claims = '{"sub": "user-a-uuid"}';
SET role = 'authenticated';
SELECT * FROM projects;  -- Should return user A's org projects only

-- Test as user B  
SET request.jwt.claims = '{"sub": "user-b-uuid"}';
SELECT * FROM projects;  -- Should return user B's org projects only

-- Test unauthenticated
SET role = 'anon';
SELECT * FROM projects;  -- Should return only public projects

For automated testing, use pgTAP or test via your application's test suite with different user JWT tokens.

The Service Role Bypass Pattern

Sometimes your backend needs to bypass RLS (admin operations, batch jobs, data migrations). Use the service_role key with care:

// Server-side only - NEVER in browser code
import { createClient } from "@supabase/supabase-js";

const adminSupabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,  // Bypasses RLS
);

// Admin operation: read all orgs
const { data: allOrgs } = await adminSupabase.from("organizations").select();

Restrict service role key usage to:

  • Server-side API routes / Edge Functions
  • Background jobs
  • Administrative tooling

Never expose it to client-side code or environment variables prefixed with NEXT_PUBLIC_.

Common RLS Mistakes

1. Forgetting WITH CHECK: USING filters reads; WITH CHECK enforces rules on writes. A missing WITH CHECK allows users to insert/update data violating your policy.

2. Infinite recursion: if policy A queries table B and policy B queries table A, you get infinite recursion. Use SECURITY DEFINER functions that bypass RLS when accessed through the function.

3. SECURITY DEFINER without schema restriction: a SECURITY DEFINER function runs with the privileges of the function owner. Make sure it only accesses what it should.

4. Not testing the anon role: always test that unauthenticated users (the anon role) cannot access private data. Use Supabase's API directly without a JWT.

5. Bypassing RLS with foreign key joins: RLS applies to the table being queried, not to tables fetched via foreign keys in nested selects. Test cross-table access explicitly.

FAQ

Q: Does RLS apply to database functions? Functions marked SECURITY DEFINER bypass RLS and run as the function owner. Functions marked SECURITY INVOKER (default) respect RLS. Be explicit about which your functions need.

Q: Can I use RLS with Prisma or Drizzle? Yes, but you need to set the JWT claims on each connection. This typically means using a connection string that goes through Supabase's pgBouncer with raw_jwt_token passed per-query, or using the Supabase REST API.

Q: What about performance at scale? With proper indexes and STABLE functions, RLS overhead is typically under 5ms per query. Test with realistic data volumes.

Q: Can RLS replace application-layer authorization? RLS handles data access isolation well. It's not a full authorization system. it can't enforce complex business rules like "users can only create 5 projects on the free plan." Use RLS for data isolation and application logic for business rules.

Q: How do I handle RLS for file storage? Supabase Storage uses the same RLS syntax. Apply policies to storage.objects table the same way you apply them to your data tables.

Conclusion

RLS is the most powerful security feature in Supabase. and in Postgres. Implemented correctly, it makes multi-tenant data isolation structural rather than incidental: you can't accidentally leak data with a missing WHERE clause.

The patterns in this guide. helper functions, role hierarchies, visibility flags, cross-org sharing, and STABLE performance optimisation. are the foundation of production multi-tenant SaaS on Supabase.

See also: Supabase: The Complete Developer Guide. the full Supabase overview this article builds on.