@yawlabs/postgres-mcp

@yawlabs/postgres-mcp

Query a PostgreSQL database from Claude Code, Cursor, and any MCP client. Read-only by default - writes opt in via a single env var - so an agent can't silently drop your tables.

Built and maintained by Yaw Labs.

One click adds this to your local Yaw MCP config so it's available in every Yaw Terminal session. Or install manually below.

What's new in 0.12

An index advisor, opt-in audit logging, structured tool output, and support for current-revision MCP clients. Full detail in the CHANGELOG.

• pg_index_advisor recommends indexes for a workload and keeps only the ones that measurably lower estimated cost. Candidates are costed with HypoPG hypothetical indexes, never created on disk, and the search knows that PostgreSQL 18's skip scan changes which multi-column indexes are useful.
• Opt-in audit logging of the SQL tools send: one JSON line per audited statement, to stderr or a file. Bound parameter values are never recorded. Off by default, and not a complete record -- see Audit logging.
• Structured tool output. Every tool declares an outputSchema and returns structuredContent alongside the unchanged text block, so anything reading the text today keeps working.
• Both MCP protocol eras. The server used to speak only the legacy (2025) protocol revisions, which a client on the current 2026-07-28 revision fails against. It now serves both.

On 0.12.0? Upgrade. 0.12.1 closes a stacked-query hole in pg_index_advisor: SQL passed in its statements argument ran on a protocol that accepts several commands in one string, so SELECT 1; COMMIT; DROP SCHEMA public CASCADE; escaped the read-only transaction. The tool is annotated read-only, so hosts often auto-allow it. The same release stops pg_inspect_locks attributing a lock held in another database to whichever local table shares its OID, fixes the advisor's greedy search, and makes audit lines carry the tool field they were missing.

Coming from 0.10.x? 0.11.0 has three breaking changes: pg_seq_scan_tables, pg_unused_indexes and pg_top_queries return an envelope (read data.rows where you used to read data), pg_explain with analyze: true emits BUFFERS (pass buffers: false for the old output), and Node 22 is the floor. Details in the 0.11.0 changelog entry.

Backstory

Anthropic's reference Postgres MCP server, @modelcontextprotocol/server-postgres, was archived in May 2025 and marked deprecated on npm in July 2025. Anthropic has not shipped a replacement. Despite the deprecation, the last published version (v0.6.2) is still pulled ~20,000 times per week - a lot of agents are pointed at an unmaintained package.

That unmaintained package also has a known, publicly documented stacked-query SQL injection (Datadog Security Labs) that bypasses its BEGIN READ ONLY wrapper with input like COMMIT; DROP SCHEMA public CASCADE;. It has never been patched at npm.

A handful of community forks have appeared, but each fills a narrow slice:

• @zeddotdev/postgres-context-server - Zed's fork, primarily a security patch on the original shape.
• Postgres MCP Pro (Crystal DBA) - focused on index tuning and hypothetical-index / buffer-cache diagnostics.
• AWS Labs Postgres MCP - tied to Aurora / RDS Data API + Secrets Manager.

None of them position themselves as a general-purpose daily driver you'd hand to Claude Code or Cursor against an arbitrary Postgres: modern introspection, perf helpers, role/privilege awareness, and a write-safety posture out of the box. That's the gap @yawlabs/postgres-mcp fills.

Why this one?

