Write PostgreSQL RLS Policy Tests

PostgreSQL row-level security can help enforce tenant isolation close to the data. It is not complete merely because a table has RLS enabled. Policies differ by role and command, and owners or BYPASSRLS roles can bypass them. The PostgreSQL row security documentation describes these rules and the default-deny behavior when RLS is enabled without an applicable policy.

Test the actual roles

Create synthetic Tenant A and B rows. Use the same database roles and connection setup as the application, worker, migration job, and support tool. For each role, record expected SELECT, INSERT, UPDATE, and DELETE decisions.

Test both USING and WITH CHECK behavior for writes. A policy that hides B rows from SELECT may still allow an unexpected inserted owner if its write rule is wrong. Check the session variable or role switch that supplies tenant context; a missing or stale value must not become all-tenant access.

Run tests with fresh transactions and pooled connections. One test should switch from A to B on the same pool to expose context leakage. Verify views and security-definer functions separately because they can change which privileges apply. Do not copy a generic policy snippet into production without matching your role model and PostgreSQL version.

Run command-specific negative cases

For each application role, use a test harness that can assert SQL errors and inspect rows after rollback. This example assumes the application supplies a transaction-local app.tenant_id value from a verified request context. A caller who can run arbitrary SQL can set a custom variable too, so the variable alone is not an authentication boundary.

1. Begin a transaction as the deployed app role. Set A context with SET LOCAL app.tenant_id = 'tenant-a'. Select A and B fixture IDs separately; require A's row and no B row.
2. Attempt to insert a row whose stored tenant_id is B. Require a policy rejection and no B row after rollback. A failed SELECT cannot stand in for this WITH CHECK test.
3. Attempt to update an A row's tenant_id to B. Require rejection and unchanged owner and contents after rollback. Then attempt to update a B row as A and require no B change; these test new-row and existing-row policy paths separately.
4. Repeat DELETE as A against B and confirm B remains. Run an allowed A write as a positive control. Record SQLSTATE, row counts, and final rows for each case.
5. On the same pooled connection, finish the transaction, start B work, and require B-only results. Repeat B then A. Run a request with missing tenant context and require no tenant rows or writes.

Use savepoints or separate transactions so an expected SQL error does not abort later assertions. Test with the actual connection role, pool mode, and policy definitions. PostgreSQL defines USING and WITH CHECK, and SET LOCAL ends at the transaction boundary. Do not treat a passing restricted-role test as coverage for an owner or BYPASSRLS worker.

OWASP's multi-tenant guidance provides the broader isolation goal. The role-command-row grid is the artifact that proves what a particular database configuration did in a particular build. It does not prove that every app route uses that role or that all derived data is protected. Record those gaps rather than calling RLS a universal fix.