i suspect a lot of people are used to forwarding TCP sockets with SSH --
for example, to connect locally to a mysql daemon that runs only on the
loopback interface of a remote machine (this is debian's default
ssh -N -T -oExitOnForwardFailure=yes -L 3306:localhost:3306 email@example.com
But sometimes, the remote service runs on a UNIX-domain socket, not on a
TCP socket -- for example, debian's default configuration for
postgresql is to have it listen only on a UNIX domain socket in
/var/run/postgresql, and use
SO_PEERCRED with a simple system
account == psql account mapping scheme to authenticate users without
needing any extra credentials. This is not quite as simple to forward
over ssh, but it's doable as long as
socat is installed on both your
local host and on the remote postgres server.
Here's one way to do it if
$SOCKET_DIR points to the full path of a
directory under the user's control (this is all one command, split
across lines for easier reading):
socat "UNIX-LISTEN:$SOCKET_DIR/.s.PGSQL.5432,reuseaddr,fork" \ EXEC:'ssh firstname.lastname@example.org socat STDIO UNIX-CONNECT\:/var/run/postgresql/.s.PGSQL.5432'
Then, you'd connect with something like:
psql "user=remoteuser host=$SOCKET_DIR"
psql connection will trigger an
ssh connection to be made.
Of course, this won't work well if ssh has to prompt for passwords, but
you should be using
There are at least a couple nice features of being able to use postgresql from a local client like this:
- your psql client can load files from your local machine, and can dump/export files to the local machine.
~/.psql_historystays local, so you can review what you did even when you're offline
- you can run local RDBMS administrative GUIs like
pgadmin3with minimal network traffic and no extra packages installed on the server.
- unlike forwarding TCP ports (where any other user account on the
machine can hop onto your connection), you can control access to
your local UNIX-domain socket with standard filesystem permissions
postgresql itself already comes with a nice range of
high-quality network-capable authentication mechanisms you could use.
But many of them (like GSSAPI or X.509 mutual key-based authentication
over TLS) require additional infrastructure setup; and you probably
sshd up and running on that machine -- so why not make
use of it?