Multi-Tenant SaaS with PostgreSQL RLS, NestJS and Prisma

To isolate tenants in a NestJS and Prisma SaaS, turn on PostgreSQL row-level security and set the tenant ID inside each request's transaction.

On a 3PL warehouse system I built, one client seeing another client's stock is the worst bug the system can have. That app injects a clientId filter into every Prisma query through $extends, and the project rules name the one hole: raw SQL has to carry its own WHERE. Row-level security (RLS) closes that hole inside the database. Below is the setup I recommend for a shared-table SaaS, tested on PostgreSQL 18.6, Prisma 7.10 and NestJS 11 on 11 October 2026, with the real test output.

Key takeaways

• Filters in code are one layer, not a guarantee. One forgotten where, one raw query or one reporting script leaks data. RLS makes the database refuse.
• Connect as a role that can't skip RLS. Superusers and BYPASSRLS roles always skip it, and table owners skip it unless you add FORCE ROW LEVEL SECURITY.
• Set the tenant per transaction, not per connection. set_config('app.tenant_id', id, true) ends with the transaction, so a pooled connection can't carry one tenant into the next request.
• Wrap the setting in NULLIF(..., ''). After the first local set, the setting reads as an empty string, not NULL, and ''::uuid throws.
• Take the tenant from the verified token, never from a header or body the client controls.

Three ways to store tenants

• Database per tenant. Strongest isolation and easy per-customer backups, but every migration runs N times and connection counts grow with customers. Fits a few large enterprise customers.
• Schema per tenant. One database, one schema each. Less overhead than separate databases, but every schema change still runs once per tenant.
• Shared tables with a tenant_id column. One schema, one migration, cheap to add customers. Isolation depends entirely on every query filtering correctly, which is exactly what RLS enforces.

Most B2B SaaS products I'm asked to build start with shared tables. The rest of this post makes that option safe.

Step 1: a tenant column on every tenant-owned table

model Tenant {
  id       String    @id @default(uuid()) @db.Uuid
  name     String
  projects Project[]
}

model Project {
  id       String @id @default(uuid()) @db.Uuid
  tenantId String @map("tenant_id") @db.Uuid
  name     String
  tenant   Tenant @relation(fields: [tenantId], references: [id])

  @@index([tenantId])
  @@map("projects")
}

Child tables get their own tenant_id too, even when it seems derivable through a join. A policy can only check columns on its own row cheaply. Unique constraints should include it as well, for example @@unique([tenantId, slug]), so two customers can both have a project called "website".

Step 2: an app role that can't bypass RLS

PostgreSQL's documentation is explicit: superusers and roles with BYPASSRLS always bypass row security, and table owners normally do too. If your app connects as postgres or as the user that ran the migrations, every policy below is silently ignored. Create a separate login role for the app:

-- run once per database server, as an admin
CREATE ROLE app_user LOGIN PASSWORD 'use-a-long-random-one';

Don't put that in a Prisma migration. I tried, and prisma migrate dev failed with role "app_user" already exists: roles are cluster-wide, and the migration also runs against the shadow database on the same server. Keep the role in your server provisioning, and the grants and policies in migrations. Migrations keep running as the owner; the app's DATABASE_URL uses app_user.

Step 3: the RLS migration

Prisma's schema language has no syntax for policies, so create an empty migration with npx prisma migrate dev --create-only --name tenant_rls and write the SQL yourself:

GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO app_user;
GRANT SELECT ON "Tenant" TO app_user;

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;  -- applies to the owner too

CREATE POLICY tenant_isolation ON projects
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

USING decides which rows a query can see, update or delete. WITH CHECK decides which rows can be written, so nobody can insert a row for another tenant. With RLS on and no matching policy, PostgreSQL denies by default.

The true in current_setting(..., true) means "return NULL instead of an error if the setting doesn't exist". The NULLIF is for a quirk I confirmed in psql: once a session has set the value locally in any transaction, it reads back as an empty string afterwards, not NULL:

begin; select set_config('app.tenant_id', '1111...', true); commit;
select quote_literal(current_setting('app.tenant_id', true));  --> ''
select ''::uuid;
ERROR:  invalid input syntax for type uuid: ""

Without NULLIF, a query that forgot to set the tenant crashes with that error instead of returning nothing. With it, the comparison is NULL and the query returns zero rows.

Step 4: set the tenant inside a transaction

