Supabase RLS for Multi-Tenant Apps: What AI Gets Wrong

How to Set Up Supabase RLS for Multi-Tenant SaaS

Setting up Supabase Row Level Security (RLS) for multi-tenant applications requires three things: enabling RLS on every table containing user data, creating explicit policies that enforce tenant isolation using auth.uid(), and ensuring your service-role key never touches client code. The anon key handles client requests; service-role stays server-side only.

This sounds straightforward. In practice, AI-generated Supabase code gets it wrong more often than it gets it right.

The Three Mistakes AI Tools Make Repeatedly

When we review AI-generated Supabase code at Fairy, three patterns account for most critical vulnerabilities: disabled RLS, service-role key exposure, and policies without ownership checks. These aren't edge cases—they're the default output of most AI coding assistants when asked to build multi-tenant features quickly.

Mistake 1: RLS Not Enabled at All

AI tools optimize for working code. When you ask for a multi-tenant data model, you'll often get perfectly functional table definitions with no RLS whatsoever:

-- AI-generated: works, but every row is public
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id),
  title TEXT NOT NULL
);

This creates a complete IDOR (Insecure Direct Object Reference) vulnerability. Any authenticated user can query any organization's projects by guessing or enumerating IDs. The fix requires explicit RLS enablement:

ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

But enabling RLS without policies creates a different problem: Supabase defaults to deny-all. Your application breaks entirely until you add explicit policies.

Mistake 2: Service-Role Key in Client Code

This is the most dangerous pattern we encounter. AI assistants, when troubleshooting "permission denied" errors, often suggest switching to the service-role client:

// DANGEROUS: AI-suggested "fix" for RLS issues
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_SERVICE_ROLE_KEY! // Full DB access
)

The SERVICE_ROLE_KEY bypasses all RLS policies. When prefixed with NEXT_PUBLIC_, it ships to every browser. Anyone viewing your site can extract the key and gain complete read/write access to your entire database.

This isn't hypothetical. We've reviewed production applications where AI-generated code exposed service-role keys through client bundles. The correct pattern:

// Client-side: anon key only
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY! // RLS enforced
)

// Server-side only (API routes, server actions)
const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // Never NEXT_PUBLIC_
)

Mistake 3: Policies Without Ownership Checks

Even when AI generates RLS policies, they often miss the actual tenant isolation logic:

-- AI-generated: allows any authenticated user
CREATE POLICY "Users can view projects" ON projects
  FOR SELECT USING (auth.role() = 'authenticated');

This policy checks that someone is logged in, not that they should access this specific row. A proper multi-tenant policy must verify ownership:

-- Correct: verifies tenant membership
CREATE POLICY "Users can view their organization's projects" ON projects
  FOR SELECT USING (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

The Correct Multi-Tenant RLS Architecture

A production-ready multi-tenant Supabase setup requires a deliberate schema and policy structure. Here's the pattern that actually works:

Step 1: Design Your Tenant Hierarchy

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

-- Membership mapping
CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member',
  UNIQUE(organization_id, user_id)
);

-- Tenant-scoped data
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Step 2: Enable RLS on All Tables

ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

Step 3: Create Comprehensive Policies

Each table needs policies for every operation type. The membership table is particularly important—it controls who can see which organizations:

-- Organization members: users see their own memberships
CREATE POLICY "Users view own memberships" ON organization_members
  FOR SELECT USING (user_id = auth.uid());

-- Organizations: users see orgs they belong to
CREATE POLICY "Users view their organizations" ON organizations
  FOR SELECT USING (
    id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

-- Projects: full CRUD scoped to organization membership
CREATE POLICY "View projects in my orgs" ON projects
  FOR SELECT USING (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "Insert projects in my orgs" ON projects
  FOR INSERT WITH CHECK (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "Update projects in my orgs" ON projects
  FOR UPDATE USING (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "Delete projects in my orgs" ON projects
  FOR DELETE USING (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

Step 4: Optimize with Security Definer Functions

Subqueries in policies can create performance issues at scale. A security definer function runs with elevated privileges and caches the result:

CREATE OR REPLACE FUNCTION get_user_organization_ids()
RETURNS UUID[] AS $$
  SELECT ARRAY(
    SELECT organization_id FROM organization_members
    WHERE user_id = auth.uid()
  );
$$ LANGUAGE SQL SECURITY DEFINER STABLE;

-- Simplified policy using the function
CREATE POLICY "View projects in my orgs" ON projects
  FOR SELECT USING (
    organization_id = ANY(get_user_organization_ids())
  );

Why AI Tools Consistently Miss This

AI coding assistants are trained on vast amounts of public code, most of which doesn't implement proper security. When asked to "add multi-tenant support," they pattern-match to simpler implementations that technically work but lack isolation.

The feedback loop reinforces bad patterns: code without RLS runs faster during development (no permission errors), so developers accept AI suggestions that skip security for convenience. By the time the application reaches production, the insecure patterns are deeply embedded.

We see this across Fairy reviews: the AI generates functional code, the developer tests their own data, everything appears to work. The vulnerability only surfaces when a user realizes they can access other tenants' information—often discovered by customers rather than security testing.

Verifying Your RLS Implementation

Testing RLS requires simulating multiple users across different tenants:

// Test helper: verify cross-tenant isolation
async function testTenantIsolation() {
  // Create two test users in different organizations
  const user1 = await createTestUser('org-alpha')
  const user2 = await createTestUser('org-beta')
  
  // User 1 creates a project
  const { data: project } = await supabase
    .from('projects')
    .insert({ title: 'Secret Project', organization_id: 'org-alpha' })
    .select()
    .single()
  
  // User 2 attempts to read it
  const { data: leaked, error } = await supabaseAsUser2
    .from('projects')
    .select()
    .eq('id', project.id)
  
  // Should return empty, not the project
  assert(leaked.length === 0, 'Cross-tenant data leak detected')
}

Supabase's SQL editor includes a "Run as" feature that lets you execute queries as different auth contexts. Use this to verify policies behave correctly for various user types.

Beyond RLS: Defense in Depth

RLS is your primary isolation mechanism, but it shouldn't be your only one. Consider:

Application-level checks: Verify tenant access in your API routes before database calls. This catches bugs where RLS policies are misconfigured.

Audit logging: Track cross-tenant query attempts. Unexpected access patterns may indicate policy gaps or probing attacks.

Regular policy reviews: As your schema evolves, new tables may be added without RLS. Automated reviews can catch these gaps before deployment.

Service-role usage monitoring: If you must use service-role for admin operations, log every call and verify the calling context.

The Cost of Getting This Wrong

Multi-tenant data breaches aren't theoretical. When tenant A can access tenant B's data, you're not just dealing with a bug—you're potentially facing breach disclosure requirements, customer notification, and the trust damage that follows.

AI tools accelerate development, but they also accelerate the introduction of security gaps. The patterns they generate most confidently are often the most dangerous: simple, functional, and insecure.

Proper Supabase RLS for multi-tenant applications isn't complex once you understand the model. The challenge is catching the gaps AI tools leave behind—before your users do.

---

Building a multi-tenant application with AI-generated code? Fairy Scout provides free security reviews that catch RLS gaps, service-role exposure, and tenant isolation failures before they reach production.