Problems connecting Google Data Studio to Heroku PostgreSQL

At first it would seem simple to connect Google Data Studio with our PostgreSQL server running on Heroku, there is even a connector already available to do this. However when we enter our credentials we can see an error. The error states:

Access denied. Check your username and password.

After some research we can conclude that we get this error because Heroku requires an SSL connection. We can try to obtain the necessary certificates by following the instructions on the following page:

https://stackoverflow.com/questions/42386975/heroku-postgresql-with-google-datastudio

But we run into another problem when trying to get the server certificate through a python script. The script returns an error stating that the SSL certificate cannot be found on the server. After checking with the server, this does not make much sense, since the server states that there is a SSL certificate in place. We tried modifying the python script to see wat data was returned from the server, but this data didn't make any sense. After a couple hours of trying we decided to abandon this approach.

Server certificate through Amazon AWS

Because we were at a roadblock, we decided to check where Heroku hosted it's databases. Apparantely all databases are hosted on Amazon AWS servers. Luckily for us Amazon has al their server certificates listed on their website. This sparked some hope for our problem. All the Amazon certificates can be found on this site:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html

We found out that our server was hosted in the "EU-West-1" region, so we downloaded the certificates for that region. After trying all the certificates that came with the bundle, none of them seemed to work. It would appear that we were once again back to zero.

Using Google BigQuery

Another workaround that we stumbled across was to use Google BigQuery to load the data and then pass it on to Google Data Studio. We setup a Google BigQuery project and were ready to establish a connection to Heroku. During the connection process there were no signs of any problems and our hopes were high. But when Google BigQuery performed it's final checks, it returned an error stating that the connection was refused.

The solution?

We don't really have a solution for connecting Google Data Studio directly to Heroku PostgreSQL. There seems to be a problem with the SSL certificates on Heroku, and we couldn't find a solution online. Instead we found our workaround through the use of Dataclips. Whilst this isn't ideal, it does provide us with a way to show our data in Google Data Studio.