Connect to Postgres or MySQL through an SSH tunnel

PostgreSQL

In one terminal, leave the tunnel running:

ssh -N -o ExitOnForwardFailure=yes -L 127.0.0.1:5433:127.0.0.1:5432 user@server

In another:

psql -h 127.0.0.1 -p 5433 -U app appdb

Port 5433 is any free port on your machine. Using something other than 5432 avoids a clash with a local PostgreSQL. A connection string works the same way, in any tool or library:

postgresql://app@127.0.0.1:5433/appdb

In a graphical database tool, enter host 127.0.0.1 and port 5433 with the database login, and leave the tool's own SSH option off, since the tunnel already does that job.

On the server, the connection seems to come from the server itself. In our test, inet_client_addr() returned 127.0.0.1. So pg_hba.conf needs a line for it, which most default files already have:

host  appdb  app  127.0.0.1/32  scram-sha-256

MySQL

ssh -N -o ExitOnForwardFailure=yes -L 127.0.0.1:3307:127.0.0.1:3306 user@server

mysql -h 127.0.0.1 -P 3307 -u app -p appdb

Write 127.0.0.1 and not localhost. The MySQL client treats localhost as a request for the local Unix socket, ignores the port, and fails:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

--protocol=TCP also fixes it, if you really want to type localhost.

The account must be allowed from the address the server sees. A user created as 'app'@'localhost' can fail over TCP. On the official MySQL image, which has skip_name_resolve on, it failed with Access denied for user 'app'@'127.0.0.1'. Create the account for that address:

CREATE USER 'app'@'127.0.0.1' IDENTIFIED BY '...';
GRANT ALL ON appdb.* TO 'app'@'127.0.0.1';

Which address goes where

-L 127.0.0.1:5433:127.0.0.1:5432
   |         |    |         |
   |         |    |         +- port of the database
   |         |    +- database address, as seen by the SSH server
   |         +- port on your machine
   +- address to listen on, on your machine

Keep the first address as 127.0.0.1, so nobody else on your network can use your tunnel.
The database can listen only on loopback: listen_addresses = 'localhost' in PostgreSQL, bind-address = 127.0.0.1 in MySQL. Both worked in our test, and the tunnel does not need them opened up.
Prefer 127.0.0.1 to localhost as the destination too. The SSH server resolves the name, and it might try IPv6 first.

Remote and dynamic forwards follow the same pattern, see SSH tunnels: local, remote and dynamic.

If the database is on another machine, such as a managed service, name it as the destination. The database then sees the SSH server's private address, so that is what its allow list needs:

ssh -N -L 127.0.0.1:5433:db.internal.example.com:5432 user@server

TLS still matters

SSH encrypts from your machine to the SSH server. If the database is on another machine, the last hop is outside that encryption, so use TLS on the database connection as well.

Certificate checks need extra care, because your client thinks it is connecting to 127.0.0.1, and that is not on the certificate:

psql "host=127.0.0.1 port=5433 user=app dbname=appdb sslmode=verify-full sslrootcert=ca.crt"
... server certificate for "db.example.com" does not match host name "127.0.0.1"

In PostgreSQL, set host to the certificate's name and hostaddr to the tunnel. This passed our test with full verification:

psql "host=db.example.com hostaddr=127.0.0.1 port=5433 user=app dbname=appdb sslmode=verify-full sslrootcert=ca.crt"

MySQL has no equivalent setting. --ssl-mode=VERIFY_IDENTITY checks the name you typed, so you would need a certificate that lists 127.0.0.1, or a name in your hosts file that points to it. Plain REQUIRED encrypts without checking who answered.

A user that can only open this tunnel

A person or an app that only needs the database should not get a shell on the server. Create a user with no shell:

sudo useradd -m -s /usr/sbin/nologin dbtunnel

Install their public key in /home/dbtunnel/.ssh/authorized_keys. Then add a drop-in file, for example /etc/ssh/sshd_config.d/10-dbtunnel.conf. Match stops at the end of that file, so it does not affect the settings after it:

Match User dbtunnel
  AllowTcpForwarding local
  PermitOpen 127.0.0.1:5432
  X11Forwarding no
  AllowAgentForwarding no
  PermitTTY no
  ForceCommand /usr/sbin/nologin

Run sudo sshd -t, reload, and connect:

ssh -N -L 127.0.0.1:5433:127.0.0.1:5432 dbtunnel@server

What we saw with this configuration:

The tunnel above works, and psql connects through it.
Port 3306 or any other destination is refused.
-R fails with remote port forwarding failed for listen port.
A normal login prints This account is currently not available.
-L 5433:localhost:5432 is refused as well. PermitOpen compares text, with no lookups, so the client must ask for exactly 127.0.0.1.

To limit one key instead, you can use options in authorized_keys, on one line:

restrict,port-forwarding,permitopen="127.0.0.1:5432" ssh-ed25519 AAAA... dbtunnel

This also blocks a shell and other destinations. In our test it still allowed -R, so opening a port on the server stays possible. The Match block with AllowTcpForwarding local closes that, and we prefer it for this reason.

This user can only reach the database's own login. The database account should still have its own password or certificate, and only the rights it needs.

Questions

Why use port 5433 on my side instead of 5432?

Any free port works. A different one avoids a clash with a PostgreSQL already running on your own machine.

Why does mysql ignore the port when I write localhost?

The MySQL client treats localhost as the local Unix socket. Use 127.0.0.1, or add --protocol=TCP.

Is database traffic encrypted inside an SSH tunnel?

From your machine to the SSH server, yes. If the database runs on another machine, the last hop is outside the tunnel, so use TLS as well.

Sources

More in Tunnels and jump hosts

All guides