Connect to Azure SSL enabled Postgresql database
Setting up Dispatcher Paragon Cloud solution within Azure cloud needs some security. By default, Azure Dispatcher Paragon 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 Dispatcher Paragon Cloud where 2 Dispatcher Paragon 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 Dispatcher Paragon 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 Dispatcher Paragon Cloud application servers across the Dispatcher Paragon Cloud application server cluster.
5. Go to primary Dispatcher Paragon Cloud server. Create a temporary folder named ‘cert’ within /opt/DispatcherParagon/conf:
[user@server]$ cd /opt/DispatcherParagon/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:
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:
[user@server]$ /opt/DispatcherParagon/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):
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 Dispatcher Paragon Cloud server and run the below command:
./psql "sslmode=verify-ca sslrootcert=/opt/DispatcherParagon/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
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 Dispatcher Paragon Cloud server by /opt/DispatcherParagon/bin/safeq-cloud-server stop command and take a backup of the following configuration file /opt/DispatcherParagon/conf/application.conf
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 Dispatcher Paragon Cloud servers and change all the application.conf file in the corresponding servers. Finally start all the Dispatcher Paragon Cloud servers. Now it should connect to the Azure PostgreSQL external database with SSL enabled tunneling. You can securely login to your Dispatcher Paragon Cloud portal
Reference: https://docs.microsoft.com/en-us/azure/postgresql/concepts-ssl-connection-security