Every data team has the ritual. Open a terminal. Type ssh -N -L 9030:starrocks:9030 bastion. Switch to another terminal. Run the script. Forget which terminal has the tunnel. The tunnel drops mid-query. You restart it, re-run, lose 10 minutes.
I did this for two years before asking the obvious question: why doesn't the database client just open the tunnel itself?
The failed attempts
The Python ecosystem has libraries for this. sshtunnel wraps paramiko and gives you SSHTunnelForwarder. Clean API. Works great — until it doesn't.
Three things killed it for me:
paramiko 4.0 broke sshtunnel. Paramiko removed DSSKey (DSA support, long deprecated). sshtunnel 0.4.0 references it at import time. The upstream fix exists as a PR but hasn't been released. You can monkey-patch it, but you're now maintaining someone else's compatibility shim.
ProxyJump requires reimplementation. Our bastion setup uses ProxyJump in ~/.ssh/config. With paramiko, you need to manually open a transport to the bastion, open a channel to the target, then create a second SSH client over that channel. It's ~40 lines of plumbing that duplicates what ssh does in one flag.
2FA breaks everything. Our bastion requires keyboard-interactive auth after public key. The regular ssh command handles this seamlessly (agent forwarding, session multiplexing, cached credentials). Paramiko can't — it needs an interactive handler that doesn't exist in a headless script context.
Each problem has a workaround. Together they add up to: you're reimplementing SSH in Python, poorly.
The 50-line solution
Just use ssh. The binary. The one that already handles your ~/.ssh/config, ProxyJump chains, 2FA, ControlMaster multiplexing, agent forwarding, and every edge case that the SSH ecosystem has solved over 30 years.
import os
import socket
import subprocess
import time
def open_tunnel(remote_host, remote_port, ssh_host, ssh_port=22,
ssh_user=None, ssh_key=None):
"""Spawn `ssh -L` and return (process, local_port)."""
# grab an unused local port
with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
s.bind(("127.0.0.1", 0))
local_port = s.getsockname()[1]
cmd = [
"ssh", "-N",
"-L", f"{local_port}:{remote_host}:{remote_port}",
"-p", str(ssh_port),
"-o", "ExitOnForwardFailure=yes",
"-o", "ServerAliveInterval=60",
"-o", "ServerAliveCountMax=3",
]
if ssh_user:
cmd += ["-l", ssh_user]
if ssh_key:
cmd += ["-i", ssh_key]
cmd.append(ssh_host)
proc = subprocess.Popen(
cmd, stdin=subprocess.DEVNULL,
stdout=subprocess.DEVNULL, stderr=subprocess.PIPE,
)
# poll until the tunnel is accepting connections
deadline = time.monotonic() + 15
while time.monotonic() < deadline:
if proc.poll() is not None:
stderr = proc.stderr.read().decode(errors="replace").strip()
raise ConnectionError(
f"SSH tunnel exited (rc={proc.returncode}): {stderr}"
)
try:
sock = socket.create_connection(("127.0.0.1", local_port), timeout=0.5)
sock.close()
return proc, local_port
except OSError:
time.sleep(0.3)
proc.terminate()
raise ConnectionError("SSH tunnel did not become ready within 15s")That's it. No paramiko. No sshtunnel. No monkey-patches. No reimplemented key exchange. Zero new dependencies.
Wiring it into a database client
The trick is making this transparent. The caller shouldn't know or care whether a tunnel is involved:
from sqlalchemy.engine.url import make_url
class Database:
def __init__(self, db_url, ssh_host=None, **kwargs):
self._ssh_proc = None
ssh_host = ssh_host or os.environ.get("SSH_TUNNEL_HOST")
if ssh_host:
parsed = make_url(db_url)
proc, local_port = open_tunnel(
parsed.host, parsed.port, ssh_host,
)
self._ssh_proc = proc
db_url = parsed.set(
host="127.0.0.1", port=local_port,
).render_as_string(hide_password=False)
self.engine = create_engine(db_url)
def close(self):
if self.engine:
self.engine.dispose()
if self._ssh_proc and self._ssh_proc.poll() is None:
self._ssh_proc.terminate()
self._ssh_proc.wait(timeout=5)
def __enter__(self):
return self
def __exit__(self, *exc):
self.close()
def __del__(self):
try:
self.close()
except Exception:
passNow the usage looks like this:
# .env
# SSH_TUNNEL_HOST=bastion.corp
with Database("mysql+pymysql://user:pass@10.0.0.5:9030/analytics") as db:
df = pd.read_sql("SELECT * FROM orders LIMIT 10", db.engine)On your laptop, the tunnel opens automatically. On the production server where the DB is directly reachable, SSH_TUNNEL_HOST is unset and no tunnel is created. Same code, same URL, zero conditional logic.
Why native ssh wins
~/.ssh/config is your single source of truth. Host aliases, identity files, proxy chains, keep-alive settings, algorithm restrictions — all defined once, respected everywhere. Paramiko reads none of this by default.
ProxyJump just works. Your config says ProxyJump bastion? The ssh binary handles the entire chain. No manual transport-over-channel plumbing.
2FA just works. Keyboard-interactive, TOTP prompts, Kerberos tickets — whatever your bastion requires, the ssh binary and your agent handle it. If ssh bastion works in your terminal, it works in subprocess.Popen.
ControlMaster multiplexing. If you have ControlMaster auto in your ssh config, multiple Database() instances reuse the same underlying SSH connection. Free connection pooling at the SSH layer.
ServerAliveInterval keeps it alive. The ssh -L tunnel sends keep-alive probes every 60 seconds. No more mid-query tunnel drops because your laptop went to sleep for 30 seconds.
The tradeoffs
Requires the ssh binary. This is macOS/Linux only. On Windows you'd need OpenSSH for Windows or WSL. For a data team working on Macs and Linux servers, this is a non-issue.
Process management. You're spawning a child process. If your Python process crashes hard (SIGKILL), the ssh process might linger. The __del__ fallback handles normal exits; for paranoid cleanup, add an atexit handler.
No programmatic key management. You can't generate or rotate keys in Python. You rely on whatever ssh-agent and ~/.ssh/ provide. For a data client this is a feature, not a bug — key management belongs to your SSH infrastructure, not your query script.
The meta-lesson
The instinct when something doesn't work in Python is to find a Python library. Sometimes the right answer is to shell out to the tool that already solves the problem.
SSH has 30 years of edge-case handling baked in. Paramiko is a heroic reimplementation, but reimplementations always lag. When the gap matters — ProxyJump, 2FA, algorithm deprecations — the original wins.
The subprocess.Popen call isn't elegant. It's better than elegant. It's correct.