Review PostgreSQL Row-Level Security in Coding-Agent Patches

Last reviewed: November 1, 2026 (Asia/Shanghai).

Direct answer

A PostgreSQL row-level security review should prove three things: the application executes as a role subject to the policies, every applicable policy preserves the intended boundary, and each transaction receives the correct trusted identity context. Test reads and writes through the actual application connection path, including reuse of a connection by another tenant.

An agent can add a plausible tenant predicate while leaving a bypass elsewhere. PostgreSQL’s Row Security Policies documentation explains that table owners normally bypass RLS, while superusers and roles with BYPASSRLS always bypass it. The CREATE POLICY reference distinguishes existing-row filtering with USING from proposed-row validation with WITH CHECK. Those are separate review obligations.

For a shared application role that carries tenant context in a setting, bind that context to one explicit transaction. PostgreSQL’s configuration settings functions document set_config(..., true) for transaction-local values. The context must come from server-side authorization before the query executes.

Who this is for

This guide is for maintainers reviewing coding-agent changes to PostgreSQL policies, database roles, tenant middleware, repository methods, and connection-pool handling. It is especially useful when an agent moves tenant isolation from application predicates into database policies or changes the way a shared runtime role identifies a tenant.

The running example is a document table shared by two tenants. The database policy enforces tenant isolation. User ownership, sharing, and action-specific permissions within a tenant still need the application’s separate object-authorization review .

Key takeaways

• Separate the migration owner from the runtime role, and inspect effective authority before interpreting test results.
• Enable RLS explicitly; creating a policy alone does not activate it.
• Review the complete set of applicable policies. Another permissive policy can widen access through OR.
• Check both the existing row and the proposed new row so an update cannot transfer data across the tenant boundary.
• Set authorized tenant context inside the same transaction and connection as the protected queries.
• Test connection reuse after success, failure, and cancellation, with exact row and side-effect assertions.

Sources checked

• PostgreSQL: Row Security Policies , fetched on November 1, 2026, supports activation, default denial, role bypass, policy composition, and the limits involving table-wide operations and integrity checks.
• PostgreSQL: CREATE POLICY , fetched on November 1, 2026, supports USING, WITH CHECK, command-specific behavior, multiple-policy composition, and view-owner policy evaluation.
• PostgreSQL: SET , fetched on November 1, 2026, supports transaction-local lifetime, restoration of the session setting, and rollback-to-savepoint behavior.
• PostgreSQL: System Administration Functions , fetched on November 1, 2026, supports current_setting with missing_ok and set_config with a transaction-local flag. The relevant material is its Configuration Settings Functions section.

All four pages identified PostgreSQL 18 as the current documentation when checked. They are stable primary references for an evergreen review task. The example policy and acceptance workflow below are engineering recommendations based on those semantics, rather than a newly announced feature or a claim of executed application tests.

Contract details to verify

Establish the role that actually runs the query

Record the login role, effective role, table owner, role memberships, and any role switching or elevated function on the changed request path. Include background workers and reporting jobs that reach the same table. A test session using the migration owner does not establish how an ordinary application role behaves.

The runtime should have only the required table operations and schema access. Keep table ownership, policy changes, and role administration outside its authority. Verify that it cannot assume an owner or other role that bypasses the intended boundary.

FORCE ROW LEVEL SECURITY subjects the table owner to policies, but it does not neutralize superuser or BYPASSRLS access. Treat those identities as privileged paths requiring their own controls. PostgreSQL also excludes TRUNCATE and REFERENCES from row security; table grants must reflect that scope.

Check existing rows and resulting rows

For the example, assume app.documents has a non-null UUID tenant_id, a UUID id, and a text body. A separate migration identity owns it. The non-owner app_runtime role has only its required ordinary data privileges and cannot assume a bypass role. The table starts with no other policies.

This illustrative migration defines one tenant boundary for normal data operations:

BEGIN;

ALTER TABLE app.documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.documents FORCE ROW LEVEL SECURITY;

