How to connect to Postgres Cloud SQL via SSH tunnel & SSL using GUI

[Phongthorn Khamkankaew]

Hi, I am a newbie for Postgresql SSL. I try to use SSL and SSH, but not working.

So I connect by DataGrip and other GUI still got the same error as below.

Connection to user@db-server failed.
[08006] Could not open SSL root certificate file /home/username/.postgresql/root.crt.

First question, Can I use all of the certificate files generated from Cloud SQL?

• server-ca.pem
• client-key.pem
• client-key.pem

Do I need to do something before using it?

So anyone can help me, please?

[Nicolas (Google Cloud Platform Support)]

Hi Phongthorn, 

 

Thanks for posting here, there are two ways of connecting any external applications to Cloud SQL instances. First you can use the Cloud SQL proxy which can be set up following these steps . In a nutshell, you would need to enable the API, Install the proxy locally, authenticate the proxy, specify the instance and start the proxy then simply set the IP in Datagrip to 127.0.0.1.

 

The other way would be to configure the access via Public IP which is done by authorizing your application’s IP to connect.

 

To answer your question, to connect with SSL yes you will need to use the three files : server-ca.pem, client-cert.pem and client-key.pem

 

I hope that helps you!

[Phongthorn Khamkankaew]

Thanks for your help. I did follow steps from your link before. Everything is work. Cloud proxy, Public IP, etc.

Except connect with SSL through SSH tunnel. I using Compute Engine to connecting Cloud SQL and allow only connection from this instance's Public IP and then I can connect to Cloud SQL from my local machine.

So, I still got some struggling with SSL connection. First thing I remove SSH Tunnel and use Public IP include SSL. It still can't connect, too.

Maybe IDE tools the root cause of this problem, right? I may do not know how to configure correctly.

Anyway, your answer makes me sure about the SSL certificate files that I got from Cloud SQL. It can use without convert to other formats.

เมื่อ วันพฤหัสบดีที่ 27 มิถุนายน ค.ศ. 2019 2 นาฬิกา 51 นาที 52 วินาที UTC+7, Nicolas (Google Cloud Platform Support) เขียนว่า:

[Nicolas (Google Cloud Platform Support)]

Hi Phongthorn,

 

I’m glad you were able to connect using the Cloud Proxy and Public IP. To use SSL try to use the command given when creating your certificate. That being said if this doesn’t work I would suggest you to post on StackOverflow where you have access to a large community of enthusiasts and experts to share ideas with and get support from. Please make sure to include all relevant details and error messages which would help the community troubleshoot.

[Phongthorn Khamkankaew]

Finally I found a solution from another guy after I post this question in StackOverflow for a month.

https://stackoverflow.com/a/57626621/5808321

The root cause come from program not Cloud SQL. So I need to share answer for someone still facing the problem like me.