Row-Level Security in PostgreSQL: Enforcing Tenant Isolation at the Database Layer

Every multi-tenant SaaS has the same risk buried somewhere in its codebase: a query that's missing its WHERE client_id = actor.clientId filter. Not because the developer was careless — because the application layer is a leaky abstraction. Someone writes a utility function that queries agents, it works fine in their context because they always call it from a place that has the clientId, and then six months later someone calls it from a background job that doesn't set the filter.

No error. No exception. Just a query that returns all agents across all tenants to a caller that should only see their own.

PostgreSQL row-level security eliminates this failure mode at the database layer. The policy evaluation happens inside the database engine, regardless of what the application sends. There is no "forgot the WHERE clause" — the clause is enforced unconditionally.

This post is about how WFW implements RLS, the Drizzle ORM integration, and the one meaningful tradeoff: performance.

The Mechanism

RLS in PostgreSQL works through policies attached to tables. A policy defines a USING expression that gets evaluated for every row read from the table. If the expression returns false, the row is invisible to the query.

-- Enable RLS on the table
ALTER TABLE ww_agents ENABLE ROW LEVEL SECURITY;

-- Define the isolation policy
-- current_setting() reads a session-level variable we set at request start
CREATE POLICY tenant_isolation ON ww_agents
  USING (
    client_id::text = current_setting('app.current_client_id')
  );

