pg-connection-pool — Query Gateway

A lightweight query-execution gateway (Fastify + pg + zod + pino). It owns one pooled pg.Pool per logical datasource and runs queries on a caller's behalf under guardrails. Callers know only a logical datasource name + SQL — never a connection, credentials, host, or engine type. A parallel MCP adapter exposes the same capability to agents.

Design note (authoritative): ../docs/design-notes/pg-connection-pool-query-gateway.md

Why

External consumers (application services and debugging agents) need real data from Postgres to reproduce scenarios, without managing credentials or connection lifecycle. A live socket can't cross an HTTP/MCP boundary, so the server owns the pool and exposes a guarded QUERY capability instead of a connection.

Related MCP server: PostgreSQL MCP Server

Core invariants

• Server owns the pool, exposes a QUERY capability — never a raw connection.

• One pg.Pool per datasource, shared across tenants. Tenant isolation is per-query: every query runs inside a transaction with SET LOCAL search_path TO "<schema>", pg_temp, which auto-resets on COMMIT/ROLLBACK so a pooled connection can't leak one tenant's search_path to the next borrower. This is a P0 correctness invariant.

• Connections are returned pristine: SET LOCAL only auto-resets the settings we issue — a caller's own plain SET survives COMMIT on that pooled connection. Every query therefore ends with DISCARD ALL, which also drops temp tables and prepared statements. Without it, SET statement_timeout = 0 in one request silently disabled the timeout guardrail for every later borrower of that connection.

• One statement per request, enforced by the wire protocol. All SQL runs through the extended protocol (queryMode:'extended'), under which the server rejects multi-statement text. This is structural; the text scanner is only a fast 400.

• Read-only by default, enforced at the engine (BEGIN TRANSACTION READ ONLY) with a DB-role backstop; an app-layer statement guard (leading-keyword allowlist + banned side-effecting-function scan) adds defense-in-depth for what a read-only txn does not stop (COPY, pg_read_file, …), and a relation guard (real-parser tree walk) bounds what/where a query may read (catalog block + schema caps + denied tables) — belt to the braces, never the sole barrier. Writes are opt-in (see below).

• Layered timeouts: connectionTimeoutMillis (acquire), statement_timeout (query), idle_in_transaction_session_timeout (stalled txn).

• acquire → use → release-in-finally; a mandatory pool.on('error') handler on every pool; graceful shutdown drains pools; fail-fast boot (one SELECT 1 per datasource).

Configuration (.env)

See .env.example. Logical datasource names decouple callers from real endpoints:

DATASOURCES=main
DS_MAIN_HOST=localhost
DS_MAIN_USER=postgres
DS_MAIN_PASSWORD=postgres
DS_MAIN_DATABASE=appdb
DS_MAIN_DEFAULT_SCHEMA=public
DS_MAIN_STATEMENT_TIMEOUT_MS=10000

TOKENS=agent_ro,svc_rw
TOKEN_AGENT_RO_SECRET=...   TOKEN_AGENT_RO_DATASOURCES=main  TOKEN_AGENT_RO_MODE=read   TOKEN_AGENT_RO_SCHEMAS=*
TOKEN_SVC_RW_SECRET=...     TOKEN_SVC_RW_DATASOURCES=main    TOKEN_SVC_RW_MODE=write    TOKEN_SVC_RW_SCHEMAS=public

• Fallback: if NO DS_* datasource is configured, a single main is seeded from the canonical DATABASE_HOST/PORT/USERNAME/PASSWORD/NAME/SSL.

• Hard caps: MAX_ROWS_CEILING (default 10000) is the absolute row cap; a request's maxRows is clamped to it. A request's timeoutMs is clamped to the datasource's STATEMENT_TIMEOUT_MS.

• Binding: HOST defaults to 127.0.0.1. Only loopback binds without ALLOW_PUBLIC_BIND=true — see Network binding.

• Denied tables: DS_<NAME>_DENIED_TABLES is a comma list of relations the relation guard rejects (400) before DB contact. Entries are table (matches in ANY schema) or schema.table (exact); case-insensitive. Code default is EMPTY — the gateway is generic, so a deployment must declare its own list in .env (see .env.example); boot logs the count.

• DB role: point each datasource at a read-only Postgres role; that, not the token mode, is the real write barrier. See The read-only guarantee.

