Connect a Google CloudSQL Postgres database to Data Studio

Question

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: 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.

Wivina

Answer 1

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!

Wivina

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. – Cohesion

Can you provide any information about how you acquired the PEM file to begin? – Geibel

@Geibel You probably figured it out already but you download it from the SQL->Connections page – Cohesion

@ChrisMcCauley Cheers, thanks for that extra info! – Geibel

Answer 2

(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

Coppinger