Passwordless Google Cloud SQL access control with Cloud SQL Auth proxy

Database user and permission management can prove to be a challenging task, particularly in large-scale environments with complex authorisation requirements.

As loveholidays matured from a startup to a scale up, we were faced with a variety of challenges related to database user management:

• engineers having excessive privileges;
• shared users amongst many applications;
• reliance on a centralised team for user management and IP/Network/Allowlist management, which can be slow and inefficient as the company grows;
• lack of auditability of database user and permission changes;
  manual password rotation / no password rotation.

The above challenges can be put into two categories: permission management and password management. This article will provide insights into how we migrated to passwordless access for Google Cloud SQL by adopting Google IAM authentication with Cloud SQL Auth proxy. Furthermore, it will explore how we manage database users and their permissions.

User Permissions management

For configuration, we use Infrastructure as Code (IaC) in YAML versioned in Git, instead of executing raw SQL queries. This makes the process transparent, auditable and repeatable.
In the following code snippet, you can see the definition of permissions for both the user account and the service account:

- username: engineer1@loveholidays.com
  permissions: &platform-team-permissions
   - database: “...”
     table: "*"
     privileges:
       - SELECT
       - SHOW VIEW
       - CREATE TEMPORARY TABLES
       - TRIGGER

   - database: "*"
     privileges:
       - PROCESS
       - RELOAD
       - SHOW DATABASES

- username: engineer2@loveholidays.com
  permissions: *platform-team-permissions

- username: wi-gsa@project.iam.gserviceaccount.com
  permissions: &service-account-read-write
   - database: "..."
     privileges:
       - SELECT
       - UPDATE
       - INSERT
       - DELETE
       - SHOW VIEW
       - CREATE TEMPORARY TABLES
       - TRIGGER
       - EXECUTE

- username: wi-gsa2@project.iam.gserviceaccount.com
  permissions: *service-account-read-write

- username: wi-gsa3@project.iam.gserviceaccount.com
  permissions: *service-account-read-write

...

When managing a considerable number of users across multiple database servers in different projects, this approach becomes highly effective.
YAML anchors can be used to define a set of permissions once and then reference them for additional users, without needing to repeat the permission set for each user. Additionally, using YAML anchors can help to avoid excessive copying and pasting, which can be error-prone and time-consuming.
Granting granular access to each user enables them to access only the required data to perform their job. Additionally, mapping applications on a 1:1 basis with service accounts simplifies access control, making it easier to manage and regulate what application has access to what data.

How the Cloud SQL Auth proxy works

Cloud SQL Auth proxy is a database connector that provides secure access to Cloud SQL instances without a need for authorised networks or for configuring SSL.

Cloud SQL Auth proxy has the following benefits:

• Secure connections: Cloud SQL Auth proxy automatically encrypts traffic to and from the database. SSL certificates are used to verify client and server identities, and are independent of database protocols; we won’t need to manage SSL certificates;
• Easier connection authorisation: Cloud SQL Auth proxy uses GCP IAM permissions to control who and what can connect to our Cloud SQL instances. Thus, Cloud SQL Auth proxy handles authentication with Cloud SQL, removing the need to provide static IP addresses;
• IAM database authentication: Cloud SQL Auth proxy supports an automatic refresh of OAuth 2.0 access tokens for passwordless authentication.

Learn more about the Cloud SQL Auth proxy and Cloud SQL IAM database authentication.

To establish a secure connection between a user’s local environment or Kubernetes POD and a Cloud SQL instance, a local Cloud SQL Auth proxy should be used or deployed as a sidecar container.

The communication between the application and Cloud SQL Auth Proxy is performed through the standard database protocol:

Cloud SQL Auth proxy with Google IAM Authentication

Using passwordless authentication for Google Cloud SQL is not a new concept and has already been implemented in Cloud SQL for PostgreSQL since version 9.6, as well as in recent Cloud SQL for Mysql 5.7.39+. The approach is based on the use of short-lived tokens for Google IAM user or kubernetes service accounts running on workload identity in GKE nodes.

