PostgreSQL Row-Level Security for SaaS — Complete Implementation Guide

PostgreSQL row-level security for SaaS is the safety net most shared-database multi-tenant applications don't know they're missing until a query without a WHERE clause ships to production. The pattern is always the same: a developer writes SELECT * FROM invoices WHERE id = $1 instead of SELECT * FROM invoices WHERE tenant_id = $1 AND id = $1, and suddenly Tenant A is looking at Tenant B's billing history. Every multi-tenant SaaS team in history has believed their code was careful enough — right up until it wasn't.

The short answer: PostgreSQL Row-Level Security (RLS) lets you define policies on a table that PostgreSQL automatically applies to every query, regardless of what your application code sends. You enable RLS on the table, write a policy that compares each row's tenant_id to a session variable set per request, and the database enforces tenant isolation even when a developer forgets the WHERE clause. It is the cheapest insurance policy your SaaS database will ever have.

We originally covered RLS briefly in our multi-tenant SaaS database architecture guide, where we argued for shared schema plus RLS over fancier isolation models. This post is the full implementation playbook — the policies, the NestJS middleware, the indexing strategy, the test patterns, and the rollout plan — for teams who want to actually ship it.

What PostgreSQL Row-Level Security Actually Does

RLS attaches a policy expression to a table that acts as an invisible WHERE clause on every query. You enable it, define the rule, and PostgreSQL handles the rest. The database refuses to hand over rows that don't satisfy the policy, even if your ORM generates a query that would otherwise return them.

Here is the minimum viable setup:

1CREATE TABLE invoices (
2    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3    tenant_id   UUID NOT NULL,
4    amount      INTEGER NOT NULL,
5    status      TEXT NOT NULL DEFAULT 'pending',
6    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
7);
8
9ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
10
11CREATE POLICY tenant_isolation ON invoices
12    USING (tenant_id = current_setting('app.current_tenant')::UUID);

That ALTER TABLE command is the moment the default flips from "anyone with table access can see everything" to "nobody can see anything until a policy says so." The USING clause is the policy expression — it filters which rows are visible for SELECT, UPDATE, and DELETE. A corresponding WITH CHECK clause controls which rows can be created (INSERT or UPDATE). If you omit WITH CHECK, PostgreSQL copies the USING expression, which is fine for simple cases but dangerous for write-boundary enforcement as we'll cover later. The official PostgreSQL row security documentation spells out the permissive-versus-restrictive policy rules in full, and it's worth reading before you write your first policy in anger.

This is the database equivalent of a seatbelt — you don't notice it until the moment your application takes a hard turn, and then it's the only thing keeping you from going through the windshield.

The Problem RLS Solves That Application Code Cannot

Application-level tenant filtering — adding WHERE tenant_id = ? to every query — works perfectly until someone forgets. And people do forget. Not because they're bad engineers, but because a seven-endpoint feature request turns into twelve endpoints, and nobody reviews every generated query from a reporting library at 2am during a deployment.

The vulnerability isn't malice. It's the everyday variability of source code, as one AWS engineer put it. RLS moves the guarantee from "every developer remembers every time" to "the database enforces it regardless." That is a fundamentally different class of safeguard.

The average data breach now costs $4.88 million (IBM Cost of a Data Breach 2024). A cross-tenant leak in a B2B product is both a breach and a "your competitor just saw your pricing model" conversation with legal counsel. RLS is not expensive relative to that phone call.

Step 1: Set Up the App Role and Disable Bypass

Before writing policies, you need a database role for your application that cannot bypass RLS. PostgreSQL's default is that table owners and superusers skip RLS entirely — your application should be neither.

1CREATE ROLE app_user LOGIN PASSWORD 'secure_password' NOINHERIT;
2GRANT CONNECT ON DATABASE your_saas TO app_user;
3GRANT USAGE ON SCHEMA public TO app_user;
4GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

