Interacting with PostgreSQL on remote server through SSH tunnel?

Question

I have User A setup on my webserver with it's public SSH key copied over to User B's ~/.ssh/authorized_keys over on my database server. The idea is to use an SSH tunnel to facilitate the communication between my web application and remote database. I'm using ufw to lock down everything except ssh connections and port 5432 which PostgreSQL uses for local connections:

ufw default deny
ufw allow ssh/tcp
ufw allow 5432/tcp
ufw enable

Here's the command I use to open the SSH tunnel (via web server):

ssh -L 63333:localhost:5432 userb@1.2.3.4

Running netstat -lpnt | grep 63333 | grep ssh after the above command on my web server yields the following:

tcp      0     0 127.0.0.1:63333       0.0.0.0:*       LISTEN      22560/ssh
tcp6     0     0 ::1:63333             :::*            LISTEN      22560/ssh

Next I try and connect to the database server on port 63333 to verify that everything is working:

psql -h localhost -p 63333 userb

I'm met with the following error message:

-bash: /usr/bin/psql: No such file or directory

Is there something obvious that I'm missing here? I only have PostgreSQL installed on the database server. From my understanding, once the tunnel is in place, and given the right parameters, shouldn't I have access to the interactive terminal (psql)?

Answer 1

Doh! My SSH tunnel was connecting fine. You need to have a client installed on the server that's establishing the remote connection in order to communicate with PostgreSQL.

In my case it was a one liner on my webserver:

sudo apt-get install postgresql-client-9.3