Our production databases are only accessible from the production application servers. I am able to login to production app servers and psql to the db, but I would like to setup a ssh tunnel to allow me to access the production db from my work box.
Ideally, it would be a single command that I could run from my workbox that would set up the tunnel/proxy on the production app server
Here is what I have come up with, but it doesnt work.
user@workbox $ ssh -fNT -L 55555:db.projectX.company.com:5432 app.projectX.company.com
user@workbox $ psql -h app.projectX.company.com -p 55555
psql: could not connect to server: No route to host
Is the server running on host "app.projectX.company.com" (10.1.1.55) and accepting
TCP/IP connections on port 55555?
The reported IP address is incorrect.
When connecting to the tunnel endpoint, the hostname is your local host, since that's where the forwarded port is exposed.
ssh -fNT -L 55555:db.projectX.company.com:5432 app.projectX.company.com
psql -h localhost -p 55555
BTW, PgAdmin-III provides ssh tunnel automation. On the other hand, it's a big GUI app without psql's handy \commands.
It's pretty trivial to write a sshpsql bash script that fires up the ssh tunnel, stores the pid of the ssh process, launches psql, lets you do what you want, and on exit kills the ssh tunnel. You'll also want to TRAP "kill $sshpid" EXIT so you kill the tunnel on unclean exits.