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

Usage

Start the MCP server:

For development mode with hot reload:

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:

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