How we built Row-Level Security into a multi-tenant SaaS with PostgreSQL

When you build a multi-tenant SaaS application, you make a promise to every customer: their data is invisible to every other customer. In most applications, that promise is kept by the application layer — every query includes a WHERE tenant_id = $1 clause.

The problem is that application-layer isolation is only as reliable as your application code. A single missing WHERE clause, an ORM misconfiguration, or a cached query without a tenant filter can expose one tenant's data to another. We decided early in AISEC's development that compliance data — audit trails, risk registers, evidence files — needed a stronger guarantee. That meant Row-Level Security at the database layer.

How PostgreSQL Row-Level Security works

RLS is a PostgreSQL feature that automatically filters rows based on policies you define. Policies are attached to tables and evaluated for every query — SELECT, INSERT, UPDATE, and DELETE — before the query planner even sees the data. The application cannot accidentally bypass them; the filtering happens inside the database engine.

A policy evaluates a condition using the current session context. In our case, we pass the current tenant's ID as a session-level variable, and every tenant-scoped table has a policy that checks it.

Our schema design

Every table that holds tenant-scoped data has a tenant_id column:

tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE

This is non-negotiable. Every row in every tenant-scoped table must have a non-null foreign key to the tenants table. We enforce this at the schema level and in our ORM entity definitions.

We also index every tenant_id column — this is the single most important performance consideration for RLS. Without an index, the RLS filter requires a sequential scan on every query.

The RLS policy

For each tenant-scoped table, we enable RLS and create a policy:

ALTER TABLE policies ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON policies FOR ALL TO aisec_app USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

The key parts: FOR ALL means the policy applies to all query types. TO aisec_app means it only applies to the application database role — not to the migration role or superuser. current_setting('app.current_tenant', true) reads the session variable we set at the start of each request.

Setting the session variable per request

In our NestJS backend, we have middleware that runs on every authenticated request. After validating the JWT and extracting the tenant ID, it sets the PostgreSQL session variable before the request handler runs:

SET LOCAL app.current_tenant = '{tenantId}'

SET LOCAL is important — it scopes the variable to the current transaction, not the connection. Since we use a connection pool, connection reuse without resetting the variable would leak tenant context between requests. SET LOCAL ensures the variable resets when the transaction ends.

The gotchas

Gotcha 1: current_setting and the fail-silently parameter

current_setting('app.current_tenant', true) — the second argument true means "return NULL if the variable is not set" rather than raising an error. In development and test environments, this can mask the case where middleware did not set the variable. We recommend using false in non-production environments so missing tenant context fails loudly.

Gotcha 2: The BYPASSRLS role attribute

PostgreSQL superusers bypass RLS by default. Your migration user needs to bypass RLS — migrations add rows without a tenant context. Your application user must NOT have BYPASSRLS. Check this explicitly: SELECT rolname, rolbypassrls FROM pg_roles WHERE rolname = 'aisec_app';

Gotcha 3: JOIN queries

RLS policies apply independently to each table. If you JOIN two tenant-scoped tables, each table's policy is evaluated separately — the tenant filter applies to both sides of the join. This is the correct behaviour, but it means you cannot accidentally bypass RLS on a joined table by accessing it through a non-filtered query.

Gotcha 4: ORM abstractions and migrations

TypeORM and other ORMs respect RLS when running queries as the application user — the policies apply transparently. However, TypeORM migrations run as the migration user, which bypasses RLS. This is correct and intentional, but it means you need to be careful about seeding data in migrations: always include explicit tenant_id values.

Testing RLS correctly

The correct way to test RLS is to connect as the application role, set the session variable, and verify that queries return only the expected rows. Testing as superuser will bypass RLS and give you a false sense of security. We have integration tests that explicitly connect as aisec_app, set a tenant ID, and assert that cross-tenant data is not returned.

Performance considerations

RLS adds negligible per-query overhead for typical OLTP workloads — we have not observed more than 1ms of additional latency on any query. The real performance concern is index coverage: every tenant_id column must be indexed, and composite indexes that start with tenant_id are worth considering for high-traffic tables.

When RLS is not enough

RLS provides strong logical isolation within a shared database. For extremely sensitive data — healthcare records, financial data subject to strict regulatory requirements, or data for customers with contractual isolation requirements — consider separate schemas per tenant (higher operational complexity but cleaner isolation) or separate database instances per tenant (maximum isolation, significantly higher cost and operational overhead).

For AISEC's use case — compliance data for security teams — RLS provides the right balance of isolation, performance, and operational simplicity. It is a guarantee we can explain to customers and verify in a penetration test.