Connecting Google Data Studio to Render Postgres Instance

1
2

Hi Scott,

It looks like Google Data Studio requires SSL to connect to a Postgres instance. I was able to get this working by doing the following:

  • Fill out the database authentication fields. The database, username, and password can be found on your Render dashboard’s database page. The host will be either oregon-postgres.render.com for U.S. or frankfurt-postgres.render.com for EU. The port will be 5432.
  • Click the Enable SSL checkbox
  • Fetch the server cert by running this script, supplying the host name of the Render server. python postgres_get_server_cert.py oregon-postgres.render.com > server.crt Upload server.crt to Google Data Studio as the server certificate.
  • Generate a public/private key using openssl req -newkey rsa:2048 -nodes -keyout domain.key -out domain.csr
  • Upload domain.csr to Google Data Studio as the client certificate and domain.key as the Client Private Key.
  • Click authenticate

You should now be able to access your Render Postgres instance from Google Data Studio. Let me know how this works for you!

3

Jake, first of all thank you for this. this was the only solution I could find on the entire internet which is pretty surprising to say the least. So this solution worked for me…for a few days and now I am getting this.

Unable to connect to Host: SSL error: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

would these certs only last a few days for some reason? it’s not really feasable to have to regenerate these every few days for eternity for like 20 connectors.

thoughts?? thanks in advance!

4

Glad you found this helpful!

Which region were you connecting to? The certs are renewed every 60 days. It’s possible that you connected a few days before renewal. Have you run into this again after reconnecting?

5

we’re in the U.S. oregon server. so far so good, so yeah might’ve just got unlucky and caught it right before cert rotation. I still don’t love the idea of updating every data connector every 2 months, but not the end of the world either.

6

Glad that it’s been working for you! Unfortunately, there isn’t any way around the cert rotation issue. It’s probably best addressed with automation, if possible.

7

thanks bruv!

8

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.