Multi-Tenant SaaS on PostgreSQL: Row-Level Security, Schema-per-Tenant, and the Bug That Leaked an Invoice

Multi-tenant applications require careful consideration of data isolation at every layer of the architecture. Even when database-level protections such as PostgreSQL Row-Level Security (RLS) are implemented correctly, other components, including caching mechanisms, introduce additional considerations.

In this article, I'll explore a practical multi-tenant architecture scenario, explain how tenant isolation can be compromised through improperly structured cache keys, and discuss approaches to strengthening data isolation across the application stack.

We'll also look at implementation patterns, defensive testing strategies, and lessons that can help developers build more secure and reliable SaaS applications.

The setup

We build a B2B SaaS product. Each customer company is a tenant, and in our code the tenant is called orgId. Here is the stack:

• API: NestJS.
• Database: PostgreSQL. All tenants share the same tables, and every tenant-owned table has a tenant_id column.
• ORM: Prisma. An ORM is the library that turns code into SQL.
• Connection pooler: PgBouncer in transaction mode. A pooler lets many app connections share a small number of real database connections. In transaction mode, your app borrows a real connection for one transaction, then hands it back for the next client.
• Cache: Redis, used for invoice data that is slow to build.

Two details matter for everything that follows.

First, under PgBouncer transaction pooling, a plain SET is dangerous. The value stays on the server connection after your transaction ends. The next client that borrows that connection gets your setting, including your tenant. So we set the tenant once per transaction, never per session.

Second, our app connects to PostgreSQL as a role that does not own the tables, is not a superuser, and does not have BYPASSRLS. Migrations run as a separate owner role. Superusers and BYPASSRLS roles skip row security completely, and table owners skip it too unless you force it on.

RLS vs schema-per-tenant: how we decided

There are three common ways to keep tenants apart in PostgreSQL: a database per tenant, a schema per tenant, or shared tables with a tenant column and Row-Level Security (RLS). A database per tenant was too heavy for us, so the real choice was between the other two.

Look at the two rows where neither side wins: noisy neighbors and data residency. Both options share one server in one place. If a customer needs its own hardware or its own country, you need a separate database. That is a different decision.

We chose RLS for three reasons. We expect our tenant count to keep growing past what schema-per-tenant handles comfortably. None of our tenants needs a different table structure; they differ by settings, not by schema. And we wanted one migration path and one connection pool, with no per-request search_path switch that could go wrong.

RLS has one gap we planned for from the start. Unique, primary key, and foreign key checks in PostgreSQL ignore row security. So a "duplicate key" error can tell a user that another tenant already has a row with that value. Our answer is to put tenant_id in every primary key, unique constraint, and foreign key. Then those checks only compare rows inside one tenant.

How the leak happened

Most invoice systems number invoices per customer. Tenant A has an invoice numbered 1001. Tenant B also has an invoice numbered 1001. Inside the database this is fine, because the real key is the tenant plus the number.

We added caching to an invoice endpoint. The cache key was built from the invoice identifier alone. It looked like this:

invoice:{invoiceNumber}

Here is the sequence that followed:

1. A user in tenant A opens invoice 1001. The cache has nothing, so the app asks PostgreSQL.
2. RLS filters the query to tenant A's rows. Correct. The app stores the result under invoice:1001.
3. A user in tenant B opens their own invoice 1001. The cache finds invoice:1001 and returns tenant A's invoice.
4. PostgreSQL never sees step 3. There is nothing for RLS to filter.

That is the whole bug. One field missing from one string.

Why RLS could not stop it

RLS is a feature of PostgreSQL tables. It filters rows when a query reaches the database. It knows nothing about Redis, queue messages, files in object storage, search indexes, or objects held in Node.js memory.

Every one of those is a separate store, and every one needs its own tenant boundary. When you add a cache, you are adding a second copy of tenant data that lives outside your strongest protection. We had protected the first copy very well and the second copy not at all.

Why review missed it

We had a code review checklist item that said "include the tenant in cache keys." The key was built by hand in a new service, and the reviewer missed it, and so did the author.

A checklist is a reminder. It is not a control. If the safe way and the unsafe way take the same effort to type, someone will eventually type the unsafe one. The fix had to make the unsafe way impossible, not just discouraged.

The fix

We made three changes.

1. One helper builds every key. All cache keys now come from a function called tenantKey(). It reads the tenant from the current request and throws an error if there is none. You cannot build a cache key without a tenant, so forgetting the tenant becomes a crash during testing instead of a leak in production. We use the same rule for queue job data, file paths (tenants/{tenantId}/...), and search documents.

