Mcp Postgres Server

What is Mcp Postgres Server about?

Provides a small, auditable MCP server that exposes PostgreSQL operations (query, list schemas/tables, describe table, execute, optional runtime connect) to any MCP‑compatible client such as VS Code, Cursor, Claude Code, Codex, or Windsurf.

How to use Mcp Postgres Server?

1. Install – npm install mcp-postgres-server or run directly with npx mcp-postgres-server.
2. Configure – supply a single DATABASE_URL or the individual PG_* variables in the environment of the MCP server. Example configuration for an MCP client (stdio):

{
  "mcpServers": {
    "postgres": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-postgres-server"],
      "env": {
        "DATABASE_URL": "postgres://user:password@localhost:5432/mydb",
        "PG_ALLOW_WRITE": "false"
      }
    }
  }
}

3. Run – the server starts automatically when the client launches the command above. Use the documented tools (query, list_schemas, …) via the MCP client SDK.

Key Features

• Read‑only default; explicit PG_ALLOW_WRITE=true enables execute writes.
• Engine‑enforced BEGIN READ ONLY for every read, preventing accidental writes.
• Supports prepared‑statement parameters ($1, $2) with safe driver binding.
• Result size budgeting (PG_MAX_RESULT_BYTES) with a truncated flag to protect token limits.
• Optional SSH tunneling (PG_SSH_*) with mandatory host‑key fingerprint verification.
• Runtime database switching via connect_db when PG_ENABLE_RUNTIME_CONNECT=true.
• Comprehensive error handling that surfaces SQLSTATE codes and SSH‑specific codes.
• Minimal runtime footprint: only mcp-sdk, pg, pg-connection-string, zod, and optionally ssh2.

Use Cases

• Empowering LLM‑driven code‑assistants to explore schema metadata and run safe read‑only analytics.
• Allowing AI agents to generate insights from a replica or read‑only role without risking data mutation.
• Providing a unified MCP endpoint for multiple IDE extensions (VS Code, Cursor, Claude Code, etc.).
• Securely accessing databases behind bastion hosts via the built‑in SSH tunnel feature.
• Enabling ad‑hoc query execution in development environments while keeping production databases protected.

FAQ

Q: Do I need a special PostgreSQL role? A: Use a least‑privilege role (e.g., mcp_readonly) with pg_read_all_data and default_transaction_read_only = on. The server also enforces read‑only at the transaction level.

Q: Can the server write to the database? A: Only when PG_ALLOW_WRITE=true and the execute tool is called. Otherwise all writes are blocked.

Q: How does SSH tunneling work? A: Set any PG_SSH_* variable; the server loads the optional ssh2 package, creates a tunnel, and then connects to PostgreSQL through the local forward. Host‑key verification is required via PG_SSH_FINGERPRINT.

Q: What happens if a query returns more data than the byte budget? A: The server truncates the result set, returns returnedRows < rowCount, and sets truncated: true with a hint to limit the query.

Q: Which environments are supported? A: Node.js 20+; works on any platform where npm/npx can run.

Q: How do I enable runtime credential changes? A: Set PG_ENABLE_RUNTIME_CONNECT=true; the connect_db tool becomes available.

MCP PostgreSQL Server

A Model Context Protocol (MCP) server for PostgreSQL: local, Docker, RDS, Neon, and Supabase databases.

The server is small and auditable, with four runtime dependencies: the MCP SDK, pg, pg-connection-string, and zod (plus ssh2, an optional dependency used only for SSH tunneling).

Requires Node.js 20 or newer.

Quick start

The preferred way to configure the server is a single DATABASE_URL:

{
  "mcpServers": {
    "postgres": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-postgres-server"],
      "env": {
        "DATABASE_URL": "postgres://user:password@localhost:5432/mydb",
        "PG_ALLOW_WRITE": "false"
      }
    }
  }
}

With PG_ALLOW_WRITE set to "false" the server has read-only access to the database. This is the default; set it to "true" only if the model must write.

The same JSON works in any MCP client that speaks stdio: VS Code, Cursor, Claude Code, Codex, Windsurf.

Alternatively, set the individual PG_* variables; they are used when DATABASE_URL is not set:

{
  "mcpServers": {
    "postgres": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-postgres-server"],
      "env": {
        "PG_HOST": "your_host",
        "PG_PORT": "5432",
        "PG_USER": "your_user",
        "PG_PASSWORD": "your_password",
        "PG_DATABASE": "your_database",
        "PG_ALLOW_WRITE": "false"
      }
    }
  }
}

Manual Installation

npm install mcp-postgres-server

Or run directly with:

npx mcp-postgres-server

Connect to your database

Local Postgres:

DATABASE_URL=postgres://mcp_readonly:secret@localhost:5432/mydb

Postgres in Docker: if the database runs in a container with a published port, connect to localhost:<published-port> as usual. If the MCP server itself runs inside a container and the database runs on your host machine, use host.docker.internal instead of localhost:

DATABASE_URL=postgres://mcp_readonly:secret@host.docker.internal:5432/mydb

Amazon RDS:

DATABASE_URL=postgres://mcp_readonly:secret@mydb.xxxxxx.us-east-1.rds.amazonaws.com:5432/mydb?sslmode=require

Neon:

DATABASE_URL=postgres://mcp_readonly:secret@ep-xxx-xxx.us-east-2.aws.neon.tech/mydb?sslmode=require

Supabase:

DATABASE_URL=postgres://postgres.xxxxxxxx:secret@aws-0-us-east-1.pooler.supabase.com:5432/postgres?sslmode=require

Tools

Tool availability depends on configuration:

Tool	Available
query, list_schemas, list_tables, describe_table	Always
execute	Always (refuses writes unless PG_ALLOW_WRITE=true)
connect_db	Only when PG_ENABLE_RUNTIME_CONNECT=true

1. query

Execute a read-only SQL statement. Accepts SELECT, WITH ... SELECT, EXPLAIN, and SHOW. One statement per call - multi-statement input is rejected by the extended query protocol. In read-only mode (the default) the statement runs as BEGIN READ ONLY, the query, and ROLLBACK - three commands, roughly two network round trips with pipelining - so the database itself refuses any write. With PG_ALLOW_WRITE=true the statement is sent directly, without that wrapper, so a write run through query would execute - use execute for writes. Supports PostgreSQL-style $1, $2 prepared-statement parameters; values are bound by the driver and never spliced into the SQL text.

use_mcp_tool({
  server_name: "postgres",
  tool_name: "query",
  arguments: {
    sql: "SELECT * FROM users WHERE id = $1",
    params: [1]
  }
});

Returns compact JSON: {"rows": [...], "rowCount": n, "returnedRows": n, "truncated": false}. When the serialized rows exceed PG_MAX_RESULT_BYTES, only the rows that fit are returned (returnedRows < rowCount), truncated is true, and a hint suggests adding LIMIT/WHERE or selecting fewer columns.

2. list_schemas

List all schemas in the connected database.

use_mcp_tool({
  server_name: "postgres",
  tool_name: "list_schemas",
  arguments: {}
});

3. list_tables

List tables in the connected database. Accepts an optional schema parameter (defaults to 'public').

// List tables in the 'public' schema (default)
use_mcp_tool({
  server_name: "postgres",
  tool_name: "list_tables",
  arguments: {}
});

// List tables in a specific schema
use_mcp_tool({
  server_name: "postgres",
  tool_name: "list_tables",
  arguments: {
    schema: "my_schema"
  }
});

4. describe_table

Get the structure of a specific table (columns, types, nullability, defaults, primary keys). Accepts an optional schema parameter (defaults to 'public').

use_mcp_tool({
  server_name: "postgres",
  tool_name: "describe_table",
  arguments: {
    table: "users",
    schema: "my_schema"  // optional
  }
});

5. execute - requires PG_ALLOW_WRITE=true

Execute an INSERT, UPDATE, DELETE, or DDL statement. Always registered, but in read-only mode (the default) it refuses with an error naming PG_ALLOW_WRITE and changes nothing - the statement never reaches the database. With PG_ALLOW_WRITE=true it runs: same $1, $2 parameter handling as query, one complete statement per call, and the connecting role governs what it may do. Returns {"rowCount": n, "command": "INSERT"}.

use_mcp_tool({
  server_name: "postgres",
  tool_name: "execute",
  arguments: {
    sql: "INSERT INTO users (name, email) VALUES ($1, $2)",
    params: ["John Doe", "john@example.com"]
  }
});

6. connect_db - requires PG_ENABLE_RUNTIME_CONNECT=true

Connect to a different PostgreSQL database at runtime using provided credentials. Not registered by default - prefer configuring credentials through the environment so they never pass through model-visible arguments. Session limits (statement_timeout, idle_in_transaction_session_timeout) are re-applied after every reconnect; read-only reads enforce read-only in their own BEGIN READ ONLY transaction.

use_mcp_tool({
  server_name: "postgres",
  tool_name: "connect_db",
  arguments: {
    host: "localhost",
    port: 5432,
    user: "your_user",
    password: "your_password",
    database: "your_database"
  }
});

Configuration reference

Variable	Default	Description
DATABASE_URL	-	Full connection string (preferred). Supports ?sslmode= in the URL.
PG_HOST	-	Database host (fallback when DATABASE_URL is not set)
PG_PORT	5432	Database port
PG_USER	-	Database user
PG_PASSWORD	-	Database password
PG_DATABASE	-	Database name
PG_ALLOW_WRITE	false	When true, execute performs writes and reads are sent directly. Off (default) is read-only: execute refuses writes and each read runs in a READ ONLY transaction
PG_SSLMODE	-	disable | allow | prefer | require | verify-ca | verify-full. require/allow/prefer encrypt without verifying the certificate; verify-ca/verify-full verify (supply a CA via PG_SSL_CA). Unrecognized values fail at startup. Limitation: unlike libpq, allow/prefer do not fall back to plaintext (node-postgres has no opportunistic SSL), so a server without TLS needs disable.
PG_SSL_CA	-	Path to a CA certificate file. Setting it by itself implies verify-full
PG_ENABLE_RUNTIME_CONNECT	false	Register the connect_db tool (runtime credential switching)
PG_MAX_RESULT_BYTES	32768	Byte budget for a query result sent to the model. Whole rows are kept while they fit; over the budget returnedRows < rowCount and truncated: true (if not even the first row fits, returnedRows is 0 with a hint). ~32 KiB ≈ 8k tokens; lower it for strict clients, raise it if your client allows more.
PG_STATEMENT_TIMEOUT	30000	Statement timeout in milliseconds, applied to every session
PG_CONNECT_TIMEOUT	10000	Timeout in milliseconds for a single connect attempt (raise it for slow links or SSH tunnels)

To reach a database only accessible through a bastion, see SSH tunneling (adds PG_SSH_* variables).

Features

• Read-only by default; writes are an explicit opt-in (PG_ALLOW_WRITE=true)
• Read-only enforced by the engine (BEGIN READ ONLY), never by client-side SQL parsing
• Data access behind a small typed interface; the pg driver never leaks past it
• DATABASE_URL support with SSL (sslmode=disable|allow|prefer|require|verify-ca|verify-full, custom CA)
• Prepared-statement parameters: $1-style placeholders, bound by the driver
• Result size cap (byte budget) with an explicit truncated flag instead of flooding the model's context
• Session statement timeout plus a client deadline; transaction poolers may not preserve session settings
• Errors returned as readable tool results with SQLSTATE-based hints, so the model can self-correct
• Survives dropped connections - reconnects lazily instead of crashing
• Optional SSH tunneling (PG_SSH_*) with mandatory host-key verification, loaded only when configured
• MCP tool annotations (read-only / destructive hints) per spec 2025-11-25
• Multi-schema support for database operations

Security

Full details, including the threat model and disclosure process, are in SECURITY.md. The short version:

1. A least-privilege database role is the real boundary. The MCP works with existing credentials; creating or changing roles is not required. A dedicated role is what actually guarantees writes are impossible. On PostgreSQL 14+, the following is a starting point:

   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;                          -- adds read privileges
   ALTER ROLE mcp_readonly SET default_transaction_read_only = on;  -- read-only by default
   

   (On PostgreSQL 13 or older, grant SELECT explicitly instead of pg_read_all_data - see SECURITY.md.) The server warns on stderr if you connect as a superuser. Read grants do not revoke existing privileges, and defaults remain mutable; available functions, ownership and inherited privileges also matter.

2. The engine enforces read-only. There is no client-side SQL parsing. In read-only mode every read runs in a rolled-back BEGIN READ ONLY transaction, so PostgreSQL itself - which alone knows what a function, view, or rule does - refuses any write with SQLSTATE 25006 and reverts any session change the statement made. The extended protocol rejects multi-command strings.

Honest framing: the read-only transaction is defense-in-depth on top of the role, not a replacement for it. Read-only mode stops a confused or prompt-injected model from writing to your database; it does not stop prompt injection carried in the row data a query returns. Don't point this server at production - use a replica, a snapshot, or a tightly scoped role. See SECURITY.md.

SSH tunneling

Set PG_SSH_HOST (plus auth and host-key verification) to reach a database that is only accessible through a bastion (an SSH jump host). The connection string / PG_* fields then describe the database as seen from the bastion:

{
  "mcpServers": {
    "postgres": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-postgres-server"],
      "env": {
        "DATABASE_URL": "postgres://mcp_readonly:secret@db.internal:5432/mydb?sslmode=verify-full",
        "PG_SSH_HOST": "bastion.example.com",
        "PG_SSH_USER": "jump",
        "PG_SSH_PRIVATE_KEY": "/home/me/.ssh/id_ed25519",
        "PG_SSH_FINGERPRINT": "SHA256:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
      }
    }
  }
}

Variable	Default	Description
PG_SSH_HOST	-	SSH bastion host. Setting it enables tunneling: the server reaches the database only through an SSH tunnel to this host (see below). Optional feature; needs the ssh2 optional dependency.
PG_SSH_PORT	22	SSH bastion port
PG_SSH_USER	-	SSH username
PG_SSH_PRIVATE_KEY	-	Path to a private key file. If unset, auth falls back like ssh: a running agent (SSH_AUTH_SOCK), then a default key (~/.ssh/id_ed25519, id_rsa, id_ecdsa)
PG_SSH_PASSPHRASE	-	Passphrase for the private key, if encrypted
PG_SSH_AGENT	-	true to use the ambient agent (SSH_AUTH_SOCK), or an explicit socket path / Windows named pipe (\\.\pipe\openssh-ssh-agent)
PG_SSH_PASSWORD	-	SSH login password. Opt-in; a key or agent takes precedence. Prefer keys - a bastion often disables password auth.
PG_SSH_FINGERPRINT	-	Pinned host-key fingerprint (SHA256:...). Host-key verification is mandatory and set only this way: without it the tunnel refuses to connect (fail-closed). Get it with ssh-keygen -lF host (reads your known_hosts) or ssh-keyscan host | ssh-keygen -lf - (see the trust note below)
PG_SSH_KEEPALIVE_INTERVAL	15000	SSH keepalive interval in ms; the tunnel drops after 3 unanswered keepalives, and the next call reconnects

• SSH changes only the transport. Read-only enforcement, the result size cap, timeouts, and connect_db behave exactly as on a direct connection, and no extra SQL is sent per query.
• Host-key verification is mandatory via a pinned PG_SSH_FINGERPRINT - the tunnel will not connect without it, so a man-in-the-middle bastion is refused. Get the fingerprint over a channel you trust, most trustworthy first:
  • on the bastion itself, or from its admin: ssh-keygen -lf /etc/ssh/ssh_host_ed25519_key.pub (no network involved);
  • from your existing ~/.ssh/known_hosts, if you already reach the host over ssh: ssh-keygen -lF bastion.example.com;
  • fetched from the host: ssh-keyscan bastion.example.com | ssh-keygen -lf - (trust this only when run from a network position you trust - it accepts whatever the host returns).
• TLS validates the real database hostname. With verify-full, the certificate is checked against the database's own hostname (e.g. db.internal), not the loopback the tunnel binds locally, 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 a direct connection never initializes it. npm installs optional dependencies by default; run npm install --omit=optional to skip it entirely (a direct connection does not need it).

A tunneled connection that fails reports a stable SSH_* code - see Error Handling.

Error Handling

SQL and connection failures are returned as tool results (isError: true) with a message, the SQLSTATE code, and a hint. PostgreSQL's own server hint is used when present; otherwise these fallbacks apply:

code	meaning	first thing to check
28P01	authentication failed	PG_USER / PG_PASSWORD
3D000	database does not exist	PG_DATABASE
42P01	relation not found	call list_tables
42703	column not found	call describe_table
57014	the query was canceled (a timeout or a cancel request)	if timing out, add a LIMIT / simplify it, or raise PG_STATEMENT_TIMEOUT
25006	the transaction is read-only	source may be a read-only role, a replica, a server default, or (for query) the read-only wrapper; execute writes need PG_ALLOW_WRITE=true
ECONNREFUSED / ENOTFOUND	cannot reach or resolve the database host	PG_HOST / PG_PORT / DATABASE_URL

Over an SSH tunnel, a failure carries a stable code (and, where the cause is determinate, a hint naming the setting to fix), so the failing phase is unambiguous:

code	meaning	first thing to check
SSH_CONFIG_INVALID	invalid SSH config, incl. a malformed PG_SSH_FINGERPRINT	the PG_SSH_* values
SSH_KEY_INVALID	key unreadable, unparseable, a public key, or encrypted without the right passphrase (an encrypted key with the correct PG_SSH_PASSPHRASE works)	PG_SSH_PRIVATE_KEY, PG_SSH_PASSPHRASE
SSH_CONNECT_FAILED	the bastion is unreachable, or SSH setup failed for an unclassified reason	PG_SSH_HOST, PG_SSH_PORT, reachability
SSH_TIMEOUT	the bastion did not respond in time	network/firewall, PG_CONNECT_TIMEOUT
SSH_AUTH_FAILED	the bastion rejected authentication	PG_SSH_USER and the key/agent/password in use
SSH_HOST_KEY_MISMATCH	host key does not match PG_SSH_FINGERPRINT (stale value or MITM)	re-fetch the fingerprint
SSH_FORWARD_FAILED	tunnel is up, but the bastion could not reach the database	the DB host and port as seen from the bastion
SSH_CONNECTION_LOST	an established tunnel dropped mid-session	transient; the next call reconnects

A genuine PostgreSQL error through a healthy tunnel keeps its own code (e.g. 28P01 for wrong database credentials), not an SSH code.

Migrating from 0.1.x

Not needed for new installs. Two behavior changes since 0.1.x:

1. Read-only by default. The execute tool is always visible but refuses writes (with an error naming the flag) unless PG_ALLOW_WRITE=true, and every read runs inside an engine-enforced READ ONLY transaction. If your workflow writes 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 through tool arguments) 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 every tool (e.g. query returns {rows, rowCount, returnedRows, truncated} instead of a bare row array) - see CHANGELOG.md for the exact shapes before updating anything that parses tool output.

License

MIT