HTTP API

Auth is Authorization: Bearer <secret>. POST /query body:

{ "datasource": "main", "schema": "<account-uuid>", "sql": "SELECT ...",
  "params": [1, "x"], "readOnly": true, "maxRows": 1000, "timeoutMs": 10000 }

Response: { columns:[{name,dataType}], rows:[...], rowCount, truncated, elapsedMs, rowsAffected? }.

Writes (opt-in)

A write requires both a write-mode token and an explicit "readOnly": false. A read-only token with readOnly:false is rejected (403) before any DB contact. Write queries add write:true, command, and rowsAffected to the audit line.

MCP adapter

Exposes run_query, list_schemas, list_tables, describe_table over MCP — thin wrappers over the SAME services, so all guardrails/auth/audit apply identically.

The server also advertises usage instructions (built from the live identity — the datasource list and read/write posture are accurate, not aspirational). MCP clients like Claude Code surface these in the agent's system prompt, so any project that registers the server gets the usage guidance automatically ("prefer over ad-hoc psql", the datasource name, the params-not-literals rule) — no per-project CLAUDE.md rule to write or keep in sync. That said, the guidance appears only in clients that surface MCP server instructions; if you want the preference committed in-repo for teammates or non-surfacing clients, add a short project rule too.

• Identity is process-level: set MCP_TOKEN=<a configured secret>; the process runs with that token's capabilities.

• npm run start:mcp — stdio (default; for a local agent client).

• npm run start:mcp:http — streamable HTTP (MCP_TRANSPORT=http), binds loopback by default (MCP_HTTP_HOST/MCP_HTTP_PORT). Never expose publicly — trusted infra utility.

• npm run inspect — MCP inspector against the built server.

Quick install into any project

install-mcp.sh registers this gateway as a Claude Code MCP server pointing at this shared install (its single .env = one home for DB creds + MCP_TOKEN). It never copies the server, so credentials never fork. It builds the gateway if needed, refuses to register against a missing/placeholder .env, and is idempotent.

./install-mcp.sh              # user scope → available in EVERY project, nothing else to do
./install-mcp.sh --project    # write ./.mcp.json here (committable — teammates inherit it)
./install-mcp.sh --project DIR # write DIR/.mcp.json
./install-mcp.sh --local      # this project only, private
./install-mcp.sh --link       # symlink onto PATH as `pgcp-mcp-install` for future one-liners
./install-mcp.sh --print      # just print the JSON block

Then run /mcp in the Claude Code session to connect. Because it emits an absolute path, the entry works from any project regardless of where it lives.

Scripts

npm run dev         # tsx watch (HTTP server)
npm run build       # tsc → dist/
npm start           # node dist/server.js  (HTTP)
npm run start:mcp   # MCP over stdio
npm run typecheck
npm test            # node --test via tsx

Local Postgres (docker-compose.yml)

A PostgreSQL 18 container for local development:

docker compose up -d          # start
docker compose logs -f pg     # watch
docker compose down           # stop (data survives in the pgcp-pgdata volume)
docker compose down -v        # stop AND delete the data

Credentials are not duplicated. Compose auto-loads .env from this directory, so DS_MAIN_USER / _PASSWORD / _DATABASE become the container's POSTGRES_* and the compose file itself holds no secrets. A missing value is a startup error, not a password-less superuser. Only POSTGRES_* are passed in — the bearer secrets and MCP_TOKEN are deliberately withheld from the DB container.

The host port mirrors DS_MAIN_PORT, so pointing the gateway at this container is a one-line change: set DS_MAIN_HOST=localhost.

Two deliberate choices worth knowing:

• Published on 127.0.0.1 only. A bare "5432:5432" publishes on every interface — the same footgun Network binding refuses for the gateway itself.

• PGDATA is set explicitly (to a subdirectory of the mount) rather than inherited from the image, whose default has moved between major versions.

It is an empty database

A fresh container has none of the dev data, so two things do not work out of the box:

• DS_MAIN_DEFAULT_SCHEMA is a tenant UUID schema that will not exist. The boot ping (SELECT 1) passes, but any query resolving that search_path fails until you create the schema or repoint the var (e.g. public).

