Connection Types from Cloud Run to Managed Cloud SQL - What is the best option for production use?

[dheerajpanyam]

I see several options available for connecting from CloudRun (Fully Managed)  to Cloud SQL and I am spoiled by choice 🙂 with  the various permutations/combinations that arise  from the options mentioned below. My question is what is the recommended connection type for production use case. My understanding is "Direct VPC Egress (worth the risk considering it is Preview??) connecting to Private IP of Cloud SQL instance and using Cloud SQL Auth PROXY" would be the recommended method but I want to confirm this. Also this recommended method is not available out of the box since the Cloud Run configuration (automatically configures auth proxy) uses public IP not Private IP. How do I use private IP instead? In other words is there a way to use default Cloud Auth Proxy that CR configures to work with Private IP if so what are the steps? Do i need to include cloud sql auth proxy in the Dockerfile along with my app code and use the --private-ip command line option?

1. VPC Serverless Connector vs Direct VPC Egress (Direct VPC Egress is in Preview so probably not recommended for production)

2. Public IP vs Private IP of Cloud SQL Instance (Private IP offers lower latency )

3. Cloud SQL Auth Proxy vs  Language connectors (CR automatically configures a AUth proxy so my understanding is that language connectors cannot be used)

Below is the Cloud SQL configuration option available from Cloud Run Console and my guess is it is connecting to the public IP of the Cloud SQL instance. If that's the case it would be nice to include public IP in the hovered message.

[christianpaula]

Hi @dheerajpanyam,

Welcome to Google Cloud Community!

For production use, Direct VPC Egress connecting to the Cloud SQL instance's Private IP and utilizing the Cloud SQL Auth Proxy is the suggested connection method between Cloud Run and Cloud SQL. The optimal balance of scalability, performance, and security is offered by this approach.

You must set up the Cloud SQL Auth Proxy and enable Direct VPC Egress on your Cloud Run service in order to use this option. After completing this, you can use the private IP address of the Cloud SQL Auth Proxy instance to establish a connection to your Cloud SQL instance from your Cloud Run service.

If you are using the default Cloud Auth Proxy that Cloud Run configures, you can still use Direct VPC Egress to connect to your Cloud SQL instance using the private IP address. To do this, you will need to set the CLOUDSQL_AUTH_PROXY_PRIVATE_IP environment variable to true in your Cloud Run service's Dockerfile.

[dheerajpanyam]

Thank you so much @christianpaula. I will use the default cloud auth proxy with direct VPC Egress by setting the CLOUDSQL_AUTH_PROXY_PRIVATE_IP  environment variable. Can you point me to the documentation that refers to this setup? Also I forgot to mention the option of TCP vs UNIX sockets. UNIX sockets are faster than TCP sockets from what i know. Does Private IP work with TCP sockets only?  Also another interesting scenario came to my mind. Let's say i use the default Cloud SQL Auth proxy and also define a CR  environment variable say DB_HOST set to private IP of the Cloud SQL instance which one would be used?

[dheerajpanyam]

@christianpaula  I followed the exact settings that you mentioned - Cloud SQL Auth Proxy + Direct VPC Egress + Passing CLOUDSQL_AUTH_PROXY_PRIVATE_IP set to true in Dockerfile for CR service but i am getting  a "SequelizeConnectionRefusedError" . My suspicion for this to happen is because the DB HOST   (part of DB connection string) in application code   is set to 127.0.0.1 not the private IP of Cloud SQL instance) which in my opinion is correct since cloud sql auth proxy setups up a TCP socket and socket connection is via localhost am i wrong?.  Below is the error message. Changing the DB_HOST to the private IP of the Cloud SQL instance works but i wonder if this connection type still uses cloud sql auth proxy i think it is a direct connection.

[dheerajpanyam]

@christianpaula I shall appreciate if you can reply to my question thanks in advance.

[OliverBot]

 How can I connect to Cloud SQL with CLOUDSQL_AUTH_PROXY_PRIVATE_IP=true env? The Cloud SQL Auth Proxy that we must deploy is a VM instance? How connects Cloud Run to Cloud SQL Auth Proxy? SSH certs? or must the service implement specific code?

[dheerajpanyam]

@OliverBot  With GCE VM the connection is fairly easy. You need to install the cloud sql proxy as part of your GCE startup  script and start the cloud sql proxy passing the private ip parameter to the CLI like  this --private-ip  (you might want to check the exact syntax though from the cloud sql proxy GH Repo) available with version 2.0 of cloud sql proxy.

[OliverBot]

@dheerajpanyam , that's ok for me to connect from instance VM with cloud-sql-proxy to a Cloud SQL instance. My problem is the configuration you mention above: Cloud Run + Cloud SQL Auth Proxy to connect Cloud SQL private instance with Direct VPC Egress. Using VPC connectors is not a problem. But, with Direct VPC Egress, I can't connect to database instance from Cloud Run service.

[dheerajpanyam]

What is the error you are getting? Also what is the DB_HOST value is it localhost or private IP of the Cloud SQL instance. My understanding is cloud sql auth proxy uses localhost connection over TCP sockets so DB_HOST should be 127.0.0.1 so it did not work for me. I can connect to private IP of cloud sql instance but i doubt if the connection is using cloud sql auth proxy.

[OliverBot]

In my case, I run an external image that requires connection on MySQL instance. It requires SPRING_DATASOURCE_URL env for IP address. With private IP instance Cloud SQL address, I get:

java.sql.SQLException: Access denied for user 'user'@'10.40.68.16' (using password: YES)

10.40.68.16 is an IP from VPC egress subnet that I have configured in Cloud Run deploy.

One question: who needs Cloud SQL Client role to can connect?

[dheerajpanyam]

@OliverBot  Just to i understand your setup, are you connecting from Cloud Eun service to a self hosted database hosted on a GCE VM?

[OliverBot]

@dheerajpanyam .

• Shared VPC in Host project with one subnet
• Cloud SQL MySQL instance in service project over Shared VPC. I can connect to Cloud SQL instance throught a VM instance in same project over subnet defined in Shared VPC.
• Cloud Run service in a third project with VPC Egress traffic and Cloud SQL instance defined in spoken terms. 

While I wrote this words, I see that egress is defined in private-ranges-only and third project is not a service project of Shared VPC Host project.

May I forget some requirement?

[dheerajpanyam]

@OliverBot This is a very complex setup to me. Considering that direct vpc egress is in Preview i would try with a vpc serverless connector since direct vpc egress may not support this advanced setup.

[OliverBot]

@dheerajpanyam from Cloud Run, view which SA need Cloud SQL Client role?

[dheerajpanyam]

Depends on SA that is attached to CR. By default CR uses compute engine default service account that has predefined Editor role which encompasses the Cloud SQL client role (need to confirm but that is my guess).  Another option to create a new custom SA account with the Cloud SQL client role and attach it to the Cloud Run Service.

[brendonrapp]

This CLOUDSQL_AUTH_PROXY_PRIVATE_IP environment variable doesn't seem to actually be documented anywhere - a Google search only reveals this discussion and another one, both with people not getting it to work.