PostgreSQL MCP Server

# PostgreSQL MCP Server A Model Context Protocol (MCP) server that provides AI assistants with secure access to PostgreSQL databases. ## Features - 🔒 **Secure database access** with read-only queries by default - 🛠️ **Comprehensive database tools** for schema exploration and querying - 🧠 **Intelligent query validation** with security and performance analysis - ⚡ **Real-time optimization suggestions** for better query performance - 🎯 **SQL injection detection** and dangerous operation blocking - ⚙️ **Configurable connection pooling** and query limits - 🔍 **Schema filtering** for multi-tenant environments - 📝 **Detailed logging** and query monitoring - 🚀 **Easy setup** with environment variables or config files ## Installation 1. Clone the repository: ```bash git clone <your-repo-url> cd postgresql-mcp-server ``` 2. Install dependencies: ```bash pip install -r requirements.txt ``` 3. Configure your database connection (see [Configuration](#configuration)) ## Configuration ### Environment Variables (Recommended) Copy `.env.example` to `.env` and configure your database: ```bash cp .env.example .env ``` Edit `.env` with your database credentials: ```bash # PostgreSQL Database Configuration POSTGRES_HOST=localhost POSTGRES_PORT=5432 POSTGRES_DATABASE=your_database_name POSTGRES_USERNAME=your_username POSTGRES_PASSWORD=your_password POSTGRES_SSL_MODE=prefer POSTGRES_MIN_CONNECTIONS=1 POSTGRES_MAX_CONNECTIONS=10 # MCP Server Configuration MCP_NAME=postgresql-mcp-server MCP_VERSION=1.0.0 MCP_MAX_QUERY_TIME=30 MCP_MAX_ROWS=1000 MCP_ALLOWED_SCHEMAS= MCP_LOG_LEVEL=INFO MCP_LOG_QUERIES=true ``` ### JSON Configuration (Alternative) Copy `config.example.json` to `config.json` and modify as needed. ## Usage ### Start the MCP Server ```bash python main.py ``` ### Test Database Connection ```bash python main.py --test ``` ### Enable Verbose Logging ```bash python main.py --verbose ``` ### Demo Query Validation (No Database Required) ```bash python demo_validation.py ``` This demo script showcases the query validation features without requiring a database connection. ## Available Tools The MCP server provides the following tools for AI assistants with built-in query validation and optimization: ### 1. `query` Execute SELECT queries on the database. **Parameters:** - `sql` (required): SQL SELECT query to execute - `params` (optional): Array of parameters for the query **Example:** ```json { "name": "query", "arguments": { "sql": "SELECT id, name FROM users WHERE active = $1 LIMIT 10", "params": ["true"] } } ``` ### 2. `list_tables` List all tables in a database schema. **Parameters:** - `schema` (optional): Schema name (default: "public") **Example:** ```json { "name": "list_tables", "arguments": { "schema": "public" } } ``` ### 3. `describe_table` Get detailed information about a table's columns and structure. **Parameters:** - `table_name` (required): Name of the table to describe - `schema` (optional): Schema name (default: "public") **Example:** ```json { "name": "describe_table", "arguments": { "table_name": "users", "schema": "public" } } ``` ### 4. `list_schemas` List all available database schemas. **Example:** ```json { "name": "list_schemas", "arguments": {} } ``` ### 5. `test_connection` Test the database connection and get server information. **Example:** ```json { "name": "test_connection", "arguments": {} } ``` ### 6. `validate_query` Validate and analyze a SQL query for security issues, performance problems, and optimization opportunities. **Parameters:** - `sql` (required): SQL query to validate and analyze - `schema` (optional): Database schema name for validation context (default: "public") **Example:** ```json { "name": "validate_query", "arguments": { "sql": "SELECT * FROM users WHERE email LIKE '%@gmail.com' ORDER BY created_at", "schema": "public" } } ``` **Features:** - **Security Analysis**: Detects SQL injection patterns and dangerous operations - **Performance Warnings**: Identifies inefficient query patterns - **Optimization Suggestions**: Recommends improvements for better performance - **Complexity Scoring**: Rates query complexity on a 1-10 scale - **Index Recommendations**: Suggests indexes for better performance **Example Response:** ``` Query Analysis Report ================================================== Valid: ✅ Yes Complexity: 4/10 ⚡ Performance Warnings: WARNING: SELECT * can be inefficient 💡 Specify only needed columns instead of using SELECT * WARNING: LIKE with leading wildcard prevents index usage 💡 Avoid leading wildcards in LIKE patterns or consider full-text search 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan 2. Consider adding an index on users.email if queries are slow 3. For ORDER BY with LIMIT, ensure there's an index on the ORDER BY columns ``` ## Query Validation & Optimization The MCP server includes intelligent query analysis that automatically validates every query for security and performance issues. ### Real-Time Query Analysis - **Automatic validation** of all queries before execution - **Security threat detection** including SQL injection patterns - **Performance issue identification** for slow query patterns - **Optimization suggestions** with specific recommendations - **Complexity scoring** to help understand query resource usage ### Security Validation - **SQL injection detection** using pattern matching - **Dangerous function blocking** (e.g., `pg_read_file`, `COPY`) - **Statement type validation** (only SELECT allowed) - **Comment pattern analysis** for potential bypass attempts ### Performance Analysis - **SELECT * detection** with column-specific recommendations - **Missing index suggestions** based on WHERE/JOIN clauses - **Cartesian product warnings** for JOINs without conditions - **Leading wildcard detection** in LIKE patterns - **Query complexity scoring** (1-10 scale) ### Optimization Suggestions - **Index recommendations** for frequently filtered columns - **Query restructuring** suggestions for better performance - **LIMIT clause recommendations** for large result sets - **JOIN order optimization** for complex queries - **EXISTS vs IN** recommendations for subqueries ## Security Features ### Read-Only Queries By default, only `SELECT` statements are allowed. This prevents accidental data modification through the MCP server. ### Row Limits All queries are automatically limited to prevent excessive memory usage and long-running queries. ### Schema Filtering You can restrict access to specific database schemas using the `MCP_ALLOWED_SCHEMAS` configuration. ### Connection Pooling Database connections are managed through a connection pool to ensure efficient resource usage. ## Development ### Running Tests ```bash pip install pytest pytest-asyncio pytest tests/ ``` ### Code Formatting ```bash pip install black black . ``` ### Type Checking ```bash pip install mypy mypy src/ ``` ## Configuration Options ### Database Configuration | Variable | Description | Default | |----------|-------------|---------| | `POSTGRES_HOST` | PostgreSQL server host | `localhost` | | `POSTGRES_PORT` | PostgreSQL server port | `5432` | | `POSTGRES_DATABASE` | Database name | Required | | `POSTGRES_USERNAME` | Database username | Required | | `POSTGRES_PASSWORD` | Database password | Required | | `POSTGRES_SSL_MODE` | SSL connection mode | `prefer` | | `POSTGRES_MIN_CONNECTIONS` | Minimum pool connections | `1` | | `POSTGRES_MAX_CONNECTIONS` | Maximum pool connections | `10` | ### Server Configuration | Variable | Description | Default | |----------|-------------|---------| | `MCP_NAME` | Server name | `postgresql-mcp-server` | | `MCP_VERSION` | Server version | `1.0.0` | | `MCP_MAX_QUERY_TIME` | Max query execution time (seconds) | `30` | | `MCP_MAX_ROWS` | Maximum rows returned per query | `1000` | | `MCP_ALLOWED_SCHEMAS` | Comma-separated list of allowed schemas | All schemas | | `MCP_LOG_LEVEL` | Logging level | `INFO` | | `MCP_LOG_QUERIES` | Whether to log executed queries | `true` | ## Troubleshooting ### Connection Issues 1. Verify your database credentials in `.env` 2. Ensure PostgreSQL is running and accessible 3. Check firewall and network connectivity 4. Test connection: `python main.py --test` ### Permission Issues 1. Ensure the database user has appropriate SELECT permissions 2. Check schema access permissions 3. Verify SSL configuration if required ### Performance Issues 1. Adjust connection pool settings 2. Implement query optimization 3. Consider adding row limits to queries 4. Monitor query execution times ## Contributing 1. Fork the repository 2. Create a feature branch 3. Make your changes 4. Add tests for new functionality 5. Submit a pull request ## License MIT License - see LICENSE file for details.