The key here is no BYPASSRLS privilege and no superuser status. Any role with BYPASSRLS ignores RLS policies entirely. Your application role should be a regular role with standard table grants, and RLS does the rest of the access control. Some teams also set a default for the tenant context variable on the role itself:

1ALTER ROLE app_user SET app.current_tenant TO '';

This means if your application ever forgets to set the tenant context, queries return zero rows instead of leaking cross-tenant data. Fail closed, not open.

Step 2: Enable PostgreSQL Row-Level Security and Write the Policy

This is where the actual protection lives. Enable RLS on each tenant-scoped table and write policies that enforce the tenant boundary.

1ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
2ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
3
4CREATE POLICY invoices_tenant_select ON invoices
5    FOR SELECT
6    USING (tenant_id = current_setting('app.current_tenant')::UUID);
7
8CREATE POLICY invoices_tenant_insert ON invoices
9    FOR INSERT
10    WITH CHECK (tenant_id = current_setting('app.current_tenant')::UUID);
11
12CREATE POLICY invoices_tenant_update ON invoices
13    FOR UPDATE
14    USING (tenant_id = current_setting('app.current_tenant')::UUID)
15    WITH CHECK (tenant_id = current_setting('app.current_tenant')::UUID);
16
17CREATE POLICY invoices_tenant_delete ON invoices
18    FOR DELETE
19    USING (tenant_id = current_setting('app.current_tenant')::UUID);

The FORCE variant is important — it applies RLS even to the table owner. Without it, the table owner (whoever ran the CREATE TABLE statement) bypasses policies. In development your migrations run as a superuser, so this may never surface. In production, FORCE ensures every access path — including ad-hoc queries from an admin tool — goes through the policy.

USING vs WITH CHECK — Why the Distinction Matters

USING controls which existing rows are visible or targetable by a query. WITH CHECK controls what the new row version is allowed to look like after an INSERT or UPDATE. The distinction is critical.

Without a WITH CHECK clause on UPDATE, a user can modify a row that belongs to their tenant AND change the tenant_id to another tenant's ID in the same operation. The USING clause lets them target the row; there's no WITH CHECK stopping them from corrupting it. This is how you end up with orphaned data that belongs to nobody and a support ticket that takes a week to untangle.

Write both clauses. Every time. The extra three lines of SQL are free. The debugging session after missing them is not.

Step 3: Implement Tenant Context in NestJS

RLS only works if PostgreSQL knows which tenant is making the request. The pattern is: set a session variable at the start of each transaction, use SET LOCAL (transaction-scoped), and never let the setting bleed between requests.

1import { Injectable } from '@nestjs/common';
2import { DataSource, QueryRunner } from 'typeorm';
3
4@Injectable()
5export class TenantContextService {
6  constructor(private readonly dataSource: DataSource) {}
7
8  async runAsTenant<T>(
9    tenantId: string,
10    callback: (queryRunner: QueryRunner) => Promise<T>,
11  ): Promise<T> {
12    const queryRunner = this.dataSource.createQueryRunner();
13    await queryRunner.connect();
14    await queryRunner.startTransaction();
15
16    try {
17      // SET LOCAL scopes the tenant context to this transaction only
18      await queryRunner.query(
19        `SELECT set_config('app.current_tenant', $1, true)`,
20        [tenantId],
21      );
22
23      const result = await callback(queryRunner);
24      await queryRunner.commitTransaction();
25      return result;
26    } catch (error) {
27      await queryRunner.rollbackTransaction();
28      throw error;
29    } finally {
30      await queryRunner.release();
31    }
32  }
33}

The third argument to set_config is true, which makes it a SET LOCAL — transaction-scoped. When the transaction ends (commit or rollback), the setting disappears automatically. If you use plain SET (session-scoped) and return the connection to a pool, the next request inherits the previous tenant's identity. That is a cross-tenant data leak wearing the costume of a connection-pool optimization.

Here is how it wires into a controller:

1@Controller('invoices')
2export class InvoicesController {
3  constructor(
4    private readonly tenantContext: TenantContextService,
5    private readonly invoiceService: InvoiceService,
6  ) {}
7
8  @Get()
9  async findAll(@Req() req: Request) {
10    const tenantId = req.user.tenantId; // extracted from JWT or session
11
12    return this.tenantContext.runAsTenant(tenantId, async (qr) => {
13      // All queries inside here are RLS-scoped to this tenant
14      return this.invoiceService.findAll(qr.manager);
15    });
16  }
17}

The Guard That Catches Missing Context

The most common RLS bug in production isn't a bad policy — it's a request that never sets the tenant context at all. If you didn't set app.current_tenant, current_setting() throws an error unless you use the two-argument form with a default. Either way, your request fails or returns empty.

A NestJS guard that verifies the tenant context was set before any handler runs catches this class of bug at the framework level:

1@Injectable()
2export class TenantGuard implements CanActivate {
3  canActivate(context: ExecutionContext): boolean {
4    const request = context.switchToHttp().getRequest();
5    if (!request.user?.tenantId) {
6      throw new UnauthorizedException('Tenant context missing');
7    }
8    return true;
9  }
10}

Apply it globally and you can remove one entire category of "how did that query return zero rows" from your debugging rotation.

Read our NestJS project structure guide for where this guard lives in the module hierarchy — it goes in the Shared layer, not Core.

Step 4: Index PostgreSQL Row-Level Security for Performance

PostgreSQL Row-Level Security adds a predicate to every query. That predicate — tenant_id = current_setting(...) — needs an index to be fast. Without one, your "secure query" becomes a slow query, and your team will be tempted to disable RLS rather than fix the indexing.

The baseline rule: every tenant-scoped table should have a composite index that leads with tenant_id.

1CREATE INDEX idx_invoices_tenant_id ON invoices (tenant_id);
2CREATE INDEX idx_invoices_tenant_status ON invoices (tenant_id, status);
3CREATE INDEX idx_invoices_tenant_created ON invoices (tenant_id, created_at DESC);

The primary key lookup pattern that catches most teams is this one:

1-- Without RLS, this is fast with just an index on (id)
2SELECT * FROM invoices WHERE id = 1;
3
4-- With RLS, Postgres needs to check: id = 1 AND tenant_id = current_setting(...)
5-- Index on (id) alone doesn't help filter by tenant_id

The fix is either a composite index on (tenant_id, id) or ensuring your application code always scopes queries by tenant. Since the whole point of RLS is that you don't have to trust application code, the composite index is the right answer.

Run EXPLAIN (ANALYZE, BUFFERS) on your queries after enabling RLS and before deploying. The difference between a query with a correct index and one without is often the difference between 5ms and 500ms — and management rarely cares about the security improvement when the dashboard is loading slower than it used to.

The Unique Constraint Trap

A subtle but common issue: global unique constraints that don't include tenant_id.

1-- Bad: prevents Tenant A from having the same email as Tenant B,
2-- which is actually what you want in multi-tenant SaaS
3CREATE UNIQUE INDEX uq_customer_email ON customers (email);
4
5-- Good: email uniqueness is scoped per tenant
6CREATE UNIQUE INDEX uq_customer_email_tenant ON customers (tenant_id, email);

Without tenant_id in the unique constraint, Tenant A trying to add a customer with email "bob@example.com" will fail if Tenant B already has that email. Since RLS prevents your application from seeing Tenant B's rows, the error will appear to come from nowhere — a "unique violation" with no visible conflicting row. This is a confusing bug that has sent more than one team down a rabbit hole before they spotted the missing column in the unique index.

Step 5: Test RLS Policies in CI

Testing RLS is not optional. You test your API endpoints. You test your business logic. You should test that your tenant isolation works. The most valuable tests are the negative ones — proving that Tenant A cannot access Tenant B's data.

