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:

   cp .env.example ~/.pg_mcp/.env
   

2. Edit ~/.pg_mcp/.env with your credentials:

   # 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:

   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

pnpm install
pnpm run build

Usage

Start the MCP server:

./start_mcp.sh

For development mode with hot reload:

./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:

./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