postgres_mcp_allaccess

yty-build/postgres_mcp_allaccess

3.2

If you are the rightful owner of postgres_mcp_allaccess and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to henry@mcphub.com.

PostgreSQL MCP AllAccess is a production-ready server solution that integrates global connection pooling, PgBouncer, and automatic password rotation for AWS RDS.

Tools
12
Resources
0
Prompts
0

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

# 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:

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)

# 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

curl http://localhost:3000/health
# Should show: "OK - Pool: 2/15 connections"

View logs

docker-compose logs -f

Stop server

docker-compose down

Change connection limits

Edit pgbouncer/pgbouncer.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

# 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):

export POSTGRES_POOL_TIMEOUT=60
python3 deploy_with_aws_secrets.py  # or docker-compose up -d

Too many connections to PostgreSQL

Check your limit:

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):

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):

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

# 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

# Force refresh from AWS (if auto-rotation fails)
docker-compose restart

License

Apache License 2.0 - See file for details.

Support