Instead of Opening Up PostgreSQL to Remote Connections, Do This

8 days ago

Use your SSH credentials to create a secure tunnel to your database

TL;DR, here is a shell script. Enjoy!

#!/bin/bash

# PostgreSQL SSH Tunnel Manager
# Usage: ./tunnel.sh [start|stop|status|restart]

# Configuration
SERVER_USER="posgres"
SERVER_HOST="server"
LOCAL_PORT="5432"
REMOTE_HOST="localhost"
REMOTE_PORT="5432"
PID_FILE="/tmp/postgresql-tunnel.pid"

# Colors
GREEN='\033[0;32m'
RED='\033[0;31m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# Function to check if tunnel is running
is_running() {
    if [ -f "$PID_FILE" ]; then
        PID=$(cat "$PID_FILE")
        if ps -p "$PID" > /dev/null 2>&1; then
            return 0
        else
            # PID file exists but process is dead
            rm -f "$PID_FILE"
            return 1
        fi
    fi
    return 1
}

# Function to find tunnel process
find_tunnel() {
    ps aux | grep -E "ssh.*-L.*${LOCAL_PORT}:|autossh.*-L.*${LOCAL_PORT}:" | grep -v grep | awk '{print $2}' | head -1
}

# Function to start tunnel
start_tunnel() {
    if is_running; then
        PID=$(cat "$PID_FILE")
        echo -e "${YELLOW}Tunnel is already running (PID: $PID)${NC}"
        return 1
    fi
    
    # Check if port is already in use
    if lsof -Pi :${LOCAL_PORT} -sTCP:LISTEN -t >/dev/null 2>&1; then
        echo -e "${RED}Port ${LOCAL_PORT} is already in use${NC}"
        echo "Run './tunnel.sh stop' to kill existing tunnel, or use a different port"
        return 1
    fi
    
    echo -e "${GREEN}Starting PostgreSQL SSH tunnel...${NC}"
    echo "Local port: ${LOCAL_PORT} -> ${SERVER_USER}@${SERVER_HOST}:${REMOTE_PORT}"
    echo ""
    
    # Start tunnel in background and save PID
    ssh -N -f \
        -o ExitOnForwardFailure=yes \
        -o ServerAliveInterval=30 \
        -o ServerAliveCountMax=6 \
        -o TCPKeepAlive=yes \
        -L ${LOCAL_PORT}:${REMOTE_HOST}:${REMOTE_PORT} \
        ${SERVER_USER}@${SERVER_HOST} \
        > /dev/null 2>&1
    
    # Wait a moment for tunnel to establish
    sleep 1
    
    # Find the process and save PID
    TUNNEL_PID=$(find_tunnel)
    if [ -n "$TUNNEL_PID" ]; then
        echo "$TUNNEL_PID" > "$PID_FILE"
        echo -e "${GREEN}✓ Tunnel started successfully (PID: $TUNNEL_PID)${NC}"
        echo ""
        echo "You can now connect to PostgreSQL on localhost:${LOCAL_PORT}"
        echo "Update your .env file:"
        echo "  DB_HOST=127.0.0.1"
        echo "  DB_PORT=${LOCAL_PORT}"
        return 0
    else
        echo -e "${RED}✗ Failed to start tunnel${NC}"
        echo "Check SSH connection: ssh ${SERVER_USER}@${SERVER_HOST}"
        return 1
    fi
}

# Function to stop tunnel
stop_tunnel() {
    if [ -f "$PID_FILE" ]; then
        PID=$(cat "$PID_FILE")
        if ps -p "$PID" > /dev/null 2>&1; then
            echo -e "${YELLOW}Stopping tunnel (PID: $PID)...${NC}"
            kill "$PID" 2>/dev/null
            sleep 1
            
            # Force kill if still running
            if ps -p "$PID" > /dev/null 2>&1; then
                kill -9 "$PID" 2>/dev/null
            fi
        fi
        rm -f "$PID_FILE"
    fi
    
    # Also kill any other tunnel processes on this port
    OTHER_PIDS=$(find_tunnel)
    if [ -n "$OTHER_PIDS" ]; then
        echo -e "${YELLOW}Killing other tunnel processes...${NC}"
        echo "$OTHER_PIDS" | xargs kill 2>/dev/null
        sleep 1
        echo "$OTHER_PIDS" | xargs kill -9 2>/dev/null
    fi
    
    echo -e "${GREEN}✓ Tunnel stopped${NC}"
}

# Function to show status
show_status() {
    echo "=== PostgreSQL SSH Tunnel Status ==="
    echo ""
    
    if is_running; then
        PID=$(cat "$PID_FILE")
        echo -e "${GREEN}Status: Running${NC}"
        echo "PID: $PID"
        echo "Local port: ${LOCAL_PORT}"
        echo "Remote: ${SERVER_USER}@${SERVER_HOST}:${REMOTE_PORT}"
        echo ""
        
        # Show process details
        ps -p "$PID" -o pid,user,start,time,command 2>/dev/null || echo "Process details not available"
        echo ""
        
        # Test if port is listening
        if lsof -Pi :${LOCAL_PORT} -sTCP:LISTEN -t >/dev/null 2>&1; then
            echo -e "${GREEN}✓ Port ${LOCAL_PORT} is listening${NC}"
        else
            echo -e "${RED}✗ Port ${LOCAL_PORT} is not listening${NC}"
        fi
    else
        echo -e "${RED}Status: Not running${NC}"
        echo ""
        echo "Start tunnel with: ./tunnel.sh start"
    fi
}

# Main script logic
case "${1:-status}" in
    start)
        start_tunnel
        ;;
    stop)
        stop_tunnel
        ;;
    restart)
        stop_tunnel
        sleep 1
        start_tunnel
        ;;
    status)
        show_status
        ;;
    *)
        echo "PostgreSQL SSH Tunnel Manager"
        echo ""
        echo "Usage: $0 [start|stop|status|restart]"
        echo ""
        echo "Commands:"
        echo "  start   - Start the SSH tunnel"
        echo "  stop    - Stop the SSH tunnel"
        echo "  status  - Show tunnel status (default)"
        echo "  restart - Restart the tunnel"
        echo ""
        echo "Configuration:"
        echo "  Server: ${SERVER_USER}@${SERVER_HOST}"
        echo "  Local port: ${LOCAL_PORT}"
        echo "  Remote: ${REMOTE_HOST}:${REMOTE_PORT}"
        exit 1
        ;;
esac

What is all this about? Shouldn't you only work on your local development server or staging? Well, for development, yes but you might want to:

Connect to production database for debugging or customer support
Run migrations against your remote database
Use database tools (pgAdmin, DBeaver) with remote server for exports, backups, etc.

Option 1: Direct Remote Access

Pros: Simple, direct connection
Cons: Exposes database to the interwebs, requires rock-solid firewall

Option 2: SSH Tunnel (Recommended)

Pros: Secure, no database exposure, encrypted
Cons: Requires SSH access, slightly more setup

How SSH Tunnels Work

Local Machine          SSH Tunnel          Remote Server
     |                      |                    |
  Port 5432  ==========>  SSH  ==========>  localhost:5432
     |                      |                    |
  Laravel App                                  PostgreSQL

Your local app connects to localhost:5432, but SSH forwards behind the scenes to the remote PostgreSQL server.

Tunnel Management via Script

SSH tunnels are great but:

It's hard to see if they're already running, especially when you have multiple db servers
Difficult to stop/start (processes not obvious)
Easy to forget about (left running in background)
No easy way to monitor

Try the bash script above, tunnel.sh