• The read-only role agent_ro_pg does not exist. Creating it is a user action (see The read-only guarantee) — run that SQL against the container as the superuser above, then set DS_MAIN_USER=agent_ro_pg. Until then the boot log will correctly report read-only posture WEAK, because the superuser this container creates can write everything.

Testing

• Unit + E2E route tests run with no database (a stub QueryDriver drives the full HTTP/MCP path). npm test.

• Integration tests (test/integration/*) hit a real Postgres and are SKIPPED unless configured — they prove the P0 tenant isolation, engine read-only enforcement, statement_timeout, write commit/rollback, and the SECURITY:-prefixed regressions (server-side multi-statement rejection, read-only-escape, session scrubbing) that a stub cannot. Run with:

  PGCP_TEST_HOST=localhost PGCP_TEST_USER=postgres PGCP_TEST_PASSWORD=postgres \
  PGCP_TEST_DATABASE=postgres npm test

  (falls back to DATABASE_* if PGCP_TEST_* are absent). They create/drop throwaway pgcp_test_a / pgcp_test_b schemas.

Security notes

• Bearer secrets are compared in constant time and never logged (audit logs the token id, not the secret).

• The schema is applied as a quoted identifier (validated, double-quote-escaped); all values must be passed via params ($1…). The audit line records SQL text and error text (each capped at 2,000 chars with a …[+N chars] marker; a truncated statement also records sqlLength so it stays attributable) while params are never logged — so never inline secret values as SQL literals, use params.

• Not an ORM/migration runner (never generates or runs migrations), not a general SQL console — it's an infra utility for trusted services/agents.

• MCP over HTTP requires the caller to present the process's own token as a bearer and enables DNS-rebinding protection (Host allow-list); it still binds loopback by default.

• /health is unauthenticated but its DB pings are cached (HEALTH_CACHE_TTL_MS, default 5000ms) and de-duplicated, so probe floods can't exhaust the pool.

• Query error messages are returned to the caller verbatim (a debugging affordance); combined with the boundary below, treat error text as revealing structure.

The read-only guarantee lives in the database

A token's MODE=read and the BEGIN TRANSACTION READ ONLY wrapper are app-layer controls. They are correct, but they are app logic: one gateway bug, or one MODE=read→write edit, removes them. The guarantee that survives that is a Postgres login role with no write grants:

-- USER ACTION (privilege changes are never run by an agent). Run as superuser/owner.
CREATE ROLE agent_ro_pg WITH LOGIN PASSWORD '<strong-password>';
-- pg_read_all_data (PG14+): SELECT on all current AND FUTURE relations + schema USAGE.
-- Exactly right for schema-per-tenant, where new schemas appear at runtime.
GRANT pg_read_all_data TO agent_ro_pg;
-- Second lock: a plain BEGIN (the write path) is read-only too, so a mode misconfig
-- still cannot write. Advisory only — it is USERSET, so a caller can turn it off.
ALTER ROLE agent_ro_pg SET default_transaction_read_only = on;

-- PG14 only: PUBLIC still holds TEMP on the database, so any role can CREATE TEMP
-- TABLE and write to it. Session-local and cleared by DISCARD ALL, so it cannot touch
-- tenant data — revoke it anyway if you want the "no writes at all" claim to be literal.
-- REVOKE TEMP ON DATABASE <db> FROM PUBLIC;
-- Also PG14: PUBLIC holds CREATE on schema `public` unless revoked (PG15 changed the
-- default). Check with:
--   SELECT has_schema_privilege('public','public','CREATE');
-- REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Then set DS_<NAME>_USER=agent_ro_pg + its password. After this the role can write no existing relation regardless of token mode, transaction mode, a smuggled statement, or a gateway bug — it holds no privilege to do so.

Do not substitute GRANT pg_write_all_data for explicit grants on a write datasource: its privileges are implicit in the ACL check and leave no per-table grant row, so naive audit queries report it as harmless. (The boot probe below handles it explicitly.)

Every boot reports the posture for each datasource (read-only posture OK / WEAK / UNVERIFIED). It asks whether the role can write — has_table_privilege / has_any_column_privilege over every non-catalog relation, plus explicit superuser and pg_write_all_data checks — rather than counting information_schema.table_privileges rows, which are built from relacl and therefore blind to column-level grants, predefined roles and superuser. It fails closed: a probe error or an unexpected result shape is reported UNVERIFIED, never OK. It is a warning, never a hard failure — a legitimately write-capable datasource may exist — so the point is that a misconfiguration is loud rather than found in a later audit.

One caveat the probe cannot cover: if dblink or postgres_fdw is installed, EXECUTE defaults to PUBLIC and dblink('…','INSERT …') writes over a separate connection — outside the read-only transaction and outside any grant the role holds. Revoke EXECUTE on those functions, or don't install them on a database this gateway reaches.

Runbook for creating the role: docs/runbooks/agent-ro-pg-role.md.

Statement guard (defense-in-depth)

A Postgres READ ONLY transaction blocks DB data/catalog writes but not COPY … TO PROGRAM/'file', pg_read_file/pg_ls_dir, backend signals (pg_terminate_backend), or WAL messages (pg_logical_emit_message) — all catastrophic under a superuser role (see the risk report). Every call therefore passes an app-layer guard at the single QueryService.run() choke point (so HTTP /query, MCP run_query, and introspection are all covered), before any DB contact:

• Leading-keyword allowlist. Read mode permits SELECT, WITH, EXPLAIN, VALUES, TABLE; write mode adds INSERT, UPDATE, DELETE, MERGE. Anything else (COPY, CALL, DO, SET, ALTER, CREATE, …) is rejected — an allowlist needs no per-keyword maintenance. SHOW was removed — it leaks server settings (SHOW all → data_directory, config_file, connection strings) and exposes no relation for the relation guard to police; read a specific setting with current_setting('name') or SELECT version() instead.

• Banned-function scan. Side-effecting/host-access functions (pg_read_file, pg_ls_*, lo_export, dblink*, pg_reload_conf, pg_terminate_backend, pg_logical_emit_message, pg_stat_reset*, …) are rejected in both modes, even inside an allowed statement (SELECT pg_read_file(…)).

Both run over the shared stripToCode view (sql-lexer.ts) that blanks comments/strings/dollar-bodies, so the checks can't be comment- or string-bypassed (SELECT 'pg_read_file(' AS note is allowed; SELECT/**/pg_read_file(…) is not). The function scan reads an ident-revealing variant so a quoted call SELECT "pg_read_file"(…) — which Postgres resolves to the same function — is caught too. A rejection is a 400 and is audited (blocked attempts, including ;-smuggle, show up in the security stream). The multi-statement scan and the read-only transaction are separate and always-on.