• Read-only by default, with an unconditional read-only tool too - pg_query runs user SQL in a BEGIN READ ONLY transaction, so postgres itself (not string parsing) blocks writes; opt in to writes with ALLOW_WRITES=1. pg_readonly is a separate tool that stays read-only regardless of ALLOW_WRITES, so hosts that gate tools individually (Claude Code permissions, mcp.hosting) can auto-allow it -- paired with a least-privileged role, since READ ONLY bounds writes to the database rather than every side effect (details).
• Role-based access as the primary control - the recommended posture is to use a least-privileged postgres role in DATABASE_URL (e.g. one with GRANT pg_read_all_data); postgres itself then enforces the boundary, no env var needed. See Configuring access.
• Extended query protocol for all user SQL - pg_query sends user input with queryMode: 'extended', which restricts each request to a single statement. This closes the stacked-query injection class (COMMIT; DROP SCHEMA x CASCADE;) that defeated the reference server's BEGIN READ ONLY wrapper. Integration test asserts the rejection.
• Parameterized queries - pg_query takes a params array for $1, $2, etc. No string-interpolated SQL in our code path.
• Written from scratch, actively maintained - not a fork of the deprecated code. Unit + integration tests (npm test, npm run test:integration) run against a real Postgres; releases cut via release.sh.
• Schema introspection built in - pg_list_schemas, pg_list_tables, pg_describe_table return columns, primary keys, foreign keys, and indexes without the agent having to remember pg_catalog joins.
• EXPLAIN as a first-class tool - text or JSON format, with optional ANALYZE. ANALYZE for non-SELECT statements requires ALLOW_WRITES=1 and always rolls back, so the plan is real but the written rows don't persist. (What Postgres never rolls back still sticks: a sequence the statement advanced stays advanced.)
• Perf diagnostics the deprecated server never had - pg_top_queries (from pg_stat_statements), pg_seq_scan_tables, pg_unused_indexes, pg_table_bloat, pg_inspect_locks, pg_replication_status. Answer "why is this slow?" in one tool call.
• Health snapshot - pg_health returns version, db size, connection counts, and the 10 longest-running active queries in one call.
• Role and privilege awareness - pg_list_roles and pg_table_privileges for the common "who can touch what?" questions.
• Instant startup - ships as a single bundled file with zero runtime dependencies. No multi-minute node_modules install on every npx cold start.
• Result truncation - large result sets are capped at POSTGRES_MAX_ROWS (default 1000) with a truncated: true flag, so a stray SELECT * FROM events doesn't blow out the model context.

Quick start

1. Create .mcp.json in your project root

macOS / Linux / WSL:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@yawlabs/postgres-mcp@latest"],
      "env": {
        "DATABASE_URL": "postgres://user:password@host:5432/dbname"
      }
    }
  }
}

Windows:

{
  "mcpServers": {
    "postgres": {
      "command": "cmd",
      "args": ["/c", "npx", "-y", "@yawlabs/postgres-mcp@latest"],
      "env": {
        "DATABASE_URL": "postgres://user:password@host:5432/dbname"
      }
    }
  }
}

