マルチテナント 2026 向けの Drizzle ORM + Postgres 行レベル セキュリティ

Postgres Row-Level Security (RLS) eliminates the risk by pushing the filter into the database itself: even raw SQL queries can't bypass it. Drizzle ORM's SQL-first nature makes RLS integration natural — easier than with most "managed" ORMs. Here's the production playbook we run on ECOSIRE.COM.

Key Takeaways

• RLS enforces tenant isolation at the Postgres level — even raw SQL queries respect it
• The pattern: every tenant-scoped table gets a policy referencing a session variable like app.tenant_id
• On every query, set the session variable from your authenticated user's tenant ID
• Drizzle's sql template literal lets you set session variables inside the same connection as your queries
• Connection pooling needs care — pg-pool and pgbouncer both work but require explicit reset on connection release
• Performance overhead is typically <5% on indexed queries
• This pattern eliminates entire classes of "missing WHERE clause" bugs forever

The case for RLS in multi-tenant SaaS

Most multi-tenant apps filter manually:

const orders = await db.select().from(ordersTable)
  .where(eq(ordersTable.organizationId, currentUser.organizationId));

This works until someone:

• Forgets the where clause on a new query
• Writes a raw SQL escape hatch and forgets to filter
• Adds a JOIN that fans out to data from another tenant
• Implements a sub-query that bypasses the filter

We've seen all four bugs ship to production. The blast radius is enormous: customer A's data shows up to customer B. RLS makes this structurally impossible.

How Postgres RLS works

You enable RLS on a table:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

You define policies:

CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (organization_id = current_setting('app.tenant_id')::uuid);

You set the session variable on every connection:

SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

From now on, any query against orders — including raw SELECTs, JOINs, and aggregations — only returns rows where organization_id matches the session variable. Forget the variable, get zero rows. Set it wrong, get zero rows. There's no escape.

Drizzle schema for multi-tenant SaaS

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

