MCP PostgreSQL Gateway

Read-only MCP (Model Context Protocol) server for PostgreSQL database access. Enables AI agents (Claude Code, Cursor, VS Code) to query databases safely.

Architecture

┌─────────────────┐     HTTP/SSE      ┌──────────────────────┐
│   AI Agent      │◄──────────────────►│  MCP PostgreSQL      │
│  (Claude Code,  │    X-API-Key      │     Gateway          │
│   Cursor, etc)  │                   │                      │
└─────────────────┘                   │  ┌────────────────┐  │
                                      │  │ Auth Middleware│  │
                                      │  └───────┬────────┘  │
                                      │          │           │
                                      │  ┌───────▼────────┐  │
                                      │  │ Rate Limiter   │  │
                                      │  └───────┬────────┘  │
                                      │          │           │
                                      │  ┌───────▼────────┐  │
                                      │  │ SQL Validator  │  │
                                      │  │ (read-only)    │  │
                                      │  └───────┬────────┘  │
                                      │          │           │
                                      │  ┌───────▼────────┐  │
                                      │  │ FastMCP Tools  │  │
                                      │  └───────┬────────┘  │
                                      └──────────┼───────────┘
                                                 │
                                      ┌──────────▼───────────┐
                                      │     PostgreSQL       │
                                      │  (read-only mode)    │
                                      └──────────────────────┘

Features

• pg_execute_sql - Execute read-only SQL queries (SELECT, WITH, EXPLAIN, SHOW)
• pg_search_objects - Search database objects (schemas, tables, columns, indexes, procedures)
• API Key authentication - Secure access via X-API-Key header
• Rate limiting - Protection from request overload
• Read-only enforcement - Double protection at SQL parser and PostgreSQL level
• Row limiting - Automatic truncation of large results

Quick Start

1. Configure environment

cp .env.example .env
# Edit .env with your database credentials and API key

2. Run with Docker Compose

docker-compose up --build

3. Verify health

curl http://localhost:8080/healthz
# {"status": "healthy", "database": "...", ...}

Configuration

All configuration via environment variables:

Variable	Description	Default
DATABASE_URL	Full PostgreSQL connection string	-
DB_HOST	Database host (if not using DATABASE_URL)	localhost
DB_PORT	Database port	5432
DB_NAME	Database name	postgres
DB_USER	Database user	postgres
DB_PASSWORD	Database password	-
DB_SSLMODE	SSL mode (disable, prefer, require)	prefer
API_KEY	API key for authentication (min 16 chars)	required
READONLY	Enable read-only mode	true
MAX_ROWS	Maximum rows in response	1000
QUERY_TIMEOUT	Query timeout in seconds	60
RATE_LIMIT_REQUESTS	Max requests per window	100
RATE_LIMIT_WINDOW	Rate limit window in seconds	60
PORT	Server port	8080
LOG_LEVEL	Logging level	INFO

Client Configuration

Claude Code

Add to .mcp.json:

{
  "mcpServers": {
    "pg-gateway": {
      "type": "http",
      "url": "https://your-domain.com/mcp",
      "headers": {
        "X-API-Key": "${PG_GATEWAY_API_KEY}"
      }
    }
  }
}

Or via CLI:

claude mcp add --transport http pg-gateway https://your-domain.com/mcp \
  --header "X-API-Key: your-api-key"

Cursor

Add to ~/.cursor/mcp.json:

{
  "mcpServers": {
    "pg-gateway": {
      "url": "https://your-domain.com/mcp",
      "headers": {
        "X-API-Key": "your-api-key-here"
      }
    }
  }
}

VS Code

Add to .vscode/mcp.json:

{
  "servers": {
    "pg-gateway": {
      "type": "http",
      "url": "https://your-domain.com/mcp",
      "headers": {
        "X-API-Key": "${input:pg-api-key}"
      }
    }
  },
  "inputs": [
    {
      "type": "promptString",
      "id": "pg-api-key",
      "description": "PostgreSQL Gateway API Key",
      "password": true
    }
  ]
}

MCP Tools

pg_execute_sql

Execute read-only SQL queries.

{
  "name": "pg_execute_sql",
  "arguments": {
    "sql": "SELECT * FROM users LIMIT 10"
  }
}

Response:

{
  "rows": [...],
  "count": 10,
  "total_count": 10,
  "truncated": false,
  "execution_time_ms": 12.5
}

pg_search_objects

Search database objects with progressive disclosure.

{
  "name": "pg_search_objects",
  "arguments": {
    "object_type": "table",
    "pattern": "user%",
    "schema": "public",
    "detail_level": "summary",
    "limit": 50
  }
}

Parameters:

• object_type: schema, table, column, index, procedure
• pattern: SQL LIKE pattern (% = any chars, _ = single char)
• schema: Filter to specific schema
• table: Filter to specific table (for columns/indexes)
• detail_level: names, summary, full
• limit: 1-1000

Troubleshooting

Connection refused

Error: Connection refused to localhost:5432

• Check PostgreSQL is running
• Verify DB_HOST and DB_PORT in .env
• For Docker: use host.docker.internal instead of localhost

Authentication failed

Error: password authentication failed

• Verify DB_USER and DB_PASSWORD
• Check PostgreSQL pg_hba.conf allows connections

Query timeout

Error: Query cancelled due to timeout

• Increase QUERY_TIMEOUT in .env
• Optimize your SQL query

Read-only violation

Error: Read-only mode: Query must start with SELECT, WITH, EXPLAIN, SHOW

• Only SELECT queries are allowed
• Use pg_search_objects to explore database structure

Rate limit exceeded

Error: Rate limit exceeded

• Wait for the rate limit window to reset
• Increase RATE_LIMIT_REQUESTS if needed

Development

Local run

pip install -r requirements.txt
DATABASE_URL="postgres://..." API_KEY="test-key-1234567890" python -m src.server

Run tests

pip install pytest pytest-asyncio
pytest

Generate API key

python -c "import secrets; print(secrets.token_urlsafe(32))"

Contributing

See CONTRIBUTING.md for guidelines.

License

MIT