Schema-Per-Tenant Architecture: Routing, Query Scoping & Security

Schema-per-tenant gives every tenant a dedicated PostgreSQL schema inside a shared database, trading the operational weight of a database per customer for logical isolation that still maps cleanly onto roles and grants. It operates within the broader Multi-Tenant Database Isolation Models framework, sitting between a shared table with row filters and full physical separation.

Each tenant owns a namespace such as tenant_acme. Tables, indexes, sequences, and constraints live inside that namespace, while the connection pool, planner cache, and compute are shared across all tenants. Isolation is enforced at query time by selecting the correct namespace with search_path, and at rest by GRANT/REVOKE on the schema. The hard part is making the namespace selection deterministic and impossible to bypass — that is what most of this page is about.

Prerequisites

Confirm the following before you wire any of the code below into a production path.

• [ ] PostgreSQL 14+ (this page uses SET LOCAL, ALTER DEFAULT PRIVILEGES, and search_path semantics current as of 14).
• [ ] A connection pooler — PgBouncer 1.21+ in transaction mode, or pgcat — terminating application connections.
• [ ] A dedicated low-privilege application role (saas_app_role) that is not a superuser and does not own the database.
• [ ] A separate migration/provisioning role with CREATE on the database, used only by background jobs.
• [ ] A request-scoped context mechanism in your app (Express middleware, AsyncLocalStorage, Go context.Context, or equivalent).
• [ ] A schema naming convention that is collision-free and injection-safe (slug allowlist, never raw tenant input).
• [ ] A migration tool that can iterate schemas (Flyway with placeholders, Sqitch, or a custom runner).

Step-by-Step Implementation

Step 1 — Resolve the tenant at the edge

Identify the tenant before any database work begins. Extract the identifier from a subdomain, a verified JWT claim, or an API key, then reject the request immediately if it is missing or ambiguous. Never let an unresolved tenant fall through to a default schema.

import { AsyncLocalStorage } from "node:async_hooks";
import type { Request, Response, NextFunction } from "express";

export const tenantStore = new AsyncLocalStorage<{ id: string; schema: string }>();

const SLUG = /^[a-z][a-z0-9_]{1,62}$/;

export function resolveTenant(req: Request, res: Response, next: NextFunction) {
  const claim = (req as any).user?.tenant_id as string | undefined;
  const host = req.hostname.split(".")[0];
  const id = claim ?? host;

  if (!id || !SLUG.test(id)) {
    return res.status(401).json({ error: "tenant_unresolved" });
  }
  if (claim && host && claim !== host) {
    return res.status(403).json({ error: "tenant_mismatch" });
  }

  tenantStore.run({ id, schema: `tenant_${id}` }, () => next());
}

The regex is load-bearing: the schema name is interpolated into SQL later, so the only safe input is one that already matches a strict allowlist. A mismatch between the JWT claim and the host is treated as a hostile request, not a warning.

Two design choices matter here. First, resolution runs before the request reaches any route handler, so there is no code path where a controller can issue a query without a bound tenant — the absence of context is a 401, not a default. Second, AsyncLocalStorage carries the context implicitly through the async call chain, which means downstream service functions, ORM hooks, and even background-job enqueues read the same store without passing a tenantId argument through every signature. That removes the most common source of routing bugs: a deeply nested call that simply forgot to thread the tenant through.

Step 2 — Bind the namespace per transaction

Set the schema with SET LOCAL search_path inside an explicit transaction. SET LOCAL is scoped to the transaction and reverts on COMMIT or ROLLBACK, which is exactly the lifetime you want under a transaction-pooled connection. A plain SET would persist on the pooled server connection and leak into the next, unrelated, tenant's queries.

BEGIN;
SET LOCAL search_path TO tenant_acme, public;

SELECT id, email FROM users WHERE status = 'active';
INSERT INTO audit_log (actor, action) VALUES ('system', 'login');

COMMIT;  -- search_path is discarded here

public is appended deliberately so shared reference tables (currencies, feature flags) resolve, while tenant tables shadow anything of the same name. Put public first only if you intend the opposite, which you almost never do.

The ordering also has a subtle correctness implication for unqualified writes. With tenant_acme, public, an INSERT INTO users lands in tenant_acme.users; reversing the order would silently write tenant rows into the shared public.users if one exists, which is the worst class of bug — it succeeds, returns no error, and corrupts the shared namespace. Keep the tenant schema first and treat any unqualified reference to a known shared table name as a code smell worth a lint rule.

Step 3 — Inject the namespace from the app layer

Wrap every query in a helper that opens the transaction, applies the resolved schema, and runs the caller's work. Application code never types a schema name; it only ever calls withTenant. Use a parameterized set_config rather than string-building the SQL where the driver allows it.

import { Pool, PoolClient } from "pg";
import { tenantStore } from "./resolve-tenant";

const pool = new Pool({ max: 20 });

