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

Related MCP server: Simple PostgreSQL MCP Server

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