Building Secure B2B Apps: Keeping Client Data Separate in PostgreSQL

When building a Business-to-Business (B2B) SaaS application, the most critical architectural decision you will make is how to handle multi-tenancy — the problem of safely serving multiple independent companies from a single product.

If Company A and Company B both subscribe to your platform, their data must be absolutely isolated. A bug in your API that lets Company A glimpse Company B's financial records is not just a bad day — it is a regulatory incident, a potential GDPR breach with fines up to 4% of global annual turnover, and an extinction-level trust event for your business. In Verizon's 2023 Data Breach Investigations Report, misconfigured access controls accounted for 26% of all data breaches. Multi-tenant logic errors are a leading source of those misconfigurations.

As the architect of this system, you must choose how to partition tenant data inside your infrastructure. The decision falls into three broad camps, each with dramatically different cost, complexity, and security trade-off profiles.

The Three Multi-Tenancy Models

Model 1 — Silo: One Database Per Tenant

Every tenant gets their own physically isolated database instance.

How it works:
Your infrastructure provisioning code (Terraform, Pulumi, etc.) spins up a fresh PostgreSQL instance the moment a new company signs up. Their connection string is stored in your master routing database, and your API layer routes every request to the correct isolated instance.

Company A → postgres://tenant-a.db.internal:5432/main
Company B → postgres://tenant-b.db.internal:5432/main
Company C → postgres://tenant-c.db.internal:5432/main

Pros:

• Absolute cryptographic isolation. It is physically impossible for Company A's query to touch Company B's data because they do not share a database, a schema, or a connection string.
• Individual tenant backups and restores are trivial. If Company A's data is corrupted, you restore one database, not a multi-tenant monolith.
• Per-tenant performance tuning: you can give high-value enterprise clients dedicated, higher-spec database instances.
• Compliance simplicity: when a GDPR "right to erasure" request comes in, you drop one database.

Cons:

• Enormous operational complexity. 500 clients means 500 PostgreSQL instances to monitor, patch, back up, and migrate.
• Schema migrations become a distributed systems problem. A simple ALTER TABLE invoices ADD COLUMN status TEXT requires a migration runner that safely applies the change to every tenant database, handles failures gracefully, and reports on partial rollouts.
• Cloud cost scales with tenant count, even if most tenants are inactive.

When to choose Silo:
Regulated industries (financial services, healthcare) with contractual data residency requirements. Enterprise deals where tenants are demanding dedicated infrastructure as a procurement condition. Typically only viable once you have the DevOps team to support it.

Model 2 — Bridge: Shared Database, Separate Schemas

All tenants share one database server, but each gets their own PostgreSQL schema.

-- Company A's tables
tenant_a.invoices
tenant_a.users
 
-- Company B's tables
tenant_b.invoices
tenant_b.users

Pros:

• One database server to manage, one backup to run.
• Schema-level isolation is stronger than row-level filtering in application code.
• Easier to migrate individual schemas.

Cons:

• PostgreSQL schema creation per tenant means connection pool management becomes complex (each tenant needs its own search_path).
• Still requires orchestrated schema migrations across all tenant schemas.
• Most ORM frameworks (Prisma, Drizzle) have limited or awkward multi-schema support.

Model 3 — Pool: Shared Database, Shared Tables (with RLS)

All tenants share the same database and the same tables. Every row is tagged with a tenant_id column. Security is enforced at the database engine level via Row-Level Security (RLS).

This is the most operationally lean model — and with RLS configured correctly, it is also extremely secure.

For most early-to-mid stage B2B SaaS companies, Pool + RLS is the pragmatic, cost-effective, and battle-tested choice. It is what Supabase, Neon, and most major multi-tenant platforms are built on.

Deep Dive: PostgreSQL Row-Level Security

RLS is a PostgreSQL feature (available since version 9.5) that allows you to define policies on tables. A policy is a SQL expression that PostgreSQL evaluates silently on every row, for every query, before returning results.

Even if a developer writes the most catastrophically broken query imaginable:

-- Buggy query in application code
SELECT * FROM invoices;

PostgreSQL will intercept the request, evaluate the policy, and silently rewrite it to something equivalent to:

SELECT * FROM invoices WHERE tenant_id = '{{current_session_tenant_id}}';