CREATE POLICY documents_tenant ON app.documents
FOR ALL TO app_runtime
USING (
    tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
)
WITH CHECK (
    tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
);

COMMIT;

Apply and test the candidate in a disposable database through the approved migration identity. Table privileges remain a separate prerequisite; a policy does not replace GRANT. Review schema changes and recovery with the site’s database migration workflow .

USING filters existing rows available to the operation. WITH CHECK evaluates the proposed inserted or updated row. Under this policy, an update that can see a tenant-A document still cannot change its tenant_id to tenant B. PostgreSQL rejects a proposed row whose check is false or null. An ordinary read of an invisible foreign row instead returns no matching row; a standalone update or delete targeting only that invisible row normally affects zero rows.

PostgreSQL reuses USING as the check for an ALL or UPDATE policy when WITH CHECK is omitted. The explicit repetition here makes both obligations visible in review. It does not imply that every policy needs both clauses: INSERT policies use WITH CHECK, while SELECT and DELETE policies use USING.

The expression treats a missing or empty setting as null, which cannot satisfy the tenant comparison. A malformed nonempty UUID causes a conversion error. The application should reject invalid identity context before querying; neither case should trigger an unscoped retry.

Review every policy that can apply

Compare the policy inventory before and after the migration. Include policies applying to PUBLIC, inherited roles, and ALL commands. A new restrictive-looking predicate can coexist with an older permissive USING (true) policy and leave reads broadly available.

For policies of the same applicable command type, PostgreSQL combines permissive policies with OR, restrictive policies with AND, and requires a permissive policy to grant access. Adding only restrictive policies does not create an access grant. Review the resulting expression against each role and operation instead of approving each policy in isolation.

Exercise the actual statements the application uses. Updates that read columns can also require a SELECT policy. RETURNING, upserts, and MERGE have additional policy interactions documented in CREATE POLICY; successful plain SELECT tests do not clear those paths automatically. Include triggers that can change the proposed row before WITH CHECK runs.

Keep tenant context inside the transaction

The tenant selector supplied by a request is input to authorization. Resolve it against the authenticated principal’s allowed tenants, then use the authorized canonical identifier as the database context. A well-formed UUID or a signed-in caller alone does not establish tenant membership.

This framework-neutral pseudocode shows the required ordering. execute_bound represents the chosen driver’s parameter API; adapt the placeholder syntax to that driver.

tenant := authorize_tenant(principal, requested_tenant)
connection := lease_one_connection()
try:
    begin_transaction(connection)
    execute_bound(connection,
        "SELECT set_config('app.tenant_id', $1, true)",
        [tenant.canonical_uuid])
    result := execute_bound(connection,
        "SELECT id, body FROM app.documents WHERE id = $1",
        [requested_document_id])
    commit_transaction(connection)
finally:
    rollback_if_open(connection)
    return_connection_only_if_clean(connection)

The context assignment and protected statements must use the same connection inside the same transaction. In autocommit mode, putting the assignment in a separate statement ends its transaction before the protected query. An ORM helper that silently uses another connection breaks the same contract.

The SET reference explains that transaction-local settings restore the previous session-level value at transaction end. Initialize and verify pooled sessions without a session-wide tenant value; a local setting cannot repair an already contaminated baseline. Avoid session-wide tenant assignments in request handlers. After rolling back to a savepoint preceding the assignment, establish context again before any protected query.

Treat the setting as trusted application state. This design depends on the server controlling the context setter and SQL execution; it does not establish isolation against arbitrary SQL execution under the shared application role. Keep parameter binding, tenant authorization, and restricted database access alongside RLS.

Trace views and elevated helpers

Follow the changed path through views and functions. CREATE POLICY documents that ordinary views use the view owner’s rights and applicable policies for underlying tables, unless the view uses security_invoker. A direct-table test does not prove the view path has the same boundary.

Inspect elevated functions for the identity they execute under and the operations they expose. Keep administrative bypass paths separate from ordinary requests and test their authorization explicitly. Avoid treating one correctly protected table as proof for every indirect access path.

