阅读中文版本

Build a Multi-Tenant API with Row-Level Isolation

A complete multi-tenant API: tenant resolution, row-level security, per-tenant connections, quota enforcement, and cross-tenant leak tests.

multi-tenant-api/
├── src/tenant.ts
├── src/db.ts
├── src/routes.ts
├── db/policies.sql
├── tests/isolation.test.ts
└── package.json

const tenantId = claims.tenant; if (!tenantId) return res.status(403).end();

await client.query('SET LOCAL app.tenant_id = $1', [tenantId]);

ALTER TABLE records ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON records USING (tenant_id = current_setting('app.tenant_id'));

await client.query('RESET app.tenant_id');

if (await overQuota(tenantId)) return res.status(429).end();

Project overview

Multi-tenancy done in application code fails the first time a query forgets a tenant filter, and the failure is a cross-tenant data leak. This build pushes isolation into the database so a request cannot read another tenant’s rows.

You will resolve the tenant from the token, set a session variable on the connection, and rely on row-level security policies that filter every table by that variable, so application code never builds tenant filters by hand.

The policy is the guarantee. A developer writing a new query cannot accidentally omit the tenant clause, because the policy applies whether or not the query mentions it, and a missing session variable yields zero rows rather than every row.

Connection pooling interacts with session state, so the service must set the variable on acquire and reset it on release, or one request can inherit another’s tenant.

Each phase adds one piece: tenant resolution, session context, the policy, pooled connections, and quotas, so isolation holds at every stage.

The project is for teams that moved from single-tenant deployments to a shared service and need to prove tenants cannot see each other. It makes that proof structural by pushing the filter into the database, so an isolation test asserts a property of the policy rather than of every query.

It is deliberately aggressive about failing closed: a missing context yields no data rather than all data, which turns the worst possible bug into an empty response a test catches immediately.

What you will build

The API provides five capabilities, each with a test that uses two tenants:

• Tenant resolution
• Session context
• Row-level policy
• Pooled connections
• Quota enforcement

Tenant resolution establishes identity from the token; session context carries it to the database; a row-level policy filters every table; pooled connections set and reset the variable; and quotas keep one tenant from consuming shared capacity.

The design accepts a small per-request setup cost in exchange for a guarantee that does not depend on every developer remembering a rule.

Architecture and data model

A request resolves the tenant from the token, opens a connection setting a session variable, and relies on row-level security policies that filter every table by that variable. Application code never builds tenant filters by hand.

A request resolves the tenant from its token, opens or borrows a connection, and sets a session variable to the tenant id before any query runs.

Row-level security policies on every table reference that variable, so a query returns only rows belonging to the current tenant regardless of what the query itself says.

When the variable is unset, the policy matches nothing, which means a bug that skips tenant resolution yields an empty result rather than a full-table leak.

The connection wrapper sets the variable on acquire and clears it on release, so a pooled connection cannot carry one tenant’s context into another request.

Because the policy is attached to the table rather than to the query, an endpoint written later inherits isolation without knowing it exists. That property is what makes the guarantee survive the growth of both the team and the code.

Prerequisites and dependencies

You need a database that supports row-level security and a token scheme that carries a trustworthy tenant claim.

• Node.js 20 LTS or later
• PostgreSQL 15+ with row-level security
• A notion of tenant identity in the token

The dependencies cover the HTTP layer, the database driver, and the tenant resolution logic:

• pg (^8.12.0) — Per-request connection with session context
• express (^4.21.0) — Tenant-aware routing

pnpm init
pnpm add pg express
pnpm add -D typescript tsx vitest

Own the migrations that create the policies together with the tables, because a policy added later to an existing table leaves a window in which isolation does not hold. Keep the tenant claim source in configuration and reject a token without one.

Directory structure

Separate tenant resolution, the connection wrapper, the policy migrations, and the endpoints so isolation can be tested with two tenants:

multi-tenant-api/
├── src/tenant.ts
├── src/db.ts
├── src/routes.ts
├── db/policies.sql
├── tests/isolation.test.ts
└── package.json

Phase 1: Tenant resolution

Derive the tenant from the authenticated token, never from a header.

const tenantId = claims.tenant; if (!tenantId) return res.status(403).end();

A user-controlled tenant id is a data-isolation hole.

Resolve the tenant from a signed token rather than a header or a body field, because a client-supplied tenant is a request to read someone else’s data.

Phase 2: Session context

Set the tenant on the connection before querying.

await client.query('SET LOCAL app.tenant_id = $1', [tenantId]);

RLS policies read this variable for every row check.

Set a session variable on the connection from the resolved tenant, so every query in the request runs with the same tenant context.

Phase 3: Row-level policy

Enable RLS and a policy that filters by the session variable.

ALTER TABLE records ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON records USING (tenant_id = current_setting('app.tenant_id'));

The database enforces isolation even if the query forgets a filter.

Create a row-level policy on every table that filters by the session variable, and verify the policy applies to selects, updates, and deletes.

Phase 4: Pooled connections

Reset session state when a connection returns to the pool.

await client.query('RESET app.tenant_id');

A leaked session variable exposes another tenant.

Wrap the pool so the variable is set on acquire and cleared on release, because a pooled connection that keeps the previous tenant is a leak waiting for a concurrency test.