1import { Test, TestingModule } from '@nestjs/testing';
2import { DataSource } from 'typeorm';
3
4describe('RLS Tenant Isolation', () => {
5  let dataSource: DataSource;
6
7  beforeAll(async () => {
8    const module: TestingModule = await Test.createTestingModule({
9      imports: [/* your test module setup */],
10    }).compile();
11    dataSource = module.get<DataSource>(DataSource);
12  });
13
14  async function runAsTenant(tenantId: string, callback: (qr: any) => Promise<any>) {
15    const qr = dataSource.createQueryRunner();
16    await qr.connect();
17    await qr.startTransaction();
18    await qr.query(`SELECT set_config('app.current_tenant', $1, true)`, [tenantId]);
19    const result = await callback(qr);
20    await qr.rollbackTransaction();
21    await qr.release();
22    return result;
23  }
24
25  it('should allow Tenant A to see their own invoices', async () => {
26    const rows = await runAsTenant('tenant-a-id', async (qr) => {
27      return qr.manager.query('SELECT id FROM invoices');
28    });
29    const allTenantA = rows.every((r: any) => r.tenant_id === 'tenant-a-id');
30    expect(allTenantA).toBe(true);
31  });
32
33  it('should prevent Tenant A from seeing Tenant B invoices', async () => {
34    const rows = await runAsTenant('tenant-a-id', async (qr) => {
35      return qr.manager.query(
36        'SELECT id FROM invoices WHERE tenant_id = $1',
37        ['tenant-b-id'],
38      );
39    });
40    expect(rows).toHaveLength(0);
41  });
42
43  it('should prevent inserting a row with a different tenant_id', async () => {
44    await expect(
45      runAsTenant('tenant-a-id', async (qr) => {
46        return qr.manager.query(
47          'INSERT INTO invoices (tenant_id, amount) VALUES ($1, $2)',
48          ['tenant-b-id', 5000],
49        );
50      }),
51    ).rejects.toThrow();
52  });
53});

The third test is the one most teams skip, and it's the one that catches the most insidious bugs. Without a WITH CHECK policy, that INSERT succeeds, writing data with the wrong tenant ID into the table. That row becomes invisible to everyone — Tenant A can't see it (wrong tenant_id), Tenant B can't see it (RLS still filters by Tenant A's context), and you have an orphan.

Run these tests in CI. Fail the build on cross-tenant reads. This is not excessive — it is the minimum bar for a multi-tenant application that handles customer data.

Step 6: Roll Out RLS to an Existing SaaS

Adding RLS to a production database that already has application-level tenant filtering is a migration. It should be treated with the same care as any schema change — which means expand-contract, not big-bang.

The rollout sequence:

1. Inventory every tenant-scoped table and the query paths that touch it
2. Add indexes — all the (tenant_id, ...) composite indexes discussed in Step 4
3. Add the tenant context plumbing — the NestJS middleware and transaction wrappers
4. Enable RLS on one low-risk table — start with a table that has low query volume and no critical path dependency
5. Add full CRUD policies — separate FOR SELECT, FOR INSERT, FOR UPDATE, FOR DELETE policies with both USING and WITH CHECK
6. Monitor — watch for policy denials in logs and query latency changes
7. Expand to the next table after one full deployment cycle

The most common mistake is enabling RLS everywhere at once. If a policy is wrong on a high-traffic table, every query against it starts returning zero rows or throwing errors. Roll out table by table, with a rollback plan for each one. The Microsoft SaaS tenancy patterns guide covers similar progressive-hardening approaches in the context of Azure SQL, and the principles translate directly to PostgreSQL.

Handling Background Jobs and Admin Access

Queue workers present a special case. A job that processes invoices for multiple tenants needs to run each operation under a different tenant context. The pattern is straightforward: include tenantId in each job's payload and wrap the job handler's work in the same runAsTenant function.

1@Processor('invoices')
2export class InvoiceProcessor {
3  constructor(private readonly tenantContext: TenantContextService) {}
4
5  @Process('send-reminder')
6  async handleReminder(job: Job<{ tenantId: string; invoiceId: number }>) {
7    await this.tenantContext.runAsTenant(job.data.tenantId, async (qr) => {
8      // RLS-scoped to the job's tenant
9      const invoice = await qr.manager.findOne(Invoice, {
10        where: { id: job.data.invoiceId },
11      });
12      // ...send reminder
13    });
14  }
15}

