I wanted to work with a PostgreSQL DB remotely with PgAdmin, but I didn’t really want to figure out how to allow the DB to accept external connections in a secure way. Since I have SSH access this should be very doable. I have seen co-workers use putty for SSH tunneling before, and had previously used Putty on Ubuntu to copy that, but setting up my Natty workstation I figured there had to be a more native way to do it. Of course I could do tunneling straight from the command line. If I could ever remember the steps for it that approach would work great. Instead I found a tool called Gnome SSH Tunnel Manager (gSTM) and installed that from the Ubuntu repos. It is pretty straight forward to configure if you understand the concept of tunneling, which I only barely do, so I needed a little help getting set up, but after that it is dead simple.
- Install gSTM and start it up.
- Click ‘Add’ for a new tunnel bookmark, and name it.
- Add IP and user login for remote machine.
- Leave port and privatekey as default (unless you know what they are used for in which case you probably know what to put in there).
- In the port redirection section click ‘Add’, a new dialog will appear.
- Type is ‘local’.
- ‘Port’ is the port on your local machine you want to assign the tunnel to (I did 5666).
- ‘To host’ can be set to ‘localhost’.
- ‘To Port’ is the port used on the remote machine. default PostgreSQL is 5432.
- Click ‘OK’ and all the settings are done for gSTM so click ‘OK’ again to close the settings dialog.
- Highlight new tunnel, and click ‘Start’ - it should prompt you for the ssh password.
- Now use pgAdmin, or another application to connect to the DB at localhost:5666 (or whatever port you set in step 7 above..).
Now I just need to make sure my tunnel is running in order to have access to the DB locally. Very cool! Probably where I got most confused was with the ‘To host’ and ‘To port’ settings, the wording seems backwards. Is that just me?
DBeaver has tunneling built in, that is pretty much the only place I need it these days anyway, but otherwise adding some alliases in .ssh/config works real nice. Haven’t used tool like gSTM for many years.