mcp-postgres-server

Purpose

mcp-postgres-server is a Model Context Protocol (MCP) server used to let AI programming clients access PostgreSQL databases. It supports local, Docker, RDS, Neon, and Supabase databases, and can also access databases reachable only through a bastion via an SSH bastion host. This server can be used with MCP clients that support stdio, such as VS Code, Cursor, Claude Code, Codex, and Windsurf.

Main capabilities

The server is small and auditable. Its runtime dependencies include the MCP SDK, pg, pg-connection-string, and zod, with the optional dependency ssh2 used only for SSH tunnels. Node.js 20 or later is required.

Tool capabilities depend on the configuration:

1. query: Executes read-only SQL statements and accepts SELECT, WITH ... SELECT, EXPLAIN, and SHOW. Only one statement is allowed per call; multi-statement input is rejected by the extended query protocol. In read-only mode (the default), statements are executed in three steps: BEGIN READ ONLY, query, ROLLBACK, and any writes are rejected by the database itself. When PG_ALLOW_WRITE=true, statements are sent directly without that wrapper, so writes executed through query will actually be performed—writes should use execute. Supports PostgreSQL-style $1, $2 prepared statement parameters; values are bound by the driver and are not concatenated into the SQL text. Returns compact JSON: {"rows": [...], "rowCount": n, "returnedRows": n, "truncated": false}. When serialized rows exceed PG_MAX_RESULT_BYTES, only the rows that fit are returned (returnedRows < rowCount), truncated is true, and a hint is provided to add LIMIT / WHERE or reduce columns.

2. list_schemas: Lists all schemas in the connected database.

3. list_tables: Lists tables in the connected database, with an optional schema parameter (default 'public').

4. describe_table: Gets the structure of the specified table (columns, types, nullability, defaults, primary key), with an optional schema parameter (default 'public').

5. execute: Requires PG_ALLOW_WRITE=true. Executes INSERT, UPDATE, DELETE, or DDL statements. This tool is always registered, but in read-only mode (the default) it rejects with an error, mentions PG_ALLOW_WRITE, and changes nothing—the statement does not reach the database. When PG_ALLOW_WRITE=true, it executes; parameter handling is the same as query, one complete statement per call, and the connection role determines what it can execute. Returns {"rowCount": n, "command": "INSERT"}.

6. connect_db: Requires PG_ENABLE_RUNTIME_CONNECT=true. Uses the provided credentials to connect to another PostgreSQL database at runtime. Not registered by default—it is recommended to configure credentials through the environment to avoid credentials passing through model-visible parameters. After each reconnect, session limits are reapplied (statement_timeout, idle_in_transaction_session_timeout); read-only reads are enforced as read-only in their own BEGIN READ ONLY transaction.

Usage

The preferred configuration method is a single DATABASE_URL. When PG_ALLOW_WRITE is set to "false", the server has read-only access to the database, which is the default; set it to "true" only when the model must write. The same JSON can be used with any MCP client that supports stdio: VS Code, Cursor, Claude Code, Codex, Windsurf.

Separate PG_* variables can also be set; they are used when DATABASE_URL is not set.

Manual installation is supported, or it can be run directly.

Connecting to a database:

• Local Postgres: connect directly.
• Postgres in Docker: if the database is running in a container with a published port, connect to localhost:<published-port> as usual. If the MCP server itself runs inside a containerAnd the database runs on the host machine, using host.docker.internal instead of localhost.

Use Cases

Suitable for development scenarios where an AI coding assistant needs to safely query a PostgreSQL database, including local development, Docker environments, cloud databases (RDS, Neon, Supabase), and internal network databases that need to be accessed through an SSH jump host. The default read-only design is suitable for letting the model explore the database structure and execute read-only queries while avoiding accidental writes.

Limitations and Security Notes

• Read-only by default; writes must be explicitly enabled (PG_ALLOW_WRITE=true).
• Read-only is enforced by the engine (BEGIN READ ONLY), not by client-side SQL parsing.
• Data access goes through a small typed interface; the pg driver does not leak outside the interface.
• Supports DATABASE_URL and SSL (sslmode=disable|allow|prefer|require|verify-ca|verify-full, custom CA).
• Prepared statement parameters: $1-style placeholders, bound by the driver.
• Result size limit (byte budget), with an explicit truncated flag, to avoid flooding the model context.
• Session statement timeout plus client deadline; transaction pooling may not preserve session settings.
• Errors are returned as readable tool results, with SQLSTATE-based hints, making it easier for the model to self-correct.
• Recoverable after connection loss—lazy reconnection rather than crashing.
• Optional SSH tunnel (PG_SSH_*), enforcing host key verification, loaded only when configured.
• MCP tool annotations compliant with the 2025-11-25 specification (read-only/destructive hints).
• Multi-schema support.

On the security side: a least-privilege database role is the real boundary. MCP uses existing credentials and does not need to create or alter roles. A dedicated role is what truly guarantees writes are impossible. For PostgreSQL 14+, refer to: CREATE ROLE mcp_readonly LOGIN PASSWORD 'change-me'; GRANT CONNECT ON DATABASE your_database TO mcp_readonly; GRANT pg_read_all_data TO mcp_readonly; ALTER ROLE mcp_readonly SET default_transaction_read_only = on; (PostgreSQL 13 or earlier requires explicitly granting SELECT; see SECURITY.md.) When connecting as a superuser, the server emits a warning on stderr. Read authorization does not revoke existing privileges, and defaults remain mutable; available functions, ownership, and inherited privileges must also be considered.

Read-only is enforced by the engine, with no client-side SQL parsing. In read-only mode, every read runs in a rolled-back BEGIN READ ONLY transaction, so PostgreSQL itself rejects any write with SQLSTATE 25006 and rolls back any session changes caused by the statement. The extended protocol rejects multi-command strings.

Honest note: read-only transactions are defense in depth on top of roles, not a replacement. Read-only mode prevents a confused or prompt-injected model from writing to the database; it cannot prevent prompt injection carried in the row data returned by a query. Do not point this server at production—use a replica, a snapshot, or a strictly scoped role. See SECURITY.md for details.

SSH tunnel: set PG_SSH_HOST (along with authentication and host key verification) to reach a database that is only reachable through a jump host (SSH bastion host). The connection string / PG_* fields describe the database as seen from the jump host. SSH only changes the transport. Read-only enforcement, result size limits, timeouts, and the behavior of connect_db are exactly the same as with a direct connection, and no extra SQL is sent per query.

Host key verification is enforced via a pinned PG_SSH_FINGERPRINT—without it the tunnel will not connect, so a man-in-the-middle jump host is rejected. Channels for obtaining the fingerprint (from highest to lowest trust): run ssh-keygen -lf /etc/ssh/ssh_host_ed25519_key.pub on the jump host itself or from its administrator (no network involved); obtain it from an existing ~/.ssh/known_hosts (if you have already reached that host via ssh): ssh-keygen -lF bastion.example.com; obtain it from the host: ssh-keyscan bastion.example.com | ssh-keygen -lf - (trustworthy only if run from a network location you trust—it accepts whatever the host returns).

TLS verifies the real database hostname. With verify-full, the certificate is checked against the database's own hostname (such as db.internal), not the loopback address bound locally by the tunnel, and rejectUnauthorized is pinned on, so an inherited NODE_TLS_REJECT_UNAUTHORIZED=0 cannot disable it.

ssh2 is an optional dependency, loaded only when PG_SSH_HOST is set, so direct connections never initialize it. npm installs optional dependencies by default; running npm install --omit=optional skips it entirely (direct connections do not need it).

Tunnel connection failures report stable SSH_* codes.

Error handling: SQL and connection failures are returned as tool results (isError: true), including the message, SQLSTATE code, and a hint. When PostgreSQL's own server hint is present, it is used; otherwise a fallback hint is applied. Over an SSH tunnel, failures carry stable codes (when the cause is determined, the hint indicates the setting to fix), so the failure stage is clear. Real PostgreSQL errors over a healthy tunnel keep their own codes (such as credential error 28P01), not SSH codes.

Migrating from 0.1.x: new installations need no migration. There are two behavior changes since 0.1.x:

1. Read-only by default. The execute tool is always visible, but unless PG_ALLOW_WRITE=true, writes are rejected (the error mentions that flag), and every read runs in an engine-enforced READ ONLY transaction. If a workflow needs to write to the database, set "PG_ALLOW_WRITE": "true" to restore 0.1.x behavior.
2. connect_db is disabled by default. Runtime connection switching (passing credentials via tool parameters) requires PG_ENABLE_RUNTIME_CONNECT=true; otherwise connection details come only from the environment.

Tool names, parameter names, and PG_* variables are unchanged. Result payloads are now structured compact JSON for all tools (for example, query returns {rows, rowCount, returnedRows, truncated} instead of a bare array of rows)—before updating any code that parses tool output, see CHANGELOG.md for the exact shapes.