The U&"\0070g_read_file" unicode-escape identifier a text scanner cannot decode is now closed by the relation guard below, which runs the same banned-function list against the real parser's decoded names. Both scans share one list (banned-functions.ts) so they cannot drift; this text scan stays as belt-and-braces for SQL the parser accepts but shapes differently.

This is defense-in-depth, not the guarantee — it ships with the non-superuser DB role above, never instead of it (denylists drift; the role holds no privilege to begin with).

Escape hatch. A datasource whose DB role is trusted and which legitimately needs admin/COPY/file statements can opt out with DS_<NAME>_ALLOW_UNSAFE_STATEMENTS=true (default false, fail-closed — any non-true value keeps the guard on). It relaxes the statement guard and the relation guard below; the multi-statement scan and the read-only transaction stay enforced. Enabling it removes two security layers, so every boot logs a WARN naming the datasource.

Relation guard (schema boundary + metadata block + denylist)

The statement guard answers whether a statement may run; it says nothing about what it reads. Read-only is a write control, and the DB role is pg_read_all_data — SELECT on every relation, forever — so on its own the gateway would expose information_schema, the pg_catalog views (pg_tables, pg_roles, pg_settings, …), and every identity/secrets table to any read token. Postgres cannot fix this at the grant layer: pg_catalog access cannot be revoked, and grants are additive so tables cannot be carved out of pg_read_all_data. So the boundary is enforced at the app layer, at the same QueryService.run() choke point, before any DB contact:

