# Saas Multi Tenant

> Row-Level Security (RLS) and multi-tenant database isolation strategies for SaaS applications. Use whenever the user is designing or implementing multi-tenancy, tenant isolation, RLS policies, or workspace-based access control. Trigger on phrases like "multi-tenant", "RLS", "row-level security", "tenant isolation", "workspace isolation", "organization data", or when the user needs to ensure one tenant cannot access another's data in PostgreSQL or Supabase.

- Skill: `roedyrustam/saas-multi-tenant-2` (Agent Skill)
- Install (CLI): `npx skillmds@latest add roedyrustam/saas-multi-tenant-2`
- Raw SKILL.md: https://api.skillmd.com/api/skills/roedyrustam/saas-multi-tenant-2/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: roedyrustam (https://skillmd.com/u/roedyrustam)
- Updated: 2026-09-21
- Page: https://skillmd.com/skills/roedyrustam/saas-multi-tenant-2

---


# 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

```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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)
```sql
-- 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:

```typescript
// 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()
  })
}
```

```sql
-- 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
$$;
```

```typescript
// 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:

```typescript
// 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

```typescript
// 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