SSH Tunnel

Rational

The case is very common. You have a database server (PostSQL/MySQL) running on a VPS with a public IP and you need to access it from your local machine or your cloud based BI tool. Publicly exposing PostgreSQL/MySQL isn’t the brightest thing to do so why not tunnel it at least though SSH.

Discalaimer - VPS should already be configured to allow SSH trafic from anywhere or from a specific ip address

Configure ssh

Αdd a user and a group with the same name, lets say tunnel:

root@postgres:~# groupadd tunnel
root@postgres:~# useradd -g tunnel tunnel

Create the .ssh directory and the authorized_keys file

root@postgres:~# mkdir -p /home/tunnel/.ssh
root@postgres:~# touch /home/tunnel/.ssh/authorized_keys

Fix file and directory pemissions

root@postgres:~# chown -R tunnel:tunnel /home/tunnel/.ssh/authorized_keys

Add tunnel’s public key and also configure:

• no-pty : to disallow login
• no-x11-forwarding : to disallow x11 forwarding
• permitopen="localhost:5432" : to allow only localhost:5432 to be tunneled
• command="/bin/echo Login Not Permitted" : to print Login Not Permitted

root@postgres:~# echo 'no-pty,no-X11-forwarding,permitopen="localhost:5432",command="/bin/echo Login Not Permitted" ssh-rsa AAAAAyOuRpublicKeYsGOeshERe user@machine' > /home/tunnel/.ssh/authorized_keys

Create Tunnel

Initiate an ssh tunnel from your local machine to the db server

user@laptop:~# ssh -N -L 5432:localhost:5432 tunnel@public_ip

Test connectivity

Open a psql connection using localhost

user@laptop:~# psql -h localhost -p 5433 -U postgres postgres

Fin

Thats all!