CODEWITHSUNDEEP
postgresqlherokulooker-studio
nobe4
nobe4

Reputation: 2842

Heroku Postgresql with Google Datastudio

I'm having troubles to connect an existing heroku database to Google Datastudio. I'm trying to add the connection and I get the following:

Access denied, please check your username and password.

Now, I'm 100% sure that I'm correct on those credentials and the problem comes from somewhere else.

I've tried with different setup, either a free or a paid PSQL instance, nothing works.

I've also setup a dummy account on elephantsql and the connection worked the first time without any issue.

Do you have any idea of the cause of that problem?

Edit:

Just found https://www.en.advertisercommunity.com/t5/Data-Studio/Heroku-Postgres-lt-gt-Google-Data-Studio/m-p/1031729 which is not helpful at the time of writing this post.

Upvotes: 37

Views: 17000

Answers (5)

Nir T
Nir T

Reputation: 41

For me the marked green solution didn't work and I managed to solve it without generating a client.crt and client.key. Thanks to this thread I only had to download the pem active self-signed root certificate from LetsEncrypt, enable enhanced certificates on heroku's side and use the certificate in Looker Studio as my server certificate as follows: enter image description here enter image description here enter image description here

Upvotes: 0

Kevin Dick
Kevin Dick

Reputation: 28

I thought I would mention that I have used this for quite awhile, but every time my database undergoes maintenance it breaks and I have to manually reconnect the certificates. I developed a better approach - connect the data to Google BigQuery and do your blends there, and then use the BigQuery Community Connector. The charts are more performant this way AND you can now use query parameters on blended data.

Of course, DataStudio won't connect directly to Heroku Postgres for the same reason, so I use a service called Fivetran to grab the raw data and send it to Google BigQuery. There is a cost to this, of course, but for some projects it may be worth it. At some point I will move my database off of Heroku to either AWS or Google itself to allow a direct connection, but that is a larger project.

Upvotes: 0

Varun Vajpayee
Varun Vajpayee

Reputation: 31

Make sure to run the openssl command on one line to generate the client.key and client.crt in one command. It took me a couple of tries of downloading the certificates (unable to reach host error), but this finally got me connected to Heroku Postgres with GDS.

Upvotes: 0

mfazekas
mfazekas

Reputation: 5699

Since the February 6, 2018 update, Google DataStudio allows SSL connections with PostgreSQL, which is necessary to connect to a database created via Heroku.

To enable SSL you need to provide client key+cert and server cert, which can be accomplished by taking the following steps:

  1. Generate a self-signed cert + key with openssl for client key + certificate:
openssl req \
       -newkey rsa:2048 -nodes -keyout client.key \
       -x509 -days 365 -out client.crt
  1. Use the postgres_get_server_cert.py script to get the self-signed server cert from heroku psql:

https://raw.githubusercontent.com/thusoy/postgres-mitm/master/postgres_get_server_cert.py

Upvotes: 73

Lee
Lee

Reputation: 1445

The problem is that Heroku Postgres requires an SSL connection which doesn't seem possible with Data Studio at the moment. Hopefully Google will add that option soon.

Upvotes: 8

Related Questions