Content
# pgmcp
**English** | [English](#english)
> Operate PostgreSQL's MCP Server with natural language, built with Rust, supporting stdio and HTTP dual modes.
```
You: Help me check the last 10 records in the orders table
AI: Call query → SELECT * FROM orders ORDER BY created_at DESC LIMIT 10
┌─────┬──────────┬─────────┐
│ id │ customer │ amount │
├─────┼──────────┼─────────┤
│ 102 │ Alice │ 299.00 │
│ 101 │ Bob │ 150.50 │
└─────┴──────────┴─────────┘
```
---
## Features
- 🟢 **Three-layer security isolation** — Provides read-only, analysis, and write approval layers with 11 functional tools
- 🔒 **SQL AST parsing to prevent injection** — Based on the sqlparser crate, not regular expressions
- ✍️ **Two-step approval for writes** — Models cannot skip human confirmation for direct write operations
- ⚡ **Rust single binary** — Zero runtime dependencies, 153MB Docker image
- 🔌 **stdio + HTTP dual modes** — Supports local IDEs and remote clients
- 🔑 **Bearer token authentication** — Optional authentication for HTTP mode
- 🐘 **PostgreSQL 16+** — Supports pg_stat_statements / pgvector
---
## Architecture
```
┌─────────────────────────────────────────────────┐
│ MCP Clients │
│ Claude Code · goose · Cursor · Windsurf · ... │
└───────────────┬─────────────────────────────────┘
│ stdio or HTTP
┌───────────────▼─────────────────────────────────┐
│ pgmcp │
│ │
│ 🟢 Read-only layer list_databases list_tables │
│ describe_table query explain │
│ │
│ 🟡 Analysis layer slow_queries table_bloat │
│ index_suggestions │
│ │
│ 🔴 Write layer execute_write → 【Human approval】 │
│ confirm_write / cancel_write │
└───────────────┬─────────────────────────────────┘
│ deadpool connection pool
┌───────────────▼─────────────────────────────────┐
│ PostgreSQL 16 / 17 / 18 │
└─────────────────────────────────────────────────┘
```
---
## Tool List
### 🟢 Read-only layer — No risk, strictly SELECT only
| Tool | Description | Key parameters |
| ---------------- | --------------------------------------- | ---------------------------------- |
| `list_databases` | List all databases and their sizes | None |
| `list_tables` | List all tables in a schema (including size/estimated row count) | `schema` (default: public) |
| `describe_table` | View table structure: columns, types, constraints, indexes | `table` (required), `schema` |
| `query` | Execute SELECT, automatically append LIMIT, 5s timeout | `sql` (required), `limit` (max 500) |
| `explain` | EXPLAIN ANALYZE execution plan analysis, 10s timeout | `sql` (required) |
### 🟡 Analysis layer — Depends on pg_stat_statements
| Tool | Description | Key parameters |
| ------------------- | ---------------------------------- | ----------------------- |
| `slow_queries` | Top N slow queries (average/total/count) | `limit` (default 10) |
| `table_bloat` | Dead tuple bloat analysis, >20% warning | `schema`, `min_size_mb` |
| `index_suggestions` | Find unused indexes | `schema` |
### 🔴 Write layer — Two-step approval required, cannot be skipped
| Tool | Description |
| --------------- | -------------------------------------- |
| `execute_write` | Submit write SQL, show impact explanation, **not executed** |
| `confirm_write` | Execute after human confirmation |
| `cancel_write` | Cancel pending operation |
Write process:
```
Model calls execute_write(sql)
↓
pgmcp shows SQL + impact explanation, enters waiting state
↓
⚠️ Human input confirmation required
↓
User calls confirm_write → execute
User calls cancel_write → cancel
```
→ Detailed tool documentation: [docs/tools.md](docs/tools.md)
---
## Quick Start
### Prerequisites
- Rust 1.75+
- PostgreSQL 16+ (with `pg_stat_statements` enabled)
### Build
```bash
git clone https://github.com/<your-name>/pgmcp.git
cd pgmcp
cargo build --release
```
### Configuration
```bash
cp .env.example .env
```
```env
PG_HOST=127.0.0.1
PG_PORT=5432
PG_USER=<your-user>
PG_PASSWORD=<your-password>
PG_DBNAME=<your-database>
# HTTP mode authentication (optional)
MCP_AUTH_TOKEN=<your-secret-token>
```
### Start
```bash
# stdio mode (for local clients like Claude Code / Cursor)
./target/release/pgmcp --transport stdio
# HTTP mode (for remote clients or Web UI)
./target/release/pgmcp --transport http --port 3000
```
### Verification
```bash
# Verify stdio
echo '{"jsonrpc":"2.0","id":1,"method":"tools/list"}' \
| ./target/release/pgmcp --transport stdio 2>/dev/null
# Verify HTTP
curl -s -X POST http://localhost:3000/mcp \
-H 'Content-Type: application/json' \
-d '{"jsonrpc":"2.0","id":1,"method":"tools/list"}' | jq .
```
---
## Deployment
| Method | Applicable scenario | Documentation |
| ------------------ | ----------------------------------- | ---------------------------------------------------------------- |
| Run binary directly | Local development / simple deployment | [docs/deployment/binary.md](docs/deployment/binary.md) |
| Docker single container | Isolated deployment | [docs/deployment/docker.md](docs/deployment/docker.md) |
| Docker Compose | pgmcp + PostgreSQL orchestration（recommended） | [docs/deployment/compose.md](docs/deployment/compose.md) |
| Remote server + nginx | Production environment, HTTPS + reverse proxy | [docs/deployment/remote.md](docs/deployment/remote.md) |
| Local development mode | stdio, zero-configuration for IDEs | [docs/deployment/development.md](docs/deployment/development.md) |
---
## Client Access
### Claude Code
```bash
# ~/.claude.json
{
"mcpServers": {
"pgmcp": {
"type": "stdio",
"command": "/opt/pgMcp/target/release/pgmcp",
"args": ["--transport", "stdio"],
"env": {
"PG_HOST": "127.0.0.1",
"PG_USER": "<user>",
"PG_PASSWORD": "<password>",
"PG_DBNAME": "<database>"
}
}
}
}
```
### goose（Recommended: with LiteLLM）
```yaml
# ~/.config/goose/config.yaml
extensions:
pgmcp:
type: sse
uri: http://localhost:3000/mcp
enabled: true
# Add Bearer authentication when enabled:
# headers:
# Authorization: "Bearer <your-token>"
```
→ [docs/clients/goose.md](docs/clients/goose.md) — Complete LiteLLM integration instructions
### Cursor / Windsurf / VS Code
```json
{
"mcpServers": {
"pgmcp": {
"command": "/opt/pgMcp/target/release/pgmcp",
"args": ["--transport", "stdio"],
"env": {
"PG_HOST": "127.0.0.1",
"PG_USER": "<user>",
"PG_PASSWORD": "<password>",
"PG_DBNAME": "<db>"
}
}
}
}
```
Config file locations:
- Cursor: `.cursor/mcp.json`
- Windsurf: `~/.codeium/windsurf/mcp_config.json`
- VS Code: `settings.json` (with MCP enabled)
### Open WebUI（v0.6.31+）
Admin Panel → Settings → Tools → Enter:
```
http://<pgmcp-host>:3000/mcp
```
### LobeChat
Settings → Tools → Custom MCP Server → Enter HTTP address and Bearer Token.
### curl（General testing）
```bash
BASE=http://localhost:3000/mcp
TOKEN=your-token
# Handshake
curl -s -X POST $BASE \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $TOKEN" \
-d '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"0.1"}}}'
# Tool list
curl -s -X POST $BASE \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $TOKEN" \
-d '{"jsonrpc":"2.0","id":2,"method":"tools/list"}'
# Call tool
curl -s -X POST $BASE \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $TOKEN" \
-d '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"list_tables","arguments":{"schema":"public"}}}'
```
→ Complete client configurations: [docs/clients/](docs/clients/)
---
## Configuration Items
| Environment variable | Type | Default value | Description |
| ---------------- | ------ | ------------ | -------------------------------------------------- |
| `PG_HOST` | string | `localhost` | PostgreSQL host address |
| `PG_PORT` | u16 | `5432` | PostgreSQL port |
| `PG_USER` | string | Required | Database username |
| `PG_PASSWORD` | string | Required | Database password |
| `PG_DBNAME` | string | Required | Database name |
| `PG_POOL_MAX` | usize | `10` | Maximum connection pool size |
| `MCP_AUTH_TOKEN` | string | Empty (no authentication) | HTTP mode Bearer token, not set skips verification and prints warning |
| `RUST_LOG` | string | `warn` | Log level: `error` / `warn` / `info` / `debug` |
---
## Security Design
- **SQL AST parsing**: Read-only tools use sqlparser crate to parse syntax trees, rejecting non-SELECT statements, not relying on regular expressions
- **Query timeout**: query tool 5s, explain tool 10s, automatic termination on timeout
- **Mandatory LIMIT**: query tool returns max 500 rows, preventing large result sets
- **Two-step write approval**: execute_write shows but does not execute, confirm_write required for actual write
- **Connection pool isolation**: Using deadpool-postgres, connections not shared across requests
- **Bearer authentication**: HTTP mode supports token verification, stdio mode relies on system-level process isolation
→ Detailed security design: [docs/security.md](docs/security.md)
---
## Roadmap
- [x] Read-only tools (5)
- [x] Analysis tools (3)
- [x] Two-step write approval (3)
- [x] stdio + HTTP dual mode
- [x] Bearer token authentication
- [x] Docker multi-stage build
- [ ] pgvector vector search support
- [ ] Multi-database runtime switching (`/use dbname`)
- [ ] Streaming response (SSE)
- [ ] Web management interface
- [ ] Audit log persistence to PG
- [ ] TLS direct connection (without nginx)
## Contributing
Welcome PRs and Issues.
**Maintainers:**
- @OFMeteoriteH
- @Claude (AI)
- @Gemini (AI)
Please ensure before submitting:
```bash
cargo fmt
cargo clippy
cargo build --release
```
## License
MIT © OFMeteoriteH 2026
# English
> A PostgreSQL MCP Server built in Rust. Connect any MCP-compatible AI client to your database with natural language — safely.
## Features
- 🟢 **Three-tier security isolation** — Provides 3 layers of isolation (Read-only, Analysis, Write-approval) with 11 tools total
- 🔒 **SQL AST injection prevention** — sqlparser crate, not regex
- ✍️ **Mandatory two-step write approval** — the model cannot bypass human confirmation
- ⚡ **Single Rust binary** — zero runtime dependencies, 153MB Docker image
- 🔌 **stdio + HTTP dual transport** — works with local IDEs and remote clients
- 🔑 **Bearer token auth** — optional authentication for HTTP mode
- 🐘 **PostgreSQL 16+** — pg_stat_statements and pgvector ready
## Quick Start
```bash
git clone https://github.com/<your-name>/pgmcp.git
cd pgmcp
cargo build --release
cp .env.example .env # fill in your PG credentials
./target/release/pgmcp --transport http --port 3000
```
Full documentation is in [docs/](docs/).
## Client Setup
| Client | Transport | Config |
| --------------- | --------- | ---------------------------------------------------------- |
| Claude Code | stdio | [docs/clients/claude-code.md](docs/clients/claude-code.md) |
| goose + LiteLLM | HTTP | [docs/clients/goose.md](docs/clients/goose.md) |
| Cursor | stdio | [docs/clients/cursor.md](docs/clients/cursor.md) |
| Windsurf | stdio | [docs/clients/windsurf.md](docs/clients/windsurf.md) |
| VS Code Copilot | stdio | [docs/clients/vscode.md](docs/clients/vscode.md) |
| Open WebUI | HTTP | [docs/clients/open-webui.md](docs/clients/open-webui.md) |
| LobeChat | HTTP | [docs/clients/lobechat.md](docs/clients/lobechat.md) |
| Zed | stdio | [docs/clients/zed.md](docs/clients/zed.md) |
| LM Studio | HTTP | [docs/clients/lm-studio.md](docs/clients/lm-studio.md) |
| ChatGPT | TBD | — |
## License
MIT © OFMeteoriteH 2026