Phase 5: Quota enforcement

Apply per-tenant limits on storage and request rate.

if (await overQuota(tenantId)) return res.status(429).end();

One noisy tenant must not degrade the others.

Apply quotas per tenant so one tenant cannot exhaust connection or request capacity that others depend on.

Testing

Create two tenants, write as tenant A, and assert that a query as tenant B returns nothing. Simulate a missing session variable and assert zero rows rather than all rows.

Create two tenants, write as tenant A, and assert that a query as tenant B returns nothing, which is the isolation guarantee expressed directly.

Simulate a missing session variable and assert zero rows rather than every row, because failing closed is the property that makes the policy trustworthy.

Run concurrent requests for different tenants against a small pool and assert no request observes another tenant’s rows, which is the pooling bug the wrapper exists to prevent.

Assert that a write as tenant A cannot create a row owned by tenant B, since isolation must hold on writes as well as reads.

Run the concurrency test with a pool smaller than the number of concurrent requests, because the pooling bug only appears when a connection is reused across tenants; a test with one connection per request would pass while production leaks context.

Add an isolation test for every new table as part of the migration review, because isolation is a property of the schema that the suite should enforce rather than assume.

pnpm vitest run

Deployment and operations

Own the migrations that create policies alongside tables. Backups and analytics must respect the same isolation, so export per tenant rather than dumping the whole database.

Create policies in the same migration as the tables, so no deploy window exists in which a table is reachable without isolation.

Back up and export per tenant where required, because an analytics job that ignores the policy is a role that can read across tenants.

Monitor the connection pool per tenant, since a single tenant with a slow query can hold connections and starve others.

Test the policy after every migration, because a new table without a policy silently reintroduces the class of bug the design exists to eliminate.

Include a migration check that fails when a table has no policy, so a new table cannot be added without isolation, and treat the policy set as a schema invariant rather than as a convention documented in a wiki.

Run analytics under a role subject to the same policy, or export deliberately per tenant, because the fastest way to leak across tenants is an internal report that bypassed the rules.

Security considerations

Row-level security is the backstop, not the only control: also scope cache keys, object storage prefixes, and logs by tenant. Test isolation continuously.

Treat the tenant claim as an authorization input and validate its signature, since a forged claim is a direct request for another tenant’s data.

Enforce isolation in the database as well as in application authorization, so a missing filter in code is a slow result rather than a leak.

Use a database role that is subject to the policy rather than a bypass role for the application, because a bypass role makes the guarantee optional.

Audit for tables without a policy as part of the migration check, since the one table that was added in a hurry is the one that leaks.

Keep a bypass role out of the application entirely, because a bypass capability used for migrations will eventually be used for a shortcut; run migrations with it and the application without it.

Log the policy decision rather than the rows it filtered, so an isolation incident can be investigated without creating another copy of the data.

Observability

Track per-tenant request rate and quota rejections. Alert on any isolation test failure and on a connection reusing a stale tenant context.

Track query latency per tenant, because one tenant’s slow query is often the cause of another tenant’s timeout through shared resources.

Chart connection pool usage and wait time, since pool exhaustion is the most common way a multi-tenant service degrades unevenly.

Log the tenant id with every request without logging the data, so an isolation investigation can identify the request without exposing content.

Alert on requests without a resolved tenant, because a rise in those is either a client bug or an attempt to skip resolution.

Track per-tenant request and row counts, because a tenant whose row count suddenly dwarfs the others is either a growth event or a filtering bug, and the distinction matters.

Measure the rate of policy-filtered rows per tenant, because a tenant that suddenly matches nothing is often a resolution bug rather than an empty account.

Chart the number of connections held by the slowest tenant, because a tenant holding several connections while others wait is the usual shape of a shared-pool problem.

Troubleshooting

A query returns all tenants rows. The session variable was unset and the policy compared against an empty setting; make the policy fail closed.

Sessions leak between requests. The pool returned a connection without resetting the session variable.

If a query returns zero rows unexpectedly, check the session variable first, because a policy with an unset variable correctly returns nothing.

If tenants intermittently see each other’s data, check the pool wrapper, since a connection reused without clearing the variable is the classic cause.

If a new table seems to leak, check the policy rather than the query, because a table created without a policy is the only way isolation can silently disappear.

If a query from a background job returns nothing, check whether the job sets the tenant context, because a job without a context sees an empty database rather than an error.

If a token without a tenant claim is accepted, check the resolver, because such a token should be rejected rather than defaulted to any tenant.

Limitations and next steps

• One database for all tenants; noisy-neighbor effects remain possible.
• No per-tenant encryption keys.
• Cross-tenant admin queries need a separate privileged path.

Isolation is row-level within a shared schema; a separate-database-per-tenant model would need a different provisioning story the example does not cover.

There is no schema-per-tenant customization, so tenants share a schema and any schema change applies to all of them.

The service assumes a single database; a sharded deployment would need the tenant routing to be part of the connection resolution.

Quotas are basic and cover request and connection limits rather than a full billing model, which would build on the same tenant context.

The service isolates rows within a shared schema and does not offer per-tenant schema customization or separate databases, so a tenant needing its own extensions would require a different deployment model.

There is no tenant-level encryption key separation, so all rows share the database encryption; per-tenant keys would need a different storage model.