# PostgreSQL MCP

Read-only [Model Context Protocol](https://modelcontextprotocol.io) server for PostgreSQL. Works with **any MCP client** — Cursor, Claude Desktop, Claude Code, VS Code, or custom clients over **stdio** or **Streamable HTTP**.

Database credentials stay on the server. Clients connect with a URL and API key (HTTP) or spawn the binary locally (stdio).

## Features

- **`query`** — read-only SQL (`SELECT`, `WITH`, `EXPLAIN`, `SHOW`) with row caps
- **`list_tables`** — list tables in a schema
- **`describe_table`** — column names, types, nullability, defaults
- **Resources** — per-table schema JSON via MCP resources
- **Safety** — SQL keyword guard + `BEGIN READ ONLY` transactions
- **Transports** — stdio (local) and Streamable HTTP (hosted)

## Quick start

```bash
git clone https://github.com/vallaksa/postgresql-mcp.git
cd postgresql-mcp
npm install
npm run build
```

### stdio (local MCP clients)

```bash
export DATABASE_URL="postgresql://mcp_reader:password@localhost:5432/devstrom"
npm start
# or: node dist/index.js
```

### HTTP (hosted / remote MCP)

```bash
export DATABASE_URL="postgresql://mcp_reader:password@localhost:5432/devstrom"
export MCP_API_KEY="your-long-random-secret"
npm run start:http
# listens on http://0.0.0.0:3000/mcp
```

## Client configuration

### stdio — Claude Desktop, local Cursor, etc.

```json
{
  "mcpServers": {
    "postgresql": {
      "command": "node",
      "args": ["/absolute/path/to/postgresql-mcp/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://mcp_reader:password@localhost:5432/devstrom"
      }
    }
  }
}
```

Or publish to npm:

```json
{
  "mcpServers": {
    "postgresql": {
      "command": "npx",
      "args": ["-y", "postgresql-mcp"],
      "env": {
        "DATABASE_URL": "postgresql://mcp_reader:password@localhost:5432/devstrom"
      }
    }
  }
}
```

### Streamable HTTP — any remote MCP client

Point the client at your hosted endpoint. **No database URL in client config.**

```json
{
  "mcpServers": {
    "postgresql": {
      "url": "https://postgres-mcp.example.com/mcp",
      "headers": {
        "Authorization": "Bearer your-api-key"
      }
    }
  }
}
```

Clients that only support stdio can bridge with [mcp-remote](https://www.npmjs.com/package/mcp-remote):

```json
{
  "mcpServers": {
    "postgresql": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote",
        "https://postgres-mcp.example.com/mcp",
        "--header",
        "Authorization:Bearer your-api-key"
      ]
    }
  }
}
```

## Docker

### Compose (homelab / shared Postgres network)

```bash
cp docker-compose.example.yml docker-compose.yml
cp .env.example .env   # set DATABASE_URL, MCP_API_KEY; encode @ in passwords as %40
docker compose up -d --build
curl -s http://127.0.0.1:3000/health
```

Expects an existing `global-network` and a Postgres container reachable as `postgres` (see comments in `docker-compose.example.yml`).

### Single container

```bash
docker build -t postgresql-mcp .
docker run --rm -p 3000:3000 \
  -e DATABASE_URL="postgresql://mcp_reader:password@host.docker.internal:5432/devstrom" \
  -e MCP_API_KEY="your-api-key" \
  postgresql-mcp
```

## Read-only database user

```bash
MCP_READER_PASSWORD='your-password' psql -U postgres -d devstrom \
  -v ON_ERROR_STOP=1 -f scripts/setup_readonly_user.sql
```

## Environment variables

| Variable | Description |
|----------|-------------|
| `DATABASE_URL` | PostgreSQL connection string (server-side) |
| `MCP_POSTGRES_URL` | Alias for `DATABASE_URL` |
| `MCP_TRANSPORT` | `stdio` (default) or `http` |
| `MCP_API_KEY` | Bearer token for HTTP auth (required on non-loopback hosts) |
| `HOST` / `MCP_HOST` | HTTP bind address (default `0.0.0.0`) |
| `PORT` / `MCP_PORT` | HTTP port (default `3000`) |
| `MCP_HTTP_PATH` | HTTP MCP path (default `/mcp`) |
| `MCP_MAX_ROWS` | Max rows per query (default `100`, max `1000`) |

## CLI

```bash
postgresql-mcp              # stdio (default)
postgresql-mcp stdio        # stdio, explicit
postgresql-mcp http           # Streamable HTTP server
postgresql-mcp postgresql://...   # stdio with URL arg
```

## Development

```bash
npm test
npm run typecheck
npm run dev -- postgresql://...
npm run dev:http
```

## License

MIT