The application never sees data it should not.

Setting Up RLS Step by Step

Step 1 — Add tenant_id to every sensitive table:

-- When creating new tables
CREATE TABLE invoices (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  amount      NUMERIC(12, 2) NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ DEFAULT now()
);
 
-- For existing tables
ALTER TABLE invoices ADD COLUMN tenant_id UUID NOT NULL REFERENCES tenants(id);
CREATE INDEX idx_invoices_tenant_id ON invoices (tenant_id);

The index on tenant_id is not optional. Without it, every query becomes a full sequential scan filtered by the RLS policy — catastrophic at scale.

Step 2 — Enable RLS on the table:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
 
-- Force RLS even for table owner (important — by default, table owners bypass RLS)
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

Step 3 — Create the isolation policy:

-- SELECT policy: Users can only read their own tenant's rows
CREATE POLICY tenant_isolation_select ON invoices
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
 
-- INSERT policy: Users can only insert rows for their own tenant
CREATE POLICY tenant_isolation_insert ON invoices
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);
 
-- UPDATE policy: Users can only update their own tenant's rows
CREATE POLICY tenant_isolation_update ON invoices
  FOR UPDATE
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);
 
-- DELETE policy
CREATE POLICY tenant_isolation_delete ON invoices
  FOR DELETE
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

Notice we use separate policies per operation (SELECT, INSERT, UPDATE, DELETE). This gives you granular control — you might want your analytics service role to be able to SELECT across all tenants, while your application role is strictly restricted.

Step 4 — Create a restricted application role:

-- Create a role for your API application
CREATE ROLE api_user LOGIN PASSWORD 'your-strong-password';
 
-- Grant it access to the tables — but NOT as a superuser
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO api_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON tenants TO api_user;
GRANT USAGE ON SCHEMA public TO api_user;
 
-- Superusers bypass RLS — ensure api_user is NOT a superuser
-- (it shouldn't be by default, but explicitly verify this)

Wiring It Up: The Node.js Middleware Pattern

The RLS policy reads from current_setting('app.current_tenant_id'). Something has to set that. This is the job of a database middleware layer that wraps every request in a short-lived transaction and injects the tenant context.

Here is a clean implementation using pg (node-postgres):

// lib/db/tenant-context.ts
import { Pool, PoolClient } from "pg";
 
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Keep connections bounded
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});
 
/**
 * Execute a query within a tenant-scoped transaction.
 * The RLS policy fires automatically based on the session variable.
 */
