Optimizing Cloud Run and Cloud SQL Auth Proxy for Private IP Connection

From my understanding the recommended production setup for connecting to Cloud SQL instance from Cloud Run is Direct VPC Egress (in Preview but should be fine to use) + Connecting to Private IP of Cloud SQL instance + Using Cloud SQL Auth Proxy however here is a catch. Cloud Run by default configures the Cloud SQL Auth proxy when you add a Cloud SQL connection from the CR configuration screen in GCP console but this connects to the public IP of Cloud SQL instance. To connect to Private IP there is an environment var CLOUDSQL_AUTH_PROXY_PRIVATE_IP that needs to be passed in dockerfile of the CR service with the value set to true so far so good. With this setup the DB connection string which also has DB host as part of it should take the value 127.0.0.1 / localhost since cloud sql auth proxy connections over TCP sockets use localhost. This doesn’t seem to work for me. When i change DB HOST to private IP of cloud sql instance connection is setup successfully but my guess is this is a direct connection and does not use the cloud sql auth proxy. Appreciate any help on why connection with localhost is failing?

I am not sure the variable CLOUDSQL_AUTH_PROXY_PRIVATE_IP is passed correctly to the Cloud SQL connections service. You can have a look in the cloud run logs when you put your db host to 127.0.0.1. If you still see something like "instance does not have IP of type “PUBLIC” it means proxy is not trying the private IP. But you might not need the proxy. You can get clients certificate and deploy it to your application and use it for SSL. Also as an option you can try language specific cloud sql proxy connectors.

Thanks let me check

Below is how i have defined the env variable in the Dockerfile for the CR service.
ENV CLOUDSQL_AUTH_PROXY_PRIVATE_IP=true

One of the Google Staff Engineers recommended private IP + Cloud SQL Auth Proxy + Direct VPC Egress as production grade setting. Below is the log message

You might need to look to the next several messages to catch up output from the cloud sql auth proxy.

okay will do thanks

I am using sequelize ORM and the next several messages only mention about the tables not getting synchronised perhaps from the ORM migration (users table and roles table as you can see in the error message)

My main concern is why is DB connection string with privateI IP working but not localhost 127.0.0.1. I guess localhost is the right setting with cloud sql auth proxy. With private IP of cloud SQL instance i have a feeling it is connecting directly and not using auth proxy.

It probably does if you put the instance private IP. Does you instance have a public IP at all?

I think you meant private IP. Yes my instance has a private IP.

No. I was asking about public IP.

Yes it has a public IP. Some of these technologies are so complex or usage / inner workings are not clear hence it is hard to troubleshoot.

I tried to test it and found that proxy was always looking to the public IP whether you use CLOUDSQL_AUTH_PROXY_PRIVATE_IP variable or not. With a public IP in place it was connecting successfully but when I removed the public IP for the instance it was not able to work. Speaking about best practices and recommendations we need to take in consideration all aspects and requirements. As I said above you might be better if you use connection directly to your private IP without proxy. Of course the SSL is the recommended way in such case and you have an option to use the client certificate provided with the instance.