PostgreSQL via SSH Tunnel

Solution 1:

Your pg_hba.conf appears to permit connections from localhost. The easiest way of causing your SSH tunnel connections to appear from localhost is to make them to localhost.

The following SSH command connects to remote.example.com as user "user", and causes your ssh client to listen on localhost, port 1111/tcp. Any connections made to that port will be forwarded over the ssh tunnel, and on the ssh server side the connections will be made to localhost, port 5432/tcp. Since we're connecting to localhost, the connections will appear to be from localhost also, and should match your existing pg_hba.conf line.

ssh -L 1111:localhost:5432 [email protected]

If this is expected to be a long-running tunnel, I would recommend using autossh

To connect using the psql client on the host where you are running the ssh client, use something like this:

psql -h localhost -p 1111 -U your-db-username database-name

You should then be prompted for your database user's password.

Alternately, you can add a line line the following to a file called .pgpass in your home directory on the client where you're running psql:

localhost:1111:database-name:your-db-user:your-db-password