Getting strange googleapi Err 400 message connecting to postgresql CloudSQL instance

Question

I´m getting a strange Err 400 missing project parameter when trying to connect to a CloudSQL instance using the cloud_sql_proxy mechanism

I have a GCE project with a working CloudSQL postgres database, my apps on the compute api can use it and I can do regular psql from any of the VM I have configured inside my GCE project.

However, when I try to connect to the database from my laptop using the cloud_sql_proxy I get this strange error.

I´m following to the letter this documentation: https://cloud.google.com/sql/docs/postgres/connect-admin-proxy#install

So, following that documentation I have:

1. CloudSQL enabled and working as I commented
2. Proxy Installed
3. I have a service account created as the documentation say with Cloud SQL Admin role as follows:

{
  "type": "service_account",
  "project_id": "my-proyect-21432",
  "private_key_id": "<hidden intentionally>",
  "private_key": "<hidden intentionally>",
  "client_email": "[email protected]",
  "client_id": "<hidden intentionally>",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/[email protected]"
}

4. I started the cloud_sql_proxy successfully as follows:

user@hostname:~$ ./cloud_sql_proxy -instances=db1=tcp:15432 -credential_file=my-proyect-21432.json
2019/05/29 10:17:25 Rlimits for file descriptors set to {&{8500 65536}}
2019/05/29 10:17:25 using credential file for authentication; email=cloudsql-serviceaccount@my-proyect-21432.iam.gserviceaccount.com
2019/05/29 10:17:25 Listening on 127.0.0.1:15432 for db1
2019/05/29 10:17:25 Ready for new connections

5. And finally I launch the psql client as follows:

psql "host=127.0.0.1 port=15432 sslmode=disable dbname=db1 user=dbuser"

I see on the cloud_sql_proxy the following error:

2019/05/29 10:17:33 New connection for "db1"
2019/05/29 10:17:34 couldn't connect to "db1": googleapi: Error 400: Missing parameter: project., required

And on the client side I'm getting:

psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

At this point I should get my psql client connected successfully and I can´t find anything about this error online or in the google's documentation

I have no clue where I need to set a project parameter, I tried crazy places like on the psql side with -v or using the url with ? at the end with no luck, I also tried on the cloud_sql_proxy side using the -projects flag, also with no luck.

---

EDIT: New findings!!!

I think I'm close to solve this, the first setup I did (as commented above) was on my windows pc that I use at home, today I'm at the office and I decided to replicate all of that using macos, I don't think that the OS matter at all, the interesting thing is that I replicated all and founded a small thing that make me to move forward

So, I started again and execute points 1., 2., 3., 4. and wait? the documentation states that the instances string is as follows: myproject:us-central1:myinstance NOT what I originally wrote, so I changed that and start having a more reasonable error:

I started cloud_sql_proxy make the connection with psql and got this:

user@hostname:~$ ./cloud_sql_proxy -instances=my-proyect-21432:us-east1:db1=tcp:15432 -credential_file=my-proyect-21432.json
2019/05/30 14:13:25 Rlimits for file descriptors set to {&{8500 65536}}
2019/05/30 14:13:25 using credential file for authentication; email=cloudsql-serviceaccount@my-proyect-21432.iam.gserviceaccount.com
2019/05/30 14:13:25 Listening on 127.0.0.1:15432 for db1
2019/05/30 14:13:25 Ready for new connections

<< when I run psql>>

2019/05/30 14:14:08 New connection for "my-proyect-21432:us-east1:db1"
2019/05/30 14:15:24 couldn't connect to "my-proyect-21432:us-east1:db1": dial tcp 10.26.112.3:3307: connect: operation timed out

My db1 instance has only the private IP 10.26.112.3

I started to look for that error around the internet and found a sugestion to allow incoming traffic to 3307 port:

Cannot Connect by Cloud SQL Proxy from Cloud Shell By Proxy https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/164

So I added the following rule:

allow-cloudsqlproxy | Ingress | Apply to all | IP Ranges 0.0.0.0/0 | tcp,udp 3307 | allow | default | 1000

But that didn't make any difference because after that I'm still getting the same error message :(

---

EDIT: from a VM on the same project

I created a VM on that project and replicate all this, I was able to connect, no connection refused on port 3307 message.

I have no idea who is blocking that traffic...

Answer 1

Thank you for keeping us updated with your findings. I encountered the same problem. I just solved it—your first edit tipped me off.

While following the google CloudSQL documentation process for connecting to CloudSQL from an external application, I started the proxy like this:

`./cloud_sql_proxy -instances=<instance_name>=tcp:5433`

It didn't let me connect. I was receiving this error

`couldn't connect to "xxxxxxx": googleapi: Error 400: Missing parameter: project., required`

After reading your edit I modified the command to use the entire instance name as stated on the instance details page, and it worked. This is the new command that got it working.

`./cloud_sql_proxy -instances=myproject:us-central1:instancename=tcp:5433`

I hope this saves someone a few hours.

Answer 2

Actually cloudsql-proxy does work when your Cloud SQL instance has only an internal IP address. In this scenario you use private services access to establish the connection between the cloudsql-proxy and the Cloud SQL instance. It is also recommended to execute the proxy using the option --ip_address_types=PRIVATE to force it to use the internal IP instead the public when connecting to the Cloud SQL instance.

Look here and here for more details.

I hope this helps.