HOWTO - Connect to a PostgreSQL DB though an SSH Tunnel

howto_connect_to_pgsql_via_ssh.md

Overview

In cases where you want a layer around your PostgreSQL DB to minimize access, versus opening another port on a server for direct access (which you can restrict to an IP address or range), there is the option of accessing your DB through an SSH tunnel.

Considerations

• This can be slower
• You will want authorized_hosts setup to allow the tunnel to open automatically
• Some tools like Navicat, will help do this for you if you set your server to "local" in the General tab and use the server dns name in the SSH tab. The following is only needed if you are using the psql command from a client system's terminal.

pg_hba.conf

The following is all I have in my pg_hba.conf for this example.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                      trust
host    addressbookdb   mydbuser        localhost                trust

If I am logged into the database server, I can run psql and connect to any DB I want. If I make a remote connection using a tunnel (described below) or psql -h localhost addressbook i'll be able to connect. Connecting to any other DB will not work to localhost. This locks me down to a user accessing a specific DB in a specific way unless you are on the server.

Making the Connection

% ssh -f -N -L 6333:localhost:5432 mydbuser@myserver.awesome.lan
% psql -h localhost -p 6333 addressbookdb

The first command tells ssh to open up a tunnel using port 6333 for the remote connections and 5432 for the localhost connection using the mydbuser on the myserver.awesome.lan server. At this point you have a tunnel created that will hit 5432 (postgresql) on localhost. The -f paramter tells ssh to run this in the background. The -N parameter tells it not to execute a remote command which helpful when all you want to do is port forward.

The second command is the postgres command line client. It is told to connect on port 63333 (the tunnel) for localhost (what it is connected to on the other end). The goal is to connect to the DB addressbookdb.

Once completed, you will be able to see everything as if you were local on the system.

Final

This is not meant as a security manual so you will need to find ways of making this as secure as you can but it will help you eliminiate the need for another network facing service. If this is interesting, use it as a starting point and find ways to lock the end-point account down along with other things.