Cannot connect looker studio to Postgres Database hosted in Google Cloud SQL

[thlpap]

Hello everyone,

I am trying to connect to Looker Studio my Postgres Database that is hosted in Google Cloud SQL using the PostgreSQL google connector for days and have the same problem: " Sorry, we encountered an error and were unable to complete your request. Error ID: 3323e234"

My database has SSL certificates.

Host Name: The Public IP address found in SQL Instance
Port: 5432
Database:  foo-database
Username: postgres
Password: ****

I have ticked "Enable SSL" and "Enable Client authentication" and imported the certificates that I use to also connect to PGAdmin.

I have included the 142.251.74.0/23 address as instructed in the docs in the Authorized networks of my db.

I still cant connect and cant find why.

Any ideas?

[gabridinova]

I have the same problem, very disappointing

[tackle]

Is this not possible!?! This is an enormous limitation and possibly a deal breaker for us.

[ArkadyZagdan]

@thlpap @gabridinova @tackle 
Consider using DataStream into BigQuery or running federated queries.
It is not a good practice to connect directly from SQL to Looker Studio due to costs issues, connectivity issues, and a limitations in data transformation to not mention it will work slower thhan feeded using BigQuery data Warehouse.

DataStream is CDC tehhhcnology thhat will capture any data change on your Db and store it in BigQuery.
It  is realtively chheap solution as you decide which tables  and columns you want to  track

[griffin97]

I had this problem myself. Client authentication to a GCP SQL instance was working locally with the provided certificates but not through looker studio with these same certificates. 

It looks like google requires the client-key file to be of format PKCS-8 whereas GCP SQL generates the key with format PKCS-1. https://support.google.com/looker-studio/answer/7288010?hl=en&sjid=12968189379248985726-NC#limits&zi...

You can use this terminal command to change your PKCS-1 client-key to a PKCS-8 client-key:
```openssl pkcs8 -topk8 -inform PEM -outform PEM -nocrypt -in client-key.pem -out client-key-pkcs8.pem```

[griffin97]

I had the same issue myself. Client authentication to my GCP SQL instance was working locally but connecting with Looker Studio failed.

It turns out GCP SQL generates a PKCS-1 formatted client key whereas Looker Studio expects a PKCS-8 formatted client key. Converting my key to PKCS-8 fixed the problem for me.

The terminal command to create a PKCS-8 client key from a PKCS-1 client key is:

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

[thlpap · Accepted solution]

I had converted my client key with openssl to PKCS-8 but it didn't work. Someone gave me the solution that actually works in stackoverflow. For anyone interested:

https://stackoverflow.com/questions/78951870/cannot-connect-looker-studio-to-postgres-database-hoste...