Heroku Postgresql with Google Datastudio

Question

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.

@nobe4 please accept mfazekas's answer. that one actually works. Lee only says "it doesnt work" when that's just a lie. — tolgap, Oct 04 '19 at 17:08

score 71 · Accepted Answer · edited May 18 '20 at 15:24

71

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:

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

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

edited May 18 '20 at 15:24

lightstrike

407
4
11

answered Feb 26 '18 at 18:36

mfazekas

5,589
1
34
25

3

This solution worked for me. Just be sure to run `postgres_get_server_cert.py` in python 2 – gtalarico Mar 22 '18 at 22:27
28

I think it's helpful also to know how to call the python script as there doesn't seem to be any documentation on it. Open terminal and use the command `python postgres_get_server_cert.py {aws-database-server}:{port} > aws-cert.crt` replacing {aws-database-server}:{port} with your server and port as appropriate. This will output the certificate to the aws-cert.crt file – Russell Jun 12 '18 at 20:28
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. – TrickSpades Nov 18 '19 at 18:38
@Mohamad on you local machine. And then use the result in the DataStudio form along with the certificate made with the python script. – Alex Freshmann May 27 '20 at 09:20
@mfazekas : Is it mandatory to use private/shield plan on Heroku, please check https://help.heroku.com/3DELT3RK/why-can-t-my-third-party-utility-connect-to-heroku-postgres-with-ssl – svikramjeet Sep 08 '20 at 09:38
@svikramjeet it is not necessary, that article refers to mTLS which is a separate auth method from extracting the self-signed certs from the Postgres server – andscoop Oct 05 '20 at 19:01
1

It looks like heroku recently removed some TLS protocols to connect, which breaks the script. Does anyone have a solution? – JohnDoe Mar 16 '22 at 16:01
I've been using this method for 1,5 year. Now authentication with generated certificates fails. Anyone knows what might have happened? There's zero info from Data/Looker Studio as of now. – Kihaf Dec 28 '22 at 10:14
You can use Heroku enhanced certificated (https://devcenter.heroku.com/articles/heroku-postgres-enhanced-certificates) and then download the ISRG Root X1 server cert from https://letsencrypt.org/certificates/ . It will work with self-generated client certificates. – Miroslav Valcicak Jan 02 '23 at 10:29
@MiroslavValcicak That beta feature is only available to enterprise plans, no? – j_d Jan 27 '23 at 19:50
@j_d according to documentation referenced earlier, it should work for "A Standard, Premium, Private, or Shield tier Heroku Postgres database, version 13 or greater". I can confirm it works for the "Heroku Postgres Standard 0". – Miroslav Valcicak Jan 31 '23 at 09:59
I'm unfortunately on the Basic plan, so @MiroslavValcicak approach won't work. Is it worth looking into fixing the script, or is it impossible to make it work with heroku's latest changes? – schlow May 10 '23 at 15:23
I'm trying to decide which route to go given these comments. My previous connections still work, but I'm redoing them now and can't remember how I set it up. A few questions: 1.) Does the self-signed cert need to be created on a heroku db, or is local OK? 2.) One response notes the {aws-database-server}:{port} but what is the aws-database-server name? is that the compute host? 2.) What should I be typing for the "ADDON_NAME" in the heroku instructions here? heroku data:labs:enable enhanced-certificates -a example-app --addon=ADDON_NAME – Jeremy L. Jun 02 '23 at 17:44
Figured it out by trial and error. First, know the original method still works, don't need the enchanced certificate. Second, all these files can be created on your local, using the instructions above, and yes, the compute host/port is what you should put in the python call. You may need to install python for the python script. Finally, note that Google won't allow you to upload a .key file into its key field, so you can just change the extention to .crt or .txt. – Jeremy L. Jun 02 '23 at 18:26
UPDATE July 2023 - I went and perform step 2 only and uploaded the cert generated to the SSL field on datastudio. Finally able to connect – a.fahmiin Jul 11 '23 at 17:01

score 8 · Answer 2 · answered Mar 17 '17 at 15:03

8

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.

answered Mar 17 '17 at 15:03

Lee

1,389
3
18
28

I just encountered this. I tried to force it into using SSL by setting a JDBC connection string with `&ssl=true`, but I still get an error: `{"reason":28,"errorStatus":{"reason":28,"code":3,"reasonStr":"JDBC_HOST_NOT_REACHABLE","errorCategoryStr":"USER_CONFIGURATION","category":2,"uniqueErrorCode":"...","errorCategoryCode":"2013"},"params":{"errorId":"0d539e57"}}`. – Jeremy Dec 18 '17 at 21:06
Here's the thread: https://www.en.advertisercommunity.com/t5/forums/v3_1/forumtopicpage/board-id/Data-Studio/page/1/thread-id/3235 – Andrei Jan 18 '18 at 18:00
Google has added the connect via SSL option now – DMTintner Aug 04 '20 at 10:53

score 0 · Answer 3 · edited Aug 19 '20 at 16:32

0

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.

edited Aug 19 '20 at 16:32

Duck

39,058
13
42
84

answered Aug 19 '20 at 15:39

Varun Vajpayee

41
1

score 0 · Answer 4 · answered Sep 16 '22 at 14:37

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.

Heroku Postgresql with Google Datastudio

4 Answers4

Linked