Query a PostgreSQL database from Claude Code, Cursor or any MCP client - schema introspection, EXPLAIN plans and perf diagnostics, read-only by default.
@yawlabs/postgres-mcp gives an agent a real connection to your database: parameterized SQL, schema and view introspection, EXPLAIN with index advice, and the perf and health diagnostics you would otherwise hand-write pg_catalog joins for. Read-only by default - writes opt in via a single env var - so an agent cannot silently drop your tables. Built and maintained by Yaw Labs, MIT licensed, published on npm.
npx -y @yawlabs/postgres-mcp@latestGitHub · npm · Add to Yaw MCP
Anyone pointing an agent at a Postgres database and wanting it to be the daily driver rather than a demo. That gap is real: Anthropic's reference Postgres MCP server was archived in May 2025 and marked deprecated on npm in July 2025, with no replacement shipped - and it carries a publicly documented stacked-query SQL injection that defeats its own read-only wrapper and has never been patched. The community forks each fill a narrow slice. This one is written from scratch as the general-purpose option.
Single-tool questions are the easy half:
pg_stat_statements with mean, total, min and max times.The leverage is in chaining them. Unstick a hung app: inspect locks to get the blocked PID, the blocking PID and the offending query, then cancel the blocker - both in one turn. Chase a slow page: rank the worst queries, explain the top hit, then let the sequential-scan and unused-index views say whether the answer is "add an index here" or "drop a dead one there". Oncall triage: health check for connections and database size, then locks and replication status, before paging the DBA.
1. Set your connection string. Put DATABASE_URL directly in the env block of the MCP config. On Windows especially, env vars exported from a bash or PowerShell profile are not inherited by a server launched through cmd, which is the usual cause of a DATABASE_URL is not set error. Point it at a least-privileged role - see Configuring access.
2. Add the server to your client. On macOS, Linux and WSL, a .mcp.json in your project root:
{
"mcpServers": {
"postgres": {
"command": "npx",
"args": ["-y", "@yawlabs/postgres-mcp@latest"],
"env": {
"DATABASE_URL": "postgres://user:password@host:5432/dbname"
}
}
}
}On Windows, wrap the call in cmd /c: npx is a .cmd file there, and Node 20+ refuses to spawn .cmd files directly.
{
"mcpServers": {
"postgres": {
"command": "cmd",
"args": ["/c", "npx", "-y", "@yawlabs/postgres-mcp@latest"],
"env": {
"DATABASE_URL": "postgres://user:password@host:5432/dbname"
}
}
}
}3. Restart and approve. Restart Claude Code (or your MCP client) and approve the postgres server when prompted. Then ask it something: "Describe the users table and tell me which columns are indexed."
Writes stay off unless you ask for them: add "ALLOW_WRITES": "1" to the same env block if you want the agent to be able to INSERT, UPDATE, DELETE or run DDL. Prefer scoping that to dev and test databases, and use migration tools out of band for production.
Running Yaw MCP? One click adds it to your local config, and it is then available in every Yaw Terminal session.
| Tool | What it does |
|---|---|
pg_readonly | Run SQL with no persistent data changes - always inside BEGIN READ ONLY, regardless of ALLOW_WRITES. The recommended tool for read access |
pg_query | Run a SQL query, with parameterized values via params. Writes gated by the role first, ALLOW_WRITES second |
pg_list_schemas, pg_list_tables, pg_list_views, pg_list_functions, pg_list_extensions | Schemas, tables with estimated row counts, views and materialized views with their definitions, functions and procedures with signatures, installed extensions with versions |
pg_describe_table | Columns, primary key, outgoing and incoming foreign keys, CHECK / UNIQUE / EXCLUDE constraints, indexes and partition parent or children. Generated and identity columns are flagged, so an agent does not try to write to them |
pg_search_columns | Find columns by name pattern across every user schema, case-insensitive, with LIKE wildcards |
pg_explain | EXPLAIN or EXPLAIN ANALYZE, text or JSON, with planner options and optional hypothetical indexes via HypoPG - ask what the plan would be without creating anything on disk |
pg_index_advisor | Recommend indexes for a workload and prove each one pays for itself: candidates come from what the planner reports as filters, join keys and sort keys, are costed with hypothetical indexes, and only what measurably lowers estimated cost survives. Returns the CREATE INDEX, a CONCURRENTLY form, cost before and after, and estimated size. Requires HypoPG |
pg_health | Server version, database size, connections against max_connections, active queries with wait events and transaction age, deadlocks, temp files and cache hit ratio |
pg_top_queries, pg_seq_scan_tables, pg_unused_indexes, pg_table_bloat | The "why is this slow" set: worst queries by execution time (pg_top_queries needs the pg_stat_statements extension), missing-index candidates, drop candidates, and VACUUM candidates |
pg_io_stats, pg_inspect_locks, pg_replication_status | I/O counts, bytes and times per backend type and context plus in-flight async I/O; who is blocking whom right now; replication slots, connected replicas and WAL position |
pg_list_roles, pg_table_privileges | Roles with their flags and group memberships, and who can SELECT / INSERT / UPDATE / DELETE on a table or a whole schema |
pg_advisor | Rolled-up DBA lints in one call: sequence exhaustion, wraparound risk for both the transaction-ID and multixact counters, 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. Requires ALLOW_WRITES=1 |
Counter-based tools return the stats_reset window alongside the rows, because a cumulative scan count means nothing without knowing when the counters were last reset - if that happened an hour ago every index looks unused, which is how a load-bearing index gets dropped. Version-dependent columns are gated on the server version, so an older server gets a slightly thinner answer rather than an error, and newer ones unlock extra fields rather than being required.
The role in DATABASE_URL is the primary access control. Postgres has had a permission system for 30 years; lean on it rather than on an env var. A least-privileged role makes writes server-rejected no matter what tools or variables 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 that role and Postgres rejects every write, every DDL and every privilege change regardless of ALLOW_WRITES. There is no app-level guard to bypass, because the database is the boundary. For a dev or test agent that should change data but not schema, grant the DML verbs and leave DDL out, then set ALLOW_WRITES=1 so pg_query will issue the writes the role already permits.
For hosts that gate tools individually - Claude Code's permissions block, or a hosting UI with per-tool toggles - the tools split cleanly into two authority classes. Auto-allow: pg_readonly and the introspection and diagnostic tools. Always prompt: pg_query, which can write when the role allows it, and pg_kill, which changes session state.
BEGIN READ ONLY transaction, so the refusal comes from the server rather than from string inspection that can be talked around.pg_query takes a params array for $1, $2 and friends - no string-interpolated SQL in the server's own code path.nextval - but not functions whose effect lands outside the table data. pg_terminate_backend, pg_cancel_backend, pg_read_file, lo_export and COPY ... TO PROGRAM all run to completion inside pg_readonly, which reaches the same capability pg_kill puts behind ALLOW_WRITES. Each still needs a privilege the role must actually hold, so the role is the control that bounds the tool - not the transaction mode.POSTGRES_MAX_ROWS with a truncated flag, so a stray SELECT * FROM events does not blow out the model's context.POSTGRES_APPLICATION_NAME sets what shows up in pg_stat_activity, so whoever is watching the database can tell agent queries from application ones.| Variable | Purpose |
|---|---|
DATABASE_URL | Connection string. Required, and the primary access control |
ALLOW_WRITES | Secondary write gate for pg_query and ANALYZE-of-writes. Does not affect pg_readonly |
POSTGRES_STATEMENT_TIMEOUT_MS | Per-statement timeout |
POSTGRES_CONNECTION_TIMEOUT_MS | TCP connect timeout |
POSTGRES_MAX_ROWS | Cap on rows returned by pg_query |
POSTGRES_POOL_MAX | Max pool connections. Set to 1 for single-threaded backends such as PgBouncer transaction mode |
POSTGRES_SSL_REJECT_UNAUTHORIZED | Set to false to skip TLS certificate verification. The connection is still encrypted |
POSTGRES_APPLICATION_NAME | Value reported in pg_stat_activity.application_name |
POSTGRES_MCP_RUNTIME | Which JS runtime runs the server: prefer oam, require it, or never use it |
Most managed databases require TLS but serve certificates signed by a private CA that Node's default trust store does not recognize. The symptom is a self signed certificate in certificate chain, unable to get local issuer certificate or unable to verify the first certificate error at connect time. Allow the connection while keeping traffic encrypted:
"env": {
"DATABASE_URL": "postgres://user:pass@host:5432/db?sslmode=require",
"POSTGRES_SSL_REJECT_UNAUTHORIZED": "false"
}That disables chain verification only - the TCP connection is still TLS-encrypted end to end. Where you can install the CA instead, prefer putting the certificate in the Node trust store with NODE_EXTRA_CA_CERTS over disabling verification globally.
One round trip is available on newer servers: Postgres 17 added direct TLS negotiation, which skips the plaintext handshake before the TLS one, and the bundled driver supports it via sslnegotiation=direct on the connection string. It is opt-in rather than a default because an older server rejects such a connection outright, and the saving is one round trip per pooled connection - worth it on a distant managed database, invisible on a local one.
It connects to a PostgreSQL database over DATABASE_URL and exposes it as MCP tools: run SQL, introspect schemas, tables, views, functions and extensions, read EXPLAIN plans, and pull perf and health diagnostics - top queries, sequential scans, unused indexes, table bloat, locks, replication status and I/O - without the agent having to remember pg_catalog joins.
Not by default. User SQL runs inside a BEGIN READ ONLY transaction, so PostgreSQL itself blocks writes rather than a string-parsing guard, and writes only become possible when you set ALLOW_WRITES=1. The stronger control is the role in DATABASE_URL: point it at a least-privileged role and the database rejects every write, every DDL and every privilege change regardless of how the server is configured.
Mostly, with one caveat worth reading first. A BEGIN READ ONLY transaction blocks writes to the database, but not functions whose effect lands outside the table data - pg_terminate_backend, pg_cancel_backend, pg_read_file, lo_export and COPY to a program all run to completion inside the read-only tool. Each one still requires a privilege the connecting role must actually hold, so the role is what bounds this tool, not the transaction mode.
Anthropic's reference Postgres MCP server was archived in May 2025 and marked deprecated on npm in July 2025, with no replacement shipped. It also carries a publicly documented stacked-query SQL injection that bypasses its own read-only wrapper and has never been patched. This server is written from scratch and actively maintained, and it sends all user SQL over the extended query protocol, which restricts each request to a single statement and closes that injection class - with an integration test asserting the rejection.
Managed databases usually require TLS but serve certificates signed by a private CA that Node does not trust, which shows up as a self-signed-certificate or unable-to-get-local-issuer error. Set POSTGRES_SSL_REJECT_UNAUTHORIZED to false in the env block to skip chain verification while keeping the connection encrypted, or install the CA in the Node trust store with NODE_EXTRA_CA_CERTS, which is the better option where you can do it.
Published by Yaw Labs.