PostgreSQL Row-Level Security Review for Offshore Development

Start with the roles that reach the table

Begin with a role graph rather than a policy screenshot. List login roles, inherited membership, SET ROLE paths, table owners, superusers, BYPASSRLS attributes, migration identities, background workers, reporting tools, and connection-pool behavior. For SELECT, INSERT, UPDATE, and DELETE, record both USING and WITH CHECK expressions and the session values they depend on. Build two synthetic tenants with distinguishable rows, then exercise direct SQL, prepared statements, ORM queries, bulk jobs, administrative paths, and transaction reuse. FORCE ROW LEVEL SECURITY deserves an explicit decision because owners normally bypass policies. Test missing identity, malformed identity, cross-tenant identifiers, joins, subqueries, and a pool connection reused after tenant context changes. Explain plans for permitted and denied fixtures so authorization does not hide a performance collapse. The database owner approves roles and production policy; the developer supplies migrations, fixtures, and evidence without copying customer rows.

A useful review starts at the connection boundary. Record the role named in the application configuration, the role that PostgreSQL reports after login, every membership it inherits, and whether the application changes role inside a transaction. Do the same for migrations, scheduled jobs, reporting tools, support consoles, and data repair scripts. This often changes the diagnosis. A policy can work perfectly for the web role while a worker reads as the table owner and bypasses it. The evidence packet should make that difference obvious before anyone edits SQL.

Test commands separately

SELECT, INSERT, UPDATE, and DELETE do not share one policy decision. Build a matrix that names the applicable USING and WITH CHECK expression for each command. Insert a row with the correct tenant, then attempt the same write with another tenant and with no tenant context. For updates, test both the rows a caller may find and the new row values it may create. For deletes, verify the caller cannot infer protected rows from counts or error details. Keep the results by command instead of summarizing the suite as "RLS passed."

Joins deserve their own fixtures. Start with two tenants whose identifiers and row values are easy to distinguish. Join the protected table to an unprotected lookup, use a subquery, follow a foreign key, and exercise any view or function the application actually calls. Capture both returned rows and observable errors. The goal is not to prove that PostgreSQL hides every conceivable signal. It is to show what the reviewed query paths expose and to identify questions that need security ownership.

Buyer decision record

Scroll sideways to read every column on a small screen.

Reproduce connection reuse

For investigation, capture current_user, session_user, active role membership, transaction-local tenant settings, and the policy expressions returned by the catalog beside every fixture result. Reuse one pooled connection across tenant A, tenant B, and a missing-context request to expose state leakage. Exercise COPY and maintenance tooling only when those paths exist. Verify that referential errors and row counts do not disclose another tenant indirectly. A policy that denies rows may still permit a timing or uniqueness signal, so record observable errors with security review. Keep a break-glass role outside normal application configuration, require named approval, and alert on its use. Re-run the entire matrix after schema ownership, ORM middleware, pool initialization, or worker identity changes.

Pool reuse is where a sound policy can meet a faulty application boundary. In one test, set tenant A, execute a query, return the connection, then borrow it for tenant B. Repeat with a failed transaction and a cancelled request. Record whether the tenant value is transaction local, when cleanup runs, and what the next borrower observes. A test that creates a fresh connection for every tenant misses this failure. The correction may belong in transaction setup or pool cleanup rather than in the policy expression.

Treat bypass as an owned exception

Table owners, superusers, and roles with BYPASSRLS need a named purpose. Keep them out of normal application configuration. If an administrative job needs wider access, document the job, the approved role, the query boundary, the audit event, and the person allowed to run it. FORCE ROW LEVEL SECURITY may be appropriate for some owner paths, but it changes operational behavior and needs database-owner approval. Do not switch it on simply to make a test green. A break-glass role also needs monitoring and an expiry or review rule.

Migration tooling is a separate concern. Schema changes may run as an owner even when the application does not. Rehearse the migration, application rollout, and rollback with the intended identities. Verify new policies exist before code depends on them and old policies remain until old code stops using them. The handoff should state which revision establishes each side of that compatibility window.

Read performance evidence beside authorization evidence

A denied query returning no rows can still have an unsuitable plan. Save EXPLAIN output for permitted and denied synthetic cases, using representative parameter shapes without production data. Compare the predicates introduced by policy, index support, estimated rows, and execution work. If the policy calls a function, record its volatility and cost assumptions. Performance evidence does not relax authorization, but it can show that an apparently safe rollout would overload the database and needs an index or a narrower query before release.

Keep timing conclusions bounded. A small fixture cannot predict production latency, and timing differences can sometimes reveal information. Report what the fixture showed, what it did not model, and which owner decides whether further side-channel review is required. Do not turn one fast plan into a guarantee.

Define a release decision that can be audited

Accept only when the role matrix proves permitted operations and rejects every cross-tenant control through direct, ORM, worker, and pooled paths. Owner or BYPASSRLS connections, stale session context, incomplete command coverage, or an unexplained plan regression block release. Retain migration and rollback SQL, policy definitions, fixture IDs, query plans, and named approvers.

Package the final role graph, policy SQL, migration sequence, test matrix, connection-reuse trace, representative plans, known bypasses, and rollback steps under immutable revisions. The database owner approves roles and production policy. The security owner decides whether indirect signals and administrative paths are acceptable. The application owner confirms tenant context is established and cleared on every request path. An offshore developer can prepare the migration and evidence, but those approvals stay with the named internal owners.

Handoff for the next working window

The next engineer should receive exact commands, fixture identifiers, expected row sets, actual row sets, PostgreSQL and client versions, the starting and ending commit, and the open decision. State whether they may continue testing, revise code, request a policy decision, or stop. Include the first condition that invalidates the result: a role change, new query path, pool configuration change, ownership transfer, ORM middleware change, or new administrative tool. That makes the review repeatable without pretending it is permanent.

Questions about assessing Philippine developers