How to securely connect to Cloud SQL from Cloud Run?

Question

How do I connect to the database on Cloud SQL without having to add my credentials file inside the container?

Cayes

Note that Cloud SQL will soon be officially supported by Cloud Run. It will not require to install the Cloud SQL proxy inside the container. – Gayn

Answer 1

UPDATE: to connect to Cloud SQL from Cloud Run see the official documentation

---

Cloud SQL is now supported by the fully managed version of Cloud Run (Cloud Run on GKE users were already able to use Cloud SQL using a private IP)

To get started:

• if you do not have one already, create a Cloud SQL instance.
• make sure that the Cloud SQL admin API is enabled
• deploy a new revision of your Cloud Run service with gcloud alpha and the following flag: $ gcloud run services update --add-cloudsql-instances [INSTANCE_CONNECTION_NAME] Where is INSTANCE_CONNECTION_NAME is of the type project:region:instancename.

When you do this, Cloud Run will activate and configure the Cloud SQL proxy for you. You should then connect to it via the /cloudsql/[INSTANCE_CONNECTION_NAME] Unix socket.

Gayn

This actually does not work. The syntax does not match the CLI. However, after the first successful update all subsequent updates crash gcloud gcloud alpha run services update wordpress2 --add-cloudsql-instances cloudrun-sql with the error: {"version": "0.0.1", "verbosity": "ERROR", "timestamp": "2019-04-30T06:09:07.382Z", "message": "gcloud crashed (TypeError): can only join an iterable"} – Incredible

Note. For those interested I have WordPress now working in Cloud Run. I plan to document this as soon as Cloud SQL is working correctly with Cloud Run. – Incredible

The crash is solved by changing the gcloud command to: gcloud alpha run services update wordpress2 --set-cloudsql-instances cloudrun-sql – Incredible

However, using --add-cloudsql-instances should not crash gcloud. – Incredible

Worth noting this is explained in the official documentation at Cloud Run: cloud.google.com/run/docs/configuring/connect-cloudsql. – Babita

My Cloud SQL instance is outside us-central1, I have followed the instructions (gcloud beta run deploy --image [IMAGE] --add-cloudsql-instances [CONNECTION-NAME]) but there is no /cloudsql directory. I have also tried --set-cloudsql-instances cloudrun-sql as per @john-hanley – Ardeth

The automatic setup does not work for me. I can see the /cloudsql mount (none on /cloudsql type 9p (rw)) but there's nothing in there. If I install cloud_sql_proxy and manually run cloud_sql_proxy -instances="[CONNECTION-NAME]" -dir=/tmp , then the socket is there in /tmp. – Elenore

The --add-cloudsql-instances works fine for me. Please ensure gcloud is up to date and open an issue at cloud.google.com/support/docs/issue-trackers with exact reproduction steps. – Gayn

I was also successfully able to connect to a Cloud SQL instance in another region. Please provide exact reproduction steps and code sample in a new StackOverflow question – Gayn

Does this support the postgres IAM integration? – Nephology

Answer 2

CONNECTING FROM CLOUD RUN (fully managed) TO CLOUD SQL USING UNIX DOMAIN SOCKETS (Java)

At this time Cloud Run (fully managed) does not support connecting to the Cloud SQL instance using TCP. Your code should not try to access the instance using an IP address such as 127.0.0.1 or 172.17.0.1. link

1.Install and initialize the Cloud SDK

2.Update components:

gcloud components update

3.Create a new project

gcloud projects create run-to-sql
gcloud config set project run-to-sql
gcloud projects describe run-to-sql

4.Enable billing

gcloud alpha billing projects link  run-to-sql --billing-account  XXXXXX-XXXXXX-XXXX

5.Set the compute project-info metadata:

gcloud compute project-info describe --project run-to-sql
gcloud compute project-info add-metadata --metadata google-compute-default-region=europe-west2,google-compute-default-zone=europe-west2-b

6.Enable the Cloud SQL Admin API:

 gcloud services enable sqladmin.googleapis.com

7.Create a Cloud SQL instance with public Ip

#Create the sql instance in the same region as App Engine Application
gcloud --project=run-to-sql beta sql instances create database-external --region=europe-west2
#Set the password for the "root@%" MySQL user:
gcloud sql users set-password root --host=% --instance database-external --password root 
#Create a user
gcloud sql users create user_name --host=% --instance=database-external  --password=user_password
#Create a database
gcloud sql databases create user_database --instance=database-external
gcloud sql databases list --instance=database-external
gcloud sql instances list

Cloud Run (fully managed) uses a service account to authorize your connections to Cloud SQL. This service account must have the correct IAM permissions to successfully connect. Unless otherwise configured, the default service account is in the format [email protected].

8.Ensure that the service account for your service has one of the following IAM roles:Cloud SQL Client (preferred)

gcloud iam service-accounts list
gcloud projects add-iam-policy-binding run-to-sql --member serviceAccount:[email protected]. --role roles/cloudsql.client

9.Clone the java-docs-repository

git clone https://github.com/GoogleCloudPlatform/java-docs-samples.git
cd java-docs-samples/cloud-sql/mysql/servlet/
ls
#Dockerfile  pom.xml  README.md  src

10.Inspect the file that handle the connection to Cloud SQL

 cat src/main/java/com/example/cloudsql/ConnectionPoolContextListener.java

11.Containerizing the app and uploading it to Container Registry

gcloud builds submit --tag gcr.io/run-to-sql/run-mysql

12.Deploy the service to Cloud Run

gcloud run deploy run-mysql --image gcr.io/run-to-sql/run-mysql

13.Configure the service for use with Cloud Run

gcloud run services update run-mysql --add-cloudsql-instances run-to-sql:europe-west2:database-external --set-env-vars CLOUD_SQL_CONNECTION_NAME=run-to-sql:europe-west2:database-external  DB_USER=user_name,DB_PASS=user_password,DB_NAME=user_database

14.Test it

 curl -H "Authorization: Bearer $(gcloud auth print-identity-token)"   https://run-mysql-xxxxxxxx-xx.x.run.app

SUCCESS!

Lobbyist

Answer 3

I was facing an issue with connecting from a dockerized FastApi application to CloudSQL via private ip. I took the following 3 steps to resolve my issue:

1. Ensure your application is utilizing the proper database-connection-string.

   • Sanity check, always do this first. You don't want to spend hours researching a solution without first ruling out a wrong connection string.
   • When testing (and only when testing): consider logging the db connection string on app init so you can explicitly confirm your connection string is correct.

2. Provide the Cloud SQL Client role to my cloudrun default service account.
   

   • Contains the following permissions:
     cloudsql.instances.connect
cloudsql.instances.get

3. Create a VPC connector within the network of the database (documentation). And assign the VPC connector to the Cloud Run service.

Reedbuck

I configured this a few weeks back and noticed that the VPC Connector was charging me. Is there a way to do this without the VPC connector? Have you noticed some costs for the VPC connector? – Allan