2. The tenant comes from the verified login token only. We read orgId from the JWT after the auth guard has checked its signature. We never trust a tenant ID from a header, a query string, or a request body, because the client controls all of those. The tenant is stored with nestjs-cls, which uses Node.js AsyncLocalStorage. In simple words, it keeps a value attached to one request as that request moves through async code. We prefer it over NestJS request-scoped providers, which force every dependent class to be rebuilt per request and slow things down.

3. The database wrapper sets the tenant inside every transaction. Every ORM call runs inside a transaction that first runs set_config('app.tenant_id', $1, true). The true means "for this transaction only." We use this function instead of SET LOCAL for two reasons. SET LOCAL cannot take a bind parameter like $1, so you end up pasting the tenant ID into the SQL text. And SET LOCAL outside a transaction block only prints a warning and does nothing, which is a quiet way to lose your tenant filter.

Our policies read the tenant with NULLIF(current_setting('app.tenant_id', true), '')::uuid. If no tenant is set, this becomes null, the comparison fails, and the query returns zero rows. A missing tenant never means "show everything," and it never throws an error that might print someone else's data.

While we were in there, we checked the rest of the setup: FORCE ROW LEVEL SECURITY on every tenant table, both USING and WITH CHECK on every policy, security_invoker = true on every view (otherwise a view runs with its owner's rights and skips the caller's policies), and tenant_id as the first column in our indexes, confirmed with EXPLAIN ANALYZE.

The regression test

A fix without a test has an expiry date. Our regression test rebuilds the exact shape of the bug: two tenants, the same invoice number, one cache. Tenant A reads invoice 1001 and fills the cache. Tenant B reads its own invoice 1001. The test checks that tenant B gets tenant B's invoice and that the two cache keys are different.

Around it, four more checks run in CI against a real PostgreSQL behind a real PgBouncer in transaction mode:

1. A cross-tenant read returns 0 rows, even when the query asks for the other tenant by ID.
2. A query with no tenant set returns 0 rows.
3. 200 parallel requests from two tenants, through a pool of 4 connections, never see each other's rows. Requests with no tenant mixed in still get 0 rows, which proves no setting sticks to a pooled connection.
4. The cache helper throws when there is no tenant.

We also added a lint rule that fails the build when code calls Redis get or set with a key that did not come from tenantKey().

The code

Table names are generic. Change them to fit your project.

-- Migration. Run as the OWNER role, never as the app role.

CREATE ROLE app_user LOGIN NOSUPERUSER NOBYPASSRLS;  -- password from your secret store

  CREATE TABLE tenants (
  id   uuid PRIMARY KEY,
  name text NOT NULL
);
 
CREATE TABLE customers (
  tenant_id uuid NOT NULL REFERENCES tenants (id),
  id        uuid NOT NULL DEFAULT gen_random_uuid(),
  PRIMARY KEY (tenant_id, id)
);
 
CREATE TABLE invoices (
  tenant_id   uuid   NOT NULL REFERENCES tenants (id),
  id          uuid   NOT NULL DEFAULT gen_random_uuid(),
  customer_id uuid   NOT NULL,
  number      text   NOT NULL,
  total_cents bigint NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, id),
  UNIQUE (tenant_id, number),                -- unique only inside a tenant
  FOREIGN KEY (tenant_id, customer_id)       -- no links across tenants
    REFERENCES customers (tenant_id, id)
);

CREATE INDEX invoices_tenant_created_idx ON invoices (tenant_id, created_at DESC);

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers FORCE  ROW LEVEL SECURITY;
ALTER TABLE invoices  ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices  FORCE  ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON customers FOR ALL
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);
 CREATE POLICY tenant_isolation ON invoices FOR ALL
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);
 
 -- Views run with the caller's rights, so the caller's policies apply (PostgreSQL 15+).
 
CREATE VIEW invoice_totals WITH (security_invoker = true) AS
  SELECT tenant_id, customer_id, sum(total_cents) AS total_cents
  FROM invoices GROUP BY tenant_id, customer_id;
  
GRANT SELECT, INSERT, UPDATE, DELETE ON customers, invoices TO app_user;

GRANT SELECT ON invoice_totals TO app_user;

// tenant.interceptor.ts: tenant comes from the verified JWT only.
@Injectable()
export class TenantInterceptor implements NestInterceptor {
  constructor(private readonly cls: ClsService) {}
   intercept(ctx: ExecutionContext, next: CallHandler) {
    const orgId = ctx.switchToHttp().getRequest().user?.orgId; // set by the auth guard
    if (typeof orgId === 'string' && UUID_RE.test(orgId)) {
      this.cls.set('tenantId', orgId);
    }
    return next.handle(); // no tenant: DB returns 0 rows, cache helper throws
  }
}

 // tenant-db.ts: Prisma extension wraps every query in a transaction with set_config.
