rjocoleman · August 28, 2024 07:39
diff --git a/ssh-tunnel.sh b/ssh-tunnel.sh
 #!/usr/bin/env bash

 # SSH Tunnel Script for Postico
 # ------------------------------
 #
 # Purpose:
 # This script creates an SSH tunnel for Postico to connect to a remote database
 # server. It's designed as a workaround for scenarios where direct SSH tunneling
 # in Postico is not suitable or available.
 #
 # How to Use:
 # 1. In Postico, set up an SSH tunnel connection with all the necessary details.
 # 2. Disable the SSH tunnel feature in Postico, but keep all the SSH settings.
 # 3. Enable Pre-Connect Shell Script and paste this whole script in.
 # 4. On connect the script will create an SSH tunnel and dynamically configure Postico to use it.
 #
 # Why Use This Script:
 # - Allows for more flexible SSH configurations (e.g., using ssh_config).
 # - Enables the use of SSH agents like 1Password's SSH agent for key management.
 # - Provides automatic cleanup of idle ssh connections.
 # - Dynamically assigns local ports for the tunnel to avoid conflicts.
 #
 # Important Notes:
 # - Password authentication to the ssh tunnel is not supported. Use key-based authentication.
 # - The script will dynamically overwrite the host and port in Postico's connection with the ssh tunnel information.
 # - Multiple instances of this script may run simultaneously, creating separate tunnels.
 # - Idle ssh connections are automatically closed after 30 seconds to clean up.
 # - Requires jq, netcat (nc), and ssh to be installed on the system.
 #
 # Limitations:
 # - As this is a workaround, it will spawn multiple SSH connections (however they should be automatically cleaned up).
 # - Relies on Postico's ability to run external scripts for connections.
 #
 # Considerations:
 # - Review and understand the script before using it.

 # Unofficial Bash Strict Mode
 set -euo pipefail
 IFS=$'\n\t'

 # Print errors to stderr
 error() {
    echo "$@" >&2
 }

 # Ensure required commands are available
 for cmd in jq nc ssh; do
    if ! command -v "$cmd" &> /dev/null; then
        error "Error: $cmd is not installed. Please install $cmd and try again."
        exit 1
    fi
 done

 # Ensure POSTICO_CONNECTION_PROPERTIES_FILE is set and exists
 if [[ -z "${POSTICO_CONNECTION_PROPERTIES_FILE:-}" || ! -f "$POSTICO_CONNECTION_PROPERTIES_FILE" ]]; then
    error "Error: POSTICO_CONNECTION_PROPERTIES_FILE is not set or does not exist."
    exit 1
 fi

 # Extract relevant values from the JSON file
 export SSH_ENABLED=$(jq -r '.connection.ssh.enabled' "$POSTICO_CONNECTION_PROPERTIES_FILE")
 export SSH_HOST=$(jq -r '.connection.ssh.host' "$POSTICO_CONNECTION_PROPERTIES_FILE")
 export SSH_USER=$(jq -r '.connection.ssh.user' "$POSTICO_CONNECTION_PROPERTIES_FILE")
 export SSH_PORT=$(jq -r '.connection.ssh.port' "$POSTICO_CONNECTION_PROPERTIES_FILE")

 # Check if SSH tunneling is enabled and do not run
 if [[ "$SSH_ENABLED" == "true" ]]; then
    error "SSH tunneling is enabled. Exiting script."
    exit 0
 fi

 # Function to find an available port
 find_available_port() {
    for port in $(seq 1024 65535); do
        if ! nc -z localhost "$port" >/dev/null 2>&1; then
            echo $port
            return 0
        fi
    done
    error "Error: No available port found."
    exit 1
 }

 # Find an available port
 export LOCAL_PORT=$(find_available_port)

 # Start the SSH tunnel with timeout options
 ssh -f -N -o ExitOnForwardFailure=yes \
    -o ServerAliveInterval=30 \
    -o ServerAliveCountMax=0 \
    -o ConnectTimeout=10 \
    -L $LOCAL_PORT:$PGHOST:$PGPORT $SSH_USER@$SSH_HOST -p $SSH_PORT

 # Output connection details using jq
 jq -n --arg host "localhost" --arg port "$LOCAL_PORT" \
    '{host: $host, port: $port|tonumber}' --raw-output
	#!/usr/bin/env bash

	# SSH Tunnel Script for Postico
	# ------------------------------
	#
	# Purpose:
	# This script creates an SSH tunnel for Postico to connect to a remote database
	# server. It's designed as a workaround for scenarios where direct SSH tunneling
	# in Postico is not suitable or available.
	#
	# How to Use:
	# 1. In Postico, set up an SSH tunnel connection with all the necessary details.
	# 2. Disable the SSH tunnel feature in Postico, but keep all the SSH settings.
	# 3. Enable Pre-Connect Shell Script and paste this whole script in.
	# 4. On connect the script will create an SSH tunnel and dynamically configure Postico to use it.
	#
	# Why Use This Script:
	# - Allows for more flexible SSH configurations (e.g., using ssh_config).
	# - Enables the use of SSH agents like 1Password's SSH agent for key management.
	# - Provides automatic cleanup of idle ssh connections.
	# - Dynamically assigns local ports for the tunnel to avoid conflicts.
	#
	# Important Notes:
	# - Password authentication to the ssh tunnel is not supported. Use key-based authentication.
	# - The script will dynamically overwrite the host and port in Postico's connection with the ssh tunnel information.
	# - Multiple instances of this script may run simultaneously, creating separate tunnels.
	# - Idle ssh connections are automatically closed after 30 seconds to clean up.
	# - Requires jq, netcat (nc), and ssh to be installed on the system.
	#
	# Limitations:
	# - As this is a workaround, it will spawn multiple SSH connections (however they should be automatically cleaned up).
	# - Relies on Postico's ability to run external scripts for connections.
	#
	# Considerations:
	# - Review and understand the script before using it.

	# Unofficial Bash Strict Mode
	set -euo pipefail
	IFS=$'\n\t'

	# Print errors to stderr
	error() {
	echo "$@" >&2
	}

	# Ensure required commands are available
	for cmd in jq nc ssh; do
	if ! command -v "$cmd" &> /dev/null; then
	error "Error: $cmd is not installed. Please install $cmd and try again."
	exit 1
	fi
	done

	# Ensure POSTICO_CONNECTION_PROPERTIES_FILE is set and exists
	if [[ -z "${POSTICO_CONNECTION_PROPERTIES_FILE:-}" \|\| ! -f "$POSTICO_CONNECTION_PROPERTIES_FILE" ]]; then
	error "Error: POSTICO_CONNECTION_PROPERTIES_FILE is not set or does not exist."
	exit 1
	fi

	# Extract relevant values from the JSON file
	export SSH_ENABLED=$(jq -r '.connection.ssh.enabled' "$POSTICO_CONNECTION_PROPERTIES_FILE")
	export SSH_HOST=$(jq -r '.connection.ssh.host' "$POSTICO_CONNECTION_PROPERTIES_FILE")
	export SSH_USER=$(jq -r '.connection.ssh.user' "$POSTICO_CONNECTION_PROPERTIES_FILE")
	export SSH_PORT=$(jq -r '.connection.ssh.port' "$POSTICO_CONNECTION_PROPERTIES_FILE")

	# Check if SSH tunneling is enabled and do not run
	if [[ "$SSH_ENABLED" == "true" ]]; then
	error "SSH tunneling is enabled. Exiting script."
	exit 0
	fi

	# Function to find an available port
	find_available_port() {
	for port in $(seq 1024 65535); do
	if ! nc -z localhost "$port" >/dev/null 2>&1; then
	echo $port
	return 0
	fi
	done
	error "Error: No available port found."
	exit 1
	}

	# Find an available port
	export LOCAL_PORT=$(find_available_port)

	# Start the SSH tunnel with timeout options
	ssh -f -N -o ExitOnForwardFailure=yes \
	-o ServerAliveInterval=30 \
	-o ServerAliveCountMax=0 \
	-o ConnectTimeout=10 \
	-L $LOCAL_PORT:$PGHOST:$PGPORT $SSH_USER@$SSH_HOST -p $SSH_PORT

	# Output connection details using jq
	jq -n --arg host "localhost" --arg port "$LOCAL_PORT" \
	'{host: $host, port: $port\|tonumber}' --raw-output