Multi-tenant SaaS isolation with Postgres RLS and workspace auth

Application guards and database policies solve different failures

Developers often treat application-level authorization and Postgres Row-Level Security as mutually exclusive. In practice, they catch different bugs: server-side guards enforce role hierarchy, entitlement checks, and audit logging before business logic runs, while RLS guarantees that a missing WHERE workspace_id = ... clause cannot dump every tenant's rows.

Whether you run Supabase or self-hosted Postgres with Drizzle, every tenant-owned table should carry a non-null workspace_id foreign key. The workspace boundaries article explains why that scope must always be derived on the server rather than read from a request parameter.

• Application guards validate session freshness, member roles (owner, admin, member), and feature entitlements.
• Database RLS policies act as a hard floor when direct client queries or raw SQL helpers are used.
• Both layers depend on a single source of truth: the workspace_members join table.

Resolve live membership instead of trusting stale cookies

Storing a currentWorkspaceId in a browser cookie or client header is convenient for navigation, but a cookie is a preference, not a capability. If an admin removes a teammate from a workspace, that user's browser still holds the old workspace cookie.

Join the active session against workspace_members on every request. When the membership row is gone, reject the request immediately instead of waiting for a JWT or session cookie to expire. The authentication architecture post traces how identity flows into this check.

export async function requireWorkspaceViewer(requestedWorkspaceId?: string) {
  const session = await requireAuthenticatedSession();
  const membership = await db.query.workspaceMembers.findFirst({
    where: and(
      eq(workspaceMembers.userId, session.user.id),
      requestedWorkspaceId
        ? eq(workspaceMembers.workspaceId, requestedWorkspaceId)
        : undefined,
    ),
    with: { workspace: true },
  });

  if (!membership) throw new ForbiddenError("Not a member of this workspace");
  return { user: session.user, workspace: membership.workspace, role: membership.role };
}

Write fast, indexed Row-Level Security policies

Naive RLS policies that call auth.uid() directly on every row force Postgres to re-evaluate the function per tuple. Wrapping (select auth.uid()) in a scalar subquery lets the query planner cache the value as an initPlan, and a composite index on (workspace_id, user_id) keeps membership lookups in sub-millisecond index scans.

You can generate ready-to-run SELECT, INSERT, UPDATE, and DELETE policies for multi-tenant tables using the free Supabase RLS Policy Generator without writing boilerplate SQL by hand.

CREATE INDEX IF NOT EXISTS idx_workspace_members_lookup
  ON workspace_members (workspace_id, user_id);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "workspace_members_manage_documents"
ON documents FOR ALL TO authenticated
USING (
  workspace_id IN (
    SELECT wm.workspace_id FROM workspace_members wm
    WHERE wm.user_id = (SELECT auth.uid())
  )
)
WITH CHECK (
  workspace_id IN (
    SELECT wm.workspace_id FROM workspace_members wm
    WHERE wm.user_id = (SELECT auth.uid())
      AND wm.role IN ('owner', 'admin', 'member')
  )
);

• Supabase RLS Policy Generator (Free Tool)supastack.dev

Common questions about RLS and workspace auth

Teams wiring multi-tenant Postgres hit the same edge cases around service roles, background jobs, and performance.

• Does Drizzle ORM or Prisma bypass RLS? — Yes, if your server connects with the postgres superuser or Supabase service_role key. That is why server actions and API routes must call requireWorkspaceViewer() and scope every query by workspaceId.
• Why wrap (select auth.uid()) in parentheses inside RLS policies? — It tells the Postgres optimizer to evaluate auth.uid() once per statement instead of once per scanned row, avoiding sequential-scan slowdowns on large tables.
• How should background jobs access tenant data? — Pass the verified workspaceId in the job payload from the authenticated server route, and scope worker reads and writes to that workspaceId.
• How do I verify that tenant isolation actually holds? — Write automated negative tests where User A requests or mutates User B's record ID across web, mobile, and API routes, and assert 403 / 404 with zero rows changed.