# postgres-mcp-server

A Postgres MCP server built for [AWS Bedrock AgentCore Runtime](https://docs.aws.amazon.com/bedrock-agentcore/latest/devguide/runtime-mcp.html).

## AgentCore Protocol Compliance

- **Transport**: Stateless streamable-http (`stateless_http=True`)
- **Port**: 8000 on `0.0.0.0`
- **Path**: `POST /mcp`
- **Platform**: ARM64 container
- **Framework**: Python FastMCP

## Tools

| Tool | Description |
|------|-------------|
| `query` | Execute read-only SQL queries (runs in `READ ONLY` transaction) |
| `list_tables` | List tables in a schema with row counts and sizes |
| `describe_table` | Column details, constraints, and indexes for a table |
| `list_schemas` | List all non-system database schemas |
| `explain_query` | Get EXPLAIN plan for a query (optionally with ANALYZE) |
| `get_table_stats` | Row counts, sizes, scan stats, vacuum info |
| `get_slow_queries` | Top slow queries from pg_stat_statements |

## Environment Variables

| Variable | Required | Description |
|----------|----------|-------------|
| `POSTGRES_URL` | Yes* | PostgreSQL connection string |
| `DATABASE_URI` | Yes* | Alternative name for connection string |

*One of `POSTGRES_URL` or `DATABASE_URI` is required.

## Security

- All queries execute inside `READ ONLY` transactions
- Point `POSTGRES_URL` at a read replica for hard DB-level enforcement
- Use a Postgres user with `SELECT`-only grants for defense in depth

## Local Development

```bash
# Run directly
POSTGRES_URL="postgresql://user:pass@localhost:5432/mydb" python server.py

# Run with Docker
docker build -t postgres-mcp .
docker run -e POSTGRES_URL="postgresql://user:pass@host:5432/mydb" -p 8000:8000 postgres-mcp
```

## Deploy to AgentCore

```bash
# Build for ARM64
docker buildx build --platform linux/arm64 -t <ecr-uri>/pgmcp:latest --push .

# Create runtime
aws bedrock-agentcore-control create-agent-runtime \
  --agent-runtime-name "mcp_postgres" \
  --agent-runtime-artifact '{"containerConfiguration":{"containerUri":"<ecr-uri>/pgmcp:latest"}}' \
  --protocol-configuration '{"serverProtocol":"MCP"}' \
  --environment-variables '{"POSTGRES_URL":"postgresql://...","PORT":"8000"}' \
  --network-configuration '{"networkMode":"VPC","networkModeConfig":{"securityGroups":["sg-xxx"],"subnets":["subnet-xxx"]}}' \
  --role-arn "arn:aws:iam::xxx:role/agentcore-execution"
```

## Test

```bash
# Health check (via MCP initialize)
curl -X POST http://localhost:8000/mcp \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2025-03-26","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}}}'

# List tools
curl -X POST http://localhost:8000/mcp \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":2,"method":"tools/list"}'
```