For admin and support access that legitimately needs to see cross-tenant data, create a separate database role with explicit read-only grants and its own policies:

1CREATE ROLE support_user LOGIN PASSWORD 'different_password';
2GRANT SELECT ON ALL TABLES IN SCHEMA public TO support_user;
3
4-- Policy that allows read across all tenants
5CREATE POLICY support_read_all ON invoices
6    FOR SELECT
7    TO support_user
8    USING (true);

The key is that the support role gets only SELECT — no INSERT, UPDATE, or DELETE. Read-only cross-tenant access is often a business requirement. Write access across tenant boundaries should be an audited, exceptional event.

Step 7: Monitor, Audit, and Iterate

PostgreSQL Row-Level Security is not set-and-forget. Policies need the same maintenance as any other production configuration.

PostgreSQL provides the pg_policies catalog view to inspect what's currently configured:

1SELECT tablename, policyname, roles, cmd, qual, with_check
2FROM pg_policies
3WHERE schemaname = 'public'
4ORDER BY tablename, policyname;

Run this periodically. Verify that every tenant-scoped table has policies, that every policy has both USING and WITH CHECK, and that no policies are attached to roles that shouldn't have them.

For early-warning detection during rollout, enable logging of policy violations:

1log_line_prefix = '%t [%p]: [%l] %q%u@%d '
2log_statement = 'ddl'

When a query is blocked by RLS, PostgreSQL logs a WARNING with the policy name. Monitor these in your centralized logging system and alert on unexpected spikes — a sudden increase in policy denials often means a new endpoint or job is not setting the tenant context correctly.

Common PostgreSQL Row-Level Security Pitfalls in Production

Every RLS-related incident I've seen or been called in to debug comes from the same small set of root causes. Naming them saves you the investigation.

Forgetting FORCE ROW LEVEL SECURITY. The table owner bypasses RLS by default. If you run migrations and ad-hoc queries as the same role that owns the tables, you will never notice that RLS is being silently skipped for those operations. FORCE RLS applies the policy to the owner too.

Skipping WITH CHECK on writes. Without it, an UPDATE can change tenant_id to a different value, corrupting the row. The USING clause lets you target the row; WITH CHECK validates the new state.

Setting tenant context once per connection. If you SET app.current_tenant when the connection is established and never reset it, a connection-pool checkout from a different request inherits the stale tenant context. Always use SET LOCAL per transaction.

Bypassing RLS for "temporary" admin scripts. Temporary scripts have a survival instinct. That one-off data fix becomes a quarterly maintenance script that runs as a superuser with full access. Create a dedicated support role with explicit policies and use it for every human-initiated data access.

Rolling out all tables at once. A bug in one policy on one table is contained. A bug in policies across twenty tables is a production incident with a blast radius measured in hours of restored backups. Go table by table.

Final Word

PostgreSQL Row-Level Security is one of those rare infrastructure investments that improves both security and developer velocity. When the database enforces tenant boundaries, your team can write queries faster because they know the safety net is there. New endpoints can't accidentally leak data. Background jobs can't silently cross tenant lines. A forgotten WHERE clause stops being a compliance incident and becomes a non-event.

But RLS is not magic. It requires correct policies, per-transaction tenant context, appropriate indexes, and a deliberate rollout. Skip any of those and you get either a false sense of security or a production outage — and neither is an improvement over the application-level filtering you already have.

We start every SaaS build with shared schema plus RLS. It's the model we can debug at 3am, the model that scales from 10 tenants to 10,000, and the model that passes an enterprise security review without a rewrite. If you're still debating whether your multi-tenant data layer needs it, you probably do — and you'll know for certain the first time a query without a WHERE clause hits production and nothing breaks.

If you're working through a multi-tenant architecture decision right now and want to talk it through with a team that has shipped it both ways, get in touch. We argue about this stuff for a living.