Disconnected transaction handles can read another user's rows through RLS

A sql.begin() transaction handle can keep issuing queries after its backend disconnects. Once the pool reconnects and starts another transaction, the old handle executes against that replacement transaction.

This has security implications for applications that use a transaction-local setting to identify users in RLS policies. I reproduced the original user's transaction handle reading the replacement user's row with RLS enabled and a non-owner database role. PostgreSQL evaluates the policy using the replacement transaction's app.user_id, so the stale query inherits that user's access.

The fix is in #1215. The related connection-reuse report in #1204 does not establish this specific RLS case, and I haven't confirmed that its production incident has the same cause.

Reproduction

Tested with Node 22.22.0, PostgreSQL 17.11, and upstream commit 411429e7bd7a3d61155ca9a70a97c111823702ea (version 3.4.9).

Save this as repro-rls.mjs in a checkout of that commit. The script creates two synthetic users' rows and a separate login with only SELECT access to the test table. It verifies that RLS is active and each live transaction initially sees only its own row.

import assert from 'node:assert/strict'
import postgres from './src/index.js'

const role = 'postgres_js_rls_test'
const owners = { original: 'original', replacement: 'replacement' }
const rows = Object.values(owners).map(user_id => ({ user_id, secret: user_id + ' secret' }))
const admin = postgres({ max: 1 })
const pool = postgres({ user: role, database: 'postgres', max: 1, fetch_types: false })
let finish
  , ready
const gate = new Promise(resolve => finish = resolve)
const connected = new Promise(resolve => ready = resolve)

try {
  await admin`create role ${ admin(role) } login`
  await admin`create table rls_repro (user_id text not null, secret text not null)`
  await admin`insert into rls_repro ${ admin(rows) }`
  await admin`alter table rls_repro enable row level security`
  await admin`create policy owner_only on rls_repro
    using (user_id = current_setting('app.user_id', true))`
  await admin`grant select on rls_repro to ${ admin(role) }`

  const failed = pool.begin(async sql => {
    await sql`select set_config('app.user_id', ${ owners.original }, true)`
    const [{ active }] = await sql`select row_security_active('rls_repro') as active`
    assert.equal(active, true)
    assert.deepEqual(Array.from(await sql`select * from rls_repro`), [rows[0]])
    const [{ pid }] = await sql`select pg_backend_pid() as pid`
    ready({ disconnected: sql, pid })
    await gate
  }).catch(error => error)

  const { disconnected, pid } = await Promise.race([
    connected,
    failed.then(error => { throw error })
  ])
  await admin`select pg_terminate_backend(${ pid }::int)`
  assert.equal((await failed).code, 'CONNECTION_CLOSED')

  await pool.begin(async sql => {
    await sql`select set_config('app.user_id', ${ owners.replacement }, true)`
    const [{ active }] = await sql`select row_security_active('rls_repro') as active`
    assert.equal(active, true)
    assert.deepEqual(Array.from(await sql`select * from rls_repro`), [rows[1]])
    const result = await disconnected`select * from rls_repro`.catch(error => error)
    console.log(result)
    assert.equal(result.code, 'CONNECTION_CLOSED')
  })
} finally {
  finish()
  await new Promise(resolve => setImmediate(resolve))
  await pool.end({ timeout: 0 })
  await admin`drop table if exists rls_repro`
  await admin`drop role if exists ${ admin(role) }`
  await admin.end({ timeout: 0 })
}

Run it against a disposable PostgreSQL instance:

docker run --rm -d --name postgres-js-rls-repro -p 127.0.0.1:55432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust postgres:17
docker exec postgres-js-rls-repro pg_isready -U postgres
PGHOST=127.0.0.1 PGPORT=55432 PGUSER=postgres PGDATABASE=postgres node repro-rls.mjs
docker stop postgres-js-rls-repro

Wait for pg_isready to report that the server is accepting connections before running the script.

On upstream, the query through the disconnected transaction prints:

Result(1) [ { user_id: 'replacement', secret: 'replacement secret' } ]

The assertion then fails. The expected result is a CONNECTION_CLOSED error: the old transaction should no longer be usable.

Running the same script against commit 18cffe016927e01481b9e746587da789f4260a4c from #1215 returns CONNECTION_CLOSED and exits successfully. I verified both outcomes.

Conditions and impact

The original callback must remain pending across a backend disconnect, the pool must reuse its connection object, and work must issue a query through the old handle after the replacement transaction establishes its user context. The reproduction uses pg_terminate_backend to force that sequence. It does not establish that an untrusted client can trigger it in every application.

The demonstrated impact is a read across the application's user boundary. The PR also includes regressions for an old callback committing or rolling back a replacement transaction, and for queued queries remaining pending after disconnect.

@porsager, could you assess this as a security issue and review #1215? It includes the fix, four regression tests, and before/after testing instructions.

Start by running repro-rls.mjs against the disposable PostgreSQL instance to observe the stale handle behavior. Then inspect src/index.js and the four regression tests mentioned in #1215. Done means the disconnected transaction handle returns CONNECTION_CLOSED and the regression coverage passes.