Test PostgreSQL row-level security through the actual application role while repeatedly reusing a connection for two synthetic tenants. Set verified tenant context inside the transaction that runs the query, then exercise commit and rollback paths. A policy tested only as the table owner can give a misleading result, and a pooled connection must not inherit the previous request's authority.
Establish who can bypass the policy
Create a disposable database with an owner role for schema setup and a separate ordinary application role for the test. Enable RLS and define the intended SELECT and write policies. PostgreSQL documents that owners normally bypass RLS and that superusers and BYPASSRLS roles bypass it. Record role membership and FORCE ROW LEVEL SECURITY decisions explicitly. A successful administrator query does not establish application isolation.
PostgreSQL 18: row security roles and policy behavior
Use two fictional tenants, A and B, with one unmistakable fixture row each. The application selects the tenant only after verifying membership. If the application role itself can freely set the tenant context used by the policy, that policy is an additional boundary behind trusted application authorization, not an independent defense against arbitrary code running with the same role. Keep that trust assumption visible.
Put context and query on the same transaction connection
The following SQL is a proposed lab fragment. The third argument to set_config requests transaction-local behavior. Run the context assignment and protected query on the same acquired connection and within the same explicit transaction; separate pool helper calls can acquire different connections. Never derive the tenant value directly from an unverified request parameter.
BEGIN;
SELECT set_config('app.tenant_id', 'fixture-tenant-a', true);
SELECT id, tenant_id FROM fixture_documents ORDER BY id;
COMMIT;
-- Acquire/reuse the connection, begin again, and set tenant B.Define how the policy behaves when context is absent, empty or invalid. A policy expression that casts a setting must handle the expected missing-state representation deliberately. Test the actual expression rather than copying one generic current_setting formula into every schema. The target result is a clear denial or empty authorized set according to the documented contract, never accidental reuse of A.
| Step | Context | Expected fixture visibility |
|---|---|---|
| A commits | Tenant A | Only A |
| B follows | Tenant B | Only B |
| No context | Absent or reset | Deny or empty by policy |
| A rolls back, then B | Fresh B transaction | Only B |
Reuse is the point of the experiment
Force the isolated application pool to one connection so A and B alternate on the same backend. First commit A's read, then run B, then omit context. Repeat with an exception in A's transaction and verify rollback before B. Also test cancellation while a query is running and ensure the connection is either cleanly reusable or discarded under the driver's documented behavior. Capture backend identity and visible fixture IDs without logging real tenant data.
A transaction-local setting is not a general promise about every pooler. PgBouncer modes and managed proxy pinning affect connection behavior. AWS's comparison explains the differences between session, transaction and other pooling choices; inspect the exact pooler mode and deployment configuration used by the application. The local driver pool and an upstream transaction pooler are two distinct layers to review.
AWS: PostgreSQL pooling modes and session-state considerations
Make the result reproducible
Save database version, role attributes, policy SQL, pool sizes, transaction sequence and returned fixture IDs. Include a cross-tenant write attempt if the role supports writes. A read test alone does not prove WITH CHECK behavior. Review privileged maintenance and security-definer functions separately so the ordinary application-role fixture does not become an oversized claim about every database path.
No PostgreSQL connection lab was executed for this article; the sequence is a runnable design for a disposable database. A source-level transaction context manager is useful implementation context but does not prove deployed RLS or pooler behavior. The release gate should be the observed two-tenant trace, including cleanup after failure, under the exact role and pool configuration intended for production.
Multi-tenant SaaS data isolation with FastAPI and PostgreSQL
Observability for small teams: start with the failed customer task
Bring one API or data workflow and a sanitized failure example for a bounded implementation assessment.
How do you test PostgreSQL RLS with pooled connections?
Alternate two synthetic tenants on a reused connection under the ordinary application role, setting verified tenant context inside each transaction and testing cleanup after failures.
Can the table owner test application isolation?
Owner behavior can bypass RLS; run the assertions as the intended ordinary application role.
Is SET LOCAL enough on its own?
The context and protected query must share the intended transaction and connection, and the tenant must come from trusted membership checks.