Row-Level Security in PostgreSQL SaaS Apps

Schema separation is not enough for multi-tenant SaaS security. Learn how PostgreSQL's native RLS policies enforce tenant isolation at the database layer, where it truly cannot be bypassed.

Most multi-tenant SaaS apps handle tenant isolation the same way: a tenant_id column on every table, a WHERE tenant_id = $current clause bolted onto every query. It works — until a developer forgets one filter, an ORM generates an unexpected join, or a background job runs without a tenant context. At that point, one tenant's data leaks into another's, and you have a serious incident on your hands.

PostgreSQL's Row-Level Security (RLS) closes that gap by moving the enforcement boundary from your application code down into the database engine itself. The policy fires on every query, regardless of how that query arrived. No middleware, no ORM magic — the database simply will not return rows that violate the policy.

What Row-Level Security Actually Does

RLS lets you attach security policies directly to a table. A policy is a boolean expression that PostgreSQL evaluates for every row before deciding whether to include it in a SELECT, UPDATE, INSERT, or DELETE operation. If the expression returns false, the row is invisible — or the write is rejected.

This is fundamentally different from application-level filtering. Application code can be bypassed by a misconfigured query, a raw SQL escape hatch, a junior developer's mistake, or a compromised internal tool. A database-level policy cannot be bypassed by any of those vectors.

Setting Up a Multi-Tenant Schema

Start with a typical SaaS table structure:

-- Tenants table
CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);

-- Example: projects belonging to tenants
CREATE TABLE projects (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id  UUID NOT NULL REFERENCES tenants(id),
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Enable RLS on the projects table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Force RLS even for the table owner
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

The FORCE ROW LEVEL SECURITY line is important. By default, the table owner bypasses all policies. In a SaaS context, you almost always want the policies to apply universally, including to the role your application connects with.

Writing the Isolation Policy

PostgreSQL policies use current_setting() to read session-level variables. You set this variable when a tenant session begins — typically at connection time or at the start of a transaction.

-- Policy: tenants can only see their own projects
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

This single policy means no SELECT, UPDATE, or DELETE against projects can ever touch a row where tenant_id does not match the session variable. One line of SQL replacing thousands of application-level guards.

In your application layer, you set the variable at the start of every request:

SET LOCAL app.current_tenant_id = '3f2d1a...';

SET LOCAL scopes the variable to the current transaction, which is exactly what you want — it resets automatically when the transaction ends, preventing accidental context bleed between requests.

Separate Read and Write Policies

For finer control, split your policy by command:

-- Read policy
CREATE POLICY tenant_select ON projects
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- Write policy: also enforces tenant_id on new rows
CREATE POLICY tenant_insert ON projects
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);

CREATE POLICY tenant_update ON projects
  FOR UPDATE
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);

The distinction between USING (filters existing rows) and WITH CHECK (validates new or modified row values) matters on writes. Without a WITH CHECK clause on inserts, a rogue query could insert a row with a foreign tenant_id even though reads are restricted.

Handling Superuser and Internal Operations

Background jobs, data exports, and admin tooling often need to operate across tenants. You have two clean options:

Use a dedicated superuser role that bypasses RLS (since BYPASSRLS is a role-level privilege, not table-level). Grant this role only to internal services that genuinely need cross-tenant access, never to the application's primary connection role.
Set the tenant context explicitly in admin jobs, iterating per-tenant with SET LOCAL inside a transaction loop. This keeps your policies active and gives you an audit trail of which tenant context each operation ran under.

Never disable RLS globally to solve a convenience problem. That defeats the entire architecture.

Performance Considerations

RLS policies add a predicate to every query plan. In practice, the overhead is negligible when:

tenant_id is indexed — which it should be regardless of RLS.
The policy expression is simple (a UUID equality check is as cheap as it gets).
Connection poolers like PgBouncer are configured in transaction mode, so SET LOCAL resets correctly between clients.

If you use session-mode pooling, SET LOCAL will not reset between pooled connections. Use transaction-mode pooling, or set the variable in a BEGIN block and rely on transaction commit/rollback to clean it up.

What This Architecture Guarantees

With RLS in place, your security model has genuine defense in depth:

Application layer filters queries by tenant for correctness and performance.
Database layer enforces isolation as a hard constraint — a missing WHERE clause in application code becomes a harmless mistake rather than a data breach.
Audit and compliance benefits from a single, reviewable policy definition rather than scattered WHERE clauses across hundreds of ORM calls.

This is the kind of architecture that holds up under a SOC 2 audit, satisfies GDPR data-separation requirements, and lets your engineering team move fast without a latent cross-tenant vulnerability lurking in every new feature they ship.

Why This Matters for Your Project

If you are building or scaling a multi-tenant SaaS product, the question is not whether to use RLS — it is how soon you can retrofit it. Introducing it early costs an afternoon. Retrofitting it after a data-leak incident costs your reputation. PostgreSQL gives you a production-grade isolation primitive for free; building a security layer on top of it rather than around it is one of the highest-leverage architectural decisions a SaaS team can make.