Integrate Google Data Studio with Postgres using SSL

Being a developer at Platanus I am pretty familiar with Heroku and its add-ons as all of our projects are hosted on it. For hosting our databases, we use the Heroku Postgres add-on.

This add-on forces you to use a Secure Sockets Layer (SSL) connection.

Let me explain how to integrate Google Data Studio (GDS) with this add-on.

1. Create an empty project on GDS. Once inside the project click the Add data button, and in the popup menu select PostgreSQL.

GDS will ask for some credentials.

2. Go to your Heroku project and open the Heroku Postgres add-on. Inside the add-on go to settings and click the view credentials button, this will reveal all the credentials that GDS asks for the connection.

If you don’t host your database on Heroku Postgres, all of those credentials can be found on the service that you use for hosting it.

3. As we said before, Heroku Postgres uses Amazon servers to store data, and to make the communication secure we have to enable the SSL connection.

When we enable it, GDS will ask for the client configuration files.

How do we generate these files?

First, to generate the client private key and client certificate we’ll use OpenSSL. You can do it with the following command on the terminal.

openssl req \
       -newkey rsa:2048 -nodes -keyout client.key \
       -x509 -days 365 -out client.crt

That will generate two files on the path that the terminal is running: client.key (client private key) and client.crt (client certificate).

We are almost there, there is only one file missing, the server certificate. For this, we have to copy this script and run it with the following parameters.

python postgres_get_server_cert.py database-server:port > server.crt

Where database-server is the host that we got previously from Heroku Postgres, and port is the port that appears there.

This will generate the last file missing for the connection, named as server.crt.

Now we go back and upload the generated files to the corresponding field in GDS

This should be enough to connect the database from our project with GDS and manipulate it in a safe way.

And TADA! There you have it. I hope this has served you.