To implement CloudSQL Auth Proxy with Google IAM authentication, we need to perform the following steps:

• Add user or service accounts to the database instance;
• Grant IAM roles within GCP to the user or service account to allow database instance login. Add the roles/cloudsql.instanceUser IAM role on the User account to perform this task. It is a predefined role that contains the necessary Cloud SQL IAM permission cloudsql.instances.login;
• Grant database/table/other object privileges using database specific SQL query.

Creating users

Creating users and granting IAM Roles is easy to do with the google terraform provider — google_sql_user and google_project_iam_member.

Grant permissions

To grant database privileges (e.g. table, schema, sequence, function) to a user or service account, we need to execute database specific SQL queries.

Despite the PostgreSQL Terraform provider support for connecting to a Cloud SQL Instance to manage permissions, we had to contribute (#48, #55) support for IAM Authentication to the petoju/terraform-provider-mysql to connect to the Cloud SQL MySQL Instance:

provider "mysql" {
 endpoint = "cloudsql://${var.connection_name}"
...
}

resource "mysql_grant" "this" {
 for_each = { for item in local.user_permissions : item.key => item }
 database   = each.value.database
 table      = each.value.table
 host       = each.value.host
 user       = each.value.username
 privileges = each.value.privileges
 grant      = each.value.grant
}

Having learned how to manage database users and their permissions, let’s now dive into how our Kubernetes workloads connect to Cloud SQL.

Kubernetes implementation

Our workloads are deployed on nodes that are enabled with Workload Identity. This allows us to configure a kubernetes service account (KSA) to act as a Google service account (GSA), which means that pods running with the KSA can automatically authenticate as the corresponding GSA when accessing Google Cloud APIs.

Service Account Workload Identity

All of our kubernetes service accounts have workload identity annotations:

apiVersion: v1
kind: ServiceAccount
metadata:
 annotations:
   iam.gke.io/gcp-service-account: wi-gsa@project.iam.gserviceaccount.com
 name: ksa-name

The Cloud SQL Auth Proxy container in each kubernetes application deployment will use this Google service account to access the Cloud SQL Instance:

apiVersion: apps/v1
kind: Deployment
metadata:
 name: application
spec:
 template:
   spec:
...
     serviceAccountName: ksa-name
     containers:
       - name: cloudsql-proxy
         image: gcr.io/cloudsql-docker/gce-proxy:1.33.1-alpine
         command:
           - /cloud_sql_proxy
           - -term_timeout=60s
           - -verbose=false
           - -enable_iam_login
         args:
           - -instances=project:region:name=tcp:3306

       - name: application
         env:
           - name: SPRING_DATASOURCE_URL
             value: jdbc:mysql://localhost:3306/db?autoReconnect=true
           - name: SPRING_DATASOURCE_USERNAME
             value: wi-gsa
           - name: SPRING_DATASOURCE_PASSWORD
             value: ""
...

Cloud SQL Auth Proxy with IAM Authentication

1. Cloud SQL Auth Proxy obtains a GSA token from the google metadata server;
2. SQL Proxy submits token to the Cloud SQL instance as the password attribute on behalf of the client;
3. The Cloud SQL Instance then validates this information with google IAM to establish the connection.

IAM database authentication tokens are short-lived and valid only for one hour. Cloud SQL auth proxy requests and renews this token, ensuring our applications have a stable connection.

Conclusion

Abstracting Cloud SQL user management with a custom terraform module and YAML based configuration has proven to be an efficient approach for granular access control and simplification of the database access management process. This is in line with one of the key objectives of our Platform Engineering team, which is to hide complexity from users by creating an abstraction layer between the user and the underlying infrastructure, allowing users to interact with the platform without needing to understand all of its technical details. It is also aligned with our engineering principle of “Invest in simplicity”. Moreover, it facilitates granular access control for users and applications with an audit trail in git history to see when and by whom a user and permission was granted.

Additionally, we have solved the problem of long-lived database credentials and shared database user accounts using workload identity and Google IAM. We have eliminated the need for password management, reducing the overhead of storing and rotating passwords.

Love engineering? We have a Site Reliability Engineering role open.