PostgreSQL MCP Server

# 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 ```bash docker build -t postgresql-mcp-server . ``` ### 2. Configure Environment Generate API key: ```bash python3 -c "import secrets; print(secrets.token_urlsafe(32))" ``` Copy `.env.example` to `.env` and update: ```bash cp .env.example .env # Edit .env with your PostgreSQL and API key ``` ### 3. Run with Docker Compose ```bash docker-compose up -d ``` Server will be available at `http://localhost:3000` ### 4. Configure Cursor IDE Add to Cursor MCP settings: ```json { "mcpServers": { "postgresql": { "url": "https://your-server.com:3000", "headers": { "Authorization": "Bearer your_api_key" } } } } ``` See [ELESTIO_DEPLOY.md](ELESTIO_DEPLOY.md) 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: ```bash docker-compose up -d ``` ### Option 2: Direct Docker Run ```bash 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: ```bash 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 ```bash # 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 [CURSOR_SETUP.md](CURSOR_SETUP.md) for setup help - Review [Troubleshooting](#troubleshooting) section - Open a GitHub issue ## Additional Documentation - [Cursor Setup Guide](CURSOR_SETUP.md) - Detailed Cursor IDE integration - [MCP Protocol](https://modelcontextprotocol.io/) - MCP specification - [PostgreSQL Docs](https://www.postgresql.org/docs/) - PostgreSQL documentation