DEV Community

Zarrar Shaikh
Zarrar Shaikh

Posted on

PostgreSQL MCP Server with Built-in SSH Tunneling

#postgres #database #ssh #mcp

GitHub logo Zlash65 / postgresql-ssh-mcp

PostgreSQL MCP server with SSH tunneling for Claude Desktop and ChatGPT

PostgreSQL SSH MCP Server

npm version npm downloads license

A secure PostgreSQL MCP server with built-in SSH tunneling. Connect to databases through bastion hosts automatically — no manual ssh -L required.

Features

  • Dual Transport — STDIO for Claude Desktop, Streamable HTTP for ChatGPT
  • SSH Tunneling — Built-in tunnel with auto-reconnect and TOFU (trust on first use)
  • Read-Only by Default — Safe for production; enable writes explicitly
  • OAuth Support — Auth0 integration for secure ChatGPT connections
  • Connection Pooling — Efficient resource management with configurable limits

Architecture

Architecture

Quick Start

Claude Desktop (STDIO)

Add to your Claude Desktop config:

Platform Config Location
macOS ~/Library/Application Support/Claude/claude_desktop_config.json
Windows %APPDATA%/Claude/claude_desktop_config.json 
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@zlash65/postgresql-ssh-mcp"],
      "env": {
        "DATABASE_URI": "postgresql://user:password@localhost:5432/mydb"
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

ChatGPT (Streamable HTTP)

DATABASE_URI="postgresql://user:pass@localhost:5432/mydb" npx @zlash65/postgresql-ssh-mcp-http
Enter fullscreen mode Exit fullscreen mode

Then configure ChatGPT to connect to…

View on GitHub

Table of Contents

Why SSH Tunneling Matters

Production databases live in private networks (VPCs, private subnets) isolated from the public internet. You can't simply provide a DATABASE_URL to an AI and expect it to connect.

Companies use bastion hosts as secure gateways to private infrastructure—servers that authenticate via SSH and act as the only entry point to internal resources.

Without SSH Tunneling:
   AI → Database URL → Connection Refused (private network)

With SSH Tunneling (this server):
   AI → MCP Server → SSH Tunnel → Bastion → Database
Enter fullscreen mode Exit fullscreen mode

Traditional approach (manual):

# Terminal 1: SSH tunnel
ssh -L 5432:db.internal:5432 user@bastion.company.com

# Terminal 2: MCP server
DATABASE_URI=postgresql://localhost:5432/db npx @zlash65/postgresql-ssh-mcp
Enter fullscreen mode Exit fullscreen mode

This server (automatic):

{
  "env": {
    "DATABASE_URI": "postgresql://db.internal:5432/db",
    "SSH_ENABLED": "true",
    "SSH_HOST": "bastion.company.com",
    "SSH_PRIVATE_KEY_PATH": "~/.ssh/id_rsa"
  }
}
Enter fullscreen mode Exit fullscreen mode

The server handles tunnel establishment, database traffic forwarding, auto-reconnection on failure, and clean shutdown automatically.

Without built-in SSH tunneling, you're stuck with bad options: VPN for everyone (overhead), exposed databases (security risk), or manual SSH tunneling (requires terminal skills). Product managers, operations teams, and analysts who need database insights shouldn't have to learn SSH commands or manage private keys. Configure it once, and it just works.

Key Features:

  • 🔐 Automatic SSH tunneling with trust-on-first-use and auto-reconnection
  • 🛡️ Read-only by default with smart SQL validation (60+ blocked patterns)
  • 🔄 Dual transport — STDIO for local, HTTP + OAuth for remote
  • 📊 12 database tools — query, schema discovery, monitoring
  • ⚡ Connection pooling with cursor-based result limiting

Try it now: npx @zlash65/postgresql-ssh-mcp

Architecture

PostgreSQL SSH MCP Architecture

Local:  Claude Desktop → STDIO → MCP Server → [SSH Tunnel] → PostgreSQL
Remote: Claude/ChatGPT → HTTPS → MCP HTTP Server → [SSH Tunnel] → PostgreSQL
Enter fullscreen mode Exit fullscreen mode

Deployment Options

Method Client Transport Use Case
1 Claude Desktop STDIO Local development, direct DB access
2 Claude Desktop HTTP + OAuth Remote DB via Connectors
3 ChatGPT HTTP + OAuth Remote DB via Developer Mode

Method 1: Claude Desktop (Local STDIO)

Best for: Local development, databases accessible from your machine

Step 1: Find Config File

Platform Location
macOS ~/Library/Application Support/Claude/claude_desktop_config.json
Windows %APPDATA%/Claude/claude_desktop_config.json

In Claude Desktop: Settings > Developer > Edit Config

Step 2: Add MCP Server

Basic (local database):

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@zlash65/postgresql-ssh-mcp"],
      "env": {
        "DATABASE_URI": "postgresql://user:password@localhost:5432/mydb"
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

With SSH Tunnel (production database) 
{
  "mcpServers": {
    "postgres-prod": {
      "command": "npx",
      "args": ["-y", "@zlash65/postgresql-ssh-mcp"],
      "env": {
        "DATABASE_URI": "postgresql://dbuser:dbpass@db.internal:5432/mydb",
        "SSH_ENABLED": "true",
        "SSH_HOST": "bastion.example.com",
        "SSH_USER": "ec2-user",
        "SSH_PRIVATE_KEY_PATH": "/Users/you/.ssh/id_rsa"
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Optional: SSH_PRIVATE_KEY_PASSPHRASE, SSH_MAX_RECONNECT_ATTEMPTS

Enable Write Mode 
{
  "env": {
    "DATABASE_URI": "postgresql://user:password@localhost:5432/mydb",
    "READ_ONLY": "false"
  }
}
Enter fullscreen mode Exit fullscreen mode

⚠️ Only enable for non-production databases.

Step 3: Restart Claude Desktop

Done! Ask Claude: "What tables are in my database?"

Remote HTTP Server Deployment

Best for: Shared team access, production databases, ChatGPT integration

This section covers deploying the MCP HTTP server with OAuth on a Linux server. Both Claude Desktop (via Connectors) and ChatGPT connect to the same server.

Prerequisites

  • Linux server (Ubuntu 22.04/24.04 recommended)
  • Domain name with DNS access
  • Firewall allowing ports 22, 80, 443
  • PostgreSQL database

OAuth Flow

Step 1: Configure DNS

Create an A record pointing your domain to your server's public IP:

Type Name Value
A your-subdomain Server IP

Verify: nslookup your-subdomain.example.com

Step 2: Install Dependencies 

# Connect to server
ssh -i your-key.pem ubuntu@your-server-ip

# Update system
sudo apt update && sudo apt upgrade -y

# Install Node.js via nvm
curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.40.3/install.sh | bash
source ~/.bashrc
nvm install 22

# Install nginx and Certbot
sudo apt install -y nginx certbot python3-certbot-nginx
Enter fullscreen mode Exit fullscreen mode

Step 3: Configure nginx 

sudo vim /etc/nginx/sites-available/mcp
Enter fullscreen mode Exit fullscreen mode 
server {
    listen 80;
    server_name your-subdomain.example.com;

    location / {
        proxy_pass http://127.0.0.1:3000;
        proxy_http_version 1.1;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        proxy_read_timeout 86400;
    }
}
Enter fullscreen mode Exit fullscreen mode

Enable and reload:

sudo ln -s /etc/nginx/sites-available/mcp /etc/nginx/sites-enabled/
sudo nginx -t && sudo systemctl reload nginx
Enter fullscreen mode Exit fullscreen mode

Step 4: Obtain SSL Certificate 

sudo certbot --nginx -d your-subdomain.example.com

# Verify auto-renewal
sudo certbot renew --dry-run
Enter fullscreen mode Exit fullscreen mode

Step 5: Install MCP Server 

sudo mkdir -p /opt/mcp-server
sudo chown ubuntu:ubuntu /opt/mcp-server
cd /opt/mcp-server

git clone https://github.com/zlash65/postgresql-ssh-mcp.git
cd postgresql-ssh-mcp
npm install && npm run build
Enter fullscreen mode Exit fullscreen mode

Step 6: Configure Auth0

6.1: Create Auth0 Tenant
  1. Sign up at Auth0
  2. Set tenant domain (e.g., postgresql-ssh-mcp)
  3. Select region

Your AUTH0_DOMAIN: {tenant-domain}.{region}.auth0.com

Auth0 Tenant

6.2: Create API
  1. Applications > APIs > + Create API
  2. Name: PostgreSQL SSH MCP
  3. Identifier: https://your-subdomain.example.com/mcp (becomes AUTH0_AUDIENCE)
  4. Signing Algorithm: RS256

Create API

6.3: Set Default Audience
  1. Settings > General > API Authorization Settings
  2. Set Default Audience to your API Identifier
  3. Save

Default Audience

6.4: Enable DCR
  1. Settings > Advanced
  2. Enable Dynamic Client Registration (DCR)

DCR

6.5: Configure Database Connection
  1. Authentication > Database > Username-Password-Authentication
  2. Enable: Disable Sign Ups and Promote Connection to Domain Level

Database

6.6: Create User
  1. User Management > Users > + Create User
  2. Connection: Username-Password-Authentication
  3. Email and password

Remember these credentials for authentication.

Create User

Step 7: Configure Environment 

vim /opt/mcp-server/postgresql-ssh-mcp/.env
Enter fullscreen mode Exit fullscreen mode 
# Database
DATABASE_URI=postgresql://user:password@your-db-host:5432/your-database
DATABASE_SSL=false
READ_ONLY=true

# SSH Tunnel (optional)
# SSH_ENABLED=true
# SSH_HOST=bastion.example.com
# SSH_USER=ubuntu
# SSH_PRIVATE_KEY_PATH=/home/ubuntu/.ssh/id_rsa

# HTTP Server
MCP_HOST=0.0.0.0

# Auth0
MCP_AUTH_MODE=oauth
AUTH0_DOMAIN=your-tenant.us.auth0.com
AUTH0_AUDIENCE=https://your-subdomain.example.com/mcp
Enter fullscreen mode Exit fullscreen mode

Step 8: Create systemd Service 

sudo vim /etc/systemd/system/postgresql-ssh-mcp.service
Enter fullscreen mode Exit fullscreen mode 
[Unit]
Description=PostgreSQL SSH MCP Server
After=network.target

[Service]
Type=simple
User=ubuntu
WorkingDirectory=/opt/mcp-server/postgresql-ssh-mcp
EnvironmentFile=/opt/mcp-server/postgresql-ssh-mcp/.env
Environment=PATH=/home/ubuntu/.nvm/versions/node/v22.21.1/bin:/usr/bin:/bin
ExecStart=/home/ubuntu/.nvm/versions/node/v22.21.1/bin/node /opt/mcp-server/postgresql-ssh-mcp/dist/http.js
Restart=on-failure
RestartSec=10

[Install]
WantedBy=multi-user.target
Enter fullscreen mode Exit fullscreen mode

Check your Node.js path with which node and update accordingly.

sudo systemctl daemon-reload
sudo systemctl enable postgresql-ssh-mcp
sudo systemctl start postgresql-ssh-mcp
Enter fullscreen mode Exit fullscreen mode

Step 9: Verify Deployment 

# Check status
sudo systemctl status postgresql-ssh-mcp

# View logs
sudo journalctl -u postgresql-ssh-mcp -f

# Test health endpoint
curl https://your-subdomain.example.com/health
Enter fullscreen mode Exit fullscreen mode

Expected response:

{"status": "ok", "timestamp": "...", "version": "1.x.x"}
Enter fullscreen mode Exit fullscreen mode

Useful Commands
Command Description
sudo systemctl restart postgresql-ssh-mcp Restart service
sudo journalctl -u postgresql-ssh-mcp -f Live logs
sudo nginx -t && sudo systemctl reload nginx Reload nginx
sudo certbot renew Renew SSL

Method 2: Connect Claude Desktop (via Connectors)

  1. Open Claude Desktop Settings > Connectors
  2. Click on the Add custom connector button

Claude Add custom connector

  1. Click Add
  2. Authenticate with Auth0 (credentials from Step 6.6)

Auth0 Login

Method 3: Connect ChatGPT

  1. Profile > Settings > Apps > Enable Developer Mode
  2. Create App:
    • Name: PostgreSQL SSH MCP
    • MCP Server URL: https://your-subdomain.example.com
    • Authentication: OAuth (leave Client ID/Secret empty)

ChatGPT Create App

  1. Authenticate with Auth0 (credentials from Step 6.6)

Auth0 Login

  1. In chat: + > More > Select your MCP server

Available Tools

Query

Tool Description
execute_query SQL with parameterized queries (results capped by MAX_ROWS)
explain_query EXPLAIN plans in text/JSON/YAML/XML

Schema

Tool Description
list_schemas Database schemas (excludes system)
list_tables Tables with row counts and sizes
describe_table Columns, constraints, indexes
list_databases All databases with sizes

Monitoring

Tool Description
get_connection_status Pool stats, tunnel state
list_active_connections Active connections
list_long_running_queries Slow queries
get_database_version PostgreSQL version
get_database_size Size breakdown
get_table_stats Vacuum/analyze stats

Security

Read-only by default — The server blocks INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER, CREATE, GRANT, REVOKE, even SELECT INTO and PREPARE/EXECUTE.

SSH Host Key Verification — Trust-on-First-Use (TOFU) saves unknown keys on first connection and verifies subsequent connections. Disable with SSH_TRUST_ON_FIRST_USE=false.

Connection string obfuscation — Passwords are redacted in logs: postgresql://user:***@host:5432/mydb

Query limits — MAX_ROWS=1000, QUERY_TIMEOUT=30000, MAX_CONCURRENT_QUERIES=10

CORS/Origin validation (HTTP mode):

MCP_ALLOWED_ORIGINS="https://chatgpt.com,https://chat.openai.com"
MCP_ALLOWED_HOSTS="your-subdomain.example.com"
Enter fullscreen mode Exit fullscreen mode

Environment Variables Reference 
# Database
DATABASE_URI="postgresql://user:pass@host:5432/db"
DATABASE_SSL="true"
DATABASE_SSL_CA="/path/to/ca.pem"

# SSH Tunnel
SSH_ENABLED="true"
SSH_HOST="bastion.example.com"
SSH_USER="ec2-user"
SSH_PRIVATE_KEY_PATH="/path/to/key"
SSH_TRUST_ON_FIRST_USE="true"
SSH_MAX_RECONNECT_ATTEMPTS="5"

# Query
READ_ONLY="true"
MAX_ROWS="1000"
QUERY_TIMEOUT="30000"

# HTTP Server
PORT="3000"
MCP_AUTH_MODE="oauth"
AUTH0_DOMAIN="tenant.us.auth0.com"
AUTH0_AUDIENCE="https://your-domain.com"
Enter fullscreen mode Exit fullscreen mode

Troubleshooting

Claude Desktop: Server Not Starting

Check logs:

  • macOS: ~/Library/Logs/Claude/mcp*.log
  • Windows: %APPDATA%/Claude/logs/mcp*.log

Common issues: Invalid DATABASE_URI, SSH key permissions (should be 600), PostgreSQL not accessible.

ChatGPT: "Unable to connect" 
# Verify server
curl https://your-subdomain.example.com/health

# Check OAuth metadata
curl https://your-subdomain.example.com/.well-known/oauth-protected-resource
Enter fullscreen mode Exit fullscreen mode

Verify Auth0: Default Audience set, DCR enabled, database connection promoted.

401 Unauthorized
  • AUTH0_AUDIENCE must match API Identifier exactly
  • Check Default Audience in Auth0 tenant settings
  • Tokens may have expired

Resources

Star on GitHub

Top comments (0)