Connect to Azure SSL enabled Postgresql database

Setting up SAFEQ Cloud solution within Azure cloud needs some security. By default, Azure SAFEQ Cloud servers need to connect to external Azure PostgreSQL (ver. 10.0) database. Azure PostgreSQL server can run as IaaS or PaaS mode and enabling SSL in the database would require some small steps to follow. Below are the steps to follow:

1. Consider the clustered architecture for SAFEQ Cloud where 2 SAFEQ Cloud application servers are running behind an Azure load balancer and connecting to an Azure PostgreSQL PaaS database. The entire environment is been secured via Azure Network Security Group

2. Enable SSL Enabled option in Azure PostgreSQL database from Azure Portal

3. Download the certificate file from the Certificate Authority (CA). For Azure, you can download from here: https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt

If Customer has their own CA, download the certificate from their verified Certificate Authority.

4. Place the certificate file within primary SAFEQ Cloud application server. It needs to be compiled using OpenSSL libraries which will create the Root certificate at the very end. Remember to put this root certificate within all SAFEQ Cloud application servers across the SAFEQ Cloud application server cluster.

5. Go to primary SAFEQ Cloud server. Create a temporary folder named ‘cert’ within /opt/safeqcloud/conf:

CODE

[user@server]$ cd /opt/safeqcloud/conf
[user@server]$ sudo mkdir cert

6. Download OpenSSL in the cert directory. In general, it is by default been installed in the OS. If not, you can download it by the following command:

CODE

sudo yum install openssl

7. To confirm that you’ve successfully installed OpenSSL on your system, run the following command and check to make sure you get the same output:

CODE

[user@server]$ /opt/safeqcloud/conf/cert/openssl version
OpenSSL 1.0.2k-fips  26 Jan 2017

8. The downloaded Root CA file is in encrypted format. Use OpenSSL to decode the certificate file. To do so, run this OpenSSL command (Remember to put the BalitimoreCyberTrustRoot.crt file in the same directory):

CODE

openssl x509 -inform DER -in BaltimoreCyberTrustRoot.crt -text -out root.crt

This will create a root.crt file within the same location.

9. Use the root.crt file created and the sslmode=verify-ca or sslmode=verify-full option. Go to pgsql directory within primary SAFEQ Cloud server and run the below command:

CODE

./psql "sslmode=verify-ca sslrootcert=/opt/safeqcloud/conf/cert/root.crt host=hcppgdb.postgres.database.azure.com dbname=eopng_config user=hcpadmin@hcppgdb"

Remember to change database host, dbname and username in the above line matching your own environment.
If everything is alright, this will connect to the database with psql prompt

CODE

psql.bin (9.6.9, server 10.5)
WARNING: psql.bin major version 9.6, server major version 10.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-SHA384, bits: 256, compression: off)
Type "help" for help.
eopng_config=>

10. Now stop the SAFEQ Cloud server by /opt/safeqcloud/bin/hcp-server stop command and take a backup of the following configuration file /opt/safeqcloud/conf/application.conf

CODE

cp -p application.conf  application.conf.backup

11. Open the application.conf in any linux editor and add the following lines:

12. Save the application.conf. Distribute the root.crt file in all the SAFEQ Cloud servers and change all the application.conf file in the corresponding servers. Finally start all the SAFEQ Cloud servers. Now it should connect to the Azure PostgreSQL external database with SSL enabled tunneling. You can securely login to your SAFEQ Cloud portal

Reference: https://docs.microsoft.com/en-us/azure/postgresql/concepts-ssl-connection-security