PostgreSQL-MCP-Server

# PostgreSQL MCP Server <p align="center"> <img src="https://img.shields.io/badge/Python-3776AB?style=for-the-badge&logo=python&logoColor=white" alt="Python"/> <img src="https://img.shields.io/badge/FastAPI-009688?style=for-the-badge&logo=fastapi&logoColor=white" alt="FastAPI"/> <img src="https://img.shields.io/badge/PostgreSQL-4169E1?style=for-the-badge&logo=postgresql&logoColor=white" alt="PostgreSQL"/> <img src="https://img.shields.io/badge/Uvicorn-499848?style=for-the-badge&logo=gunicorn&logoColor=white" alt="Uvicorn"/> <img src="https://img.shields.io/badge/MCP-FF6B6B?style=for-the-badge&logo=protocol&logoColor=white" alt="MCP"/> </p> <p align="center"> <strong>Professional PostgreSQL Database Access for AI Assistants via Model Context Protocol</strong> </p> --- ## Features **Core Capabilities** - Complete schema analysis with tables, columns, constraints, indexes, and relationships - Advanced SQL query execution (SELECT with JOINs, CTEs, window functions, subqueries) - Secure data manipulation (INSERT, UPDATE with ACID transaction safety) - Schema management (CREATE, ALTER, DROP for tables, views, indexes, functions) - Automatic foreign key relationship mapping and constraint discovery - Query planning and performance optimization with EXPLAIN ANALYZE - Multi-schema support for complex database architectures **Security & Performance** - Granular operation-level access controls - ACID-compliant transaction management with rollback safety - Multi-layer SQL injection prevention - Connection pooling with psycopg3 for optimal performance - Row-level security (RLS) support - Comprehensive validation and error handling **Governed Privacy Pipeline** - Governance Layer with risk scoring, cost checks, approval gating, and safe query rewriting - ML-backed Query Risk Score (QRS) classifier for pre-execution risk prediction - Data Exposure Layer with AI-restricted masked output and UI full-data reference retrieval - Structured professional responses with execution summary and governance verdict --- ## Governance + Data Exposure Flow ```text User -> MCP (AI reasoning) -> Governance Layer -> Database -> Data Exposure Layer (split) -> AI (restricted view) -> UI (full data via reference) -> Final Response ``` ### Governance Layer - Evaluates each query before execution - Assigns `low`, `medium`, `high`, or `critical` risk with a numeric QRS score (0-100) - Returns one action: `allow`, `rewrite`, `approval_required`, `block` - Supports password-gated approvals for high-risk operations (e.g., DELETE/DROP) ### Data Exposure Layer - AI receives masked, limited results only - UI can fetch full result set by reference using `GET /exposure/{reference_id}` - Full results are retained in a short-lived in-memory store --- ## Security Model ### Permitted Operations - **READ**: SELECT, WITH (via `run_select_psql`) and analysis via `get_query_stats` - **WRITE**: INSERT/UPDATE/DELETE/CREATE/ALTER/DROP (via dedicated tools) ### High-Risk Operations - DELETE and DROP are treated as high-risk/critical by governance - Require special permission via `governance_password` - `DROP DATABASE` is blocked by default (`GOV_ALLOW_DROP_DATABASE=false`) **Security Features**: Prepared statements, parameterized queries, connection isolation, automatic rollback, input sanitization, read-only transaction mode support --- ## Installation ### Requirements - Python 3.8+ - PostgreSQL 12+ (including AWS RDS, Google Cloud SQL, Azure Database) - 512MB RAM recommended ### Quick Start ```bash # Clone repository git clone https://github.com/your-org/postgres-mcp.git cd postgres-mcp # Create virtual environment python -m venv .venv source .venv/bin/activate # Windows: .venv\Scripts\activate # Install dependencies pip install -r requirements.txt # Configure environment cp .env.example .env # Edit .env with your credentials # Launch server uvicorn server:app --host 127.0.0.1 --port 3333 --reload ``` --- ## Configuration Create `.env` file: ```env # Database Connection POSTGRES_HOST=localhost POSTGRES_PORT=5432 POSTGRES_USER=your_database_user POSTGRES_PASSWORD=your_secure_password POSTGRES_DB=your_database_name # Optional: SSL Configuration POSTGRES_SSLMODE=require POSTGRES_SSLROOTCERT=/path/to/ca.pem # Optional: Connection Pooling POSTGRES_POOL_SIZE=10 POSTGRES_POOL_MAX_OVERFLOW=5 POSTGRES_CONNECT_TIMEOUT=10 # Optional: Query Limits MAX_ROWS=1000 QUERY_TIMEOUT=30 # Write Access Control (default: read-only) # Set to true only when you intentionally want INSERT/UPDATE/ALTER/CREATE enabled. ALLOW_DB_WRITES=false # Governance Layer GOV_DEFAULT_LIMIT=100 GOV_MAX_ESTIMATED_ROWS=100000 GOV_MAX_ESTIMATED_COST=200000 GOV_REQUIRE_APPROVAL_FOR_WRITES=false # QRS (ML Query Risk Score) GOV_APPROVAL_PASSWORD=change_me GOV_REQUIRE_PASSWORD_FOR_HIGH_RISK=true GOV_HIGH_RISK_THRESHOLD=70 GOV_CRITICAL_RISK_THRESHOLD=85 GOV_ALLOW_CRITICAL_WITH_PASSWORD=true GOV_ALLOW_BOOLEAN_OVERRIDE_WITHOUT_PASSWORD=false GOV_ALLOW_DROP_DATABASE=false # Data Exposure Layer EXPOSURE_AI_MAX_ROWS=100 EXPOSURE_STORE_TTL_SECONDS=900 EXPOSURE_STORE_MAX_ENTRIES=500 # Response Rendering # markdown (recommended for Copilot Chat) | html (experimental) RESPONSE_RENDER_MODE=markdown ``` ### SSL/TLS Configuration PostgreSQL supports multiple SSL modes: ```env # SSL Mode Options POSTGRES_SSLMODE=disable # No SSL (not recommended) POSTGRES_SSLMODE=allow # Try SSL, fallback to non-SSL POSTGRES_SSLMODE=prefer # Prefer SSL (default) POSTGRES_SSLMODE=require # Require SSL POSTGRES_SSLMODE=verify-ca # Require SSL + verify CA POSTGRES_SSLMODE=verify-full # Require SSL + verify CA + hostname ``` --- ## VS Code Copilot Integration ### Prerequisites - VS Code 1.85+ - GitHub Copilot extension with active subscription - PostgreSQL MCP Server running locally ### Setup via Extensions View (Recommended) 1. **Open Extensions View** - Press `Ctrl+Shift+X` (Windows/Linux) or `Cmd+Shift+X` (macOS) - Or use Command Palette: `Extensions: Show Extensions` 2. **Search for MCP Servers** - Type `@mcp` in the search field - Browse available MCP servers from the GitHub MCP Registry 3. **Install Custom MCP Server** - If your server isn't in the registry, click the settings icon (⚙️) - Select `Add MCP Server...` from the dropdown menu - Choose **stdio** as the server type 4. **Configure Server** ``` Name: PostgreSQL Database Server Command: /path/to/postgres-mcp/.venv/bin/python Arguments: ["/path/to/postgres-mcp/server.py"] ``` 5. **Choose Installation Scope** - **User Settings**: Available across all workspaces - **Workspace Settings**: Only for current project (saves to `.vscode/mcp.json`) 6. **Trust the Server** - VS Code will prompt you to trust the server configuration - Review the server details and click **Trust** to proceed 7. **Verify Connection** - Open Copilot Chat (`Ctrl+Alt+I` or `Cmd+Option+I`) - Switch to **Agent Mode** using the mode selector - Click the **Tools** icon (🛠️) to view available MCP tools - You should see PostgreSQL server tools listed ### Setup via Manual Configuration Add to `.vscode/mcp.json` in your workspace: ```json { "mcpServers": { "postgresql-database": { "type": "stdio", "command": "/absolute/path/to/.venv/bin/python", "args": ["/absolute/path/to/postgres-mcp/server.py"], "env": { "POSTGRES_HOST": "localhost", "POSTGRES_PORT": "5432", "POSTGRES_USER": "your_user", "POSTGRES_PASSWORD": "your_password", "POSTGRES_DB": "your_database" } } } } ``` **Note**: Use absolute paths for both command and args. Environment variables in `mcp.json` override `.env` file. ### Using the MCP Server 1. **Enable Agent Mode** - Open Copilot Chat - Select **Agent** from the mode dropdown - Agent mode allows Copilot to use external MCP tools 2. **View Available Tools** - Click the **Tools** icon in the chat interface - Toggle individual tools on/off as needed - Available tools: `list_databases`, `get_schema`, `get_relationships`, `assess_query_risk`, `run_select_psql`, `run_insert_psql`, `run_insert_cte_psql`, `run_update_psql`, `run_delete_psql`, `run_create_psql`, `run_alter_psql`, `run_drop_psql`, `get_query_stats` 3. **Use in Prompts** - Type `#` in chat to see available tools - Select a specific tool or let Copilot choose automatically - Examples: - "Show me the database schema for the users table" - "Assess this query risk before execution: DELETE FROM users WHERE id = 5" - "Find all orders from last month with their customer names" - "Analyze the performance of this query: SELECT * FROM large_table" ### QRS-First Execution Pattern 1. Assess query risk before execution: - Use `assess_query_risk` with a SQL query. - Review risk label, QRS score, confidence, and approval requirements. 2. Execute only after approval: - For high-risk queries, pass `governance_password`. - Alternative override: `governance_approved=true`. Example parameters for high-risk execution: ```json { "query": "DELETE FROM users WHERE id = 10", "database": "app_db", "governance_password": "change_me" } ``` 4. **Manage Tool Permissions** - First-time tool use requires explicit permission - Choose between: - **Continue**: Run once for this request - **Current session**: Auto-approve for this chat session - **Always allow**: Auto-approve for all sessions (use with caution) ### Advanced Configuration **Multi-Database Setup** (Connect to multiple PostgreSQL instances): ```json { "mcpServers": { "postgres-production": { "type": "stdio", "command": "/path/to/.venv/bin/python", "args": ["/path/to/postgres-mcp/server.py"], "env": { "POSTGRES_HOST": "prod.example.com", "POSTGRES_DB": "production_db" } }, "postgres-staging": { "type": "stdio", "command": "/path/to/.venv/bin/python", "args": ["/path/to/postgres-mcp/server.py"], "env": { "POSTGRES_HOST": "staging.example.com", "POSTGRES_DB": "staging_db" } } } } ``` **Toolsets** (Group related tools): ```json { "toolsets": { "database-read": { "tools": [ "postgresql-database.get_schema", "postgresql-database.run_select_psql", "postgresql-database.get_query_stats" ] }, "database-write": { "tools": [ "postgresql-database.run_insert_psql", "postgresql-database.run_insert_cte_psql", "postgresql-database.run_update_psql", "postgresql-database.run_create_psql", "postgresql-database.run_alter_psql" ] } } } ``` Use canonical `_psql` tool names only. **Copilot Instructions** (`.github/copilot-instructions.md`): ```markdown When querying the PostgreSQL database: - Always check the schema first using get_schema - Use run_select_psql for SELECT/WITH queries - Use run_insert_psql for INSERT queries - Use run_insert_cte_psql for WITH ... INSERT ... queries - Use run_update_psql for UPDATE queries - Use run_create_psql for CREATE queries - Use run_alter_psql for ALTER queries - Leverage PostgreSQL-specific features (CTEs, window functions, JSONB) - Use EXPLAIN ANALYZE for query optimization - Respect multi-schema architecture (public, auth, analytics, etc.) ``` ### Troubleshooting **Server not appearing in tools list:** - Verify server is running: `http://127.0.0.1:3333` - Check `mcp.json` configuration for syntax errors - Restart VS Code after configuration changes - Check Output panel: `View > Output > MCP Servers` **Connection errors:** - Verify PostgreSQL is running: `pg_isready -h localhost -p 5432` - Test connection: `psql -h localhost -U your_user -d your_database` - Check firewall rules and network connectivity - Verify `.env` file has correct credentials **SSL/TLS errors:** - Ensure POSTGRES_SSLMODE is set correctly - Verify SSL certificates are valid and accessible - Check PostgreSQL server SSL configuration - Use `POSTGRES_SSLMODE=disable` for local development (not recommended for production) **Permission denied errors:** - Ensure Python virtual environment is activated - Verify file paths are absolute, not relative - Check database user has required permissions - Review PostgreSQL `pg_hba.conf` authentication settings **Server fails to start:** - Review Output panel: `View > Output > MCP Servers` - Check Python path: `which python` (macOS/Linux) or `where python` (Windows) - Verify all dependencies installed: `pip list` - Check for port conflicts: `lsof -i :3333` (macOS/Linux) --- ## Available Tools | Tool | Description | Operations | |------|-------------|-----------| | `get_schema` | Retrieve complete database schema | Tables, columns, indexes, constraints, views, functions | | `get_relationships` | Map foreign key relationships | Visual relationship graph with cardinality | | `run_select_psql` | Execute read queries | SELECT, WITH | | `run_insert_psql` | Execute INSERT queries | INSERT | | `run_insert_cte_psql` | Execute CTE INSERT queries | WITH ... INSERT ... | | `run_update_psql` | Execute UPDATE queries | UPDATE | | `run_create_psql` | Execute CREATE queries | CREATE | | `run_alter_psql` | Execute ALTER queries | ALTER | | `run_create_policy_psql` | Create row-level security policies | CREATE POLICY | | `run_alter_table_rls_psql` | Enable/disable/force RLS on tables | ALTER TABLE ... ROW LEVEL SECURITY | | `verify_rls_policies` | Verify RLS flags and policies for a table | RLS inspection | | `verify_cls_permissions` | Verify column-level privileges (CLS) | Column privilege inspection | | `get_query_stats` | Query performance analysis | EXPLAIN plan and cost estimation | --- ## Usage Examples ### Schema Exploration ```sql -- Get complete schema "Show me all tables and their relationships" -- Specific schema details "Describe the users table with all constraints" -- Multi-schema support "List all tables in the analytics schema" ``` ### Data Querying ```sql -- Basic queries "Find all active users created in the last 30 days" -- Complex joins "Show customers with their order totals and most recent purchase date" -- Window functions "Rank products by sales within each category" -- CTEs (Common Table Expressions) "Use a CTE to calculate running totals of daily revenue" ``` ### Performance Analysis ```sql -- Query planning "Analyze the execution plan for this query: SELECT * FROM large_table WHERE status = 'active'" -- Index suggestions "What indexes would improve performance for queries filtering by created_at?" ``` ### Data Manipulation ```sql -- Insert data "Add a new user: email='john@example.com', name='John Doe', role='user'" -- Batch insert "Insert multiple products from this JSON array" -- Update records "Update all orders from last month to set status='processed'" ``` ### Schema Management ```sql -- Create table "Create a products table with id (UUID), name (VARCHAR), price (DECIMAL), created_at (TIMESTAMP)" -- Add constraints "Add a foreign key constraint from orders.user_id to users.id" -- Create index "Create a B-tree index on orders(created_at) for date range queries" -- Create view "Create a view showing customer order summaries" ``` ### Security Policy Management (RLS / CLS) ```sql -- Build an RLS policy "Create a policy allowing users to read only their own rows" -- Enable RLS at table level "Enable row level security on orders table" -- Verify RLS posture "Verify RLS is enabled and list all policies for orders table" -- Verify CLS posture "Show column-level privileges on users table for role app_reader" ``` ### PostgreSQL-Specific Features ```sql -- JSONB queries "Find all users where preferences->>'theme' = 'dark'" -- Array operations "Select products where 'electronics' = ANY(categories)" -- Full-text search "Find articles matching the search term 'postgresql' using tsvector" -- Recursive CTEs "Show organizational hierarchy using a recursive query on employees table" ``` --- ## PostgreSQL vs MySQL Differences | Feature | PostgreSQL | MySQL | |---------|-----------|-------| | **ACID Compliance** | Full ACID compliance | InnoDB engine only | | **Data Types** | Rich (JSONB, Arrays, UUID, Ranges) | Basic types | | **Window Functions** | Full support | Limited (MySQL 8.0+) | | **CTEs** | Recursive and non-recursive | MySQL 8.0+ only | | **Full-Text Search** | Native with tsvector | Basic FULLTEXT indexes | | **Concurrent Writes** | MVCC (better concurrency) | Row-level locking | | **Extensions** | Rich ecosystem (PostGIS, pg_stat_statements) | Limited plugins | | **Case Sensitivity** | Identifiers lowercased | Configurable | --- ## License MIT License - Copyright (c) 2025 See LICENSE file for full terms. --- ## Acknowledgments Built with [Anthropic's Model Context Protocol](https://modelcontextprotocol.io), FastAPI, and psycopg3. --- <p align="center"> <strong>Secure • Efficient • AI-Powered Database Access</strong> </p>