const tenantScope = (cls: ClsService) =>
  Prisma.defineExtension((prisma) =>
    prisma.$extends({
      query: {
        $allModels: {
          async $allOperations({ args, query }) {
            const tenantId = cls.get<string>('tenantId') ?? '';
            const [, result] = await prisma.$transaction([
              prisma.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`,
              query(args),
            ]);
            return result;
          },
        },
      },
    }),
  );
// TypeORM users: open one QueryRunner, startTransaction, run the same
// set_config with [tenantId] as a parameter, then do the work and commit.
 // tenant-key.ts: no tenant, no key.
export class MissingTenantError extends Error {}
 export function tenantKey(cls: ClsService, ...parts: Array<string | number>): string {
  const tenantId = cls.get<string>('tenantId');
  if (!tenantId) throw new MissingTenantError('No tenant, no cache key');
  return ['t', tenantId, ...parts].join(':');
}
// Before: redis.get(`invoice:${invoiceNumber}`)
// After:  redis.get(tenantKey(this.cls, 'invoice', invoiceNumber))

// tenant-isolation.spec.ts: runs against PostgreSQL through PgBouncer (transaction mode).
test('cross-tenant read returns 0 rows', async () => {
  const r = await inTx(app, A, (q) => q('SELECT * FROM invoices WHERE tenant_id = $1', [B]));
  expect(r.rowCount).toBe(0);
});
 test('missing tenant returns 0 rows', async () => {
  const r = await inTx(app, null, (q) => q('SELECT count(*)::int AS n FROM invoices'));
  expect(r.rows[0].n).toBe(0);
});
 test('200 parallel requests through PgBouncer do not leak', async () => {
  const jobs = Array.from({ length: 200 }, (_, i) => {
    const t = i % 3 === 0 ? null : i % 2 ? A : B;
    return inTx(app, t, (q) => q('SELECT tenant_id FROM invoices')).then((r) => ({ t, r }));
  });
  for (const { t, r } of await Promise.all(jobs)) {
    if (t === null) expect(r.rowCount).toBe(0);
    else r.rows.forEach((row) => expect(row.tenant_id).toBe(t));
  }
});
 test('cache helper throws without tenant, and keys differ per tenant', () => {
  const cls = new ClsService(new AsyncLocalStorage());
  cls.run(() => {
    expect(() => tenantKey(cls, 'invoice', '1001')).toThrow(MissingTenantError);
    cls.set('tenantId', A);
    const keyA = tenantKey(cls, 'invoice', '1001');
    cls.set('tenantId', B);
    expect(tenantKey(cls, 'invoice', '1001')).not.toBe(keyA); // the exact bug shape
  });
});

inTx is a small helper that borrows a connection, runs BEGIN, calls set_config when a tenant is given, runs the query, and commits.

What I would do differently

I would write tenantKey() before the first cache call, not after the first leak. The rule "anything stored outside PostgreSQL carries a tenant ID" should be a function that throws from day one, not a line in a checklist.

I would make the two-tenant test a template that every new endpoint has to fill in: two tenants, the same human-readable ID, one shared store. Our bug needed exactly that shape, and none of our tests had it.

I would keep one list of every place tenant data lives: PostgreSQL, the cache, the queue, file storage, the search index, and memory inside the app. Next to each one, I would write what enforces the tenant boundary there. For PostgreSQL the answer was RLS. For the cache, the honest answer was "someone remembering."

And I would cache less. Every cache is a second copy of customer data with its own rules. Before adding one, measure whether the speed gain is worth one more place to protect.

What I am not sure about

I am not sure our Prisma wrapper is the right long-term design. It wraps every query in its own small transaction, which costs an extra round trip each time, and work that needs several queries in one transaction needs extra care. The @nestjs-cls/transactional plugin with its Prisma adapter may be a cleaner fit. We have not measured it yet. Newer Prisma major versions also change the transaction API, so this code will need changes when we upgrade.

I am not sure FORCE ROW LEVEL SECURITY on every table is free. It means our owner role sees zero rows during data fixes unless the script sets a tenant first. That is the safe default, but it has already surprised one migration.

I am not sure how our policies behave at ten times our current data size. Our query plans look fine today. I have not tested at that scale.

And I do not know whether shared tables will hold up the day a customer asks for their data to stay in one country. That needs a separate database, and RLS cannot answer that question for us.

A short checklist before you add a cache

• Does every key include the tenant ID, built by one helper that refuses to run without it?
• Where does the tenant ID come from? It should be the verified token, never the client.
• Is there a test with two tenants using the same human-readable ID?
• If this cache served the wrong data for an hour, how would you find out?