PostgreSQL MCP Server

# Production Configuration Examples This document provides configuration examples for different production environments and use cases. ## 🏢 Enterprise Production Environment ### Configuration File (`config.json`) ```json { "database": { "host": "prod-postgres-cluster.company.com", "port": 5432, "database": "ecommerce_prod", "username": "mcp_readonly_user", "password": "${POSTGRES_PASSWORD}", "ssl_mode": "require", "min_connections": 5, "max_connections": 20 }, "server": { "name": "ecommerce-mcp-server", "version": "1.0.0", "max_query_time": 15, "max_rows": 500, "allowed_schemas": ["public", "analytics", "reporting"], "log_level": "INFO", "log_queries": true } } ``` ### Environment Variables (`.env`) ```bash # Database Configuration - Production POSTGRES_HOST=prod-postgres-cluster.company.com POSTGRES_PORT=5432 POSTGRES_DATABASE=ecommerce_prod POSTGRES_USERNAME=mcp_readonly_user POSTGRES_PASSWORD=SecureP@ssw0rd123! POSTGRES_SSL_MODE=require POSTGRES_MIN_CONNECTIONS=5 POSTGRES_MAX_CONNECTIONS=20 # MCP Server Configuration - Production MCP_NAME=ecommerce-mcp-server MCP_VERSION=1.0.0 MCP_MAX_QUERY_TIME=15 MCP_MAX_ROWS=500 MCP_ALLOWED_SCHEMAS=public,analytics,reporting MCP_LOG_LEVEL=INFO MCP_LOG_QUERIES=true # Additional Security NODE_ENV=production ``` ### Security Recommendations - ✅ **Dedicated read-only user** with minimal permissions - ✅ **SSL required** for all database connections - ✅ **Schema restrictions** to limit data access - ✅ **Lower query limits** for production stability - ✅ **Comprehensive logging** for audit trails --- ## 🧪 Development Environment ### Configuration File (`config.dev.json`) ```json { "database": { "host": "localhost", "port": 5432, "database": "ecommerce_dev", "username": "dev_user", "password": "dev_password", "ssl_mode": "prefer", "min_connections": 1, "max_connections": 5 }, "server": { "name": "dev-mcp-server", "version": "1.0.0-dev", "max_query_time": 60, "max_rows": 1000, "allowed_schemas": null, "log_level": "DEBUG", "log_queries": true } } ``` ### Environment Variables (`.env.dev`) ```bash # Database Configuration - Development POSTGRES_HOST=localhost POSTGRES_PORT=5432 POSTGRES_DATABASE=ecommerce_dev POSTGRES_USERNAME=dev_user POSTGRES_PASSWORD=dev_password POSTGRES_SSL_MODE=prefer POSTGRES_MIN_CONNECTIONS=1 POSTGRES_MAX_CONNECTIONS=5 # MCP Server Configuration - Development MCP_NAME=dev-mcp-server MCP_VERSION=1.0.0-dev MCP_MAX_QUERY_TIME=60 MCP_MAX_ROWS=1000 MCP_ALLOWED_SCHEMAS= MCP_LOG_LEVEL=DEBUG MCP_LOG_QUERIES=true ``` ### Development Features - 🔧 **Higher query limits** for testing complex queries - 🔍 **Debug logging** for detailed troubleshooting - 🌐 **No schema restrictions** for full database exploration - ⚡ **Relaxed timeouts** for development iterations --- ## ☁️ Cloud/Staging Environment ### Environment Variables (`.env.staging`) ```bash # Database Configuration - Staging POSTGRES_HOST=staging-db.amazonaws.com POSTGRES_PORT=5432 POSTGRES_DATABASE=ecommerce_staging POSTGRES_USERNAME=staging_mcp_user POSTGRES_PASSWORD=${AWS_RDS_PASSWORD} POSTGRES_SSL_MODE=require POSTGRES_MIN_CONNECTIONS=2 POSTGRES_MAX_CONNECTIONS=10 # MCP Server Configuration - Staging MCP_NAME=staging-mcp-server MCP_VERSION=1.0.0-staging MCP_MAX_QUERY_TIME=30 MCP_MAX_ROWS=750 MCP_ALLOWED_SCHEMAS=public,test_analytics MCP_LOG_LEVEL=INFO MCP_LOG_QUERIES=true # Cloud-specific Configuration AWS_REGION=us-west-2 AWS_RDS_PASSWORD=${AWS_SECRETS_MANAGER_PASSWORD} ``` ### Cloud Considerations - 🔐 **Secrets management** integration (AWS Secrets Manager, etc.) - 🌍 **Regional deployment** considerations - 📊 **Mid-tier resource limits** between dev and prod - 🔄 **CI/CD integration** for automated testing --- ## 🏪 Multi-tenant SaaS Configuration ### Environment Variables (`.env.saas`) ```bash # Database Configuration - Multi-tenant POSTGRES_HOST=multi-tenant-cluster.company.com POSTGRES_PORT=5432 POSTGRES_DATABASE=saas_platform POSTGRES_USERNAME=tenant_readonly_user POSTGRES_PASSWORD=MultiTen@ntP@ss123! POSTGRES_SSL_MODE=require POSTGRES_MIN_CONNECTIONS=10 POSTGRES_MAX_CONNECTIONS=50 # MCP Server Configuration - Multi-tenant MCP_NAME=saas-mcp-server MCP_VERSION=1.0.0 MCP_MAX_QUERY_TIME=10 MCP_MAX_ROWS=200 MCP_ALLOWED_SCHEMAS=tenant_001,tenant_002,shared_analytics MCP_LOG_LEVEL=WARN MCP_LOG_QUERIES=true # Multi-tenant Security TENANT_ID_REQUIRED=true ROW_LEVEL_SECURITY=enabled ``` ### Multi-tenant Features - 🔒 **Strict schema isolation** per tenant - ⚡ **Lower resource limits** to prevent one tenant affecting others - 📝 **Enhanced logging** for compliance and debugging - 🎯 **Tenant-specific query filtering** --- ## 📊 Analytics/BI Environment ### Configuration File (`config.analytics.json`) ```json { "database": { "host": "analytics-warehouse.company.com", "port": 5432, "database": "data_warehouse", "username": "bi_analyst_user", "password": "${BI_DB_PASSWORD}", "ssl_mode": "require", "min_connections": 3, "max_connections": 15 }, "server": { "name": "analytics-mcp-server", "version": "1.0.0", "max_query_time": 120, "max_rows": 5000, "allowed_schemas": ["dwh_sales", "dwh_marketing", "dwh_finance"], "log_level": "INFO", "log_queries": true } } ``` ### Analytics-Specific Settings - ⏱️ **Higher query timeouts** for complex analytics - 📈 **Larger result sets** for reporting needs - 🎯 **Analytics schema focus** on specific data marts - 🔍 **Detailed query logging** for optimization insights --- ## 🔧 Configuration Management Scripts ### Setup Script (`setup-config.sh`) ```bash #!/bin/bash # PostgreSQL MCP Server Configuration Setup ENV=${1:-development} echo "Setting up MCP server for environment: $ENV" case $ENV in production) cp .env.example .env echo "⚠️ Please update .env with production credentials" echo "📋 Use config.json for production settings" ;; development) cp .env.dev .env echo "✅ Development environment configured" ;; staging) cp .env.staging .env echo "✅ Staging environment configured" echo "🔐 Make sure AWS credentials are configured" ;; *) echo "❌ Unknown environment: $ENV" echo "Available environments: production, development, staging" exit 1 ;; esac echo "🚀 Configuration ready! Run 'python main.py --test' to verify" ``` ### Validation Script (`validate-config.py`) ```python #!/usr/bin/env python3 """ Configuration validation script for PostgreSQL MCP Server """ import os import sys from src.config import load_config def validate_config(): """Validate current configuration""" try: db_config, server_config = load_config() print("✅ Configuration loaded successfully") print(f"📊 Database: {db_config.host}:{db_config.port}/{db_config.database}") print(f"🔧 Server: {server_config.name} v{server_config.version}") print(f"🔒 Max query time: {server_config.max_query_time}s") print(f"📄 Max rows: {server_config.max_rows}") if server_config.allowed_schemas: print(f"🎯 Allowed schemas: {', '.join(server_config.allowed_schemas)}") else: print("🌐 All schemas allowed") return True except Exception as e: print(f"❌ Configuration error: {e}") return False if __name__ == "__main__": if validate_config(): sys.exit(0) else: sys.exit(1) ``` --- ## 🎯 Environment Comparison | Setting | Development | Staging | Production | Analytics | |---------|-------------|---------|------------|-----------| | **Query Timeout** | 60s | 30s | 15s | 120s | | **Max Rows** | 1000 | 750 | 500 | 5000 | | **Schema Restrictions** | None | Limited | Strict | Analytics-only | | **Connection Pool** | 1-5 | 2-10 | 5-20 | 3-15 | | **SSL Mode** | Prefer | Require | Require | Require | | **Log Level** | DEBUG | INFO | INFO | INFO | --- ## 🔐 Security Best Practices ### Database User Setup ```sql -- Create dedicated MCP user CREATE USER mcp_readonly_user WITH PASSWORD 'SecurePassword123!'; -- Grant minimal required permissions GRANT CONNECT ON DATABASE ecommerce_prod TO mcp_readonly_user; GRANT USAGE ON SCHEMA public TO mcp_readonly_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly_user; -- Restrict to specific schemas only GRANT USAGE ON SCHEMA analytics TO mcp_readonly_user; GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO mcp_readonly_user; -- Prevent access to sensitive tables REVOKE SELECT ON sensitive_table FROM mcp_readonly_user; ``` ### Connection Security - ✅ **Always use SSL** in production - ✅ **Rotate passwords** regularly - ✅ **Network restrictions** (VPC, firewall rules) - ✅ **Audit logging** enabled - ✅ **Read-only permissions** only --- This configuration guide ensures your PostgreSQL MCP Server is properly secured and optimized for each environment! 🚀