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

Related MCP server: PostgreSQL MCP Server

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

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 CURSOR_SETUP.md for setup help

• Review Troubleshooting section

• Open a GitHub issue

Additional Documentation

• Cursor Setup Guide - Detailed Cursor IDE integration

• MCP Protocol - MCP specification

• PostgreSQL Docs - PostgreSQL documentation