Client authentication for PostgreSQL¶
This guide covers how to open a port to a remote server and configure client authentication for 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. Opening a port involves a security risk, and the port should only be opened for specific and trusted IPs.
If deploying in a public cloud, the PostgreSQL connection may have to be specifically opened. In the AWS cloud, a security group with inbound rules for the relevant ports and IPs must be created.
This guide covers Linux firewall and PostgreSQL client authentication configuration.
IP addresses¶
The IP addresses for the prod and test environments of AP are described below.
| Environment | Hostname | IP address |
|---|---|---|
| Prod | datapipeline.baosystems.com | 3.93.131.28 |
| Test | test.manager.baosystems.com | 54.173.36.156 |
Linux firewall¶
The PostgreSQL port must be accessible in the iptables (firewall) of the Linux operating system. This can be achieved using the firewalld management tool and the firewall-cmd command. Create a dedicated zone bao-ap for the analytics platform, add the various source IP addresses and add the PostgreSQL port. Invoke as root user.
firewall-cmd --new-zone=bao-ap --permanent
firewall-cmd --reload
firewall-cmd --zone=bao-ap --add-source=3.93.131.28/32 --permanent
firewall-cmd --zone=bao-ap --add-source=54.173.36.156/32 --permanent
firewall-cmd --zone=bao-ap --add-port=5432/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-all --zone=bao-ap
PostgreSQL authentication¶
PostgreSQL must be configured for authentication from external clients. This is done in the pg_hba.conf configuration file. Invoke as postgres user. Note that the PostgreSQL version might be different (e.g. 14 instead of 16).
Under address, /32 refers to the CIDR notation for a single host, not a port. This will enable access for the dhis2 database and dhis user for the specific IP addresses only. Add the lines below to the end of the file.
# Analytics platform connections
# host database user ip-address/mask auth-method
host dhis2 dhis 3.93.131.28/32 md5
host dhis2 dhis 54.173.36.156/32 md5
PostgreSQL listen port¶
PostgreSQL must be configured to listen for external connections. In the PostgreSQL configuration file, ensure listen_addresses is set to '*'. The file is typically located at /var/lib/pgsql/16/data/postgresql.conf. Listening addresses is not restricted since there are multiple layers of security above in place.
Restart services¶
PostgreSQL must be restarted for changes to take effect. Invoke as root user.
The DHIS 2 instance might need to be restarted because of the PostgreSQL restart. Invoke as root user.