Author: Ashish Singh Chauhan
Introduction
Heroku Postgres is a managed SQL database service provided directly by Heroku. You can access a Heroku Postgres database from any language with a PostgreSQL driver, including all languages officially supported by Heroku.
In this blog, I will show you how to connect with the heroku postgresql database with client security certificates.
This type of connection is mostly used in your Production Environment.
It provides more security and reliability in the transfer of data from the database to the Mule application.
Prerequisite
- You must have all the required certificates and credentials string provided by your client.
- Your public IP address must be unblocked by the client for accessing Heroku Postgresql. This will help to test your application on a local machine.
Steps of connection
- Download the certificates provided by your client. It must contain following 3 files →
root.crt, <<name>>.key, <<name>>.crt
- Since the postgresql.key file is in non java format and MuleSoft is able to read only java format file so convert it to java format by using following command →
openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -nocrypt -out postgresql.key.der
This command will generate a new file named as postgresql.key.der
Note → You must have openssl installed and its path is set in the system.
- Create a Mule application and put root.crt, postgresql.crt and postgresql.key.der in your src/main/resources.
- Add a Database module in your Mule Palette.
Go to global elements → create → Connector configuration → Database config → Select generic connection.
- Provide the url in following format –
jdbc:postgresql://<DB_HOST>:<DB_PORT>/<DB_NAME>?sslmode=verify-ca&sslcert=${mule.home}/apps/${app.name}/postgresql.crt&sslkey=${mule.home}/apps/${app.name}/postgresql.key.der&sslrootcert=${mule.home}/apps/${app.name}/root.crt
Note – 1) Replace <DB_HOST>, <DB_PORT>, and <DB_NAME> with values provided by Client.
2) Leave ${mule.home} and ${app.name} as-is to be replaced at runtime by the Connector.
Driver class name – org.postgresql.Driver
Username – <<username>>
Password – <<password>>
After configuring the test connection, I will fail because your ${mule.home} and ${app.name} are going to be replaced at runtime.
- Add Select component in Mule Flow and write your query in it.
- Run your application and you will get the output in postman.
- For deployment to Cloudhub, make sure to tick the checkbox “Use Static IP” and a Static IP will be applied to your application.
Provide this IP to your client and he will allow the traffic from that and you will be able to connect to heroku postgresql.
Note → 1) Static IP will not be changed if you restart, redeploy your application.
2) Static IP will get changed if you delete the application and deploy it again.
