cd..→blog

Hardening Multi-Tenant Data Isolation with PostgreSQL RLS and Drizzle ORM

const published = "May 5, 2026, 10:27 PM";const readTime = 5 min;
PostgreSQLSecurityDrizzle ORMMulti-tenancyDatabase Security
Learn how to implement robust multi-tenant data isolation using PostgreSQL Row Level Security (RLS) and Drizzle ORM to prevent cross-tenant data leaks at the database layer.

Hardening Multi-Tenant Data Isolation with PostgreSQL RLS and Drizzle ORM

In multi-tenant SaaS architectures, the most critical security failure is a cross-tenant data leak. Traditionally, engineers have relied on application-level filtering—appending WHERE tenant_id = $1 to every query. However, as a codebase grows, the risk of a developer forgetting this clause increases, leading to catastrophic privacy breaches.

By shifting isolation logic from the application layer to the database layer using PostgreSQL Row Level Security (RLS), we can enforce data boundaries at the runtime level. This article explores implementing RLS with Drizzle ORM, a TypeScript ORM that provides type-safe SQL generation while staying close to native SQL features.

The Problem with Application-Layer Filtering

Application-layer isolation is fragile. It relies on developer discipline and comprehensive code reviews. A single missed filter in a complex join or a reporting dashboard can expose sensitive data. Furthermore, using a single database user for all application requests (the 'super-user' pattern) means that if your application is compromised via SQL injection, the attacker has access to every tenant's data.

PostgreSQL Row Level Security (RLS) Explained

RLS allows you to define policies on a table that restrict which rows are returned or modified based on the user executing the query. Instead of relying on the application to filter data, the database engine itself evaluates a boolean expression for every row access.

To make this work in a stateless web application (like a Node.js API), we use a session-level variable. The application sets a current_tenant_id at the start of a transaction, and the RLS policy references this variable.

Step 1: Defining the Schema and Policies

First, we enable RLS on our tables and define the policies. In this example, we assume a standard projects table owned by a tenant.

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

-- Create a policy that allows access only if the tenant_id matches the session variable
CREATE POLICY tenant_isolation_policy ON projects
USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

PostgreSQL Documentation provides deep dives into how these policies interact with different command types like INSERT and UPDATE.

Step 2: Integrating with Drizzle ORM

Drizzle is an excellent choice for this pattern because it doesn't abstract away the underlying SQL. To implement RLS safely, we need to ensure that every database interaction occurs within a transaction where the app.current_tenant_id is set.

We can create a higher-order function or a scoped client to handle this. Using Drizzle's transaction API, we can inject the configuration before executing business logic.

import { pgTable, uuid, text } from 'drizzle-orm/pg-core';
import { db } from './db';
import { sql } from 'drizzle-orm';

export const projects = pgTable('projects', {
  id: uuid('id').primaryKey().defaultRandom(),
  tenantId: uuid('tenant_id').notNull(),
  name: text('name').notNull(),
});

async function withTenant<T>(tenantId: string, callback: (tx: any) => Promise<T>): Promise<T> {
  return await db.transaction(async (tx) => {
    // Set the session variable for the duration of the transaction
    await tx.execute(sql`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`);
    return await callback(tx);
  });
}

Step 3: Handling Connection Pooling Challenges

When using connection poolers like PgBouncer, session-level variables can be dangerous. If a connection is returned to the pool without resetting the variable, the next request might inherit the previous tenant's ID.

To mitigate this, always use the third parameter in set_config(..., true), which ensures the setting is local to the current transaction. Once the transaction commits or rolls back, the setting is cleared. This is the most reliable way to prevent 'leakage' between pooled connections in a serverless or high-concurrency environment.

Step 4: Performance Implications

Engineers often worry that RLS adds significant overhead. While there is a nominal cost to evaluating the policy for every row, PostgreSQL optimizes this heavily. If your tenant_id column is indexed (which it should be for performance anyway), the RLS policy acts similarly to a standard index scan.

To verify performance, use EXPLAIN ANALYZE. You will see the RLS filter applied in the query plan:

EXPLAIN ANALYZE SELECT * FROM projects;
-- Filter: (tenant_id = (current_setting('app.current_tenant_id'::text))::uuid)

Tradeoffs and Considerations

While RLS provides a 'fail-safe' security layer, it introduces complexity in certain areas:

  1. Schema Migrations: Tools like Drizzle Kit don't natively manage RLS policies yet. You must manage these via custom migration files or db.execute calls during deployment.
  2. Superuser Bypass: By default, the table owner (usually the user running migrations) bypasses RLS. Ensure your application connects with a restricted 'web_user' role that does not own the tables.
  3. Local Development: Developers need to be aware that standard SELECT * queries in a SQL GUI will return zero results unless they manually set the session variable.

Conclusion

Implementing Row Level Security with Drizzle ORM shifts the responsibility of data isolation from the developer's memory to the database's engine. By wrapping database calls in a tenant-aware transaction, you create a robust perimeter that prevents cross-tenant data leaks even if application-level filters are omitted. In an era of increasing data privacy regulations, this 'Defense in Depth' strategy is no longer optional for serious SaaS engineering teams.

cd..→back to blog