Connect postgres cloud sql through cloud sql proxy

Question

I created a Single Zone postgres db instance on Cloud Sql, and I am trying to connect by cloud sql proxy.

/cloud_sql_proxy -instances=<PROJECT_ID>:us-central1:staging=tcp:5432 -credential_file=./<SERVICE_ACCOUNT_KEY_FILE> 

This is running well. But when i run below command,

psql "host=127.0.0.1 sslmode=disable dbname=postgres user=postgres"

the proxy shows this error:

2019/11/14 15:20:10 using credential file for authentication; email=<SERVICE_ACCOUNT_EMAIL>
2019/11/14 15:20:13 Listening on 127.0.0.1:5432 for <PROJECT_ID>:us-central1:staging
2019/11/14 15:20:13 Ready for new connections
2019/11/14 15:20:34 New connection for "<PROJECT_ID>:us-central1:staging"
2019/11/14 15:22:45 couldn't connect to "<PROJECT_ID>:us-central1:staging": dial tcp 34.70.245.249:3307: connect: connection timed out

Why is this happening? I am doing this from my local.

Answer 1

I've just followed this tutorial step by step and it worked perfectly for me.

I did not have to do any extra steps(whitelisting ip, opening port etc...) and this was done in a clean project.

Are you trying to do this from local with the SDK or from Cloud Shell? Do you have any firewall restrictions in place?

Any further information about specific setup from your side that might affect will surely help.

Let us know.

EDIT:

Make sure your port 3307 is not blocked by anything.

Have a look at this official documentation specifying that.

Answer 2

The issue is probably that you are not in the VPC network, like when you connect from localhost, so what happens is the cloud proxy showing it cannot connect to the remote IP. Read this carefully if you use a private IP :

https://cloud.google.com/sql/docs/postgres/private-ip

Note that the Cloud SQL instance is in a Google managed network and the proxy is meant to be used to simplify connections to the DB within the VPC network.

In short: running cloud-sql-proxy from a local machine will not work, because it's not in the VPC network. It should work from a Compute Engine VM that is connected to the same VPC as the DB.

What I usually do as a workaround is use gcloud ssh from a local machine and port forward over a small VM in compute engine, like:

gcloud beta compute ssh --zone "europe-north1-b" "instance-1" --project "my-project" -- -L 5432:cloud_sql_server_ip:5432 Then you can connect to localhost:5432 (make sure nothing else is running or change first port number to one that is free locally)

What should also work is to setup a VPN connection to the VPC network and then run the cloud proxy in node in that network.

I have to say I found this really confusing because it gives the impression the proxy does similar magic like gloud does. It's beyond me why some Google engineers have not wired that together yet, can't be too hard.