4 min read Emadideen Ghannam

Multi-tenant Postgres: when row-level security earns its keep

Three options, one default. After 14 years of multi-tenant systems I default to RLS for SMB SaaS, with database-per-tenant as the escape hatch.

A side project I’m working on needs to support a few hundred customer accounts in a single Postgres without one tenant’s queries melting another’s. I have shipped multi-tenant systems for 14 years - retail SCM with dozens of client deployments, fleet platforms with multi-country tenants, and now a personal SaaS - and I have used every option in production.

The defaults I land on these days:

  • Row-level security (RLS) on a single shared database for SMB SaaS.
  • Database-per-tenant as the escape hatch for compliance customers.
  • Schema-per-tenant almost never.

Here is why.

What the three options actually mean

Schema-per-tenant gives each tenant their own Postgres schema (tenant_42.users, tenant_42.attendance). Same database, separate logical containers.

Database-per-tenant gives each tenant their own Postgres database, often on the same instance. Total data isolation, separate backups, separate migrations.

Single database with RLS keeps every tenant’s rows in the same tables, with a tenant_id column on every row, and a Postgres policy that filters every query by the current tenant.

Why RLS is my default

Three reasons.

It is one schema to migrate. With schema-per-tenant or db-per-tenant, every migration runs N times. On an enterprise SCM platform a previous employer ran one migration across forty client schemas; the bash script that orchestrated it became its own piece of infrastructure. With RLS, one migration, one apply, done.

It is one query plan to tune. Postgres caches plans per session. When you have one schema, you have one set of plans to reason about. When you have forty, you have forty.

It is the cheapest one to operate. One backup. One pg_stat_statements view. One connection pool. One read replica. The complexity of “which tenant is on which DB” is gone.

The tradeoff is real: noisy neighbours. A tenant with bad queries can hurt others on the same instance. RLS by itself does not solve that. You still need query timeouts, connection limits per tenant, and pg_stat_statements monitoring. But that is a much smaller problem than “operate forty Postgres instances.”

The RLS policy

The simplest version, on an attendance table:

ALTER TABLE attendance ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON attendance
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

USING filters reads. WITH CHECK blocks writes that would land in another tenant. app.tenant_id is a session-level GUC (grand unified configuration) you set per request.

The footgun

The whole thing falls apart if you forget to set app.tenant_id. Postgres does not warn you. The query just returns nothing - or worse, if you forget on a write, returns a row.

I have never seen a junior remember to set the GUC every time. The fix is to make it un-forgettable in the stack, not in the developer’s head.

Make the GUC un-forgettable

In NestJS with Prisma, I use a request-scoped middleware that sets the GUC on every query the request triggers:

// tenant.middleware.ts
import { Injectable, NestMiddleware } from '@nestjs/common';
import { ClsService } from 'nestjs-cls';
import { PrismaService } from './prisma.service';

@Injectable()
export class TenantMiddleware implements NestMiddleware {
  constructor(private cls: ClsService, private prisma: PrismaService) {}

  use(req: any, _res: any, next: () => void) {
    const tenantId = req.user.tenantId;
    this.cls.set('tenantId', tenantId);
    next();
  }
}

// prisma.service.ts
this.$use(async (params, next) => {
  const tenantId = cls.get('tenantId');
  if (!tenantId) throw new Error('No tenant in context');
  await this.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`;
  return next(params);
});

The pattern: ClsService binds the tenant to the async context, Prisma middleware sets the GUC before every query. A query without a tenant context throws loudly, instead of silently returning empty.

The same pattern works in raw node-postgres with a transaction wrapper, or in .NET with EF interceptors.

When db-per-tenant wins

I switch off RLS in three cases:

  • Regulated workloads where a regulator demands separate physical storage (some healthcare, some defence, some banking).
  • Contractual isolation where the contract explicitly says “no shared infrastructure.”
  • Tenant-specific extensions or schema - one customer wants a custom column or a custom table that the others should never see.

For SMB SaaS aimed at five-figure ARR customers, none of those apply. My side projects use RLS. The B2B SaaS I lead at $work uses RLS. The escape hatches stayed empty.

Take

RLS gets a bad reputation because the GUC is invisible. Make it un-forgettable in your stack and the rest is engineering.

Edit on GitHub