Connect to IBM Cloud Database using DbVisualizer

One of my favourite database is PostgreSQL. I started to use it back in year 2007 when I was tasked to build a HR application. Compared to old days, it is much easier to setup PostgreSQL nowaday. With a simple click in IBM Cloud, a PostgreSQL instance can be provisioned, setup in HA within minutes.

DbVisualizer is one of the tool I used in old days. Below is some trick to configure a connection in DbVisualizer.

Once you have a IBM Cloud Database for PostgreSQL provisioned, you will be presented with connection details such as public endpoint, hostname, port and so on. Private endpoint can be enabled as well. I will have a separate post for private endpoint.

The next thing is to set a password at Settings page. I will suggest to randomly generate a complex password to security purposes. Once the password is generated, write it some for later use.

IBM Cloud Database for PostgeSQL only allow SSL connection with self-sign certificate for security. Another word, the connection from DbVisualizer or application needs to be established in SSL mode as well. What we can do is, simple to save the certificate to a file — ~/path/cert

Please take note that the certificate is in Base64, you may need to convert this cert from Base64 to UTF-8/ASCII to be used in application coding.

In DbVisualizer, you can use wizard to create a new database connection. Give a name for your connection — icd-posgresql.

Enter the details for connection, you can refer to Overview page to extract the key connection value. And don’t forget the password you have saved temporarily. Click Finish.

DbVisualizer will throw connection error. Don’t panic, this is expected behaviour as DbVisualize tried to establish a non-SSL connection by default.

This is where we navigate to Properties tab, and scroll down to section with SSl section

There are few values we need to modify to enable SSL connection: ssl, sslkey, sslmode, and sslrootcert.

• ssl is set to “true”
• sslkey is set to name of TLS certificate (You can find at Overview page of PostgreSQL instance).
• sslmode is set to “verify-full” (You can find at Overview page of PostgreSQL instance).
• sslrootcert is set to the path where the certificate is stored ~/path/cert

Back to Connection page and click Connect.

You will see the connection have small green tick and Connection message will show PostgeSQL version and driver version.