• Parse, don't pattern-match. Every statement is parsed with the real Postgres parser (libpg-query); the guard walks the parse tree for the relations it references (CTE-aware — a WITH name is not a table) and the functions it calls. This includes write/create targets on the write path — the INSERT/UPDATE/ DELETE/MERGE target and SELECT … INTO — so a write-mode token is confined to its caps and the denylist just as reads are (a target named like an enclosing CTE is still the real table). Decoded identifiers close the U&"\0070g_read_file" / U&"\0075ser" unicode-escape gap a lexer cannot. Unparseable SQL is rejected (400) — SQL the gateway cannot understand is SQL it cannot police (fail-closed).

• Five relation rules: a relation qualified with a system schema (pg_catalog, pg_toast, pg_temp*, information_schema) → 400; qualified with another schema outside the token's SCHEMAS caps → 403; an unqualified pg_% name (which resolves to pg_catalog, implicitly first on search_path) → 400; the effective schema + relation on the datasource's DS_<NAME>_DENIED_TABLES list → 400; and a relation whose name matches the built-in sensitive-relation denylist → 400.

• Built-in sensitive-relation denylist (secure-by-default). Because DS_<NAME>_DENIED_TABLES ships empty, a name-pattern safety net (sensitive-relations.ts) blocks obvious credential/secret/token/key stores (secrets, api_keys, oauth_tokens, private_keys, recovery_codes, mfa_secrets, vault, …) even when no denylist is configured. It is a token-boundary match (user_secrets, oauth_access_tokens are caught), with a small curated set of zero-false-friend roots (credential, apikey, password) also matched as a substring so glued compounds (credentialstore) are caught, and it deliberately excludes generic identity tables (users, accounts, sessions) to avoid over-blocking ordinary reads. Default on, fail-closed — only an explicit DS_<NAME>_SENSITIVE_RELATION_DENYLIST=false/0/no/off turns it off (boot WARNs when it does); skipped by ALLOW_UNSAFE_STATEMENTS.

• Connection-identity functions blocked (secure-by-default, always on). run_query returns table data; it must not answer "who / where am I?". Identity functions (connection-info-functions.ts) that would read back the datasource's real user / database / host — current_user, session_user, current_role, user, current_database, current_catalog, current_schema(s), inet_server_* / inet_client_* — are rejected (400). Both parse shapes are covered: the keyword forms are SQLValueFunction nodes, the call forms FuncCall. current_setting is allowed except for the identity GUCs session_authorization / role (and a non-literal argument fails closed) — so current_setting('session_authorization') cannot be used as a back door to the session user. Date/time value functions (now(), current_date, …) carry no identity and pass. This closes the SELECT current_user, current_database() path an agent could otherwise use to disclose the values .env holds.

• Metadata path. list_schemas / list_tables / describe_table are the sanctioned, caps-filtered way to see structure. They run fixed, parameterized information_schema reads on an internal trusted route that a caller cannot request — the trust flag is a second positional argument to run(), never a request field, so no HTTP body or MCP args object can set it. (The boot posture probe uses the same internal route.)

• Escape hatch. DS_<NAME>_ALLOW_UNSAFE_STATEMENTS=true skips the statement guard and this relation guard; boot WARNs. Every boot also logs the guard state and denied-table count per datasource.

Rejections (both 400 and 403) are audited — a qualified cross-schema 403 is the tenant-probe signal worth alerting on.

Error redaction. Error text returned to a caller (MCP tool result / HTTP body) is run through redact-error.ts: connection URIs, password=/user=… params, pg auth-failure user names, and getaddrinfo/connect host/address details are masked, so a connection failure can never echo connection metadata to the LLM. The full detail still goes to the server-side audit log. Useful SQL errors (syntax, undefined column) pass through unchanged.

Residual risks (accepted). A view in an allowed schema over a denied table still reads it (views run with owner privileges; no such views are known — the DB-grants runbook is the durable fix). Connection-identity functions (current_user, current_database(), inet_server_*, current_setting('session_authorization')) are now blocked (above), but non-identity scalar metadata such as version() and other current_setting('<guc>') reads stay readable — server fingerprint, not connection identity or table data. And this is app-layer enforcement over a shared pg_read_all_data role: it holds only as long as the guard does. The belt exists; explicit per-token grants (runbook) are the braces.

Network binding

HOST defaults to 127.0.0.1, and only loopback binds without an explicit opt-in — anything else needs ALLOW_PUBLIC_BIND=true. This gateway holds live DB credentials and, over HTTP, the only thing in front of them is a plaintext bearer secret, so binding beyond this machine must be a deliberate decision made with TLS termination and a rotated secret.

