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 had this issue previously when I didn't specify the port argument to psql for some reason, try this:

psql "host=127.0.0.1 port=5432 sslmode=disable user=postgres"

Don't specify the db, and see if that lets you get to the prompt.

Answer 2

Make sure you have all the required IAM roles attached to the service account before you connect to it:

For instance, the list of roles for cloudsql can be retrieved from gcloud with:

$ gcloud iam  roles list --filter 'name~"roles/cloudsql"' --format 'table(name, description)'                                          

NAME                         DESCRIPTION
roles/cloudsql.admin         Full control of Cloud SQL resources.
roles/cloudsql.client        Connectivity access to Cloud SQL instances.
roles/cloudsql.editor        Full control of existing Cloud SQL instances excluding modifying users, SSL certificates or deleting resources.
roles/cloudsql.instanceUser  Role allowing access to a Cloud SQL instance
roles/cloudsql.serviceAgent  Grants Cloud SQL access to services and APIs in the user project
roles/cloudsql.viewer        Read-only access to Cloud SQL resources.

If your service account is lacking the appropriate roles, it won't be able to connect to the instance for IAM authentication to work.

Answer 3

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.

Answer 4

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.