Tenant Isolation in PostgreSQL: The Connection Pool Is Part of the Threat Model

A tenant filter in every repository method is a convention. One forgotten predicate can turn that convention into a data breach. I would use PostgreSQL row-level security as another enforcement layer, but only after making the identity path explicit: who selects the tenant, how that context reaches the database, and when it disappears.

Consider an invoice service that reuses database connections across customers. The application authenticates the caller and resolves tenant membership from trusted server-side state. A tenant header alone is not sufficient authority. The database receives the resolved tenant for one transaction, not for the lifetime of a pooled connection.

Put the boundary inside the transaction

This example assumes an invoices table with a UUID tenant_id and a separately provisioned invoice_app runtime role:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_boundary ON invoices
TO invoice_app
USING (
  tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
)
WITH CHECK (
  tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
);

The request handler starts a transaction, executes SELECT set_config('app.tenant_id', $1, true) with a bound, validated tenant UUID, performs its queries, and commits or rolls back. The final true scopes the setting to the transaction. Do not run that statement in a separate autocommit transaction and expect the context to survive for the following query.

The runtime role must not be a superuser, have BYPASSRLS, or inherit administrative privileges. Keep migrations on a separate identity. PostgreSQL documents the owner bypass behavior and the effect of FORCE ROW LEVEL SECURITY in its row security reference.

This design catches missing application predicates. It does not protect against arbitrary SQL execution under an identity that can change its own tenant setting. That is a different threat requiring stronger identity separation, query controls, and prevention of SQL injection.

Test connection reuse deliberately

A useful test forces the pool size to one. Request A reads tenant A’s invoice, exits through an exception, and returns the connection. Request B then reads tenant B’s data. A third request has no tenant context and must return no protected rows or fail before querying.

Also attempt an insert into another tenant, a tenant-changing update, and a query under the actual production role. Tests run as the database owner can give a dangerously inaccurate picture. Inspect all applicable policies: permissive policies can combine to widen access.

Include background jobs, bulk exports, and support tooling in the test population. Those paths often bypass the request middleware that establishes the context.

Lead the migration as a shared contract

I would assign the platform team ownership of transaction setup and pool hygiene, service teams ownership of tenant resolution, and security ownership of adversarial test cases. Start with one service and measure query latency and rejected operations before expanding.

The release condition is specific: every tenant-owned table is classified, every runtime path establishes context, and the negative tests pass under runtime credentials. An exception needs a named owner and a migration date. Counting enabled policies is weaker evidence than showing that a reused connection cannot disclose the previous customer’s invoice.