Why SET LOCAL, not SET

You're using a shared connection pool in Node.js. You call SET app.current_tenant = 'abc' to scope your RLS policies. The query runs fine.

But the next request — from a completely different tenant — gets that same connection from the pool. Their queries now run as tenant 'abc'. You've just leaked data across tenants.

This is one of the most dangerous bugs in multi-tenant PostgreSQL, and it comes from a single keyword: SET instead of SET LOCAL.

The Problem: Session-Scoped SET Persists

PostgreSQL's SET command changes a session variable for the entire connection. When the transaction commits, the variable stays. When the connection is returned to the pool, the variable stays. When the next request picks up that connection, the variable is still there.

-- Request A (tenant 'abc')
SET app.current_tenant = 'abc';
SELECT * FROM orders; -- Returns tenant abc's orders ✓
 
-- Connection returned to pool...
 
-- Request B (tenant 'xyz') picks up the SAME connection
-- app.current_tenant is STILL 'abc'
SELECT * FROM orders; -- Returns tenant abc's orders ✗ DATA LEAK

This is not a theoretical risk. In any application using a connection pool — which is every production Node.js application — SET creates a silent data leak that passes every test but fails in production under concurrent load.

Why Connection Pools Make This Dangerous

In a single-connection setup, SET is fine. One process, one connection, one tenant at a time. But Node.js applications use connection pools (via pg, Drizzle, Prisma, or any ORM). A pool of 10-20 connections serves hundreds of concurrent requests.

The lifecycle looks like this:

1. Request arrives → pool assigns a connection
2. Application runs queries
3. Request completes → connection goes back to the pool
4. Next request arrives → pool assigns the same connection

Between steps 3 and 4, the connection carries all its session state. That includes variables set by SET, temporary tables, prepared statements, and advisory locks. If you're setting tenant context with SET, every connection in the pool becomes a landmine.

SET LOCAL: Transaction-Scoped Safety

SET LOCAL does exactly what SET does, but scoped to the current transaction. When the transaction commits or rolls back, the variable is automatically cleared. The connection returns to the pool in a clean state.

BEGIN;
SET LOCAL app.current_tenant = 'abc';
 
-- All queries in this transaction see tenant 'abc'
SELECT * FROM orders;
-- Returns only tenant abc's orders ✓
 
COMMIT;
-- app.current_tenant is automatically cleared
-- Connection is safe to reuse

The key difference:

This is documented in the PostgreSQL SET docs, but it's easy to miss. The docs say: "The effects of SET LOCAL last only till the end of the current transaction." That single sentence is the difference between a secure multi-tenant system and a data leak.

How nest-tenant Implements This

In nest-tenant, the RLS isolation adapter wraps every tenant-scoped operation in a transaction and uses SET LOCAL to set the tenant context. This happens transparently through ORM adapters — application code never calls SET directly.

// Simplified from nest-tenant's RLS adapter
async executeWithTenant<T>(
  tenantId: string,
  operation: () => Promise<T>
): Promise<T> {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();
 
  try {
    // SET LOCAL — scoped to this transaction only
    await queryRunner.query(
      `SET LOCAL app.tenant_id = $1`,
      [tenantId]
    );
 
    const result = await operation();
    await queryRunner.commitTransaction();
    return result;
  } catch (error) {
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    await queryRunner.release(); // Connection returns clean
  }
}

The critical detail: even if operation() throws and the transaction is rolled back, SET LOCAL still clears. There is no code path where the tenant variable leaks to the next request. The database guarantees this, not the application.

What Happens When a Transaction Fails

This is the part most implementations get wrong. Consider a "cleanup" approach without SET LOCAL:

// ❌ Dangerous — cleanup can fail
try {
  await db.query(`SET app.tenant_id = $1`, [tenantId]);
  await doWork();
} finally {
  await db.query(`RESET app.tenant_id`); // What if this fails?
}

If the RESET call fails (network error, connection dropped, timeout), the variable persists. You've leaked tenant context. With SET LOCAL, there is no cleanup step to fail — the database engine clears the variable when the transaction ends, regardless of how it ends.

Testing This With Real Databases

You can't test connection pool behavior with mocks. In nest-tenant, integration tests use Testcontainers to spin up a real PostgreSQL instance and verify that:

1. Tenant A's query returns only tenant A's data
2. After the transaction, the connection has no tenant context
3. Tenant B's query on the same connection returns only tenant B's data
4. Under concurrent load, no cross-tenant data leakage occurs

it('should not leak tenant context between requests', async () => {
  // Request 1: tenant 'alpha'
  const resultA = await service.executeWithTenant('alpha', () =>
    repo.find()
  );
  expect(resultA.every(r => r.tenantId === 'alpha')).toBe(true);
 
  // Request 2: tenant 'beta' — same pool, possibly same connection
  const resultB = await service.executeWithTenant('beta', () =>
    repo.find()
  );
  expect(resultB.every(r => r.tenantId === 'beta')).toBe(true);
 
  // No overlap
  const alphaIds = resultA.map(r => r.id);
  const betaIds = resultB.map(r => r.id);
  expect(alphaIds).not.toEqual(expect.arrayContaining(betaIds));
});

This test catches the exact bug that SET introduces. If you replace SET LOCAL with SET in the adapter, this test fails — tenant beta's query returns tenant alpha's data because the connection was poisoned.

The Rule

If you're building multi-tenant PostgreSQL with connection pools:

• Always use SET LOCAL inside a transaction
• Never use SET for tenant context on pooled connections
• Never rely on application-level cleanup (RESET, SET to null) — it's a code path that can fail
• Always test with real databases and concurrent connections

The database should enforce isolation, not your application's error handling. SET LOCAL makes the database your ally. SET makes it a liability. (For a deeper comparison of RLS vs schema-per-tenant isolation, see Schema-per-Tenant vs RLS: When to Use Which.)

nest-tenant implements this pattern as a configurable adapter for NestJS — supporting both RLS and schema-per-tenant isolation strategies with Drizzle and Prisma. See the project page or the source on GitHub.