Multi-Tenant PostgreSQL Row-Level Security on Supabase: Leak-Proof RLS and Fast Query Caching

In multi-tenant SaaS applications, cross-tenant data leakage is the nightmare scenario. If a user in Organization A accidentally views invoices, customer records, or API keys belonging to Organization B, customer trust evaporates immediately.

Traditional web frameworks often handle tenancy at the application layer by appending WHERE organization_id = ? to every SQL query. But application-layer isolation relies on developer discipline: one junior engineer forgetting a WHERE clause on a new endpoint can expose entire tables to unauthorized users.

PostgreSQL Row-Level Security (RLS) moves tenancy isolation directly into the database engine. On Supabase, every query executed through client SDKs or GraphQL APIs is evaluated against cryptographic row policies.

However, naive RLS policies introduce severe performance bottlenecks: querying parent membership tables on every single row evaluation turns simple SELECT queries into slow sequential scans.

A few weeks ago, Amir and I audited a B2B SaaS platform on Supabase where queries were taking over two seconds due to un-indexed, repeated RLS subqueries. Here is the exact architecture we deployed to lock down tenancy while keeping response times under 15 milliseconds.

The Three Most Dangerous RLS Mistakes

Before writing policies, here are the three mistakes that compromise security or kill database speed:

• Evaluating auth.uid() row by row: Calling auth.uid() directly inside a policy causes PostgreSQL to re-evaluate the authentication context for every row returned. Wrapping it in (SELECT auth.uid()) caches the value for the entire query statement.
• Missing table owner security: Enabling RLS with ALTER TABLE x ENABLE ROW LEVEL SECURITY; protects against standard users, but table owners still bypass RLS unless you add ALTER TABLE x FORCE ROW LEVEL SECURITY;.
• Volatile helper functions: Writing membership lookup functions without marking them STABLE forces PostgreSQL to re-query organization memberships on every evaluated row.

Schema Hierarchy and Tenant Membership Modeling

A solid multi-tenant model requires a clean separation between users, organizations, and organization-owned resources:

-- Core Organization Table
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL
);

-- Organization Memberships (Join Table)
CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  UNIQUE(organization_id, user_id)
);

-- Tenant-Owned Resource Table
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL
);

-- Index foreign keys and auth lookups (RLS checks hit these on every query)
CREATE INDEX idx_org_members_user_org ON organization_members(user_id, organization_id);
CREATE INDEX idx_documents_org_id ON documents(organization_id);

High-Speed SECURITY DEFINER STABLE Helper Functions

Instead of embedding raw JOIN subqueries inside every table policy, encapsulate membership checks in a dedicated function:

CREATE OR REPLACE FUNCTION get_user_organization_ids()
RETURNS SETOF UUID
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = ''
AS $$
  SELECT organization_id
  FROM public.organization_members
  WHERE user_id = (SELECT auth.uid());
$$;

Why these specific keywords matter:

• SECURITY DEFINER: The function runs with the privileges of its creator, bypassing recursive RLS checks on the organization_members table itself.
• STABLE: Informs the PostgreSQL optimizer that the function output will not change within a single transaction, allowing the planner to cache the organization ID list across millions of evaluated rows.
• SET search_path = '': Protects against search path hijacking vulnerabilities by ensuring all table references resolve to explicit schemas.

Writing Leak-Proof RLS Policies

Now, apply policies to the tenant resource table using our cached helper function:

-- Step 1: Enable and FORCE RLS on the table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

-- Step 2: Read policy (Users can only see documents from their organizations)
CREATE POLICY "Users can view documents in their organization"
ON documents
FOR SELECT
TO authenticated
USING (
  organization_id IN (SELECT get_user_organization_ids())
);

-- Step 3: Insert policy (Users can only create documents in organizations they belong to)
CREATE POLICY "Users can insert documents into their organization"
ON documents
FOR INSERT
TO authenticated
WITH CHECK (
  organization_id IN (SELECT get_user_organization_ids())
);

-- Step 4: Delete policy (Only admins and owners can delete documents)
CREATE POLICY "Admins can delete documents"
ON documents
FOR DELETE
TO authenticated
USING (
  organization_id IN (
    SELECT organization_id 
    FROM organization_members 
    WHERE user_id = (SELECT auth.uid()) 
      AND role IN ('owner', 'admin')
  )
);

Verification and Cross-Tenant Penetration Testing

Never mark an RLS deployment complete without executing negative security tests:

-- Verification Query: Run as User A
SET LOCAL ROLE authenticated;
SET LOCAL "request.jwt.claims" = '{"sub": "user-a-uuid-here"}';

-- Should return rows only for User A's organizations
SELECT id, organization_id, title FROM documents;

-- Attempt unauthorized insertion into Organization B (must fail with policy violation)
INSERT INTO documents (organization_id, title)
VALUES ('org-b-uuid-here', 'Unauthorized Document');

If the unauthorized insert returns an error, your tenant boundary is verified at the database level.