Setup and teardown an SSH tunnel

[cbandy]

I have some databases that are only accessible via SSH tunnel. Right now, I have to start a tunnel in another tab or the background, then use pgcli to connect to the tunnel locally. When I'm done, I have to remember to kill the tunnel. It would be great if I could pass another argument(s) to pgcli and it would setup and teardown the tunnel for me.

I imagine something like pgcli --ssh 'ssh_user:ssh_password@ssh_host:ssh_port' 'postgresql://pg_user:pg_password@pg_host:pg_port/dbname' which would do the equivalent of:

ssh -p ssh_port -L localhost:0000:pg_host:pg_port -N ssh_user@ssh_host
psql -U pg_user -h localhost -p 0000 -d dbname

where 0000 is some random available port.

sshtunnel looks like it might be well suited to manage the tunnel. It also appears to respect settings in ssh_config(5), so pgcli users with more complicated SSH needs can configure them there.

[amjith]

Thank you for the request. I'm not sure if there is enough interest for this to be built into pgcli as opposed to keeping them separate.

I'm hesitant to build this in as this has potential to cause a lot more support load. People might have different versions of paramiko or pycrypto or openssl lib etc and debugging the issues raising from that can be quite hairy.

I can keep this issue open and if I see more people interested in this, I'll reconsider.

[diddeb]

I still like this idea. Perhaps dealing with it as a first-class citizen is too complex as @amjith points out. Would a more generic setup/teardown hook structure be more appropriate? I can see multiple use-cases where something like pre/post hooks would be helpful. SSH connection management being one...

[meeuw]

I'm using a wrapper for mycli to setup the tunnel(s) and have some fancy bash auto completion (so I can auto complete server/database names). I've posted it on gist.github.com: https://gist.github.com/meeuw/f55485fd3d35d2102d31d3e3513e7489

I don't know why you want to use hooks, could you give an example how that will be useful?

[tsroten]

@cbandy @diddeb You can also connect via SSH without worrying about closing the tunnel via the ExitOnForwardFailure configuration option (-o). And, you can make SSH go in the background so you don't need two tabs (-f).

I have a shell function I can call whenever I want to bind a local port to a postgres port on the SSH server. The sleep 10 means that I'll have 10 seconds to connect to the port before the SSH connection closes automatically.

sshb_pg() {
    ssh -f -o ExitOnForwardFailure=yes -L 9432:localhost:5432 \
    $1 sleep 10
}

Usage:

$ sshb_pg ssh_user@ssh_host && pgcli [...]

Use shell aliases to do it all at once:

alias pg_ssh="sshb_pg ssh_user@ssh_host && pgcli -U pg_user -h localhost -p 9432 -d dbname"

$ pg_ssh
dbname> 

[cbandy]

I don't know why you want to use hooks, could you give an example how that will be useful?

Perhaps requesting/revoking temporary credentials from something like Vault could happen in hooks.

[meeuw]

Hmm so you'd like to use something like the following command:
PGHOST=somethingfromvaultscript PGPASSWORD=secret PGUSER=postgres pgcli --startup-hook-script "/path/to/vaultscript" ?
That wouldn't be very different to the wrapper shell script as suggested by @tsroten:
/path/to/vaultscript ; PGHOST=somethingfromvaultscript PGPASSWORD=secret PGUSER=postgres pgcli

I only think a hookscript might be useful for reconnects or to revalidate/rerequest authentication from vault.

[dvdbng]

A workaround I have been using is having a node.js daemon that runs ssh when a connection comes in and pipes the two connections together:
https://gist.github.com/dvdbng/621043d7b60713208bafb1f06191884e
Then you can connect to localhost and the daemon will forward it through the ssh connection to the db server

pgcli postgres://db_user:db_pass@localhost:5433/db_name

[aesyondu]

Here is what I did when using ssh alias:

# 5433 is the local port
# 5432 is the remote port
# my-dev is the ssh alias
ssh -L 5433:localhost:5432 -N my-dev

# connect to the local port
pgcli -U pg_user -h localhost -p 5433 -d my_database

[sepbot]

Auto clean-up approach using control path. It won't clean-up if you sigkill it.

~/.ssh/config:

Host jump
  HostName [jumpbox address here]
  ControlPath ~/.ssh/jump.ctl

pgcli.sh:

#!/usr/bin/env bash

set -x

function exit_ssh() {
  ssh -T -O "exit" jump
  exit 0
}

trap exit_ssh SIGINT SIGTERM

ssh -f -N -T -M -L 5432:[postgres address here]:5432 jump
pgcli [dsn using localhost as address here]
exit_ssh