Why the extra step on Windows? Since Node 20, child_process.spawn cannot directly execute .cmd files (that's what npx is on Windows). Wrapping with cmd /c is the standard workaround.

2. Restart and approve

Restart Claude Code (or your MCP client) and approve the postgres MCP server when prompted.

3. (Optional) Enable writes

Read-only is the default. If you want the agent to be able to INSERT, UPDATE, DELETE, or run DDL, add ALLOW_WRITES=1 to the env block:

"env": {
  "DATABASE_URL": "postgres://...",
  "ALLOW_WRITES": "1"
}

Prefer scoping this to dev/test databases - for production, leave writes off and use migration tools out-of-band.

Configuring access

The role in DATABASE_URL is the primary access control. Postgres has had a battle-tested permission system for 30 years; lean on it instead of relying on ALLOW_WRITES alone. A least-privileged role makes writes server-rejected no matter what tools or env vars are configured.

Read-only agent (recommended default):

CREATE ROLE mcp_reader LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE your_db TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT pg_read_all_data TO mcp_reader;

Point DATABASE_URL at mcp_reader. Postgres rejects every write, every DDL, every privilege change - regardless of ALLOW_WRITES. No app-level guard to bypass; the database is the boundary.

Scoped write agent (dev/test or narrow production use):

CREATE ROLE mcp_writer LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE your_db TO mcp_writer;
GRANT USAGE ON SCHEMA public TO mcp_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO mcp_writer;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO mcp_writer;
-- DDL not granted -- the agent can change data but not schema.

Set ALLOW_WRITES=1 so pg_query will issue writes, and rely on the role to keep the agent away from DDL and other schemas.

Per-tool gating in the host:

Tools split cleanly across two authority classes:

• Auto-allow: pg_readonly (server-side BEGIN READ ONLY, unconditional), pg_index_advisor (only ever EXPLAINs the statements it is given, never with ANALYZE, inside BEGIN READ ONLY), plus the introspection and diagnostics tools (pg_list_*, pg_describe_table, pg_search_columns, pg_health, pg_inspect_locks, pg_table_bloat, pg_unused_indexes, pg_seq_scan_tables, pg_top_queries, pg_io_stats, pg_replication_status, pg_advisor, pg_table_privileges).
• Always prompt: pg_query (writes when ALLOW_WRITES=1 is set and the role allows it), pg_explain (analyze: true executes the statement; with ALLOW_WRITES=1 that includes writes, which are rolled back, and without it the tool has the same reach as pg_readonly), pg_kill (changes session state; requires ALLOW_WRITES=1).

The split follows each tool's readOnlyHint annotation, and Claude Code's permissions block and mcp.hosting's per-tool toggle both honor it.

What READ ONLY does and does not cover. A BEGIN READ ONLY transaction blocks writes to the database -- INSERT/UPDATE/DELETE, DDL, nextval/setval. It does not block functions whose effect lands outside the table data. SELECT pg_terminate_backend(...), pg_cancel_backend, pg_read_file, lo_export, and COPY ... TO PROGRAM all run to completion inside pg_readonly, which means auto-allowing pg_readonly reaches the same capability that pg_kill puts behind ALLOW_WRITES=1. Every one of them still requires a privilege the DATABASE_URL role must actually hold (pg_signal_backend, pg_read_server_files, superuser), so the role is the control that bounds this tool, not the transaction mode. If you auto-allow pg_readonly, use a least-privileged role -- see Configuring access.

ALLOW_WRITES as defense-in-depth:

ALLOW_WRITES is a secondary belt-and-braces gate. Useful when:

• You're on a managed database where creating a second role is awkward (some Supabase/Neon plans).
• You want a single role that can write, but want the MCP server to refuse writes anyway during normal operation.

Otherwise, configure the role and stop relying on ALLOW_WRITES.

What can an agent do with this?

Once connected, the agent picks tools automatically based on what you ask. A few single-tool examples:

• "Describe the users table" -> pg_describe_table -> returns kind, columns, PK, FKs, indexes.
• "Which tables have a user_id column?" -> pg_search_columns with pattern user_id -> one call instead of iterating every table.
• "This query is slow, why?" -> pg_explain with analyze: true -> returns the plan with actual row counts and timing.
• "What's the slowest query we run?" -> pg_top_queries -> returns the top N from pg_stat_statements with mean/total/min/max times.
• "Do we have any unused indexes?" -> pg_unused_indexes -> returns non-unique, non-primary indexes with zero or low scan counts + their size.
• "Is pgvector installed?" -> pg_list_extensions -> yes/no with version.

The bigger leverage is multi-tool reasoning. A few real workflows:

• Unstick a hung app. pg_inspect_locks returns blocked PID + blocking PID + the offending query, then pg_kill (ALLOW_WRITES=1 required) cancels the blocker. The agent can run both in one turn - it's the fastest path from "the app is frozen" to "back up."
• Chase a slow page. pg_top_queries ranks the worst queries, pg_explain with analyze: true shows the plan for the top hit, pg_seq_scan_tables and pg_unused_indexes say whether the answer is "add an index here" or "drop a dead one there."
• Oncall triage. pg_health checks connectivity + active-query count + database size; pg_inspect_locks and pg_replication_status confirm whether contention or replication lag is in play before paging the on-call DBA.

Tools

Tool	Description
pg_readonly	Run a SQL statement with no persistent data changes - always inside BEGIN READ ONLY, regardless of ALLOW_WRITES. The recommended tool for read access, and the one to auto-allow for ad-hoc SQL; pair it with a least-privileged role (why).
pg_query	Run a SQL query. Writes gated by the role in DATABASE_URL first, ALLOW_WRITES second. Supports parameterized queries via params. Result fields include dataTypeName (e.g. int4, jsonb) alongside dataTypeID.
pg_list_schemas	List non-system schemas.
pg_list_tables	List tables (and optionally views) in a schema with estimated row counts. Paginated via limit/offset.
pg_describe_table	Kind, columns, PK, outgoing FKs, incoming FKs (referenced_by), CHECK / UNIQUE / EXCLUDE constraints, indexes, and partition parent/children for a relation. Generated and identity columns are flagged (generated, identity, generation_expression) so an agent doesn't try to write to them. Constraints carry validated, plus enforced / has_period on PG18+.
pg_list_views	List views and materialized views in a schema, including their SQL definitions.
pg_list_functions	List functions, procedures, and aggregates in a schema with signatures and return types.
pg_list_extensions	List installed extensions (pgvector, postgis, pg_stat_statements, etc.) with versions.
pg_search_columns	Find columns by name pattern across all user schemas. Case-insensitive, supports SQL LIKE wildcards.
pg_explain	EXPLAIN or EXPLAIN ANALYZE for a SQL statement. Text or JSON output. Planner options: buffers (on by default with analyze), settings, verbose, wal, costs, timing, plus generic_plan (PG16+, plan a parameterized query with no values) and memory / serialize (PG17+). Optional hypothetical_indexes (requires the HypoPG extension) lets you ask "what would the plan be with these indexes?" without creating them on disk.
pg_index_advisor	Recommend indexes for a workload and prove each one pays for itself first. Takes statements you pass or the top N from pg_stat_statements, harvests candidate columns from what the planner reports as filters / join keys / sort keys (no SQL parser - every token is intersected with the real pg_attribute column list), then costs each candidate with HypoPG hypothetical indexes and keeps only what measurably lowers estimated cost. Greedy and bounded via max_candidates / max_explains, so a big workload cannot run away; budget_exhausted flags a truncated search. Returns the CREATE INDEX (plus a CONCURRENTLY form), cost before/after, which statements each index helps, and the estimated size. PG18-aware: PG18 added B-tree skip scan, so a multi-column index whose leading column is never filtered is no longer useless - that classic prune is gated on the server version rather than applied blindly. Requires HypoPG; indexes are session-scoped and reset on every exit path.
pg_health	Server version, database size, connections against max_connections, active queries with wait events and transaction age, pg_stat_database rollup (deadlocks, temp files, cache hit ratio), table count.
pg_top_queries	Top N queries by total/mean execution time. Requires the pg_stat_statements extension. Returns stats_reset (from pg_stat_statements_info, a different clock from the other stats tools) and dealloc on extension 1.9+ - a non-zero dealloc means entries were evicted past pg_stat_statements.max, so the ranking is drawn from an incomplete population.
pg_seq_scan_tables	Tables with heavy sequential scans - missing-index candidates. Returns the stats_reset window alongside the rows, since the counters mean nothing without it. last_seq_scan / last_idx_scan on PG16+.
pg_unused_indexes	Non-unique, non-primary indexes with low scan counts - drop candidates. Also returns stats_reset: a recently reset counter makes every index look unused, which is how a load-bearing index gets dropped. last_idx_scan on PG16+.
pg_io_stats	I/O observability: pg_stat_io read/write/extend/fsync counts, bytes and times per backend type and context (PG16+), plus in-flight async I/O handles from pg_aios and the active io_method (PG18+).
pg_inspect_locks	Who is blocking whom right now (blocked PID, blocker PID, lock type, queries).
pg_list_roles	Database roles with login/superuser/createdb flags and group memberships.
pg_table_privileges	Who has SELECT/INSERT/UPDATE/DELETE/etc. on a table or whole schema.
pg_table_bloat	Tables with high dead-tuple ratios - VACUUM candidates.
pg_replication_status	Replication slots, connected replicas, and current WAL position.
pg_advisor	Rolled-up DBA lints in one call: sequence-exhaustion candidates, wraparound risk for both counters (per-database and per-table age(relfrozenxid) against autovacuum_freeze_max_age, and mxid_age(relminmxid) against autovacuum_multixact_freeze_max_age -- a lock-heavy workload can exhaust multixacts while xids look healthy; triggered_by says which), tables without a primary key, and (configurable) public tables with RLS disabled. The "what should I be looking at?" starting point.
pg_kill	Cancel a running query or terminate a backend connection. Requires ALLOW_WRITES=1.

Configuration

All env vars are read from the MCP server's environment:

Variable	Default	Purpose
DATABASE_URL	(required)	PostgreSQL connection string.
ALLOW_WRITES	unset	Secondary write gate for pg_query, pg_explain ANALYZE-of-writes, and pg_kill. Set to 1 or true to lift the BEGIN READ ONLY wrapper on the first two and let pg_kill run. The role in DATABASE_URL is the primary control - see Configuring access. Does not affect pg_readonly, which is unconditional.
POSTGRES_STATEMENT_TIMEOUT_MS	30000	Per-statement timeout.
POSTGRES_CONNECTION_TIMEOUT_MS	10000	TCP connect timeout. Without this, a dead host hangs until the OS gives up (~2 minutes).
POSTGRES_MAX_ROWS	1000	Cap on rows returned by pg_query.
POSTGRES_POOL_MAX	5	Max pool connections. Set to 1 for single-threaded backends (pglite-socket, PgBouncer transaction mode).
POSTGRES_SSL_REJECT_UNAUTHORIZED	unset	Set to false to skip TLS cert verification (for managed DBs using private-CA certs). Connection is still encrypted.
POSTGRES_APPLICATION_NAME	postgres-mcp	Value reported in pg_stat_activity.application_name, so agent traffic is identifiable to whoever is watching the database. An application_name in DATABASE_URL takes precedence over this.
POSTGRES_AUDIT_LOG	unset (off)	1, true or stderr turns on the audit log, one JSON line per audited statement, written to stderr unless POSTGRES_AUDIT_LOG_FILE is also set. 0, false or off is the explicit off. Case-insensitive; an empty value counts as unset. Any other value stops the server at startup. See Audit logging.
POSTGRES_AUDIT_LOG_FILE	unset	Append the audit lines to this file instead of stderr. Setting it alone turns auditing on. The server refuses to start if the value is empty, if the file cannot be opened, or if POSTGRES_AUDIT_LOG is explicitly off.
POSTGRES_AUDIT_REDACT	unset (off)	1 or true logs each statement's first keyword plus a SHA-256 of its text instead of the SQL. 0, false or off is the explicit off; an empty value counts as unset, so full SQL is logged. Does not turn auditing on by itself. Any other value stops the server at startup, even with auditing off.
POSTGRES_MCP_RUNTIME	auto	Which JS runtime executes the server: auto (the newest oam at 0.15.2 or newer, else Node), oam (the same, but exit with an error instead of falling back to Node), node (always Node -- launched with oam run, it hands off to Node on PATH). Case-insensitive; any other value behaves like auto. See Runtime.
OAM_BIN	unset	Path to an oam binary to use in preference to discovery, when it is 0.15.2 or newer. If it does not exist, is older, or will not run, the launcher says so on stderr and carries on with discovery.
POSTGRES_MCP_SANDBOX	unset	Exactly 1 runs the server under oam's --permission sandbox: filesystem and child processes denied, network limited to the host and port in DATABASE_URL (port 5432 if the URL names none). If DATABASE_URL names no host (e.g. postgres:///db with PGHOST), lists several hosts, or cannot be parsed, the network grant is left open. Any other value is ignored without a warning. It has no effect unless the server really runs under a freshly launched oam, and a fallback that runs without it does not say so, so pair it with POSTGRES_MCP_RUNTIME=oam, which exits instead of falling back. The launcher only runs oam 0.15.2 or newer; per oam's changelog, --permission did not cover all of fs and child_process until 0.9.1, and the port grant was not exact until 0.15.0. The file audit sink cannot open under the sandbox; use the stderr sink.

Supported Postgres versions

Tested on PostgreSQL 15, 17 and 18 in the integration matrix.

Works on PG13+, but note where upstream support actually sits: PG13 reached end of life on 2025-11-13 and PG14 does so on 2026-11-12. PG13/14 are not exercised here and are not a compatibility target going forward. PG12 and below are further out of support and some tools rely on columns that landed in PG13 (pg_replication_status reading wal_status, pg_top_queries reading *_exec_time).

Newer server versions unlock extra fields rather than being required. Every version-dependent column is gated on server_version_num and simply omitted on servers that predate it, so nothing errors -- you get a slightly thinner answer. The cut points that matter:

Server	What it adds
PG16+	last_idx_scan / last_seq_scan in the stats tools (index/table staleness rather than a bare counter), pg_explain generic_plan
PG17+	pg_explain memory and serialize
PG18+	Generated-column form (stored vs virtual), NOT NULL constraint validity, conenforced / conperiod constraint metadata in pg_describe_table, relallfrozen freeze coverage in pg_advisor. BUFFERS is on by default with EXPLAIN ANALYZE server-side

If the version probe fails, the server assumes the oldest supported shape rather than emitting SQL a server might reject.

Runtime

The published postgres-mcp command is a small launcher that prefers the newest oam runtime it can find and falls back to Node.

If you do not have oam, nothing changes. The fallback is not a re-exec: npm already started Node to run the launcher, so falling back is a plain import() of the server into that same process. It costs a few existsSync calls and no subprocess, and behaves identically to running dist/index.js under Node directly.

Which oam. Only oam 0.15.2 or newer -- the latest release -- is used. The launcher looks in the installed locations (%LOCALAPPDATA%\oam\bin then ~/.oam/bin on Windows, ~/.oam/bin elsewhere) and on PATH, asks every oam it finds for its version, and runs the newest; on a tie the installed copy wins. An older oam is passed over. On Windows only oam.exe counts; an oam.cmd / oam.bat shim is never run. The passed-over binaries and any shim are named on stderr only when no usable oam is found -- when a newer oam runs, nothing is printed about them. An OAM_BIN that cannot be used is always named whenever the launcher looks for an oam, even when a newer one then runs. When a host launches the command with oam run on oam 0.15.2 or newer, the server runs inside that oam with no second one -- except under POSTGRES_MCP_SANDBOX=1, which needs a freshly launched oam. A host oam older than 0.15.2 never serves the server itself: it hands off to the newest usable oam, else to Node on PATH, else exits with an error.

If you do have a usable oam, the server runs under it. Verified equivalent on both runtimes: all 23 tools register, queries return identical rows and dataTypeName values, and the error paths match. oam supplies every node: builtin the driver needs, including net, tls, crypto, and dns (SCRAM auth and the extended query protocol both work).

Startup cost, measured. windows-arm64, 1.4 MB bundle, postgres-mcp version (full module init), every binary warmed first, mean of 12 runs:

path	startup
standalone binary (oam compile)	298ms
oam run dist/index.js	306ms
node dist/index.js	358ms
launcher -> Node (in-process)	370ms
launcher -> oam (spawn)	409ms

oam starts faster than Node here. What the launcher costs is the spawn: reaching oam means Node has already booted, and that hop (~100ms) is larger than oam's ~52ms advantage. So through the npm bin, the two land within ~40ms of each other, and POSTGRES_MCP_RUNTIME=node is a marginal win rather than a meaningful one. Those figures were taken with one oam on the machine: the launcher now runs --version on every oam binary it finds, so each extra copy adds a probe.

Either way it is a one-time cost per MCP session, not per tool call -- hosts spawn the server once and hold it open. If startup genuinely matters, the standalone binary avoids the launcher entirely and is the fastest option.

Earlier releases of this README reported ~650-900ms for Node and ~980-1290ms for oam, and advised opting out of oam on that basis. Those figures were measured against cold, freshly-built binaries and reflected the Windows on-access virus scanner rather than either runtime. They were wrong in both magnitude and direction. Corrected in 0.9.1.

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@yawlabs/postgres-mcp"],
      "env": {
        "DATABASE_URL": "postgres://...",
        "POSTGRES_MCP_RUNTIME": "node"  // opt out of oam
      }
    }
  }
}