export async function withTenant<T>(fn: (c: PoolClient) => Promise<T>): Promise<T> {
  const ctx = tenantStore.getStore();
  if (!ctx) throw new Error("no_tenant_context");

  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // set_config(name, value, is_local=true) — value is parameterized, never concatenated
    await client.query("SELECT set_config('search_path', $1, true)", [`${ctx.schema}, public`]);
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

set_config(..., true) is the function-call equivalent of SET LOCAL and accepts a bind parameter, so even though the schema name is already allowlisted in Step 1, the value never touches the SQL string. Defense in depth.

Step 4 — Provision schemas asynchronously

Run schema creation in a background worker, not in the signup request. DDL takes locks and can fail; coupling it to the synchronous onboarding flow creates race conditions and timeouts. Make the job idempotent so retries are safe.

-- executed by the migration role, with :slug already validated against ^[a-z][a-z0-9_]{1,62}$
CREATE SCHEMA IF NOT EXISTS tenant_:slug AUTHORIZATION saas_app_role;

-- apply the canonical table set
\i /migrations/v1_base_schema.sql

REVOKE ALL ON SCHEMA tenant_:slug FROM PUBLIC;
GRANT USAGE ON SCHEMA tenant_:slug TO saas_app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tenant_:slug TO saas_app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA tenant_:slug
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO saas_app_role;

ALTER DEFAULT PRIVILEGES is what keeps grants correct after later migrations add tables — without it, tables created in a future migration would carry no grant for the app role and queries would fail with permission errors weeks after onboarding.

Step 5 — Roll out migrations across every schema

Schema-per-tenant multiplies migration work by tenant count. Iterate the live schemas, apply each evolution script, and batch the work so a thousand-schema rollout does not hold a single transaction open for minutes.

import psycopg

DDL = open("migrations/v2_add_phone.sql").read()  # uses {{schema}} placeholder

with psycopg.connect("dbname=saas_core user=migrator") as conn:
    schemas = conn.execute(
        "SELECT schema_name FROM information_schema.schemata "
        "WHERE schema_name LIKE 'tenant_%' ORDER BY schema_name"
    ).fetchall()

    for (schema,) in schemas:
        with conn.transaction():                      # one tx per schema, not one giant tx
            conn.execute(f'SET LOCAL search_path TO "{schema}"')
            conn.execute(DDL.replace("{{schema}}", schema))
        print(f"migrated {schema}")

One transaction per schema keeps lock duration short and lets the runner resume from the last successful schema after a failure.

Backward compatibility is the discipline that makes this survivable at scale. A migration that drops a column or renames a table breaks every tenant the instant it runs against their schema, and with thousands of schemas the rollout is not instantaneous — there is a window where some tenants are on the new shape and some on the old. Use the expand-and-contract pattern: add the new column nullable, backfill, deploy code that reads both shapes, then remove the old column in a later migration once every schema has been migrated and the old code path is gone. Record per-schema migration state in a control table (schema_migrations(schema_name, version, applied_at)) so the runner is idempotent and observable, and so you can answer "which tenants are on v2" without scanning catalogs.

Routing Decision Table

How search_path Switching Works

The figure below traces a single request from edge to execution, showing where the namespace is chosen and where it is discarded. The discard step is what makes a pooled connection safe to hand to the next tenant.

Dynamic Query Scoping & Connection Handling

Schema selection must align with transaction boundaries, and the pooler must cooperate. In transaction pooling mode each transaction may land on a different server connection, so any state that outlives the transaction is a leak. SET LOCAL (and set_config(..., true)) confine the namespace to the transaction; a bare SET would pin it to the server connection and corrupt the next checkout.

Configure the pooler to scrub residual state on every server reset as a safety net. DISCARD ALL resets search_path, prepared statements, and session variables.

[databases]
saas_prod = host=127.0.0.1 port=5432 dbname=saas_core

[pgbouncer]
pool_mode = transaction
max_client_conn = 4000
default_pool_size = 50
server_reset_query = DISCARD ALL
server_reset_query_always = 1

Prepared statements are the common trap here. Under transaction pooling, a statement prepared on one server connection is invisible on the next, so disable client-side prepared statements or use protocol-level prepared statements that PgBouncer 1.21+ tracks. For deeper sizing math and burst handling, the Connection Pooling in Multi-Tenant Systems cluster covers pool formulas and backpressure, and the ORM integration patterns live under Tenant-Aware Data Routing & Query Scoping.

Plan caching deserves a second look in this model. PostgreSQL caches generic plans per prepared statement per backend, and because search_path participates in name resolution, a plan built for tenant_acme is not reused for tenant_globex even when the SQL text is identical. With many tenants cycling through a shared backend, the plan cache thrashes and the server falls back to re-planning, which raises CPU under load. Two mitigations apply: prefer simple, fully-qualified queries for the hottest paths so the planner has less to recompute, and keep default_pool_size modest so each backend serves a bounded set of tenants and its cache stays warm rather than churning across the entire population.

The other handling concern is transaction discipline at the application boundary. Because the namespace is bound per transaction, any work that must be tenant-scoped has to run inside a withTenant block — a query issued on a raw pool client outside that wrapper inherits whatever search_path the pooler last left, which is undefined. Make the raw pool private to the data-access layer and expose only the wrapper, so it is structurally impossible to query without a bound tenant.

Security Enforcement & Access Control

Database grants are the second line of defense behind application routing. The application role gets USAGE only on its own schemas and SELECT/INSERT/UPDATE/DELETE on the tables within — never CREATE on the database, and never ownership. Shared metadata that genuinely must be visible across tenants lives in public with read-only grants; anything tenant-scoped that nonetheless sits in a shared table must fall back to row filters, the same mechanism described in Shared Database with Row-Level Security.

A single misconfigured grant can defeat the whole model: if the app role can read every schema, a routing bug becomes a cross-tenant breach. Audit grants continuously and alert on any access outside the resolved schema.

The grant model is only as strong as the role hierarchy beneath it. The application connects as saas_app_role, which has no CREATE on the database and owns nothing; the migration role owns the schemas and is used exclusively by background jobs that never accept request traffic. This split means a compromised application connection cannot create new schemas, alter grants, or drop tables — it can only read and write within schemas it has already been granted. Reconcile the live grant graph against the intended policy on a schedule by querying information_schema.role_table_grants and information_schema.usage_privileges, and treat any grant that does not match the provisioning template as an incident. Drift here is invisible until exploited, so detection has to be proactive rather than triggered by a failure.

To verify the role cannot reach beyond its lane, assume the app role in a session and confirm a foreign schema is invisible.

SET ROLE saas_app_role;
SET search_path TO tenant_globex, public;
SELECT * FROM users;   -- expect: ERROR: permission denied for schema tenant_globex
RESET ROLE;

Operational Overhead & Scaling Metrics

Per-schema metadata is the dominant cost. PostgreSQL caches catalog entries per backend, so thousands of schemas inflate pg_catalog lookups, planner memory, and pg_dump time. Track these thresholds and act before they bite.

When schema count or migration time crosses these lines, the next move is usually splitting the population across instances or graduating heavy tenants upward. Teams arriving here from a single shared table should follow the cutover sequence in Migrating from Shared DB to Schema-Per-Tenant, and regulated workloads weighing this model against row filters should read Schema-Per-Tenant vs Row-Level Security for HIPAA before committing.

Pitfalls & Anti-Patterns

Session-level search_path under transaction pooling. A bare SET search_path persists on the pooled server connection and is inherited by the next tenant that borrows it — a silent cross-tenant read. Always use SET LOCAL or set_config(..., true) inside an explicit transaction, and set server_reset_query = DISCARD ALL as a backstop.

Interpolating raw tenant input into schema names. Building tenant_${input} from unvalidated request data is SQL injection by another name. Validate against a strict allowlist regex at the edge and pass the value as a bind parameter to set_config so it never enters the SQL string.

Forgetting ALTER DEFAULT PRIVILEGES. Grants applied at provisioning time do not cover tables created by later migrations. Without ALTER DEFAULT PRIVILEGES, a migration that adds a table will produce permission-denied errors for the app role long after onboarding, in a schema that worked yesterday.

Synchronous DDL in the signup path. Creating a schema and its tables during the request that registers a tenant couples a slow, lock-taking operation to a latency-sensitive flow. Push provisioning to an idempotent background worker and let signup complete against a "provisioning" state.

Unbounded schema growth. Treating schema-per-tenant as infinitely scalable ignores catalog and planner pressure. Define an archival and instance-sharding policy before you cross a few hundred active schemas, not after the planner slows down.

Frequently Asked Questions

Why use SET LOCAL instead of SET for search_path? SET LOCAL scopes the change to the current transaction and reverts on commit or rollback. Under a transaction-mode pooler, server connections are recycled between tenants, so a plain SET would leave the previous tenant's schema active for the next request — a direct cross-tenant data leak. SET LOCAL (or set_config(name, value, true)) is the only safe form with pooling.

How many tenants can a single PostgreSQL instance hold this way? Practically a few hundred to low thousands of schemas before catalog and planner overhead dominate. Total relation count matters more than schema count: 1,000 schemas with 50 tables each is 50,000 relations, which strains pg_dump, autovacuum, and planner memory. Past those thresholds, shard tenants across instances or promote large tenants to a dedicated database.

How do I run migrations across thousands of schemas safely? Iterate information_schema.schemata for tenant_% names and apply each script in its own short transaction, recording the last successful schema so the job can resume after a failure. Batch and throttle the rollout, run it off-peak, and keep DDL backward-compatible so the application tolerates both old and new shapes during the window.

How do I share reference data across tenant schemas? Keep shared, static data in the public schema with read-only grants and append public to every tenant's search_path so the tables resolve. Avoid copying static data into each schema — duplication invites consistency drift. For shared tables that still need per-tenant filtering, enable row-level security rather than splitting them per schema.

Related

• Multi-Tenant Database Isolation Models
• Shared Database with Row-Level Security
• Database-Per-Tenant Isolation
• Migrating from Shared DB to Schema-Per-Tenant