Cloud SQL Auth proxy demystified
By Mike Pietruszka, SADA Sr. Cloud Infrastructure Engineer & Jake Luna, SADA Cloud Security Engineer
Introduction
While relational databases still offer basic authentication, many of our clients are realizing that this isn’t sufficient or secure enough in today’s complex cloud environments. Some of the commonly stated complaints are lack of two-factor authentication, easier susceptibility to leaked passwords, as well as missing credential rotation capabilities. In order to simplify and secure authorization and authentication into database instances, Google Cloud has developed IAM Database Authentication that supports basic and IAM authentication. Along with this capability, Google released Cloud SQL Auth proxy to provide a secure path for clients to log into their database while eliminating other hurdles.
In-Depth
What is Cloud SQL Auth proxy?
Cloud SQL Auth proxy is used to create secure connections to Cloud SQL database instances. With Cloud SQL Auth proxy, you can specify a database instance connection string and it will handle secure connectivity to the instance for you. The proxy will use automatically-managed short-lived SSL certificates that will ensure that your connectivity is authorized and encrypted. The proxy will then allow you to use Identity and Access Management (IAM) authentication to log into the database by generating an OAuth 2.0 token for your Cloud Identity.
With Cloud SQL Auth proxy, you do not have to worry much about setting up authorized networks, managing certificates, and in some cases, worrying about rotating user credentials. Think of it as a secure way to access your Cloud SQL database instances.
How the proxy works
Cloud SQL Auth proxy is a binary that you run on your local client machine. The proxy initiates a connection using a secure tunnel (TLS with 128-bit cipher) to the proxy service running in Cloud SQL. This server-side proxy service then connects to your SQL instance on the outgoing port tcp/3307. If your resource is behind an outgoing firewall rule, ensure that this rule allows tcp/3307 to your Cloud SQL instance.
It is important to note that the proxy will only create one connection to the Cloud SQL instance and does not provide connection pooling. A connection pool is a set of connections to the database that can be shared and reused to improve performance of the connection. This pool is used by applications to return unneeded connections to the database so they can be reused at a later time. This is something that you will need to handle on your end using client-libraries (like SQLAlchemy). For Python, Google Cloud Platform has released a Cloud SQL Python Connector which utilizes SQLAlchemy for that purpose. Check it out on GitHub if you are interested in connecting to Cloud SQL using Python applications.
Logging In
In order to log in to database instances, you have the option to do so as a local database user or using your Cloud Identity (part of Google Workspace). If you choose to go with basic authentication, you will have to create a local database user and manage or rotate this user’s credentials. This is rather cumbersome, as providing users with credentials and ensuring that they’re reset creates a management burden.
If you choose to use your Cloud Identity, you will use OAuth 2.0 for the authentication and authorization. This is a framework used by Google APIs and it is widely adopted at a global scale. In fact, even the gcloud CLI command uses OAuth 2.0 under the hood. In addition, Google Cloud IAM is used to provide permissions for Cloud Identities such as Google Accounts or IAM Service Accounts. The gcloud command can use either Google Account or Service Account as its cloud identity. Therefore, if you are using IAM authentication for logging into Cloud SQL, you will be using OAuth2.0. OAuth 2.0 access tokens are valid for one hour.
Proxy can be configured to run with automatic IAM database authentication or manual IAM database authentication. If you’re using automatic authentication, the proxy will automatically get the OAuth 2.0 credentials for you. The proxy’s binary must be launched with the -enable_iam_login flag. You must use the same Cloud Identity as the one that will authenticate to the database.
In the manual process, you have to manually request an OAuth 2.0 token with a Cloud SQL Admin API scope and present it to the database. This can be simply accomplished with the gcloud command if you are accessing the database from the command line. Your Cloud Identity email is used as the username and your access token is used as the password. If the Cloud Identity is an IAM Service Account, omit the .gserviceaccount.com domain suffix.
Note that Cloud SQL Auth proxy should be launched with the same Cloud Identity as the one that will be logging into the database instance. This means that every unique client that will log into the database will need to run and connect through the proxy.
Best Practices
Here are some best practices that are recommended for use with Cloud SQL Auth proxy.
Log when local/basic auth is used to login
With Cloud SQL Auth proxy, users can log into the database instances using either password or IAM authentication. IAM database authentication is recommended as a security best practice. Unfortunately Cloud SQL does not have a constraint policy to force everyone to use IAM authentication. As a compensating control, it is recommended to log and monitor when users utilizing basic authentication are created and when they log into a database instance. This way you can catch anyone logging into the database outside of the IAM bounds. The following queries will show these logins.
Query user logins to determine who is logging in:
resource.type="cloudsql_database"
protoPayload.methodName = "cloudsql.instances.login"Query to see creation of basic auth users (BUILT_IN type):
resource.type="cloudsql_database"
protoPayload.authorizationInfo.permission="cloudsql.users.create"
NOT (protoPayload.request.body.type = "CLOUD_IAM_USER" OR protoPayload.request.body.type = "CLOUD_IAM_SERVICE_ACCOUNT")
protoPayload.request.@type = "type.googleapis.com/google.cloud.sql.v1beta4.SqlUsersInsertRequest"Consider permissions when running the proxy
Your Cloud SQL Auth proxy should run using the same Cloud Identity (such as a Google Workspace user or IAM Service Account) as the one that connects to the database instance. Your Cloud Identity must have permissions equivalent to those found in the Cloud SQL Client (roles/cloudsql.client) and Cloud SQL Instance User (roles/cloudsql.instanceUser) IAM roles. Please note that if the Cloud Identity is an IAM Service Account you must omit the .gserviceaccount.com domain suffix when connecting to a database instance.
Use a bastion host
It is highly recommended to run your Cloud SQL instances on a private network and avoid using a public interface. However this makes it harder for database administrators to access their private SQL instances from their workstations. Therefore using a bastion host that permits connectivity from allowed clients is a viable solution for this problem.
A bastion host is a server that is exposed to the public networks and that allows clients to use it as a jumphost to get to private networks. Bastion hosts typically have a higher degree of security and scrutiny. They may limit concurrent or rapidly created connections, use additional authentication measures (such as MFA), and log every command typed into the terminal. These hosts may also run additional Host Intrusion Detection Systems (HIDS) and other security tools to ensure that no malicious activity happens. They’re used throughout the IT industry for remotely accessing infrastructure.
Use SSH tunneling to connect tools such as MySQL Workbench or PGadmin
SSH tunneling (also known as port forwarding) is the ability to securely connect client applications to internal services by using a jumphost. With tunneling, the traffic between the client and the server instance is encrypted using SSH.
With this in mind, you can use SSH tunneling to connect through a bastion host to the Cloud SQL instance. We can then use a tool like MySQL Workbench or PGadmin to connect to a localhost port that is forwarded to the server. The traffic between the client and the database instance is then encrypted using SSH. This way, encrypted client traffic travels through the bastion host to the Cloud SQL instance.
To get this setup in PGadmin you will navigate to the SSH Tunnel tab and enter the tunnelhost IP address, port 22, and oslogin username along with the private key identity file.
In MySQL Workbench, you can specify the bastion host as the SSH Hostname and use 127.0.0.1 under MySQL Hostname.
Otherwise, you can use the following OpenSSH command to create a tunnel and then use your favorite CLI database client to connect to 127.0.0.1 on local port:
$ ssh -L $LOCAL_IP:$LOCAL_PORT:$DESTINATION:$DESTINATION_PORT $USER@$BASTION_HOSTFor added security, we can use Google Cloud’s OSLogin feature that utilizes Google Cloud IAM to manage SSH key-based access to Google Compute Engine instances and then use the gcloud command to create the tunnel. OSLogin will sync an SSH public key to your instance for you. From there, you can create an SSH tunnel to your bastion host.
$ gcloud compute os-login ssh-keys add -key-file=.ssh/pgadmin.pub
$ gcloud compute ssh $username@$instance_name \
--zone=us-central1-a \
--ssh-flag="-NL 5432:localhost:5432Use Private Services Access
You can use Private Services Access to connect your VPC resources to the private Cloud SQL instance. This works by creating a VPC peering connection between your VPC network and the Google-managed VPC network that runs your Cloud SQL instance. Your VPC workloads then use their internal IP addresses to connect to the SQL instances. This is a great way to avoid forwarding your sensitive traffic through the Internet and keep everything within your managed virtual network.
Use Serverless VPC Access Connector
Serverless VPC Access Connector allows you to connect your serverless workloads to the Cloud SQL instances using private IP addressing without exposing your connections to the Internet. These workloads consist of Cloud Functions, Cloud Run containers, or App Engine Standard applications.
With the Serverless VPC Access Connector, your serverless workloads use a resource called connector that lives in your VPC network. This connector forwards outbound network traffic from the serverless environment to your VPC. These requests can then use Private Services Access to connect to the Cloud SQL instances.
It is important to note that the VPC Access Connector requires a separate, unused /28 CIDR block in order to create a subnetwork in your VPC. If you use a Shared VPC, your Shared VPC Administrator will need to create a /28 subnetwork and attach it to your service project.
In addition, there are some IAM considerations if you are using serverless workloads with the Serverless VPC Access Connector.
Serverless VPC Access uses the Serverless VPC Access Service Agent service account (service-$PROJECT_NUMBER@gcp-sa-vpcaccess.iam.gserviceaccount.com) to perform operations in the project. This service account will need to be bound to the roles/vpcaccess.serviceAgent IAM role that is typically enabled by default when you enable the Serverless VPC Access API. Its presence can be viewed in the IAM console or using the gcloud command:
$ gcloud projects get-iam-policy $PROJECT_ID \
--flatten="bindings[].members" \--filter="bindings.members=serviceAccount:service-012345678901@gcp-sa-vpcaccess.iam.gserviceaccount.com"
---
bindings:
members: serviceAccount:service-012345678901@gcp-sa-vpcaccess.iam.gserviceaccount.com
role: roles/vpcaccess.serviceAgent
etag: XXXXXXXXX=
version: 1
There are additional IAM bindings needed if you use a shared VPC and your VPC Access connector is attached to one of the subnetworks that belong to a host project. You can keep your VPC Access connector in either the host or service projects but there are important IAM policy bindings needed in both cases.
If the connector is in the host project, you will need the following binding:
$ gcloud projects add-iam-policy-binding $HOST_PROJECT_ID \
--member=serviceAccount:service-SERVICE_PROJECT_NUMBER@gcf-admin-robot.iam.gserviceaccount.com \
--role=roles/vpcaccess.userIf the connector is in the service project, you will need the following bindings:
$ gcloud projects add-iam-policy-binding HOST_PROJECT_ID \
--role "roles/compute.networkUser" \
--member "serviceAccount:service-SERVICE_PROJECT_NUMBER@gcp-sa-vpcaccess.iam.gserviceaccount.com"
$ gcloud projects add-iam-policy-binding HOST_PROJECT_ID \
--role "roles/compute.networkUser" \
--member "serviceAccount:SERVICE_PROJECT_NUMBER@cloudservices.gserviceaccount.com"Run Cloud SQL Auth proxy as a sidecar container to connect to GKE
By running Cloud SQL Auth proxy in a Kubernetes cluster, you can offload the burden of having to authenticate against a database instance since the proxy will be doing the authentication for your application containers.
Google Cloud Platform has a really nice demo about integrating GKE to Cloud SQL Auth proxy in order to allow the pods to connect to Cloud SQL. In this demo, Cloud SQL Auth proxy is run as a sidecar container. Sidecar containers are small containers that run in the same pod as your application. This provides numerous advantages, such as:
- Places access to the database close to your application, as each Pod has independent database access.
- Provides encryption to database connectivity from your applications.
- Limits access to the Cloud SQL Auth proxy from the entire cluster.
Sidecar and application containers can share volumes where you can share Service Account credentials that are stored as secrets. If you are using GKE, you can also use Workload Identity to avoid sharing secrets between containers in your pods. This is more secure and allows you to bind Kubernetes Service Accounts to Google Service Accounts.
Setup and Usage
Let’s look at the installation instructions for a 64-bit Linux operating system. Instructions for other operating systems can be found in the Google Cloud Auth proxy documentation.
Download the proxy from Google and make the binary executable:
$ wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
$ chmod +x cloud_sql_proxyRun the proxy with appropriate flags:
$ ./cloud_sql_proxy \
-instances=$PROJECT:$REGION:$INSTANCE_NAME=tcp:127.0.0.1:5432 \
-enable_iam_login \
-verboseSome notes about the option flags:
-instances— Lets you specify which Cloud SQL instances that you would like to access. You can specify multiple instances with a comma (for example,$PROJECT:$REGION:$INSTANCE_NAME1=tcp:127.0.0.1:5432,$PROJECT:$REGION:$INSTANCE_NAME2=tcp:127.0.0.1:5433). You can also specify which IP address and TCP port on your local machine to use for the binding.-enable_iam_login— Forces you to use IAM authentication. Omit it if you’d like to log using basic authentication.
Add Cloud Identity user or Service Account to the Cloud SQL instance. This can be done in the Cloud SQL console or by using gcloud CLI or Google API.
Next up, you should be able to connect to the database instance with a client application. Please note that if you are using an IAM Service Account, you will need to omit the .gserviceaccount.com domain suffix. If you are using basic authentication, you will need to provide the--password flag.
$ PGPASSWORD=$(gcloud auth print-access-token) psql \
--host=127.0.0.1 \
--username=$CLOUD_IDENTITY \
--dbname=postgresIn lieu of using psql, you can also use an SDK in your programming language of choice to initiate a connection. Google Cloud Platform has various libraries such as cloud-sql-python-connector for Python or cloud-sql-go-connector for Golang. Both help orchestrate connectivity between your application and Cloud SQL database instances.
Troubleshooting Problems
Here are some tips for a few common problems.
Connection timed out
This generally means that the connectivity to your Cloud SQL database instance could not be established. There are couple things to check when encountering this condition:
- Ensure that there are firewall rules configured to allow connectivity from the client to the bastion host.
- Verify that any outgoing firewall policies on the bastion host include tcp/3307 to your Cloud SQL instance’s private IP address.
Connection reset
When you receive this error, verify that your client is connecting to the right ports specified by the Cloud SQL Auth proxy. The Cloud SQL Auth proxy flag-instances lets you specify which Cloud SQL instances that you would like to access. The port number in $PROJECT:$REGION:$INSTANCE_NAME1=tcp:127.0.0.1:5432 specifies a TCP port on which the local end of the proxy’s connection to the database will be available. The proxy will connect to the server-side end of Cloud SQL proxy on TCP port 3307 (which is somewhat confusing as MySQL listens on TCP port 3306).
IAM authentication failure
There are two things to check with this failure condition:
- Cloud SQL Auth proxy needs to run as the Cloud Identity that is trying to log into the Cloud SQL instance. Verify that you are logged with the command
gcloud auth listorgcloud config configurations list. - Verify that the Cloud Identity has an IAM user created in the Cloud SQL console. Ensure that this Cloud Identity has a LOGIN role assigned to it if it’s a PostgreSQL instance.
Conclusion
There you have it. Cloud SQL Auth proxy is a powerful tool that will help you efficiently manage your secure connections to your Cloud SQL database. Hopefully this article has helped you understand how this tool works and given you some ideas on how you can make it work for you. For more information, check out the links in the References section, below.
References
About Mike & Jake
Mike Pietruszka is a Senior Cloud Infrastructure Engineer with deep enterprise experience who loves building out serverless and DevSecOps solutions.
Jake Luna is a Cloud Security Engineer who enjoys the challenge of implementing secure solutions in complex environments. In his spare time he likes to garden and spend time with his chickens.
About SADA
At SADA, we climb every mountain, clear every hurdle, and turn the improbable into possible — over and over again. Simply put, we propel your organization forward. It’s not enough to migrate to the cloud, it’s what you do once you’re there. Accelerating application development. Advancing productivity and collaboration. Using your data as a competitive edge. When it comes to Google Cloud, we’re not an add-on, we’re a must-have, driving the business performance of our clients with its power. Beyond our expertise and experience, what sets us apart is our people. It’s the spirit that carried us from scrappy origins as one of the Google Cloud launch partners to an award-winning global partner year after year. With a client list that spans healthcare, media and entertainment, retail, manufacturing, public sector and digital natives — we simply get the job done, every step of the way. Visit SADA.com to learn more.
If you’re interested in becoming a part of the SADA team, please visit our careers page.
