PostgreSQL Read-Only MCP Server

# PostgreSQL Read-Only MCP Server A secure MCP (Model Context Protocol) server that provides read-only access to PostgreSQL databases through an SSH tunnel. ## Features - **Secure SSH Tunnel**: Connects to PostgreSQL through encrypted SSH tunnel - **Read-Only Enforcement**: All queries run in read-only transactions - **Connection Pooling**: Efficient database connection management - **Query Timeout**: 15-second timeout for all operations - **Comprehensive Tools**: Query, list tables, describe schemas, analyze data ## Configuration 1. Copy the example configuration file: ```bash cp .env.example ~/.pg_mcp/.env ``` 2. Edit `~/.pg_mcp/.env` with your credentials: ```bash # PostgreSQL Database Configuration POSTGRES_DB=your_database POSTGRES_USER=readonly_user POSTGRES_PASSWORD=your_password # SSH Tunnel Configuration SSH_HOST=your-ec2-instance.amazonaws.com SSH_USER=ec2-user SSH_KEY_PATH=/path/to/ssh/key # RDS Configuration RDS_HOST=your-database.rds.amazonaws.com ``` 3. Create a read-only database user: ```sql CREATE USER readonly_mcp WITH PASSWORD 'secure_password'; GRANT CONNECT ON DATABASE your_database TO readonly_mcp; GRANT USAGE ON SCHEMA public TO readonly_mcp; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_mcp; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_mcp; ``` ## Installation ```bash pnpm install pnpm run build ``` ## Usage Start the MCP server: ```bash ./start_mcp.sh ``` For development mode with hot reload: ```bash ./start_mcp.sh dev ``` ## Available MCP Tools - `postgres_query`: Execute read-only SQL queries - `postgres_list_tables`: List all tables with optional row counts - `postgres_describe_table`: Get detailed schema information - `postgres_analyze_table`: Analyze table for data quality issues - `postgres_find_related`: Find foreign key relationships - `postgres_explain_query`: Get query execution plans ## SSH Tunnel Management The server implements a shared SSH tunnel system: - Multiple instances share a single tunnel - Reference counting prevents premature closure - Automatic reconnection on failure - Lock files in `/tmp/pg_mcp_tunnel/` Monitor tunnel status: ```bash ./check_tunnel.sh ``` ## Security - All queries validated for read-only operations - Enforced read-only transactions - 15-second query timeout - SSH encryption for all connections - Credentials stored outside repository ## Requirements - Node.js v24+ - pnpm package manager - PostgreSQL database - SSH access to database server