Forwarding Unix sockets via SSH to TCP connection

Problem

I like Postico for connecting to PostgreSQL databases. But now I found myself with a database that ran only via a UNIX domain socket. psql connects fine to the socket located at /path/to/postgres/socket/.s.PGSQL.5432 but Postico couldn’t….and for good reason. It’s a Mac sandboxed-app, which means I can trust that Postico won’t leak any information stored within it and explicitly can’t access things on my computer (like microphone and camera) that I don’t want it to. Unfortunately, this means I can’t directly connect to databases via UNIX sockets. However, there is a solution via SSH tunnelling.

Can’t connect to a socket path via Postico because of sandbox limitations. Boohoo.

After some digging, I found bits and pieces of information that were relevant but not precisely what I was looking for. This Github comment on a similar topic gave me a clue that SSH tunnels could work.

(MacOS instructions ahead, but with sshd turned on and running, the instructions could be adapted for linux too)

To begin with, Remote Login should be enabled.

Enable Remote Login (Mac OS “Sharing” in system preferences)

In a terminal,

$ ssh -L 5555:/path/to/postgres/socket/.s.PGSQL.5432 -N username@machinename

This command forwards activity on the socket to TCP port 5555 on localhost. The “-N” option is optional and merely keeps the ssh tunnel session alive in the terminal instead of ending up in a bash/zsh ssh session. “username” is what you use to login to your machine and “machinename” is visible in the “Computer Name” field in the Remote Login screen shown in the picture above.

Now, you should be able to connect to localhost:5555 via Postico as you normally would.