Multi-tenant Postgres without leaking rows

By Abinashi Singh · ~10 min read · Practical Node + Postgres

The most expensive bug in a B2B SaaS is quiet: tenant A loads tenant B’s notes, invoices, or API keys because a query forgot an orgId filter. Shared-database multi-tenancy is fine — most products start there — but it only works if every path that touches tenant data goes through the same membership and scoping rules.

This guide covers a pragmatic model: users, organizations, memberships with roles, invite stubs, and middleware that attaches a verified tenant to the request. The goal is not theoretical isolation (row-level security can come later); the goal is a codebase where forgetting orgId is hard and cross-tenant tests fail loudly.

Shared database, explicit tenant key

Three common approaches:

• Database per tenant — strongest isolation, painful ops early on.
• Schema per tenant — middle ground; migrations multiply.
• Shared tables + tenant column — simplest deploy story; discipline required.

For most indie and small-team SaaS products, shared tables with an orgId (or tenantId) column win. Postgres indexes that column well. You get one migration stream, one connection pool, and straightforward backups. The tradeoff is application-level correctness. Treat that as a first-class design constraint, not an afterthought.

Core tables

A minimal spine looks like this:

User          id, email, passwordHash, name
Organization  id, name, slug
Membership    userId, orgId, role  (owner | admin | member)
              UNIQUE (userId, orgId)
Invite        orgId, email, role, token, expiresAt
Note          id, orgId, authorId, title, body   -- example resource

Everything tenant-owned carries orgId. Resources do not hang off userId alone unless they are truly personal and never shared inside an org. Even then, decide whether “personal” still lives under an org (common) or under the user (rarer in B2B).

Roles stay coarse at first. Owner can manage billing and delete the org; admin can invite and manage members; member can CRUD day-to-day resources. Fine-grained permissions can wait until a customer asks for them.

Resolve tenant from a trusted source

Never take the active org solely from a JSON body field like { "orgId": "..." } without verifying membership. Attackers control bodies. Prefer:

• Header — e.g. X-Tenant-Id or X-Tenant-Slug, set by your own SPA after the user picks a workspace.
• Subdomain — acme.app.com maps to org slug acme (DNS and cookie care required).
• Path prefix — /o/:orgSlug/... when you want visible context.

Middleware order that works:

1. requireAuth — validate JWT (or session), attach req.user.
2. requireTenant — resolve org from header/slug/subdomain, load membership for req.user, attach req.tenant and req.membership.
3. Handlers — query with where: { orgId: req.tenant.id, ... }.

If membership is missing, return 403, not 404 of another tenant’s resource. For resource-by-id routes, fetch by id + orgId together so a guessed UUID from another org does not confirm existence.

The rule that prevents leaks

Always filter by orgId from req.tenant, never from an unchecked client field alone. Creating a note:

await prisma.note.create({
  data: {
    orgId: req.tenant.id,      // from middleware
    authorId: req.user.id,
    title: body.title,
    body: body.body,
  },
});

Listing notes:

await prisma.note.findMany({
  where: { orgId: req.tenant.id },
  orderBy: { createdAt: "desc" },
});

Updating or deleting: where: { id: noteId, orgId: req.tenant.id }. If zero rows update, respond with 404 for that tenant’s view of the world.

Invites without boiling the ocean

Store invites with org, email, role, random token, and expiry. Accepting an invite while authenticated should create a membership in a transaction and invalidate the token. Sending email can be a stub that logs the accept URL in development. You do not need a full customer identity platform to get membership right — you need a clear accept path and tests.

Tests that actually catch leaks

Unit tests of helpers are fine; the tests that matter seed two orgs, two users, and assert:

• User in org A cannot list org B’s notes (empty or 403 — pick a policy and stick to it).
• User in org A cannot update a note id that belongs to org B.
• User without membership cannot set X-Tenant-Id to a foreign org.
• Owner can invite; member cannot (if that is your rule).

Run these in CI against real Postgres (Docker). Mocking the database often mocks away the bug.

Optional next steps (when you feel pain)

• Postgres RLS — defense in depth; set app.current_org_id per transaction. Still keep app filters.
• Composite indexes — (orgId, createdAt), (orgId, id) for common paths.
• Audit log — org-scoped events for invites, role changes, deletes.
• Billing — attach Stripe customer to Organization, not User, for B2B.

Skip RLS on day one if it slows you down — but do not skip membership checks and orgId on every query.

Checklist

• Organization + Membership with unique (userId, orgId)
• Tenant resolved from header/slug/subdomain after auth
• Every tenant resource query includes orgId: req.tenant.id
• Resource-by-id lookups are composite
• Automated cross-tenant denial tests

Multi-tenancy is not a library you sprinkle on later. It is a habit: trust middleware, distrust the body, and let orgId appear in every WHERE clause that matters.

← Back to Singh DevTools · Webhook idempotency guide →