SSH tunnel for PostgreSQL connection¶
This guide covers how to set up an SSH tunnel to a remote server running PostgreSQL.
Overview¶
The DHIS2 data pipeline in AP benefits from a database connection to the DHIS2 PostgreSQL database for efficient loading of huge amounts of data. The database connection must however be set up in a secure way, and exposing the PostgreSQL port to the outside is not recommended.
Using an SSH tunnel have several security benefits. It adds a layer of encryption to the connection and uses public-private key authentication, which is more secure than password-based login.
Since using an SSH tunnel requires that the public key of the AP server is installed on the remote PostgreSQL server, it is important to create a dedicated user with minimal authority which only has access to the PostgreSQL service, not admin or root access.
Configuration¶
The local port forwarding approach for SSH tunneling is most appropriate type for remote connections to PostgreSQL.
SSH¶
Set up the tunnel with the following command.
The -f flag ensures the process is run in the background. The -N flag prevents the command from executing the command, i.e. avoids logging in and opening a shell. The -L defines the local port number to be forwarded and the remote host and port number. The local hostname is omitted and defaults to localhost. Using localhost as the remote host leads traffic to arrive on the localhost address at the remote server, meaning no changes to the PostgreSQL pg_hba.conf configuration should be necessary.
systemd¶
To manage the SSH tunnel, in terms of starting, stopping and enable it on server boot, the systemd process managed can be used. Create a systemd service file with a descriptive name.
[Unit]
Description = SSH Tunnel for PostgreSQL at my.domain.org
After = network.target
[Service]
ExecStart = /usr/bin/ssh -N -L 5001:localhost:5432 bao-admin@my.domain.org
User = bao-admin
Restart = always
RestartSec = 3
[Install]
WantedBy = multi-user.target
Note
The -f flag must be omitted in the systemd file to avoid the SSH process being deactivated
To enable the SSH tunnel on server boot.
To start and stop the SSH tunnel
Testing¶
To test, first ensure that the public key of the local server is installed at the remote server and that you can SSH into the remote server.
Exit the remote server. Now verify that you can connect to the remote PostgreSQL instance on localhost using the port defined by the SSH tunnel and the regular psql CLI syntax, where the PostgreSQL database name is dhis2 and the user is dhis.
If the psql CLI returns with a password prompt, the connection is valid.
View current SSH connections with the following command.
View current SSH processes with the following command.