Using psql with a SSH tunnel

psql-ssh.sh

#!/bin/bash

set -e
set -u
set -o pipefail

if [ $# -eq 0 ]; then
  echo "Usage: $0 <host> [psql args]" 2>&1
  exit 1
fi

export PSQL_SSH_HOSTNAME="$1"
shift

# https://unix.stackexchange.com/questions/55913/whats-the-easiest-way-to-find-an-unused-local-port
# This has a tiny race condition, but fuck it
SSH_TUNNEL_PORT=$(python -c 'import socket; s=socket.socket(); s.bind(("", 0)); print(s.getsockname()[1]); s.close()')
POSTGRES_PORT="5432"

# We would like to change the hostname we see in psql. Here's what WON'T work:
#   - unshare --uts -- zsh -c "sudo hostname $PSQL_SSH_HOSTNAME && hostname && psql -h localhost -p $PORT $*"
#   - LD_PRELOAD
#   - PROMPT1=%`echo ${PSQL_SSH_HOSTNAME:-%M}`
# What works is iptables.

function cleanUp() {
  sudo iptables --wait -t nat -D PREROUTING -p tcp -d $PSQL_SSH_HOSTNAME --dport $POSTGRES_PORT -j DNAT --to-destination 127.0.0.1:$SSH_TUNNEL_PORT || true
  sudo iptables --wait -t nat -D OUTPUT -p tcp -d $PSQL_SSH_HOSTNAME --dport $POSTGRES_PORT -j DNAT --to-destination 127.0.0.1:$SSH_TUNNEL_PORT || true
}
trap cleanUp EXIT

sudo iptables --wait -t nat -A PREROUTING -p tcp -d $PSQL_SSH_HOSTNAME --dport $POSTGRES_PORT -j DNAT --to-destination 127.0.0.1:$SSH_TUNNEL_PORT
sudo iptables --wait -t nat -A OUTPUT -p tcp -d $PSQL_SSH_HOSTNAME --dport $POSTGRES_PORT -j DNAT --to-destination 127.0.0.1:$SSH_TUNNEL_PORT

ssh -f -N -T -L "$SSH_TUNNEL_PORT:localhost:$POSTGRES_PORT" $PSQL_SSH_HOSTNAME

export PGAPPNAME="$USER@$(hostname)/psql-ssh.sh"

psql -h $PSQL_SSH_HOSTNAME "$@"

trap - EXIT
cleanUp