Connecting to managed Postgres (Supabase, Neon, RDS, etc.)

Most managed databases require TLS but serve certs signed by a private CA that Node's default trust store doesn't recognize. The symptom is one of:

• self signed certificate in certificate chain
• unable to get local issuer certificate
• unable to verify the first certificate

To allow the connection while keeping traffic encrypted, add POSTGRES_SSL_REJECT_UNAUTHORIZED=false to the env block:

"env": {
  "DATABASE_URL": "postgres://user:pass@host:5432/db?sslmode=require",
  "POSTGRES_SSL_REJECT_UNAUTHORIZED": "false"
}

This disables certificate chain verification only -- the TCP connection is still TLS-encrypted end-to-end. For production setups where you can install the CA, prefer putting the cert in the Node trust store (NODE_EXTRA_CA_CERTS) over disabling verification globally.

Shaving a round trip on PG17+. Postgres 17 added direct TLS negotiation, which skips the plaintext SSLRequest handshake before the TLS one. The bundled driver supports it, so append sslnegotiation=direct to your DATABASE_URL:

postgres://user:pass@host:5432/db?sslmode=require&sslnegotiation=direct

It is opt-in rather than a default because a PG16-or-older server will reject the connection outright, and the saving is one round trip per pooled connection -- worth it on a distant managed database, invisible on a local one.