Happy-path operator workflow

1. Freeze the proposed revision and write the allowed tenant, operation, role, and resulting-row contract.
2. Recreate the prior schema in a disposable database using the deployment’s PostgreSQL major version and relevant driver and pool configuration. Prepare known synthetic documents in tenants A and B through the fixture setup path.
3. Apply the reviewed migration and retain the complete policy inventory, ownership, role attributes, and grants.
4. Run allowed reads and mutations as the actual runtime identity through the application’s connection path, using the prepared documents.
5. Exercise the negative matrix below, then repeat it after connection reuse and transaction failures.
6. Attach sanitized assertions to the revision: expected and actual row identifiers, affected-row counts, transaction outcome, and cleanup result. Use synthetic labels rather than production document contents or connection credentials.

Prove isolation with an acceptance matrix

Each negative case needs an allowed control so a blanket outage cannot masquerade as isolation. Use a known foreign document identifier rather than a random missing identifier.

Test the pool path deliberately with a configuration that forces sequential reuse of a connection. Also exercise rollback to an earlier savepoint if the framework uses that pattern. Assert database state after denied writes; an error response alone does not show whether an earlier statement committed.

These are proposed acceptance checks. The SQL and pseudocode have not been executed against a reader’s schema or application.

Error-path operator workflow

If a negative test returns foreign data or changes a foreign row, block approval and retain the smallest synthetic reproduction tied to the revision. Check the effective role first, then policy composition, trusted context assignment, connection affinity, and indirect access paths. Repair the responsible boundary and rerun both allowed and denied cases from a clean fixture.

On a policy error, roll back the failed transaction before reuse. If cleanup cannot be confirmed, discard the connection according to the pool’s supported lifecycle. Do not retry with an owner credential, remove the tenant check, or disable RLS to make a request succeed.

Keep the API’s public error mapping consistent with its disclosure policy. PostgreSQL warns that uniqueness and foreign-key integrity checks bypass RLS and can reveal that a hidden value exists. A no-row SELECT result therefore does not prove the absence of every existence side channel. Review those constraints and outward errors separately.

Failure modes

Policies exist but RLS is disabled. Include activation state in the migration assertions; policy definitions alone do not enforce the boundary.

Tests run as a bypass identity. Repeat through the effective application role and verify role switching cannot recover privileged access.

A second permissive policy widens access. Compare the combined role-and-command expression before and after the patch.

The context setter trusts a request field. Require server-side tenant authorization before installing the setting.

A transaction-local setting runs in its own autocommit transaction. Keep assignment and protected statements in one explicit transaction on one connection.

A prior session setting returns after commit. Establish a clean pool baseline and test successive borrowers after both commit and rollback.

A denied write is followed by privileged fallback. Preserve the denial and repair the contract instead of changing execution authority.

FAQ

Is ENABLE ROW LEVEL SECURITY enough?

It activates policies for access subject to RLS. You still need appropriate policies, table privileges, a non-bypass runtime identity, and tests of the actual query path. With RLS enabled and no applicable policy, PostgreSQL uses default denial.

Does FORCE ROW LEVEL SECURITY cover every role?

It makes the table owner subject to row security. Superusers and roles with BYPASSRLS still bypass it. Keep those identities outside the ordinary runtime path.

Does row_security = off bypass policies?

No. The Row Security Policies documentation says this setting raises an error when a query would be filtered by a policy. It does not grant a normal role bypass authority. PostgreSQL describes it as useful for detecting unintentionally filtered backup results.

Does RLS replace object authorization?

A tenant policy establishes the tenant boundary it expresses. It does not automatically implement private-document ownership, sharing, or separate read and edit grants inside that tenant. Define and test those rules at their intended enforcement points.

Reader next step

Choose one agent-written change that reaches a tenant table. Record its effective runtime role, complete applicable policy set, and transaction context lifecycle. Create two tenants with known documents, force connection reuse, and prove both legitimate access and denied cross-tenant writes before approving that revision.