export const organizations = pgTable('organizations', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const orders = pgTable('orders', {
  id: uuid('id').primaryKey().defaultRandom(),
  organizationId: uuid('organization_id')
    .notNull()
    .references(() => organizations.id),
  customerEmail: text('customer_email').notNull(),
  totalAmount: text('total_amount').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Every tenant-scoped table has an organization_id column with a foreign key to the organizations table.

Generating the RLS migration

drizzle-kit doesn't generate RLS policies natively (yet — there's an open RFC), so you write them as a manual migration:

-- migrations/0010_enable_rls.sql

-- Enable RLS on all tenant-scoped tables
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
-- ... every other tenant-scoped table

-- Create the policy. Same pattern for every table.
CREATE POLICY tenant_isolation_orders ON orders
  FOR ALL
  USING (organization_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_customers ON customers
  FOR ALL
  USING (organization_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_products ON products
  FOR ALL
  USING (organization_id = current_setting('app.tenant_id', true)::uuid);

-- Force the application role to honor RLS (even superusers respect it)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
ALTER TABLE customers FORCE ROW LEVEL SECURITY;
ALTER TABLE products FORCE ROW LEVEL SECURITY;

The , true second argument to current_setting makes the call return NULL instead of erroring when the variable isn't set. We then make the cast safe:

USING (organization_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

Setting the tenant on each request (NestJS pattern)

In NestJS with Drizzle, the pattern we use:

// db.module.ts — single connection pool
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const client = postgres(process.env.DATABASE_URL!, { max: 20 });
export const db = drizzle(client, { schema });

// tenant.middleware.ts — set the variable per request
@Injectable()
export class TenantMiddleware implements NestMiddleware {
  async use(req: AuthenticatedRequest, res: Response, next: NextFunction) {
    const orgId = req.user?.organizationId;
    if (!orgId) {
      throw new UnauthorizedException();
    }
    // Set the tenant variable for this connection
    await db.execute(sql`SELECT set_config('app.tenant_id', ${orgId}, true)`);
    next();
  }
}

The set_config(..., true) second argument makes the variable transaction-scoped (cleared at end of transaction). For a per-request lifetime, use false and explicitly reset.

The connection-pool gotcha

postgres-js and pg-pool reuse connections across requests. If you set app.tenant_id on connection A in request 1, then request 2 grabs connection A but forgets to reset, request 2 inherits request 1's tenant. Catastrophic data leak.

Solutions:

Option A: Set tenant inside a transaction

await db.transaction(async (tx) => {
  await tx.execute(sql`SELECT set_config('app.tenant_id', ${orgId}, true)`);
  // tx scope: variable is set
  const orders = await tx.select().from(ordersTable);
  // tx scope: variable cleared on COMMIT
});

true makes set_config local to the transaction. End of transaction, variable resets. Safe.

Option B: Reset on release

const conn = await pool.connect();
try {
  await conn.query(`SET app.tenant_id = $1`, [orgId]);
  // ... do work ...
} finally {
  await conn.query(`RESET app.tenant_id`);
  conn.release();
}

Manual but works on any pool implementation.

Option C: pgbouncer with discard all

If you use pgbouncer in transaction-pooling mode, configure server_reset_query = DISCARD ALL. This resets all session state when the connection returns to the pool.

We use Option A (transaction-scoped) on ECOSIRE.COM because it's foolproof — the variable lifetime is tied to the transaction lifecycle. Every request runs inside a transaction; the variable can't leak.

Drizzle integration helper

Wrap the pattern in a helper:

export async function withTenant<T>(orgId: string, fn: (tx: typeof db) => Promise<T>): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`SELECT set_config('app.tenant_id', ${orgId}, true)`);
    return fn(tx);
  });
}

Usage:

const orders = await withTenant(req.user.organizationId, (tx) =>
  tx.select().from(ordersTable).where(eq(ordersTable.status, 'paid'))
);

The where clause is no longer required for tenant isolation — the policy handles it. But you can keep it for clarity and defense in depth. Adding it doesn't hurt; the policy still enforces the actual boundary.

Bypassing RLS for admin/system queries

Sometimes you need cross-tenant queries (admin dashboards, batch jobs, reporting). Two options:

Option 1: Separate role with BYPASSRLS

CREATE ROLE app_admin WITH BYPASSRLS LOGIN PASSWORD '...';
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_admin;

Connect as app_admin for admin operations. Make sure the regular app role (app_user) does NOT have BYPASSRLS.

Option 2: Set a "bypass" tenant ID

CREATE POLICY admin_bypass ON orders
  FOR ALL
  USING (current_setting('app.is_admin', true) = 'true');

Add an is_admin policy alongside the tenant policy. When admin queries run, set app.is_admin = 'true' in the session. Each policy is OR'd, so either condition matches.

We use Option 1 for batch jobs (cleaner separation) and Option 2 for admin UI surfaces (same pool, different session config).

Performance impact

We benchmarked RLS-on vs RLS-off on identical queries:

The overhead is negligible for indexed queries — Postgres folds the policy condition into the query plan as a regular WHERE clause. Full table scans see slightly more overhead because the policy adds a filter step.

The key requirement: make sure organization_id is indexed on every tenant-scoped table. We use composite indexes like (organization_id, created_at) for time-ordered queries.

Schema-design rules with RLS

1. Every tenant-scoped table has an organization_id column (or equivalent — tenant_id, account_id, your terminology).
2. Index organization_id (composite with the most-selective-next column).
3. Define foreign keys to the parent organizations table.
4. Enable RLS + force RLS on every tenant-scoped table.
5. Cross-tenant tables (e.g., users shared across orgs) need different policies — typically a join through a membership table.

Rolling out RLS to an existing database

If you have a multi-tenant app already using WHERE organization_id = ? filters and want to add RLS:

1. Audit: confirm every query has the filter. We had to fix 12 places in our 200+ NestJS services.
2. Add organization_id to any table missing it. Backfill from joins.
3. Index organization_id everywhere.
4. Deploy without RLS first, with set_config('app.tenant_id', ...) on every request. This catches "missing tenant" bugs without breaking production.
5. Enable RLS in a separate deploy. If queries return zero rows where they should return data, you have a missing set_config somewhere.
6. Force RLS as a final hardening step.

We rolled this out on ECOSIRE.COM over 3 sprints. Two near-misses were caught at step 4 (queries on a new analytics module weren't going through the middleware). Both were trivial fixes. RLS would have caught them immediately as "no rows returned."

Frequently Asked Questions

Does RLS work with prepared statements and connection pooling?

Yes. set_config() is a function call like any other; it's not a prepared statement complication. The pooling concern is about session-variable lifetime, addressed above with transaction-scoped variables.

Can I use RLS with read replicas?

Yes — replicas inherit table policies via streaming replication. Reads against the replica honor the policy. Make sure your read connection also sets app.tenant_id per request.

How does RLS interact with VIEWs?

VIEWs by default run with the privileges of the view owner, which can bypass RLS. Use CREATE VIEW ... WITH (security_invoker = true) (Postgres 15+) to make views run with the caller's privileges, including RLS.

What about Drizzle's relational queries (db.query.users.findMany)?

These compile to standard JOINs that the policy applies to. No special handling needed. We've stress-tested complex relational queries with RLS in production — works as expected.

Should I keep manual WHERE organizationId = ? clauses too?

Yes, for defense in depth and for query plan visibility. Postgres' planner uses the WHERE clause for index selection; relying solely on the policy can sometimes produce slower plans. Keep both. RLS is your safety net; manual filtering is your primary path.

---

ECOSIRE.COM runs Drizzle + Postgres RLS in production for multi-tenant SaaS. Our backend engineering team ships RLS-hardened SaaS architectures with NestJS, Drizzle, and Postgres 17. See also our Drizzle vs Prisma comparison for the broader ORM choice.