PostgreSQL Read-Only MCP Server

# CLAUDE.md This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository. ## Project Overview This is a PostgreSQL Read-Only MCP (Model Context Protocol) Server that provides secure access to a production PostgreSQL database through an SSH tunnel. The server implements multiple tools for database querying and analysis while enforcing strict read-only access. ## Architecture The project consists of a Node.js server that: 1. Establishes a shared SSH tunnel to an EC2 instance 2. Connects to a PostgreSQL RDS instance through the tunnel 3. Provides MCP tools for database operations 4. Enforces read-only access and query timeouts Key architectural decisions: - Uses ES modules (`"type": "module"` in package.json) - Implements connection pooling for efficient resource usage - All queries run in READ ONLY transactions - 15-second timeout for all database operations - SSH tunnel is shared between multiple server instances using lock files and reference counting - First instance creates the tunnel, subsequent instances reuse it - Tunnel is closed when the last instance shuts down ## Running the Server Start the MCP server: ```bash ./start_mcp.sh ``` This script: - Sets database credentials as environment variables - Logs debug output to `/tmp/mcp-postgres-debug.log` - Starts the Node.js server with proper stdout/stderr handling for MCP protocol ## Available MCP Tools The server provides these tools: - `postgres_query`: Execute read-only SQL queries - `postgres_list_tables`: List tables with optional row counts - `postgres_describe_table`: Get detailed schema information - `postgres_analyze_table`: Analyze table for data issues - `postgres_find_related`: Find foreign key relationships - `postgres_explain_query`: Get query execution plans ## Security Considerations - All queries are validated to prevent write operations - Database connection uses read-only user credentials - SSH tunnel provides encrypted connection to production database - Query timeouts prevent long-running operations - Credentials are loaded from ~/.pg_mcp/.env file for security - Never commit credentials to the repository ## Development **IMPORTANT: This project uses pnpm as the package manager. Always use pnpm commands instead of npm.** Common commands: ```bash pnpm install # Install dependencies pnpm run build # Build TypeScript to JavaScript pnpm run dev # Run in development mode with hot reload pnpm run typecheck # Check TypeScript types pnpm run lint # Run linter pnpm run format # Format code ``` The project is written in TypeScript and requires building before running in production. No tests are currently configured. ## SSH Tunnel Management The server implements a shared SSH tunnel system: - Lock files are stored in `/tmp/pg_mcp_tunnel/` - Multiple server instances share a single SSH tunnel - Reference counting tracks active instances - The tunnel is automatically closed when the last instance exits Monitoring scripts: ```bash ./check_tunnel.sh # Check tunnel status and active instances ./test_shared_tunnel.sh # Test the shared tunnel functionality ```