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