Audit logging

Off by default. Turn it on in the env block with POSTGRES_AUDIT_LOG=stderr, or with POSTGRES_AUDIT_LOG_FILE alone:

"env": {
  "DATABASE_URL": "postgres://...",
  "POSTGRES_AUDIT_LOG_FILE": "/var/log/postgres-mcp/audit.jsonl"
}

The server then writes one JSON line per audited statement. Two captured from PostgreSQL 17 -- a parameterized query, then a failing one:

{"ts":"2026-09-12T19:26:42.587Z","tool":"pg_query","source":"user","sql":"SELECT $1::int AS n","params":1,"ms":2.827,"rows":1,"ok":true}
{"ts":"2026-09-12T19:26:42.591Z","tool":"pg_readonly","source":"user","sql":"SELECT * FROM does_not_exist_xyz","params":0,"ms":2.217,"rows":null,"ok":false,"sqlstate":"42P01"}

Field	Meaning
ts	ISO 8601 time the line was written, just after the statement finished.
tool	The MCP tool that issued the statement. Absent only when the server's query functions are called outside a tool handler, e.g. when embedding it in-process.
source	user for SQL passed to pg_query, pg_readonly or pg_explain. internal for everything else: the server's own catalog queries (one pg_describe_table call writes several), and every statement pg_index_advisor runs -- including workload SQL passed in its statements. Filtering on user does not show all agent-supplied SQL.
sql	The statement text as sent. For pg_explain that is the composed statement: EXPLAIN SELECT ..., or EXPLAIN (ANALYZE, BUFFERS) SELECT ... with options.
sqlKeyword, sqlSha256	Replace sql when POSTGRES_AUDIT_REDACT is on: the first run of letters after any leading whitespace or (, uppercased (UNKNOWN when anything else comes first, such as a comment), and a hex SHA-256 of the full text. The keyword is the first word, not the effect: a CTE that deletes logs WITH, and every pg_explain line logs EXPLAIN.
params	How many bound parameters were passed. The count, never the values.
ms	Wall-clock milliseconds for the audited step, not server execution time. user lines include the row-cap cursor's round trips; internal lines can include waiting for a pool connection or behind the same call's other catalog queries.
rows	null on failure. Otherwise the count postgres reported, or the rows returned when it reports none (SET logs 0). A capped SELECT reports at most POSTGRES_MAX_ROWS + 1, the extra row being how truncation is detected; statements that cannot use the cursor (EXPLAIN, SHOW, ... RETURNING) report their full count even when the response is truncated.
ok	Whether the statement ran. For agent SQL it does not mean it committed: the COMMIT that follows is not logged, so a write whose commit fails still reads true.
sqlstate	Only when ok is false and the error carries a code. Usually a five-character SQLSTATE such as 42P01, but a failed connection can record a Node error code instead (ECONNREFUSED, ENOTFOUND), and a connect timeout records none.

Never logged, in any mode: bound parameter values (only their count) and error messages (only the code), since postgres quotes offending values back in its messages. Statement text is logged as written, so pass sensitive literals as params to pg_query, pg_readonly or pg_explain. pg_index_advisor takes no params; write $1-style placeholders in its statements instead (planned with GENERIC_PLAN, PostgreSQL 16+).

Values are parsed strictly. A value outside the accepted sets in the Configuration table -- yes, on, hash -- stops the server at startup instead of reading as off, even for POSTGRES_AUDIT_REDACT with auditing off. An audit control that quietly disabled itself would be worse than none. Case and surrounding whitespace are ignored, and an empty POSTGRES_AUDIT_LOG or POSTGRES_AUDIT_REDACT counts as unset -- so check that a variable your MCP client expands really has a value.

The file sink. POSTGRES_AUDIT_LOG_FILE alone turns auditing on, and it wins over POSTGRES_AUDIT_LOG=stderr without a warning when both are set. Unlike the other two variables, an empty or whitespace-only value is an error, not unset. The path must name a file in an existing directory: the file is created if missing, opened for append at startup, and held open for the life of the process. Use an absolute path; a relative one resolves against whatever directory your MCP client launches the server from. A write that fails later (full disk) never fails the query: one warning goes to stderr and later lines keep trying.

On stderr, audit lines are mixed with the startup banner and warnings, so skip lines that are not JSON; the file sink holds only audit lines. stdout is never used -- it is the MCP protocol channel.

Redaction hides text, not guesses. Identical statements hash identically, so you can still count and correlate them -- and for the same reason, anyone holding the log can confirm a guessed statement, so a short literal inside a predictable query is recoverable. Bound parameters are the safe place for sensitive values.

Under POSTGRES_MCP_SANDBOX=1 the sandbox denies the filesystem, so the file sink cannot open and the server exits at startup (could not be opened for append: Access to this API has been restricted). Use POSTGRES_AUDIT_LOG=stderr there; all three audit variables pass through the sandbox.

What the trail does not show.

• pg_kill writes no line. Its pg_cancel_backend / pg_terminate_backend call bypasses the audit path. If it cancelled one of this server's own statements, that statement's line reads ok: false (sqlstate 57014), with nothing tying it to pg_kill.
• The scaffolding around agent SQL (BEGIN READ ONLY, the row-cap savepoint and cursor, the closing COMMIT / ROLLBACK), the server-version probe, the pg_type lookup behind dataTypeName, and the hypopg_create_index / hypopg_reset calls behind pg_explain's hypothetical_indexes. pg_index_advisor is the exception: apart from the version probe, everything it sends is logged, including its transaction control and the EXPLAIN of each workload statement.
• Usually, a call that never gets a database connection. Tools that start with a single catalog query (the pg_list_* tools, pg_search_columns, pg_inspect_locks, pg_table_privileges, pg_table_bloat, pg_top_queries, pg_index_advisor, and pg_explain with hypothetical_indexes) log the failed attempt as ok: false; every other tool writes nothing.
• A pg_explain call whose hypothetical index cannot be created never runs its statement, so its only line is the ok: true check that HypoPG is installed.

PostgreSQL's own logging is the complete server-side record: log_statement = 'all' records every statement that reaches execution, and log_min_error_statement, at its default error, adds statements that fail earlier. It also records exactly what this trail keeps out -- bound parameter values and full error messages.

Troubleshooting

DATABASE_URL is not set - Your MCP client is launching the server without the env var. On Windows especially, env vars set in bash / PowerShell profiles are not inherited by MCP servers launched via cmd. Put DATABASE_URL directly in the env block of .mcp.json.

password authentication failed - Check the username, password, and that the user has CONNECT privilege on the database. URL-encode special characters in the password (@ → %40, # → %23, / → %2F).

SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string - The password in your connection string is empty or became null after URL decoding. Re-check your connection string.

canceling statement due to statement timeout - A single query exceeded POSTGRES_STATEMENT_TIMEOUT_MS (default 30s). Increase it, narrow the query with WHERE, or add an index. This is working as designed -- the timeout exists so a runaway query cannot hang the agent.

Write blocked: this server is in read-only mode - You asked the agent to write via pg_query but ALLOW_WRITES is not set. Either add ALLOW_WRITES=1 to the env block of .mcp.json and restart your MCP client (dev/test DBs), or - cleaner for production - use a role with INSERT/UPDATE/DELETE grants in DATABASE_URL and keep ALLOW_WRITES unset. See Configuring access. Note that pg_readonly always rejects writes; if you want writes, the call has to go through pg_query.

Error: [postgres-mcp] POSTGRES_AUDIT_... or Error: [postgres-mcp] audit log file ... could not be opened for append at startup - An audit variable is misconfigured, and the server exits with a stack trace rather than guess. The Error: line says which case you hit: an unrecognized value (it lists the accepted ones), an empty POSTGRES_AUDIT_LOG_FILE, a file set while POSTGRES_AUDIT_LOG is off, or the OS error from opening the file -- ENOENT usually means its directory does not exist, and Access to this API has been restricted means POSTGRES_MCP_SANDBOX=1. See Audit logging.

Connection pool exhaustion with PgBouncer transaction mode or pglite-socket - These backends don't support concurrent queries on a single connection. Set POSTGRES_POOL_MAX=1 in the env block.

First query is slow, subsequent queries are fast - Expected. The pg driver lazily establishes the first connection; subsequent queries reuse the pool.

Development

Run the full suite (unit + integration) against a real Postgres:

DATABASE_URL='postgres://user:pass@host:5432/db' POSTGRES_MCP_INTEGRATION=1 npm run test:integration

The integration suite assumes a disposable database -- it creates and drops a test_fixture schema. Don't point it at anything you care about.

To also run the destructive tests (REVOKE / restricted-role path), add POSTGRES_MCP_DESTRUCTIVE_TESTS=1. Only safe on a disposable cluster:

DATABASE_URL='postgres://user:pass@host:5432/db' POSTGRES_MCP_INTEGRATION=1 POSTGRES_MCP_DESTRUCTIVE_TESTS=1 npm run test:integration

Windows: integration tests via WSL2

Native Postgres on Windows ARM64 is fragile (UCRT runtime gaps, missing ARM64 builds). The reliable path is a disposable Ubuntu under WSL2 with the integration suite running inside WSL (WSL2's NAT blocks the Windows host from reaching :5432, so don't try to run the tests from PowerShell):

wsl --install -d Ubuntu --no-launch
# reboot, then:
wsl -d Ubuntu -u root bash -c "apt-get update && apt-get install -y nodejs npm rsync"
wsl -d Ubuntu -u root bash /mnt/c/path/to/postgres-mcp/scripts/wsl-pg-setup.sh
wsl -d Ubuntu -u root bash /mnt/c/path/to/postgres-mcp/scripts/wsl-test-matrix.sh

wsl-pg-setup.sh installs PG15, PG17 and PG18 from the PGDG apt repo (ports are auto-assigned by pg_createcluster -- typically 17 on 5432, 18 on 5433, 15 on 5434), sets the postgres password to postgres, and creates postgres_mcp_test in each. wsl-test-matrix.sh rsyncs the working tree into /root/postgres-mcp, runs npm ci once, and runs the integration suite against every cluster found via pg_lsclusters.

Running these from Git Bash instead of PowerShell? Prefix both script invocations with MSYS_NO_PATHCONV=1. Git Bash rewrites the /mnt/c/... argument before wsl.exe sees it, so the script arrives as C:/Users/<you>/scoop/apps/git/<ver>/mnt/c/... and bash exits with "No such file or directory" having run nothing. Also avoid piping either script into tail/head -- the pipeline's exit status is the last command's, so a failing matrix reports success.

Tear down when finished: wsl --unregister Ubuntu.

License

MIT © 2026 YawLabs