# PostgreSQL MCP AllAccess

Production-ready PostgreSQL MCP server with global connection pooling, PgBouncer integration, and automatic password rotation for AWS RDS.

## Quick Start (2 minutes)

### With AWS Secrets Manager

```bash
# 1. Set AWS credentials
export AWS_ACCESS_KEY_ID=your_key
export AWS_SECRET_ACCESS_KEY=your_secret
export AWS_REGION=us-west-2

# 2. Deploy (fetches DB credentials from AWS)
python3 deploy_with_aws_secrets.py

# 3. Restart Claude Code to connect
# The deploy script shows the URL - it's http://localhost:3000/sse
```

That's it! The MCP server is now running and Claude Code will auto-detect it.

**Optional:** Set custom AWS secret names:
```bash
export AWS_SECRET_NAME=your/secret/name
export AWS_PASSWORD_SECRET_NAME=rds!your-password-id
python3 deploy_with_aws_secrets.py
```

### Without AWS (Simple Credentials)

```bash
# 1. Copy .env.example and fill in your database credentials
cd /path/to/postgres_mcp_allaccess
cp .env.example .env
# Edit .env and fill in the REQUIRED fields (first 5 lines)

# 2. Deploy (docker-compose reads .env automatically)
docker-compose up -d

# 3. Restart Claude Code to connect
# MCP server is at http://localhost:3000/sse
```

## What You Get

✅ **Global connection pooling** - 15 max connections to PostgreSQL (configurable)
✅ **Automatic password rotation** - Zero downtime when AWS rotates passwords
✅ **Transaction-level pooling** - Efficient connection reuse across all queries
✅ **Production ready** - Health checks, logging, monitoring built-in

## How It Works

```
Multiple Claude Sessions → MCP Server → PgBouncer → PostgreSQL
   (90+ clients)          (max 15)     (max 15)    (sees ≤15 connections)
```

- **PgBouncer enforces max 15 connections** to your PostgreSQL database
- **Automatic queueing** - Extra requests wait, then process when connections free up
- **Zero config needed** - Default settings work for most use cases

## Common Tasks

### Check if it's running
```bash
curl http://localhost:3000/health
# Should show: "OK - Pool: 2/15 connections"
```

### View logs
```bash
docker-compose logs -f
```

### Stop server
```bash
docker-compose down
```

### Change connection limits
Edit `pgbouncer/pgbouncer.ini`:
```ini
default_pool_size = 10      # Max connections to PostgreSQL
```
Then redeploy: `docker-compose up -d --build`

## Available Tools (Use in Claude)

**Query Execution:**
- `list_tables` - List tables in a schema
- `execute_query` - Run any SQL query
- `describe_table` - See table structure
- `execute_file` - Run SQL from a file

**Schema Operations:**
- `list_schemas` - List all schemas
- `search_tables` - Find tables by name pattern
- `get_database_context` - Overview of database structure

**Analysis:**
- `explain_query` - Get query execution plan
- `analyze_query` - Performance analysis
- `suggest_indexes` - Index recommendations

**Session:**
- `get_session_info` - Current session status
- `get_query_history` - View query history

## Troubleshooting

### Claude Code can't connect
**Solution:** Restart Claude Code after deploying the MCP server.

### "Pool not initialized" error
**Solution:** Run any query first - pool initializes on first use.

### Container won't start
```bash
# Check what went wrong
docker-compose logs

# Common fixes:
# 1. Port 3000 in use: Change MCP_SSE_PORT in .env
# 2. Bad credentials: Check .env file or AWS secrets
# 3. Build cache issue: docker-compose up -d --build --force-recreate
```

### Connection timeout
Increase pool timeout (default 30 seconds):
```bash
export POSTGRES_POOL_TIMEOUT=60
python3 deploy_with_aws_secrets.py  # or docker-compose up -d
```

### Too many connections to PostgreSQL
Check your limit:
```bash
docker exec -e PGPASSWORD='your_pass' postgres-mcp-allaccess \
  psql -h localhost -p 6432 -U your_user -d your_db \
  -c "SELECT count(*) FROM pg_stat_activity WHERE usename='your_user'"
```
Should show 2-15 connections (never exceeds `default_pool_size`).

## Configuration

### Connection Pool Settings

**Environment variables** (set before deploying):
```bash
POSTGRES_MAX_CONNECTIONS=15     # MCP pool size
POSTGRES_MIN_CONNECTIONS=2      # Warm connections
POSTGRES_POOL_TIMEOUT=30        # Queue wait time (seconds)
```

**PgBouncer limits** (edit `pgbouncer/pgbouncer.ini`):
```ini
default_pool_size = 15          # MAX connections to PostgreSQL
min_pool_size = 2               # MIN kept alive
pool_mode = transaction         # Release after each transaction
```

### Understanding the Pool

**Question:** If 90 Claude sessions query at once, does PostgreSQL see 90 connections?

**Answer:** **NO!** PostgreSQL sees maximum 15 connections (or whatever you set in `default_pool_size`).

- **Connections auto-release** after each query
- **Extra requests queue** and process when connections free up
- **No session management needed** - everything is automatic

## Automatic Password Rotation

When AWS rotates your password:
1. Next query fails with auth error
2. MCP fetches new password from AWS Secrets Manager
3. Updates both MCP pool and PgBouncer
4. Retries query - succeeds!

**Zero downtime.** No restart needed.

**Requirements:**
- Deploy with `deploy_with_aws_secrets.py` (AWS credentials available)
- AWS secret names configured (done automatically)

## Security

✅ **Never commit:**
- `.env` files
- `*.log` files
- `config/postgres_config.ini`

✅ **Safe to commit:**
- AWS secret **names** (e.g., "postgres/dev/db")
- Database **hostnames** (e.g., "mydb.rds.amazonaws.com")

All sensitive data is gitignored automatically.

## Advanced

### Project Structure
```
postgres_mcp_allaccess/
├── deploy_with_aws_secrets.py   # AWS deployment script
├── docker-compose.yml            # Container orchestration
├── Dockerfile                    # Single container (MCP + PgBouncer)
├── docker-entrypoint.sh          # Startup script
├── pgbouncer/pgbouncer.ini       # Connection pool config
└── src/postgres_mcp_allaccess/
    ├── server.py                 # MCP server
    ├── database.py               # Global pool + auto rotation
    ├── pgbouncer_manager.py      # PgBouncer control
    └── transports/sse_transport.py  # HTTP/SSE transport
```

### Health Monitoring
```bash
# Health check endpoint
curl http://localhost:3000/health

# Check PgBouncer process
docker exec postgres-mcp-allaccess ps aux | grep pgbouncer

# View all environment variables
docker exec postgres-mcp-allaccess env | grep -E "POSTGRES|AWS|PGBOUNCER"
```

### Manual Password Refresh
```bash
# Force refresh from AWS (if auto-rotation fails)
docker-compose restart
```

## License

Apache License 2.0 - See [LICENSE](LICENSE) file for details.

## Support

- **Issues:** https://github.com/yty-build/postgres_mcp_allaccess/issues
- **Discussions:** https://github.com/yty-build/postgres_mcp_allaccess/discussions
- **Security:** Report privately via [GitHub Security Advisories](https://github.com/yty-build/postgres_mcp_allaccess/security/advisories)