The check is an allowlist, not a denylist, because the set of hosts that bind every interface cannot be enumerated: 0.0.0.0, ::, 0, 0.0, ::0, 0x0 and the empty string all do it (an empty or bare-integer host is resolved rather than rejected, and net.isIP('0') is 0, so IP parsing does not catch it either). Requiring loopback is the one rule that cannot be out-spelled. Note this means binding a specific private address also needs the flag — deliberate either way.

The guard covers both listen() sites: the Fastify gateway (HOST) and the MCP streamable-HTTP transport (MCP_HTTP_HOST), whose env vars bypass zod and so are normalised for the empty-string case at the call site.

Rotating the bearer secret

Secrets are plaintext in .env and compared as constant-time SHA-256 digests — never logged. Rotation is therefore the only exposure control:

1. Generate: openssl rand -base64 24

2. Update both TOKEN_<ID>_SECRET and MCP_TOKEN in .env — the MCP process authenticates as one of the configured tokens, so a mismatch fails boot with MCP_TOKEN does not match any configured token.

3. Restart the gateway (npm start) and/or reconnect the MCP client (/mcp in Claude Code).

4. Rotate the DB password separately (ALTER ROLE … PASSWORD …, a user action) and update DS_<NAME>_PASSWORD; the bearer and the DB credential are independent secrets.

⚠️ Trust boundary — schema caps, and how far they now reach

Since the relation guard, a token's SCHEMAS capability is enforced against the relations a query actually references — not just the declared schema / search_path. A token scoped to public now gets a 403 on SELECT * FROM "other_tenant".t, because the guard parses the statement and checks every qualified relation against the caps (the same capabilityAllows used for the declared schema). SET LOCAL search_path still guarantees tenant isolation between pooled borrowers (the P0 invariant); the guard adds confinement of the SQL body on top of it.

Two limits remain, so this is a strong app-layer boundary, not a database-enforced one:

• It holds only as long as the guard does — every query still runs as one shared pg_read_all_data role, and a view in an allowed schema can read across into a schema the token could not name directly.

• For a hard per-token boundary, enforce it in Postgres: a DB role per token with USAGE revoked on other schemas, or explicit per-schema grants (docs/runbooks/agent-ro-pg-role.md). That is a deliberate follow-up, incompatible with the current single shared-pool topology.

TLS note

When SSL=true, the client uses rejectUnauthorized:false (a typical TypeORM config for managed Postgres with a self-signed chain) — this encrypts the connection but does not authenticate the server. Provide a CA / set stricter TLS if MITM is in scope.

search_path note

Only the target schema is put on search_path (not public), for isolation — so references to shared public objects must be fully qualified (public.foo). pg_temp is appended explicitly: when it is absent Postgres still searches it, and searches it first, ahead of the tenant schema, for relation names — so a temp table left on a pooled connection would shadow the next borrower's real table. Naming it demotes it to last place.

Why one statement per request is enforced twice

assertSingleStatement is a text scanner and text scanners lose. It previously missed E'…' escape strings, where \' is an escaped quote rather than a terminator: the scanner believed the literal was still open, swallowed the rest of the input, and let SELECT E'\''; COMMIT; <anything> through. That smuggled COMMIT ended BEGIN TRANSACTION READ ONLY and handed a read-scoped token a read-write session — the read-only guarantee and the "defence in depth" guard were never independent, because read-only lives on a transaction the caller could simply commit away.

The fix moves enforcement into the wire protocol. pg selects the protocol by whether params were supplied — with none it uses the simple protocol, which executes a; b from one call. PostgresDriver.exec now pins queryMode:'extended', so Postgres answers cannot insert multiple commands into a prepared statement regardless of what the scanner thinks. The scanner is kept purely to fail such input earlier, as a 400, before any DB contact. Do not revert exec to the two-argument client.query(sql, params) form — that silently restores the simple protocol and the whole exploit chain.

Known limitation

The row cap is enforced by fetching the result and slicing to maxRows (truncated:true if exceeded), rather than a server-side cursor. For this trusted-caller utility that is bounded in practice by statement_timeout + the row ceiling; a cursor-based bounded fetch is a possible future optimization.