CODEWITHSUNDEEP
postgresqlgoogle-cloud-sqlpemlooker-studiopkcs#8
Thomas Kaliakos
Thomas Kaliakos

Reputation: 3304

Connect a Google CloudSQL Postgres database to Data Studio

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:

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.

Upvotes: 12

Views: 3848

Answers (2)

Kyri Paterson
Kyri Paterson

Reputation: 1

(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

Upvotes: 0

Thomas Kaliakos
Thomas Kaliakos

Reputation: 3304

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!

Upvotes: 23

Related Questions