A missing tenant filter: can PostgreSQL RLS stop the leak?

Fictional setting, real lab evidence. Fortex in this series is a fictional setting; Tom and Ben are characters. Their conversations illustrate the reasoning. Measurements and assertions come from the linked Docker lab, not Fortex production incidents.

The Fortex journey · Chapter 7/8

The export Ben didn't want to send

Fortex was opening a management portal for merchant partners. Ben reused an invoice repository and added a query for exports. During review he noticed the new query was missing its tenant condition.

“I've fixed the WHERE clause and added a regression test,” he told Tom. This time he brought the mistake forward himself.

Tom read the change. “Good. If another read path forgets it later, what else catches it?”

“RLS. Put the filter in the database so every query goes through it.”

“Which role are you testing with?”

Ben checked the account in his SQL tool. He was thinking in layers now, but still assumed every caller entered through the same door. Tom suggested leaving the WHERE clause absent and changing the caller in the lab.

One missing WHERE clause

Ben retained the faulty query as the baseline. The new protection had to handle the actual missing-filter mistake, not only a sample query that already filtered correctly.

A shared invoices table stores data for several companies. Most queries include a tenant predicate. A new export endpoint forgets it. If its database role can read the whole table, PostgreSQL returns the whole table; it cannot infer the caller's company from application intent.

Repository helpers, tests, and review help prevent this mistake. Row-Level Security adds policy enforcement at the database. It is useful for omitted filters, but only if the runtime role actually obeys the policy.

Row policies and database roles work together

“Let's test cross-tenant writes too,” Ben said. “Hiding rows on reads isn't enough.”

Tom asked him to write expectations for each role. Runtime, ownership, and special privileges had to be separated before a COUNT result meant anything.

The lab stores four invoices: two per tenant. The application role is neither a superuser nor a BYPASSRLS role and does not own the table. Without RLS, its unfiltered count returns four.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (
  tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int
)
WITH CHECK (
  tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int
);

USING determines which rows are visible or eligible for policy-governed operations. WITH CHECK validates newly inserted or updated rows. Read filtering alone does not define a complete tenant write policy. The PostgreSQL 17 RLS documentation describes policy composition and privilege rules.

In this policy, a missing or empty context becomes NULL, so no row passes the equality comparison. A malformed integer context produces a cast error instead. Both behaviors deserve explicit tests and application handling.

Context needs a trusted identity source

Ben planned to copy the tenant header into the context. Tom asked: “If I change that header, what proves I belong to the new tenant?”

Ben returned to server-side session and membership checks. Then he added a question of his own: “How long does context survive on a reused connection?” Tom let him connect their earlier transaction lesson to this boundary.

The test harness uses one transaction:

BEGIN;
SET LOCAL ROLE app_user; -- harness impersonation, not production login advice
SET LOCAL app.tenant_id = '1';
SELECT id, amount FROM invoices; -- deliberately no tenant predicate
COMMIT;

A production application should connect using a least-privileged runtime role from the outset. Do not grant superuser access just to switch roles later. Resolve the tenant from an authenticated session and verified membership, rather than trusting an arbitrary client header. Set the value through an appropriate parameterized driver API, not SQL string concatenation.

SET LOCAL limits context to the transaction, reducing the risk that a pooled connection retains the previous request's tenant. Every query in that unit of work must use the same connection. A helper that quietly obtains another connection will not inherit the context.

A custom setting is not an identity certificate. An attacker who can execute arbitrary SQL as the application role can change it. This pattern protects a trusted application against omitted filters; it does not replace SQL-injection prevention or isolate a fully compromised application. Stronger boundaries may require distinct roles, schemas, or databases, depending on the threat model.

Test reads, writes, and ownership separately

Ben ran the same query as the app role and as the owner. Different row counts didn't automatically mean a broken policy. Tom asked him to check the documented privilege exceptions. After FORCE they retested a non-superuser owner, keeping it distinct from a superuser.

The digest-pinned PostgreSQL 17.6 environment creates separate non-superuser, non-BYPASSRLS application and owner roles. All data is disposable.

An unfiltered application query sees four rows before RLS and two afterward with tenant 1 context. Missing context returns zero. An attempted tenant 2 insert while acting for tenant 1 is rejected, and a subsequent check confirms that its ID is absent.

The table owner initially still sees four rows. After FORCE ROW LEVEL SECURITY, that non-superuser owner sees two under tenant 1 context. FORCE does not remove superuser or BYPASSRLS privileges. A successful test made only with an administrator account cannot establish application-role isolation.

The report and raw logs include the actual rejected-write error and assertions. Download the source below and run make reproduce.

Boundaries worth reviewing

“Can I write ‘tenants are isolated’ now?”

“State which mistake you're protecting against,” Tom replied. “A trusted application forgetting a filter isn't the same threat as someone executing arbitrary SQL.”

Ben kept the repository filter and earlier tests, added role/context tests for RLS, and listed views, functions, and grants to review before rollout at Fortex.

Policy predicates can change query planning. Indexes need to support the tenant condition and business query together. An index is not a universal guarantee of efficiency; the selectivity and query-plan lab explains why matching-row counts matter. This experiment does not benchmark RLS overhead or exhaustively test every view, function, and join configuration.

For shared-table SaaS, RLS can be a valuable additional defense against forgotten predicates. That benefit depends on treating roles, context, and policies as versioned, tested parts of the data model. Enabling a table option alone provides much less assurance than that complete design.

The page Ben takes into tomorrow

Ben ended the page with: “A correct policy needs the right enforcing role and a trusted identity.” He sent Tom more than SQL: a role matrix, the context-setting path, and rollback cases to check.

For their next session Tom switched roles. He brought Ben a requirement to retry after timeouts and asked him to lead. The question Ben had saved during the inventory lesson was back.

Previous chapter: The database committed. Where did the event go? · Next chapter: A timeout is not a failed operation: making retries safe · Start the journey