export async function withTenantContext<T>(
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
 
  try {
    await client.query("BEGIN");
 
    // Inject tenant context into this transaction's session
    await client.query(`SET LOCAL app.current_tenant_id = $1`, [tenantId]);
 
    // Run the actual database work — RLS does the rest
    const result = await fn(client);
 
    await client.query("COMMIT");
    return result;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

Critical: Use SET LOCAL, not SET. SET LOCAL scopes the variable to the current transaction only. When the transaction ends (COMMIT or ROLLBACK), the variable is cleared. Using SET without LOCAL would persist the tenant context across connection pool reuse, which in a Pool model is a data leakage vector.

Now wire this into your API middleware:

// middleware/tenant.ts (Express example)
import { Request, Response, NextFunction } from "express";
import jwt from "jsonwebtoken";
import { withTenantContext } from "../lib/db/tenant-context";
 
export function tenantMiddleware(
  req: Request,
  res: Response,
  next: NextFunction,
) {
  const token = req.headers.authorization?.replace("Bearer ", "");
 
  if (!token) {
    return res.status(401).json({ error: "Unauthorised" });
  }
 
  try {
    const payload = jwt.verify(token, process.env.JWT_SECRET!) as {
      sub: string;
      tenant_id: string;
    };
 
    // Attach tenant context to request for downstream usage
    req.tenantId = payload.tenant_id;
    next();
  } catch {
    return res.status(401).json({ error: "Invalid token" });
  }
}
 
// Usage in a route
app.get("/invoices", tenantMiddleware, async (req, res) => {
  const invoices = await withTenantContext(req.tenantId, async (client) => {
    // This SELECT is automatically filtered by RLS — no WHERE clause needed
    const { rows } = await client.query(
      "SELECT * FROM invoices ORDER BY created_at DESC",
    );
    return rows;
  });
 
  res.json(invoices);
});

Even if a developer completely forgets to add tenant filtering to a query, the RLS policy has their back.

Using RLS with Prisma

If you are using Prisma as your ORM, the pattern works cleanly with Prisma's $executeRaw method to set the session variable before queries:

// lib/db/prisma-tenant.ts
import { PrismaClient } from "@prisma/client";
 
const prisma = new PrismaClient();
 
export async function withPrismaTenantContext<T>(
  tenantId: string,
  fn: (
    tx: Omit<
      PrismaClient,
      "$connect" | "$disconnect" | "$on" | "$transaction" | "$use" | "$extends"
    >,
  ) => Promise<T>,
): Promise<T> {
  return prisma.$transaction(async (tx) => {
    // Set the tenant context for this transaction
    await tx.$executeRaw`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`;
 
    // The `true` flag in set_config = scoped to transaction (equivalent to SET LOCAL)
    return fn(tx);
  });
}
 
// Usage:
const invoices = await withPrismaTenantContext(tenantId, (tx) => {
  return tx.invoice.findMany({ orderBy: { createdAt: "desc" } });
});

Using RLS with Supabase

Supabase is built entirely on PostgreSQL RLS. If you are using Supabase for your backend, RLS is first-class. The auth.uid() function returns the current authenticated user's UUID, and Supabase automatically sets the JWT claims in the session:

-- Supabase-native RLS policy using the built-in auth system
CREATE POLICY "Users can only view their organisation's invoices"
  ON invoices FOR SELECT
  USING (
    tenant_id = (
      SELECT organisation_id FROM profiles WHERE id = auth.uid()
    )
  );

For custom B2B apps that manage their own auth (rather than Supabase Auth), you can pass your tenant ID via the request.jwt.claims custom claim and read it with auth.jwt() ->> 'tenant_id'.

Schema Design Patterns for Multi-Tenancy

A clean multi-tenant schema puts tenant_id at the top of the hierarchy and cascades it everywhere:

-- Central tenants table
CREATE TABLE tenants (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  slug        TEXT NOT NULL UNIQUE,   -- used in subdomains: acme.yourapp.com
  plan        TEXT NOT NULL DEFAULT 'starter',
  created_at  TIMESTAMPTZ DEFAULT now()
);
 
-- Users belong to a tenant
CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  email       TEXT NOT NULL,
  role        TEXT NOT NULL DEFAULT 'member',   -- admin | member | viewer
  UNIQUE(tenant_id, email)
);
 
-- Everything downstream inherits tenant_id
CREATE TABLE invoices (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  created_by  UUID NOT NULL REFERENCES users(id),
  amount      NUMERIC(12, 2),
  status      TEXT DEFAULT 'draft'
);
 
-- Composite indexes: always put tenant_id FIRST for query efficiency
CREATE INDEX idx_users_tenant      ON users     (tenant_id, email);
CREATE INDEX idx_invoices_tenant   ON invoices  (tenant_id, created_at DESC);

The composite index (tenant_id, created_at DESC) is important: since RLS always filters by tenant_id first, putting it at the front of the index means PostgreSQL can use an index scan to immediately narrow results to that tenant before sorting — rather than scanning all rows and filtering.

Managing Connection Pools with PgBouncer

Multi-tenant Pool deployments have a specific scaling challenge: serverless environments.

AWS Lambda, Vercel Functions, and Cloudflare Workers spin up and down in milliseconds. Each invocation establishes a fresh TCP connection to PostgreSQL. At modest scale, this is fine. At peak — say, 5,000 concurrent API requests — you are trying to open 5,000 simultaneous connections to a database with a max_connections default of 100. The database crashes.

PgBouncer is the standard solution. It acts as a lightweight proxy that sits between your application and PostgreSQL:

[ 5,000 Lambda invocations ]
          ↓
    [ PgBouncer ]          ← Accepts all 5,000 connections
          ↓                   Queues them in lightweight user-space
[ PostgreSQL (50 connections) ] ← Only 50 real connections open

PgBouncer operates in three modes:

For RLS with SET LOCAL, transaction pooling is the correct mode. SET LOCAL is transaction-scoped, so it correctly resets when the transaction ends — even when the underlying connection returns to the pool.

A minimal PgBouncer config for a multi-tenant Node.js app:

# pgbouncer.ini
[databases]
myapp = host=postgres-primary port=5432 dbname=myapp
 
[pgbouncer]
listen_port    = 6432
listen_addr    = 0.0.0.0
auth_type      = scram-sha-256
auth_file      = /etc/pgbouncer/userlist.txt
pool_mode      = transaction       ; ← Critical for RLS compatibility
max_client_conn = 10000            ; How many connections PgBouncer accepts
default_pool_size = 50             ; How many real Postgres connections to keep open
min_pool_size  = 10
reserve_pool_size = 5
server_idle_timeout = 600
log_connections = 1

For modern cloud deployments, Supabase Supavisor and Neon's connection pooler are managed PgBouncer alternatives that handle this for you.

Verifying Policies Are Working

Never trust that RLS is configured correctly without testing it explicitly. Here is how to verify your policies in psql by impersonating an application-level role:

-- Switch to the restricted application user
SET ROLE api_user;
 
-- Simulate a request from Company A
SET LOCAL app.current_tenant_id = 'aaaaaaaa-0000-0000-0000-000000000001';
SELECT COUNT(*) FROM invoices;   -- Should return ONLY Company A's invoice count
 
-- Simulate a request from Company B
SET LOCAL app.current_tenant_id = 'bbbbbbbb-0000-0000-0000-000000000002';
SELECT COUNT(*) FROM invoices;   -- Should return ONLY Company B's invoice count
 
-- Attempt a cross-tenant query (this should return 0 rows, not error)
SELECT * FROM invoices WHERE tenant_id = 'aaaaaaaa-0000-0000-0000-000000000001';
-- Returns 0 rows for Company B ✅

Write these as automated integration tests that run in CI:

// tests/rls.test.ts
describe("RLS Tenant Isolation", () => {
  it("should not leak Company A data to Company B sessions", async () => {
    // Insert an invoice for Tenant A
    await withTenantContext(TENANT_A_ID, async (client) => {
      await client.query(
        `INSERT INTO invoices (tenant_id, amount) VALUES ($1, 500.00)`,
        [TENANT_A_ID],
      );
    });
 
    // Query as Tenant B — should get zero results
    const leaked = await withTenantContext(TENANT_B_ID, async (client) => {
      const { rows } = await client.query("SELECT * FROM invoices");
      return rows;
    });
 
    expect(leaked).toHaveLength(0);
  });
});

Performance Benchmarks

A common concern with RLS is query overhead — after all, PostgreSQL is evaluating an extra expression on every row. In practice, the overhead is negligible when indexes are correct.

Benchmark on a 10-million-row invoices table with 5,000 tenants (~2,000 rows per tenant on average, on a db.t3.medium RDS instance):

The overhead is largely absorbed by the tenant_id index. Without the index, the same queries on an unindexed table can be 10–100x slower. Index your tenant_id columns — it is not optional.

The Bottom Line

Multi-tenant data isolation is not a feature to bolt on after launch. It is a foundational architectural constraint that shapes every table, every query, and every middleware decision from day one.

For most B2B SaaS companies, the Pool + RLS model — all tenants sharing a single PostgreSQL database, protected by database-engine-level row filtering — strikes the right balance:

• Operationally lean: one database, one backup, one migration to run.
• Mathematically secure: isolation enforced at the engine level, not the application level.
• Economically scalable: a single db.t3.large RDS instance can serve thousands of tenants before you need to think about sharding.

The key moves are:

1. tenant_id on every sensitive table, indexed, with a NOT NULL constraint.
2. ENABLE ROW LEVEL SECURITY + FORCE ROW LEVEL SECURITY on every sensitive table.
3. Separate policies for SELECT, INSERT, UPDATE and DELETE.
4. SET LOCAL app.current_tenant_id inside a transaction — never SET.
5. PgBouncer in transaction pooling mode to protect the connection limit.
6. Automated RLS breach tests running in every CI pipeline.

Build the cage at the database level. Then let your application developers write clean code without the constant fear that a missing WHERE clause will make the front page of Hacker News.