Skip to content

About

NestJS + Postgres reference that isolates tenants at the database layer with Row-Level Security. Ships integration tests that actively try to leak data across tenants.

Topics

Resources

Stars

1 star

Watchers

0 watching

Forks

Repository files navigation

nestjs-multitenant-starter

ci node nestjs postgres license

A reference NestJS + Postgres starter that isolates tenants at the database layer using Row-Level Security, not just at the app layer. If your query forgets WHERE tenant_id = ..., Postgres still returns zero rows. If a shared connection leaks, the other tenant's data still can't be read.

The repo ships the wiring end to end: a header-based tenant resolver, an AsyncLocalStorage-scoped tenant context via nestjs-cls, a per-request transaction that pins the current tenant with SET LOCAL, seed data for two tenants, and integration tests that actively try to leak data across the boundary and fail loudly if the boundary ever weakens.

Why not just add WHERE tenant_id = $1 everywhere

Because one day someone won't. A junior adds a new query. A generated migration forgets. A raw SQL escape hatch skips the ORM. A background job runs with the wrong context. The whole class of bug disappears if Postgres itself refuses to return rows outside the current tenant. RLS is the answer, this repo is the boilerplate.

Architecture

flowchart LR
    Client -->|X-Tenant-Slug: acme| Middleware
    Middleware -->|store slug in ALS| Cls[nestjs-cls context]
    Cls --> Interceptor[Rls interceptor]
    Interceptor -->|resolve slug -> uuid| Tenants[(tenants)]
    Interceptor -->|BEGIN; SET LOCAL app.current_tenant = uuid| Tx[Postgres tx]
    Tx --> Handler[Controller + service]
    Handler -->|SELECT * FROM documents| Docs[(documents<br/>RLS enforced)]
    Handler -->|response| Interceptor
    Interceptor -->|COMMIT or ROLLBACK| Tx
Loading

The isolation contract

At any point in a request:

  1. If no tenant is set, every query on a tenant-scoped table returns zero rows. Zero is safer than "oops".
  2. If tenant A is set, no query can read or write tenant B's rows, even a raw SQL one on the tenant-scoped tables.
  3. Rules 1 and 2 hold even if application code has a bug.

Rules are enforced by Postgres, not by application code. Application code decides which tenant is active. Postgres decides what that tenant can see.

How the boundary is enforced (defense in depth)

Layer What it does What it protects against
X-Tenant-Slug header + middleware Resolves slug to a UUID and stores it in ALS via nestjs-cls Missing or malformed tenant identity
RLS interceptor Opens a transaction and runs the tenant prelude before the handler: SET LOCAL ROLE app_user; SET LOCAL app.current_tenant = '<uuid>'; Handlers forgetting to pass tenant context; connection role bypassing RLS
SET LOCAL ROLE app_user Switches the transaction to a non-superuser role. Postgres's bootstrap user (the one the app connects as) is a SUPERUSER and would bypass RLS entirely; superusers do so regardless of FORCE ROW LEVEL SECURITY. app_user is created explicitly non-super, non-bypass. The whole class of "RLS silently disabled because the app runs as a superuser"
Postgres RLS policies on documents Filter every SELECT / INSERT / UPDATE / DELETE by tenant_id = NULLIF(current_setting('app.current_tenant', true), '')::uuid Any query, ORM or raw, that omits the filter
NULLIF(..., '') in the policy predicate Postgres custom GUCs return empty string once touched in a session, not NULL. Wrapping in NULLIF treats the empty case as NULL, so tenant_id = NULL (unset context) yields no rows without a uuid parse error. Runtime errors on default-deny paths (background jobs, cron, admin actions without a tenant)

Techniques applied

Each is a specific choice, not the only choice. The tradeoffs section lists why.

# Choice Why
1 RLS at the database layer, not just app-level filters ORM leaks, raw SQL, and generated code can't bypass it
2 SET LOCAL inside a transaction, not SET at connection level Connection pool reuse never leaks the previous tenant
3 SET LOCAL ROLE app_user before every RLS-scoped query The bootstrap connection user is a superuser and bypasses RLS. Switching to a non-super role inside the tx makes RLS actually apply.
4 nestjs-cls (AsyncLocalStorage) for request-scoped context No request-scoped providers, no @Inject(REQUEST) gymnastics in services
5 Slug-based tenant identifier in a header Human-friendly (acme, globex), decoupled from internal UUIDs
6 Tenants table has no RLS So the interceptor can resolve slug -> UUID before any tenant context is set
7 Integration tests that try to break the boundary The one thing you should test in a multi-tenant system is that the boundary holds

Trying it yourself

Prereqs: Docker and Node 20+.

# 1. Bring up Postgres and seed 2 tenants with a few documents each
cp .env.example .env
docker compose up -d
npm install
npm run rls:setup      # creates the RLS policies (idempotent)
npm run seed           # inserts tenants: acme, globex

# 2. Start the API
npm run start:dev      # listens on :3000

# 3. In another terminal, prove tenant isolation
curl -s -H 'X-Tenant-Slug: acme'   http://127.0.0.1:3000/documents | jq
curl -s -H 'X-Tenant-Slug: globex' http://127.0.0.1:3000/documents | jq
# each tenant sees only its own documents, no matter how you slice it

Trying to break it

npm test

The integration tests in test/isolation.e2e-spec.ts actively attempt to leak data across tenants:

  • Set tenant A, run raw SQL SELECT * FROM documents, assert tenant B's rows are invisible.
  • Set no tenant, run the same query, assert zero rows.
  • As tenant A, request tenant B's document by UUID, assert 404.
  • As tenant A, try to update a tenant B row by UUID in raw SQL, assert 0 rows affected.

If any of those assertions ever fails, the boundary is broken and CI goes red immediately.

Tradeoffs

Nothing is free. Here's what this pattern costs you.

  • Every request is a transaction. Handlers that don't touch the DB still pay for BEGIN/COMMIT. On a chatty read-only service this can matter; profile before you worry.
  • Long-running requests hold a Postgres connection. File uploads, LLM calls in the request path, or slow downstreams tie up the pool. Consider offloading to workers (see the companion webhook-ingest-worker pattern) for anything not sub-second.
  • You cannot bulk-query across tenants from the app. Admin dashboards need a separate connection or role that bypasses RLS (via SET row_security = OFF, requires a superuser or explicitly granted role). Design for it up front, and audit the paths that use it.
  • Schema-per-tenant is a different pattern. RLS gives you one database and one schema for all tenants. If you need per-tenant custom columns or backup granularity, schema-per-tenant or database-per-tenant may fit better. This repo assumes the shared-schema case, which covers ~90% of B2B SaaS.

What this is not

It's not a production-ready service. There's no auth (the tenant slug is trusted, in a real app the slug should be derived from a signed JWT or session, not a header). There's no rate limiting, no observability, no admin plane. Add those before you put it in front of real customers.

It's not a claim that RLS is the only correct approach. Application-layer filters with rigorous review can work too. RLS is just the pattern with the smallest blast radius when someone makes a mistake.

License

MIT (c) Hasibul Islam · @devhasibulislam

About

NestJS + Postgres reference that isolates tenants at the database layer with Row-Level Security. Ships integration tests that actively try to leak data across tenants.

Topics

Resources

Stars

1 star

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages