mcp-postgres-server

# MCP PostgreSQL Server

[![npm version](https://img.shields.io/npm/v/mcp-postgres-server.svg)](https://www.npmjs.com/package/mcp-postgres-server)
[![CI](https://github.com/antonorlov/mcp-postgres-server/actions/workflows/ci.yml/badge.svg)](https://github.com/antonorlov/mcp-postgres-server/actions/workflows/ci.yml)

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`:

```json
{
  "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:

```json
{
  "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

```bash
npm install mcp-postgres-server
```

Or run directly with:

```bash
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.

```javascript
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.

```javascript
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').

```javascript
// 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').

```javascript
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"}`.

```javascript
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.

```javascript
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](#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](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