Connect to Azure SSL enabled Postgresql database

Setting up CloudStream solution within Azure cloud needs some security. By default, Azure CloudStream 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 CloudStream where 2 CloudStream 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 CloudStream 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 CloudStream application servers across the CloudStream application server cluster.

5. Go to primary CloudStream server. Create a temporary folder named ‘cert’ within /opt/hcp/conf:

CODE

[user@server]$ cd /opt/hcp/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/hcp/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 CloudStream server and run the below command:

CODE

./psql "sslmode=verify-ca sslrootcert=/opt/hcp/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 CloudStream server by /opt/hcp/bin/hcp-server stop command and take a backup of the following configuration file /opt/hcp/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 CloudStream servers and change all the application.conf file in the corresponding servers. Finally start all the CloudStream servers. Now it should connect to the Azure PostgreSQL external database with SSL enabled tunneling. You can securely login to your CloudStream portal

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