compilotbv/mcpg

PostgreSQL MCP Server

Model Context Protocol server for PostgreSQL with HTTP/SSE transport for remote deployment. Built with Python, FastAPI, and Docker for Elestio or any cloud platform.

Features

• HTTP/SSE Transport: Remote access via HTTPS
• API Key Authentication: Secure access control
• Query Execution: Execute SELECT queries with natural language
• Schema Management: Create, alter, and drop tables and indexes
• Data Manipulation: Insert, update, and delete data
• User Management: Create users and manage permissions
• Database Maintenance: VACUUM, backups, and connection management
• Secure: Read-only mode, connection pooling, and query timeouts

Quick Start

1. Build the Docker Image

docker build -t postgresql-mcp-server .

2. Configure Environment

Generate API key:

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

Copy .env.example to .env and update:

cp .env.example .env
# Edit .env with your PostgreSQL and API key

3. Run with Docker Compose

docker-compose up -d

Server will be available at http://localhost:3000

4. Configure Cursor IDE

Add to Cursor MCP settings:

{
  "mcpServers": {
    "postgresql": {
      "url": "https://your-server.com:3000",
      "headers": {
        "Authorization": "Bearer your_api_key"
      }
    }
  }
}

See for deployment guide.

Available Tools

Query Tools

• execute_query - Run SELECT queries
• execute_explain - Get query execution plans
• list_databases - List all databases
• list_tables - List tables in schema
• list_columns - Show column information
• get_table_info - Get detailed table metadata
• get_database_size - Get database and table sizes

Schema Management (DDL)

• create_table - Create new tables
• drop_table - Remove tables
• alter_table - Modify table structure
• create_index - Create indexes
• drop_index - Remove indexes
• get_table_ddl - Generate CREATE TABLE statements

Data Operations (DML)

• insert_data - Insert single row
• bulk_insert - Insert multiple rows
• update_data - Update rows
• delete_data - Delete rows

User Management (DCL)

• list_users - List database users
• create_user - Create new users
• grant_permissions - Grant privileges
• revoke_permissions - Revoke privileges
• list_permissions - Show user permissions

Maintenance

• vacuum_analyze - Run VACUUM ANALYZE
• backup_database - Create pg_dump backup
• restore_database - Restore from backup
• kill_connections - Terminate connections
• get_active_connections - Show active connections
• test_connection - Test database connection

Usage Examples

Once configured in Cursor, interact with your database naturally:

"Show me all tables in the database"
"What's the structure of the users table?"
"Find the top 10 customers by order count"
"Create a products table with id, name, and price columns"
"Insert a new user with email test@example.com"
"Show me all database users and their permissions"

Configuration Options

Environment variables:

• POSTGRES_HOST - Database host (default: localhost)
• POSTGRES_PORT - Database port (default: 5432)
• POSTGRES_DB - Database name
• POSTGRES_USER - Database user
• POSTGRES_PASSWORD - Database password
• POSTGRES_READONLY - Read-only mode (default: false)
• POSTGRES_SSLMODE - SSL mode (default: prefer)
• POSTGRES_POOL_MIN - Min connections (default: 1)
• POSTGRES_POOL_MAX - Max connections (default: 10)
• QUERY_TIMEOUT - Query timeout in seconds (default: 30)

Security Best Practices

1. Use environment variables for credentials, never hardcode
2. Enable read-only mode for exploration: POSTGRES_READONLY=true
3. Create dedicated users with minimal required privileges
4. Use SSL connections for production: POSTGRES_SSLMODE=require
5. Set query timeouts to prevent long-running queries
6. Limit connection pools based on your database capacity

Deployment Options

Option 1: Using Docker Compose (Recommended)

Edit .env with your PostgreSQL credentials:

docker-compose up -d

Option 2: Direct Docker Run

docker run -i --rm \
  -e POSTGRES_HOST=your_host \
  -e POSTGRES_PORT=5432 \
  -e POSTGRES_DB=your_db \
  -e POSTGRES_USER=your_user \
  -e POSTGRES_PASSWORD=your_password \
  postgresql-mcp-server

Project Structure

mcpg/
├── src/postgresql_mcp_server/
│   ├── __init__.py
│   ├── server.py              # Main MCP server
│   ├── db_manager.py           # Connection management
│   ├── query_executor.py       # Query execution
│   ├── schema_manager.py       # DDL operations
│   ├── data_manager.py         # DML operations
│   ├── user_manager.py         # User management
│   └── maintenance_manager.py  # Maintenance tools
├── Dockerfile
├── docker-compose.yml
├── requirements.txt
├── .env.example
├── cursor-mcp-config.json
├── CURSOR_SETUP.md
└── README.md

Troubleshooting

Connection Issues

1. Verify PostgreSQL is accessible:

   docker run -it --rm postgres:16-alpine psql -h host.docker.internal -U user -d db
   

2. Check Docker network settings for container-to-container communication

3. Review Cursor logs for MCP errors

Permission Errors

• Verify database user has required privileges
• Check PostgreSQL pg_hba.conf for access rules
• Use test_connection tool to diagnose

Docker Issues

• Ensure Docker daemon is running
• Check for port conflicts (5432)
• Verify image is built: docker images | grep postgresql-mcp-server

Development

Requirements

• Python 3.11+
• PostgreSQL 12+
• Docker 20.10+

Local Development

# Install dependencies
pip install -r requirements.txt

# Set environment variables
export POSTGRES_HOST=localhost
export POSTGRES_PORT=5432
# ... other vars

# Run directly
python -m src.postgresql_mcp_server.server

License

MIT License - see LICENSE file for details

Contributing

Contributions welcome! Please open an issue or PR.

Support

For issues and questions:

• Check for setup help
• Review Troubleshooting section
• Open a GitHub issue

Additional Documentation

• - Detailed Cursor IDE integration
• MCP Protocol - MCP specification
• PostgreSQL Docs - PostgreSQL documentation