Saas multi tenant

SaaS Multi-Tenant — RLS & Isolation Strategies

Robust tenant isolation patterns for production SaaS on PostgreSQL.

---

Isolation Strategy Comparison

Strategy	Isolation	Complexity	Cost
Separate databases	Strongest	High	High
Separate schemas	Strong	Medium	Medium
Shared schema + RLS	Good	Low	Low ✅

Recommendation: Shared schema + RLS for most SaaS MVPs. Upgrade to separate schemas when a customer requires it (enterprise tier).

---

Schema Design for Multi-Tenancy

-- Every tenant-scoped table has org_id / workspace_id
CREATE TABLE workspaces (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id    UUID NOT NULL REFERENCES users(id),
  name        TEXT NOT NULL,
  slug        TEXT NOT NULL UNIQUE,
  plan        TEXT NOT NULL DEFAULT 'free',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE projects (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id  UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE workspace_members (
  workspace_id  UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role          TEXT NOT NULL DEFAULT 'member', -- owner | admin | member | viewer
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (workspace_id, user_id)
);

-- Indexes for RLS performance — critical!
CREATE INDEX idx_projects_workspace_id ON projects(workspace_id);
CREATE INDEX idx_workspace_members_user_id ON workspace_members(user_id);
CREATE INDEX idx_workspace_members_workspace_id ON workspace_members(workspace_id);

---

Row-Level Security (RLS) Policies

Enable RLS

-- Enable on all tenant-scoped tables
ALTER TABLE workspaces ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE workspace_members ENABLE ROW LEVEL SECURITY;

-- IMPORTANT: Also set FORCE so table owners can't bypass
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Workspace Policies

-- Users can only see workspaces they belong to
CREATE POLICY "workspaces_select" ON workspaces
  FOR SELECT USING (
    id IN (
      SELECT workspace_id FROM workspace_members
      WHERE user_id = auth.uid()
    )
  );

-- Only workspace owners can update
CREATE POLICY "workspaces_update" ON workspaces
  FOR UPDATE USING (owner_id = auth.uid());

-- Only workspace owners can delete
CREATE POLICY "workspaces_delete" ON workspaces
  FOR DELETE USING (owner_id = auth.uid());

Project Policies

-- Members can read projects in their workspaces
CREATE POLICY "projects_select" ON projects
  FOR SELECT USING (
    workspace_id IN (
      SELECT workspace_id FROM workspace_members
      WHERE user_id = auth.uid()
    )
  );

-- Members and admins can insert
CREATE POLICY "projects_insert" ON projects
  FOR INSERT WITH CHECK (
    workspace_id IN (
      SELECT workspace_id FROM workspace_members
      WHERE user_id = auth.uid()
        AND role IN ('owner', 'admin', 'member')
    )
  );

-- Only admins and owners can update
CREATE POLICY "projects_update" ON projects
  FOR UPDATE USING (
    workspace_id IN (
      SELECT workspace_id FROM workspace_members
      WHERE user_id = auth.uid()
        AND role IN ('owner', 'admin')
    )
  );

-- Only owners can delete
CREATE POLICY "projects_delete" ON projects
  FOR DELETE USING (
    workspace_id IN (
      SELECT workspace_id FROM workspace_members
      WHERE user_id = auth.uid()
        AND role = 'owner'
    )
  );

Helper Function (DRY)

-- Reusable function to check workspace membership
CREATE OR REPLACE FUNCTION is_workspace_member(ws_id UUID, required_role TEXT DEFAULT NULL)
RETURNS BOOLEAN
LANGUAGE sql STABLE SECURITY DEFINER AS $$
  SELECT EXISTS (
    SELECT 1 FROM workspace_members
    WHERE workspace_id = ws_id
      AND user_id = auth.uid()
      AND (required_role IS NULL OR role = ANY(
        CASE required_role
          WHEN 'viewer' THEN ARRAY['viewer','member','admin','owner']
          WHEN 'member' THEN ARRAY['member','admin','owner']
          WHEN 'admin'  THEN ARRAY['admin','owner']
          WHEN 'owner'  THEN ARRAY['owner']
          ELSE ARRAY[required_role]
        END
      ))
  );
$$;

-- Simplified policies using the helper
CREATE POLICY "projects_select" ON projects
  FOR SELECT USING (is_workspace_member(workspace_id, 'viewer'));

CREATE POLICY "projects_insert" ON projects
  FOR INSERT WITH CHECK (is_workspace_member(workspace_id, 'member'));

CREATE POLICY "projects_update" ON projects
  FOR UPDATE USING (is_workspace_member(workspace_id, 'admin'));

CREATE POLICY "projects_delete" ON projects
  FOR DELETE USING (is_workspace_member(workspace_id, 'owner'));

---

Setting auth.uid() from Application Layer

When NOT using Supabase (using raw PostgreSQL + your own auth), you need to set the user context yourself:

// lib/db/tenant.ts — Drizzle + PostgreSQL
import { db } from "./index"
import { sql } from "drizzle-orm"

export async function withTenantContext<T>(
  userId: string,
  fn: () => Promise<T>
): Promise<T> {
  return db.transaction(async (tx) => {
    // Set the user context for RLS
    await tx.execute(sql`SET LOCAL app.current_user_id = ${userId}`)
    return fn()
  })
}

-- PostgreSQL: create auth.uid() using app setting
CREATE OR REPLACE FUNCTION auth.uid() RETURNS UUID
LANGUAGE sql STABLE AS $$
  SELECT NULLIF(current_setting('app.current_user_id', TRUE), '')::UUID
$$;

// Usage in Server Actions
export async function getProjects(workspaceId: string) {
  const { userId } = await auth()
  if (!userId) throw new Error("Unauthorized")

  const user = await getUserByClerkId(userId)

  return withTenantContext(user.id, () =>
    db.select().from(projects).where(eq(projects.workspaceId, workspaceId))
    // RLS automatically filters — user can only see their workspaces' projects
  )
}

---

Application-Level Tenant Guard (Defense in Depth)

Even with RLS, add an app-level check as a second layer:

// lib/auth/tenant.ts
import { auth } from "@clerk/nextjs/server"
import { db } from "@/lib/db"
import { workspaceMembers } from "@/lib/db/schema"
import { and, eq } from "drizzle-orm"

export async function requireWorkspaceAccess(
  workspaceId: string,
  requiredRole: "viewer" | "member" | "admin" | "owner" = "viewer"
) {
  const { userId: clerkId } = await auth()
  if (!clerkId) throw new Error("Unauthorized")

  const user = await getUserByClerkId(clerkId)

  const ROLE_HIERARCHY = { viewer: 0, member: 1, admin: 2, owner: 3 }

  const [membership] = await db
    .select()
    .from(workspaceMembers)
    .where(
      and(
        eq(workspaceMembers.workspaceId, workspaceId),
        eq(workspaceMembers.userId, user.id)
      )
    )
    .limit(1)

  if (!membership) throw new Error("Forbidden: not a workspace member")

  if (ROLE_HIERARCHY[membership.role as keyof typeof ROLE_HIERARCHY] < ROLE_HIERARCHY[requiredRole]) {
    throw new Error(`Forbidden: requires ${requiredRole} role`)
  }

  return { user, membership }
}

// Usage in Server Actions
export async function deleteProject(projectId: string, workspaceId: string) {
  await requireWorkspaceAccess(workspaceId, "admin")
  await db.delete(projects).where(eq(projects.id, projectId))
  revalidatePath(`/workspaces/${workspaceId}`)
}

---

Data Export / Tenant Offboarding

// Export all tenant data (GDPR compliance)
export async function exportTenantData(workspaceId: string) {
  await requireWorkspaceAccess(workspaceId, "owner")

  const [workspace, members, projectsList] = await Promise.all([
    db.select().from(workspaces).where(eq(workspaces.id, workspaceId)),
    db.select().from(workspaceMembers).where(eq(workspaceMembers.workspaceId, workspaceId)),
    db.select().from(projects).where(eq(projects.workspaceId, workspaceId)),
  ])

  return { workspace, members, projects: projectsList, exportedAt: new Date() }
}

// Cascade delete (ON DELETE CASCADE handles DB, but clean up external resources)
export async function deleteWorkspace(workspaceId: string) {
  await requireWorkspaceAccess(workspaceId, "owner")

  // 1. Cancel Stripe subscription
  await cancelStripeSubscription(workspaceId)

  // 2. Delete files from S3/Blob
  await deleteWorkspaceFiles(workspaceId)

  // 3. Delete from DB (cascades to all child tables)
  await db.delete(workspaces).where(eq(workspaces.id, workspaceId))
}

---

Key Rules

1. Every tenant-scoped table needs workspace_id — no exceptions
2. Enable AND FORCE RLS — FORCE prevents owner bypass
3. Always index workspace_id — RLS policies scan this column on every query
4. App-level guard + RLS — defense in depth; don't rely on RLS alone
5. SECURITY DEFINER for helper functions — so they run as the function owner, not the calling user
6. Test RLS with a non-owner session — SET ROLE in psql to verify isolation
7. Cascade deletes in schema — ON DELETE CASCADE on all workspace_id foreign keys
8. Role hierarchy in app code — viewer < member < admin < owner
9. Log cross-tenant attempts — suspicious if app guard fires (means RLS would have caught it)
10. GDPR: implement data export + deletion before launch if serving EU users