import { Injectable, OnModuleDestroy } from '@nestjs/common';
import { Prisma, PrismaClient } from '@prisma/client';
import { PrismaPg } from '@prisma/adapter-pg';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleDestroy {
  constructor() {
    // DATABASE_URL logs in as app_user: no superuser, no BYPASSRLS, not the owner.
    super({ adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }) });
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }

  // Every tenant query runs in here. The setting is transaction-local, so a
  // pooled connection can never carry one tenant's id into the next request.
  forTenant<T>(tenantId: string, fn: (tx: Prisma.TransactionClient) => Promise<T>): Promise<T> {
    return this.$transaction(async (tx) => {
      await tx.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`;
      return fn(tx);
    });
  }
}

Prisma has an official RLS client extension example, but its README says it's an example and not intended for production, and that explicit $transaction() calls may not work as intended because it wraps every query in its own batch transaction. An explicit forTenant() has neither problem: you can run several queries, including raw SQL, in the same tenant transaction.

Step 5: take the tenant from the token in NestJS

// tenant.decorator.ts: req.user is set by your JWT guard from a verified token
export const TenantId = createParamDecorator((_: unknown, ctx: ExecutionContext): string => {
  const tenantId = ctx.switchToHttp().getRequest().user?.tenantId;
  if (!tenantId) throw new UnauthorizedException();
  return tenantId;
});

// projects.controller.ts
@Controller('projects')
export class ProjectsController {
  constructor(private readonly db: PrismaService) {}

  @Get()
  list(@TenantId() tenantId: string) {
    // No tenant filter here on purpose: RLS adds it.
    return this.db.forTenant(tenantId, (tx) => tx.project.findMany({ orderBy: { name: 'asc' } }));
  }

  @Get(':id')
  async one(@TenantId() tenantId: string, @Param('id', ParseUUIDPipe) id: string) {
    const project = await this.db.forTenant(tenantId, (tx) => tx.project.findUnique({ where: { id } }));
    if (!project) throw new NotFoundException();
    return project;
  }

  @Post()
  create(@TenantId() tenantId: string, @Body() body: { name: string }) {
    return this.db.forTenant(tenantId, (tx) =>
      tx.project.create({ data: { name: body.name, tenantId } }),
    );
  }
}

I left the where: { tenantId } out of list() to prove the point. In a real app keep both: the app filter makes intent obvious to the next developer, and RLS catches the day someone forgets it. Answer 404, not 403, for another tenant's ID, so an attacker can't tell which IDs exist.

Step 6: prove it with a test

I seeded two tenants, Acme with two projects and Globex with one, then ran the app as app_user and attacked it over HTTP and directly in the database. Real output:

acme sees: acme-api, acme-website
globex sees: globex-crm
acme GET globex project by id -> 404
acme POST with tenantId=globex -> saved under acme
no token -> 401
200 concurrent requests, cross-tenant rows: 0
raw SQL inside forTenant(acme), no WHERE: 3 rows
same connection, outside forTenant: 0 rows
acme inserts row for globex -> new row violates row-level security policy for table "projects"
acme updateMany on globex row -> 0 rows changed
all assertions passed

The lines that matter most: raw SQL with no WHERE only counted Acme's three rows (two seeded plus the "sneaky" one), and a query on the same pooled connection after the transaction saw nothing. That last test used a pool of exactly one connection, so a leaked setting would have shown up. Put a test like this in CI; it's the proof a security review will ask for.

Production notes

• Transaction timeout. Prisma 7.10's interactive transactions time out after 5,000 ms by default. I hit The timeout for this transaction was 5000 ms with a deliberate 6-second query. Keep slow work, emails and HTTP calls outside forTenant().
• Connection poolers. PgBouncer's transaction pooling doesn't support session-level SET. A transaction-local setting lives and dies inside one transaction, which is why this pattern fits poolers. If you're already short on connections, read my "too many clients" guide.
• Background jobs must call forTenant() too. A worker that connects as app_user without it sees zero rows, which is the safe failure.
• Admin and reporting that genuinely need every tenant should use a separate role and connection string, outside the request path and audited.
• Indexes. The policy adds a tenant_id condition to every query, so index it, usually as the first column of composite indexes. Check slow queries with EXPLAIN ANALYZE as the app role, because the plan includes the policy.

Frequently asked questions

Does Prisma support PostgreSQL row-level security?

Prisma works with RLS, but its schema language can't declare policies, so you write them in a SQL migration. Set the tenant with set_config(..., true) inside an interactive transaction, as in the forTenant() helper above.

Is RLS enough on its own for tenant isolation?

No. It protects rows, not everything else: files in S3, cache keys, search indexes and logs also need tenant scoping. Treat RLS as the database layer of defence, alongside app-level filters and tests.

Why does my RLS policy return all rows?

Almost always because the app connects as a superuser, a role with BYPASSRLS, or the table owner without FORCE ROW LEVEL SECURITY. Check with SELECT current_user from the app and look at pg_class.relforcerowsecurity for the table.

Should I use schema-per-tenant instead?

Only if you have a small number of large customers that demand it, or need per-tenant restores. For many small and mid-size customers, shared tables with RLS are simpler to migrate, back up and monitor.

Can I add RLS to an existing SaaS?

Yes. Backfill tenant_id on every table, switch the app to a non-owner role, enable policies one table at a time, and run an isolation test after each step. Do it behind a rehearsal on a copy of production data first, like any migration without data loss.

Need a secure SaaS backend?

I build multi-tenant backends with NestJS, Prisma and PostgreSQL, including tenant isolation, roles and permissions, billing and the tests that prove it all works. See my web development services or tell me about your product.