12

I am trying to connect a Postgres database hosted in a google Cloud Sql instance to Data Studio. I have followed the instructions (found here https://support.google.com/datastudio/answer/7288010) but to my disappointment, I still cannot connect to the database from Data Studio.

I think the problem lies with the SSL connection, as if I enable "non-secure connections" in the options in CloudSql I can successfully connect from Data Studio.

I have created the 3 files required for the ssl connection to work:

  • Client certificate
  • Client key
  • Server certificate

I have uploaded them in the relevant fields in the Data Studio connection page: enter image description here When I press authenticate I get an error saying: Please make sure that the private key is in PKCS8 format.

I tried changing the format of the client key file by running: openssl pkcs8 -topk8 -inform PEM -outform DER -in client-key.pem -out client-key.pkcs8 -nocrypt

but then I get an error: Can't reach the host. Please double check your connection parameters. Learn more about database connectors here.

Finally it must be noted that if I try to connect to the database from my local machine using ssl and the exact same keys, but the client key in the pem format, I can connect successfully.

Should I convert the certificate to other formats too? I feel like connecting a Postgres db to Data Studio over SSL is something that shouldn't be that difficult, am I missing something obvious?

Any help would be greatly appreciated.

Thanks.

Thomas Kaliakos
  • 3,274
  • 4
  • 25
  • 39

2 Answers2

23

Eventually it turns out that just changing the format of the client key works. The reason the above didn't work was that the command to change the format was wrong.

It should be:

openssl pkcs8 -topk8 -inform PEM -outform PEM -in client-key.pem -out client-key.key -nocrypt

(notice the PEM instead of DER parameter that is posted in the question).

So Data Studio connection works! Happy days!

Chris McCauley
  • 25,824
  • 8
  • 48
  • 65
Thomas Kaliakos
  • 3,274
  • 4
  • 25
  • 39
  • 3
    It's really annoying that while the connector config will complain that the client key is not in PKCS8 format, it won't complain that the PKCS8 is in DER instead of PEM. Lots of people on the Google DataStudio community pages having problems with this. Thank you Thomas for this answer which I wish I had read before wasting more than a day on this. – Chris McCauley Jun 27 '20 at 08:44
  • Can you provide any information about how you acquired the PEM file to begin? – blong Oct 08 '20 at 20:53
  • 1
    @blong You probably figured it out already but you download it from the SQL->Connections page – Chris McCauley Feb 15 '21 at 10:10
  • @ChrisMcCauley Cheers, thanks for that extra info! – blong Feb 15 '21 at 18:59
0

(On getting the certs in the first place)

If you're using a GCP Cloud Postgres connection like here:

  1. Go to your GCP Console -> SQL
  2. Click into your DB instance -> Connections
  3. Scroll down to "Configure SSL client certificates"
  4. Click "create a client certificate", give it a name, and download the outputs
Dharman
  • 30,962
  • 25
  • 85
  • 135
Kyri Paterson
  • 1