QGIS PostGIS SSH Tunnel: Keep the Server Name for TLS

The tunnel comes up, QGIS connects, and the login dies with a certificate error naming 127.0.0.1 instead of the database host. The tempting next move is to relax sslmode, which silences the check and throws away the identity guarantee you wanted in the first place. The actual problem is that the local port and the server's name are being stored in the same field. A libpq service entry separates them, so the socket goes to the forwarded port while TLS still verifies the name printed on the certificate.

What the client thinks it is connecting to

Under sslmode=verify-full, libpq compares the certificate the server presents against the host name it was asked to reach. When that name is a loopback address, the comparison fails even though the certificate is valid for the real database host. The SSH forward changes which socket carries the bytes, not the identity the client should expect. Once those two ideas are separated, the error stops looking like a TLS fault.

A forward is not authorization either. PostgreSQL still applies its own rules to the connection the SSH server makes, so keep the transport layer and the database layer distinct before changing anything.

Bind the forward to loopback only

The listener should exist on the QGIS machine and nowhere else. Naming 127.0.0.1 explicitly keeps it off other interfaces, while an omitted bind address can expose the port depending on the SSH client's gateway settings.

ssh -N -L 127.0.0.1:15432:127.0.0.1:5432 [email protected]

The destination after the second colon is resolved from the SSH server's side, so this example assumes PostgreSQL sits on the same host as sshd; a database on another machine needs that address and its permissions instead. Run the session with -N so no shell is opened, leave host-key verification alone, and confirm the listener appears before blaming QGIS. If the port is already in use, the forward fails at startup rather than at query time.

Put the name and the socket in one service entry

A service file lets the same connection carry a verification name and a different socket destination. Save the entry where libpq looks for services, or point PGSERVICEFILE at it.

[gis-via-ssh]
host=db.geo.example
hostaddr=127.0.0.1
port=15432
dbname=gisdb
user=gis_reader
sslmode=verify-full
gssencmode=disable
sslrootcert=/path/to/approved-root.crt

Here host keeps the identity used for certificate checks while hostaddr sends packets to the forwarded loopback port, and both are required for this arrangement to work. gssencmode=disable matters on builds that would otherwise negotiate GSSAPI encryption first, because that path ignores the TLS settings just configured. Reference the service by name from QGIS's PostgreSQL connection dialog and supply the password through the normal prompt rather than inside a shared file. The names and paths above are placeholders; substitute your own approved values.

What PostgreSQL still checks after the tunnel

The server sees a connection from the SSH host, loopback in this arrangement, and matches it against the host-based authentication rules in order. Your database role still has to authenticate, and the SSH login grants no database identity by itself. Verify what the server believes rather than what the client assumed.

psql "service=gis-via-ssh" -c "select inet_server_addr(), current_user"

Close the forwarding session when the work is finished; the local entry then stops working and nothing else changes. One database path is not a VPN for the whole GIS client, and treating it as one is how the next connection gets misdiagnosed.