Once this policy exists, every SELECT, UPDATE, and DELETE on ww_agents is automatically filtered to rows where client_id matches app.current_client_id. If a query somehow runs without setting this variable, PostgreSQL throws an error (because current_setting() with no default raises an exception when the setting doesn't exist).

We set the session variable at the start of every API request, before any query runs:

SET LOCAL app.current_client_id = '123';

SET LOCAL is important — it scopes the setting to the current transaction. If we use SET (without LOCAL), the setting persists for the duration of the database connection, which in a connection pool means it can leak to subsequent requests that reuse the same connection. SET LOCAL resets when the transaction ends.

The Drizzle ORM Integration

We use Drizzle ORM throughout the WFW codebase. Setting the session variable before queries in a Next.js API route context requires a small wrapper:

// lib/db/tenant-context.ts

import { db } from "./client";
import { sql } from "drizzle-orm";
import type { ActorContext } from "@/lib/auth/context";

/**
 * Execute a database operation with tenant isolation enforced via RLS.
 * Sets app.current_client_id as a transaction-scoped session variable
 * before running the callback, then resets it.
 *
 * All DB queries that access tenant-scoped tables MUST go through this function.
 * Queries run outside this wrapper will fail if RLS is enabled and the
 * session variable isn't set — which is the correct failure mode.
 */
export async function withTenantContext<T>(
  actor: ActorContext,
  fn: (tx: typeof db) => Promise<T>
): Promise<T> {
  return db.transaction(async (tx) => {
    // SET LOCAL scopes this to the current transaction.
    // The variable resets when the transaction commits or rolls back.
    await tx.execute(
      sql`SET LOCAL app.current_client_id = ${actor.clientId}`
    );

    // Run the provided function with the tenant-scoped DB connection
    return fn(tx);
  });
}

Usage in a route handler:

// app/api/v2/agents/route.ts

export async function GET(req: NextRequest) {
  const actor = await withBotAuthCtx(req);

  const agents = await withTenantContext(actor, async (tx) => {
    // No WHERE clause needed — RLS filters to actor.clientId automatically
    return tx.select().from(agentsTable);
  });

  return NextResponse.json({
    data: agents,
    meta: buildMeta(req),
  });
}

The withTenantContext wrapper is the entire enforcement mechanism from the application side. Set the variable, run the queries, the policy handles isolation. The agents query above returns only the rows belonging to actor.clientId — no explicit filter written by the developer.

Full Policy Definitions

For completeness, here are the policies across all tenant-scoped tables:

-- Apply to all tables that contain per-client data
ALTER TABLE ww_agents        ENABLE ROW LEVEL SECURITY;
ALTER TABLE ww_kb_documents  ENABLE ROW LEVEL SECURITY;
ALTER TABLE ww_call_logs     ENABLE ROW LEVEL SECURITY;
ALTER TABLE ww_webhooks      ENABLE ROW LEVEL SECURITY;
ALTER TABLE ww_api_keys      ENABLE ROW LEVEL SECURITY;

-- Standard tenant isolation policy
-- Applies to SELECT, INSERT, UPDATE, DELETE
CREATE POLICY tenant_isolation ON ww_agents
  AS PERMISSIVE
  FOR ALL
  USING (
    client_id::text = current_setting('app.current_client_id', true)
    OR current_setting('app.current_client_id', true) = '0'
  )
  WITH CHECK (
    client_id::text = current_setting('app.current_client_id', true)
    OR current_setting('app.current_client_id', true) = '0'
  );

-- Repeat for each table...

Two things to note about the USING expression:

current_setting('app.current_client_id', true) — The second argument true means "return null if the setting doesn't exist" instead of throwing an error. We handle this at the application level (the wrapper always sets it) but the true prevents cryptic errors during migrations and admin queries.

OR current_setting('app.current_client_id', true) = '0' — This is the platform bot exemption. WFW platform bots (our internal automation, compliance auditing) run with clientId = '0' in their actor context. When app.current_client_id = '0', the RLS policy allows access to all rows. This lets our platform services query across tenants for aggregate operations like benchmarks and cross-account compliance checks.

The INSERT Policy (WITH CHECK)

The WITH CHECK clause applies to INSERT and UPDATE. Without it, RLS would filter what you read but not what you write — you could insert a row with a different client_id than the current session.

-- WITH CHECK ensures inserted rows must satisfy the policy too
WITH CHECK (
  client_id::text = current_setting('app.current_client_id', true)
  OR current_setting('app.current_client_id', true) = '0'
)

This means an application bug that tries to write client_id = 'client_b' while authenticated as client_a will fail at the database layer, not produce a silent incorrect write.

The 404-Not-403 Principle

When RLS hides a row from a query — because the row belongs to a different tenant — the query returns zero rows. Not a permission error. Zero rows.

The application layer should return 404 Not Found in this case, not 403 Forbidden. Here's why: 403 reveals that the resource exists. 404 doesn't. This prevents tenant enumeration — a bot that makes requests across agt_000001 through agt_999999 gets 404 for resources it can't access, which is the same response it would get for resources that don't exist at all.

// In a GET /v2/agents/:id handler
const agent = await withTenantContext(actor, async (tx) => {
  return tx.query.agents.findFirst({
    where: eq(agents.id, params.id),
    // No client_id filter needed — RLS handles it
  });
});

if (!agent) {
  // Could be: doesn't exist, or belongs to a different tenant.
  // We return 404 either way — don't reveal which.
  return v2Error({
    code: "agent_not_found",
    message: `Agent ${params.id} not found.`,
    retryable: false,
    httpStatus: 404,
  });
}

The Performance Tradeoff

RLS is not free. Every query against an RLS-enabled table includes policy evaluation in the query plan. For simple equality checks like ours (client_id = $1), the overhead is small — PostgreSQL can use the client_id index and the policy adds a filter that's evaluated alongside the normal predicates.

We benchmarked on our ww_call_logs table, which has ~40M rows:

Query	Without RLS	With RLS	Overhead
SELECT * FROM ww_call_logs WHERE client_id = $1 LIMIT 100	1.2ms	1.4ms	+17%
SELECT * FROM ww_call_logs ORDER BY created_at DESC LIMIT 100	3.1ms	45ms	+1350%

The second query is the concerning one. Without an explicit client_id filter in the query, PostgreSQL can't use the client_id index for the RLS policy — it has to evaluate the policy on every row it touches. A query that sorts by created_at across all rows becomes a full table scan filtered by the policy.

The fix is simple: always include client_id in queries against high-volume tables, even though it's redundant with RLS. The explicit filter lets PostgreSQL use the index; the RLS policy is a safety net, not a performance optimization.

// Anti-pattern: relies on RLS for filtering, can be slow on large tables
const logs = await tx.select().from(callLogs).orderBy(desc(callLogs.createdAt)).limit(100);

// Correct: explicit filter enables index use, RLS is the safety net
const logs = await tx
  .select()
  .from(callLogs)
  .where(eq(callLogs.clientId, actor.clientId))  // explicit, for index
  .orderBy(desc(callLogs.createdAt))
  .limit(100);

We enable RLS on every tenant-scoped table. But for tables with more than a few million rows, we treat the application-level WHERE client_id as mandatory, not optional. RLS prevents the bug where someone forgets the filter. The explicit filter prevents the performance cliff when they don't.

When RLS Is Not Enough

RLS handles per-row tenant isolation. It doesn't handle cross-tenant aggregate queries — things like "what's the average call duration across all clients in the dental vertical?" (benchmark data we expose in the admin dashboard).

For these queries, we use a separate admin connection that bypasses RLS entirely:

// lib/db/admin-client.ts

/**
 * Admin database client that bypasses RLS.
 * Use ONLY for platform-level aggregate queries.
 * Never pass this to route handlers or use it with untrusted actor contexts.
 */
export const adminDb = drizzle(adminPool, { schema });
// adminPool connects as a superuser role with BYPASSRLS privilege

The admin client connects as a PostgreSQL role with BYPASSRLS privilege. It's not imported in any user-facing route handler — only in internal jobs and platform admin routes protected by a separate authentication layer.

The database enforces the separation at the role level, not just the application level.