Google Cloud SQL with IAM Authentication for GKE

Disclaimer

Most of the instructions in this guide are specific to Google Cloud Platform (GCP) infrastructure setup rather than Zymtrace itself. We've documented our own experience setting up Cloud SQL with GKE to make it easier for users who want to use this configuration. The zymtrace-specific configuration is minimal - it's primarily about connecting to your PostgreSQL database once it's properly configured in GCP.

The Cloud SQL Proxy acts as a secure tunnel, handling IAM authentication and encryption transparently while providing Zymtrace with a standard PostgreSQL connection. This guide demonstrates how to set up Google Cloud SQL PostgreSQL with IAM authentication for use with GKE clusters. This configuration provides secure, password-less database access using Google Cloud's Workload Identity.

Info

This document assumes you're setting up Cloud SQL from scratch. If you already have an existing Cloud SQL instance or GKE cluster, feel free to skip to the relevant sections that apply to your setup.

Overview

What You'll Accomplish

By following this guide, you will:

Create a Cloud SQL PostgreSQL instance with IAM authentication
Set up Workload Identity between GKE and Cloud SQL
Configure automatic database creation (optional)
Deploy Zymtrace that securely connects to Cloud SQL without managing passwords

Architecture

┌─────────────────┐    ┌──────────────────┐    ┌─────────────────┐
│   GKE Pod       │───▶│  Cloud SQL Auth  │───▶│   Cloud SQL     │
│                 │    │      Proxy       │    │   PostgreSQL    │
│ zymtrace        │    │                  │    │                 │
│ K8s SA          │    │ IAM Auth         │    │ IAM User        │
└─────────────────┘    └──────────────────┘    └─────────────────┘
        │                        │                       │
        └────────────────────────┼───────────────────────┘
                    Workload Identity Binding

Prerequisites

Before you begin, ensure you have:

Requirement	Description
GKE Cluster	Running with Workload Identity enabled (or ability to enable it)
CLI Tools	`gcloud` and `kubectl` installed and authenticated
Permissions	IAM roles for Cloud SQL, GKE, and service account management

Required IAM Roles

Your user account needs these roles:

roles/cloudsql.admin - To create and manage Cloud SQL instances
roles/container.admin - To manage GKE clusters and node pools
roles/iam.serviceAccountAdmin - To create and manage service accounts
roles/resourcemanager.projectIamAdmin - To bind IAM policies

Configuration

Environment Setup

First, set up your environment variables. Replace the example values with your actual project details:

# Project and instance configuration
export PROJECT_ID="your-project-id"
export REGION="us-central1"
export INSTANCE_NAME="zymtrace-postgres"
export DATABASE_VERSION="POSTGRES_17"

# GKE configuration
export CLUSTER_NAME="your-gke-cluster"
export NAMESPACE="your-namespace"

# Service account configuration
export SA_NAME="cloudsql-proxy-sa"
export K8S_SA_NAME="cloudsql-k8s-sa"

Implementation Steps

Step 1: Create Cloud SQL Instance

Create a new PostgreSQL instance with recommended settings for production use:

gcloud sql instances create $INSTANCE_NAME \
  --database-version=$DATABASE_VERSION \
  --region=$REGION \
  --cpu=2 \
  --memory=7680MB \
  --storage-size=50GB \
  --storage-type=SSD \
  --storage-auto-increase \
  --enable-bin-log \
  --maintenance-release-channel=production \
  --deletion-protection \
  --project=$PROJECT_ID

Set a password for the default postgres user (required for initial setup):

gcloud sql users set-password postgres \
  --instance=$INSTANCE_NAME \
  --password="$(openssl rand -base64 32)"

tip

The postgres password is only used for initial database setup. Zymtrace will use IAM authentication.

Step 2: Configure Service Accounts

Create a Google Cloud service account for Cloud SQL access:

# Create the service account
gcloud iam service-accounts create $SA_NAME \
  --display-name="zymtrace Cloud SQL Proxy Service Account" \
  --description="Enables zymtrace IAM authentication to Cloud SQL from GKE"

# Grant required Cloud SQL permissions
gcloud projects add-iam-policy-binding $PROJECT_ID \
  --member="serviceAccount:$SA_NAME@$PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/cloudsql.client"

gcloud projects add-iam-policy-binding $PROJECT_ID \
  --member="serviceAccount:$SA_NAME@$PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/cloudsql.instanceUser"

Step 3: Database Setup

Create IAM Database User

Create a database user that corresponds to your service account:

gcloud sql users create $SA_NAME@$PROJECT_ID.iam \
  --instance=$INSTANCE_NAME \
  --type=cloud_iam_service_account

Configure Database Permissions

There are two approaches for setting up database permissions, depending on whether you want to use automatic database creation or manual database creation.

Option 1: Using autoCreateDB Mode (recommended, if possible)

If you plan to use autoCreateDBs: true in your Helm configuration, you only need to grant the CREATEDB permission. The databases will be created automatically by Zymtrace on startup.

Connect to your instance:

# Connect as postgres user
gcloud sql connect $INSTANCE_NAME --user=postgres --database=postgres

Run this SQL command:

-- Grant CREATEDB permission (allows zymtrace to create databases automatically)
ALTER USER "your-sa-name@your-project.iam" CREATEDB;

-- Exit psql
\q

That's it! When you deploy Zymtrace with autoCreateDBs: true, the required databases (zymtrace_identity, zymtrace_symdb, and zymtrace_web) will be created automatically with the correct permissions.

Option 2: Using Manual Database Creation Process

If you prefer to manually create and manage databases (using autoCreateDBs: false), follow these steps:

Connect to your instance:

# Connect as postgres user
gcloud sql connect $INSTANCE_NAME --user=postgres --database=postgres

Run these SQL commands:

-- Step 1: Create the databases manually

CREATE DATABASE zymtrace_identity OWNER your-sa-name@your-project.iam;
CREATE DATABASE zymtrace_symdb OWNER your-sa-name@your-project.iam;
CREATE DATABASE zymtrace_web OWNER your-sa-name@your-project.iam;

-- Step 2: Alter schema

\c zymtrace_identity
ALTER SCHEMA public OWNER TO "your-sa-name@your-project.iam";

\c zymtrace_symdb
ALTER SCHEMA public OWNER TO "your-sa-name@your-project.iam";

\c zymtrace_web
ALTER SCHEMA public OWNER TO "your-sa-name@your-project.iam";

-- Exit psql
\q

Database Creation Issues

The above SQL commands don't always work consistently in Cloud SQL with IAM users. If you encounter permission errors when creating databases with the IAM user as owner, try this:

Run this command first as a superuser (postgres or admin user):

GRANT "your-sa-name@your-project.iam" TO postgres;

Then proceed with the original database creation commands.

Create the databases first, then change ownership:

-- Create databases without specifying owner
CREATE DATABASE zymtrace_identity;
CREATE DATABASE zymtrace_symdb;
CREATE DATABASE zymtrace_web;

-- Then alter ownership
ALTER DATABASE zymtrace_identity OWNER TO "your-sa-name@your-project.iam";
ALTER DATABASE zymtrace_symdb OWNER TO "your-sa-name@your-project.iam";
ALTER DATABASE zymtrace_web OWNER TO "your-sa-name@your-project.iam";

--  Then alter schema ownership

\c zymtrace_identity
ALTER SCHEMA public OWNER TO "your-sa-name@your-project.iam";

\c zymtrace_symdb
ALTER SCHEMA public OWNER TO "your-sa-name@your-project.iam";

\c zymtrace_web
ALTER SCHEMA public OWNER TO "your-sa-name@your-project.iam";

If these approaches still don't work, consider contacting GCP support.

info

Important**: Replace your-sa-name@your-project.iam with your actual service account name (e.g., cloudsql-proxy-sa@your-project.iam).

Step 4: Configure GKE Workload Identity

Verify Node Pool Scopes

Check if your node pool has the required OAuth scopes:

gcloud container node-pools describe default-pool \
  --cluster=$CLUSTER_NAME \
  --region=$REGION \
  --format="value(config.oauthScopes)"

The output should include https://www.googleapis.com/auth/cloud-platform.

Node pool scope limitation

If your existing node pool doesn't have the cloud-platform scope, you cannot update it after creation. Node pool scopes are immutable. You'll need to create a new node pool with the correct scopes.

Create Node Pool with Required Scopes (if needed)

If your existing node pool lacks the required scopes, create a new node pool specifically for the Cloud SQL proxy:

# Create a new node pool with cloud-platform scope
gcloud container node-pools create cloudsql-pool \
  --cluster=$CLUSTER_NAME \
  --region=$REGION \
  --scopes=https://www.googleapis.com/auth/cloud-platform \
  --num-nodes=1 \
  --machine-type=e2-medium \
  --enable-autorepair \
  --enable-autoupgrade

Add the node selector to your custom-values.yaml to ensure the proxy pod is scheduled on this new node.

postgres:
  mode: "gcp_cloudsql" 
  nodeSelector:
    cloud.google.com/gke-nodepool: cloudsql-pool

Enable Workload Identity

If not already enabled, enable Workload Identity on your cluster:

# Check current status
gcloud container clusters describe $CLUSTER_NAME \
  --region=$REGION \
  --format="value(workloadIdentityConfig.workloadPool)"

# Enable if needed (takes 10-30 minutes)
gcloud container clusters update $CLUSTER_NAME \
  --workload-pool=$PROJECT_ID.svc.id.goog \
  --region=$REGION

Set Up Identity Binding

Create Kubernetes resources and bind them to Google Cloud:

# Create namespace
kubectl create namespace $NAMESPACE

# Create Kubernetes service account
kubectl create serviceaccount $K8S_SA_NAME -n $NAMESPACE

# Annotate for Workload Identity
kubectl annotate serviceaccount $K8S_SA_NAME -n $NAMESPACE \
  iam.gke.io/gcp-service-account=$SA_NAME@$PROJECT_ID.iam.gserviceaccount.com

# Create the binding
gcloud iam service-accounts add-iam-policy-binding \
  --role roles/iam.workloadIdentityUser \
  --member "serviceAccount:$PROJECT_ID.svc.id.goog[$NAMESPACE/$K8S_SA_NAME]" \
  $SA_NAME@$PROJECT_ID.iam.gserviceaccount.com

Verification and Testing

Verify Workload Identity

Check that Workload Identity is properly configured:

# Verify cluster configuration
gcloud container clusters describe $CLUSTER_NAME \
  --region=$REGION \
  --format="value(workloadIdentityConfig.workloadPool)"

# Check service account annotation
kubectl get serviceaccount $K8S_SA_NAME -n $NAMESPACE -o yaml

# Verify IAM binding
gcloud iam service-accounts get-iam-policy $SA_NAME@$PROJECT_ID.iam.gserviceaccount.com

Test Database Connection

Create a test pod to verify connectivity:

kubectl run postgres-test --rm -it --image=postgres:17 -n $NAMESPACE -- bash

Inside the test pod, connect to the database:

psql -h postgres-proxy -p 5432 -U "your-sa@your-project.iam" -d zymtrace_identity

Run test queries:

-- Verify connection and user
SELECT current_user, current_database();

Helm Install

After completing the verification and testing steps above, you need to configure your Helm deployment with the nodeSelector to ensure the Cloud SQL proxy runs on the node pool with proper scopes.

Configure custom-values.yaml

Add the nodeSelector configuration to your custom-values.yaml:

postgres:
  mode: "gcp_cloudsql" 
  gcp_cloudsql:
    instance: "PROJECT_ID:REGION:INSTANCE_NAME"  # e.g., "local-bebop-448118-g4:us-central1:zymtrace-cloudsql-psql-1"
    user: "SERVICE_ACCOUNT_NAME@PROJECT_ID.iam"  # e.g., "cloudsql-proxy-sa@local-bebop-448118-g4.iam"
    database: "zymtrace"
    autoCreateDBs: true  # Set to false if you manually created databases
    workloadIdentity:
      enabled: true
    proxy:
      # IMPORTANT: Use nodeSelector to ensure proxy runs on node pool with cloud-platform scope
      nodeSelector:
        cloud.google.com/gke-nodepool: cloudsql-pool
    serviceAccount: "cloudsql-k8s-sa"  # Your Kubernetes service account name

Deploy and Restart

Deploy your Helm chart with the updated configuration:

# Deploy or upgrade with the new configuration
helm upgrade --install backend zymtrace/backend \
  -f custom-values.yaml \
  -n $NAMESPACE \
  --debug \
  --atomic

# Restart the proxy deployment to ensure it gets scheduled on the correct node pool
kubectl rollout restart deployment zymtrace-postgres-proxy -n $NAMESPACE

Verify Proxy Deployment

Check that the proxy is running on the correct node pool:

# Check which node the proxy is running on
kubectl get pods -l app=postgres-proxy -n $NAMESPACE -o wide

# Verify the node has the correct node pool label
kubectl get nodes -l cloud.google.com/gke-nodepool=cloudsql-pool

For complete YAML configuration examples, refer to the storage configuration guide.

Common Error Without nodeSelector

If you don't configure the nodeSelector properly, you'll see authentication scope errors like:

025/05/26 13:31:09 [xx:us-central1:zymtrace-cloudsql-psql-1] failed to connect to instance: failed to get instance: Refresh error: failed to get instance metadata (connection name = "xx:us-central1:zymtrace-cloudsql-psql-1"): googleapi: Error 403: Request had insufficient authentication scopes.
Details:
[
  {
    "@type": "type.googleapis.com/google.rpc.ErrorInfo",
    "domain": "googleapis.com",
    "metadata": {
      "method": "google.cloud.sql.v1beta4.SqlConnectService.GetConnectSettings",
      "service": "sqladmin.googleapis.com"
    },
    "reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT"
  }
]
More details:
Reason: insufficientPermissions, Message: Insufficient Permission

This happens because the proxy is running on a node pool without the cloud-platform scope. The nodeSelector ensures the proxy runs on the correct node pool.

Troubleshooting

Common Issues

Issue	Symptoms	Solution
Authentication Scope Error	"Insufficient authentication scopes"	Verify node pool has `cloud-platform` scope
Permission Denied	Database connection fails	Check IAM database user and CREATEDB permission
Workload Identity Issues	Service account authentication fails	Verify annotation and IAM binding
Proxy Connection Fails	Cannot connect to postgres-proxy	Check proxy pod logs and configuration

Diagnostic Commands

# Check proxy pod status
kubectl get pods -l app=postgres-proxy -n $NAMESPACE

# View proxy logs
kubectl logs -l app=postgres-proxy -n $NAMESPACE

# Test Workload Identity from pod
kubectl run test-wi --rm -it --image=google/cloud-sdk:slim \
  --serviceaccount=$K8S_SA_NAME -n $NAMESPACE -- \
  gcloud auth list

Expected Proxy Logs

When the Cloud SQL proxy is working correctly, you should see log messages similar to this:

kubectl logs -n your-namespace zymtrace-postgres-proxy-6db65b7446-56nqp

Expected output:

2025/05/26 13:39:58 Authorizing with Application Default Credentials
2025/05/26 13:39:58 [local-bebop-448118-g4:us-central1:zymtrace-cloudsql-psql-1] Listening on [::]:5432
2025/05/26 13:39:58 The proxy has started successfully and is ready for new connections!
2025/05/26 13:40:14 [local-bebop-448118-g4:us-central1:zymtrace-cloudsql-psql-1] Accepted connection from 10.56.4.1:46902
2025/05/26 13:40:14 [local-bebop-448118-g4:us-central1:zymtrace-cloudsql-psql-1] client closed the connection

Overview​

What You'll Accomplish​

Architecture​

Prerequisites​

Required IAM Roles​

Configuration​

Environment Setup​

Implementation Steps​

Step 1: Create Cloud SQL Instance​

Step 2: Configure Service Accounts​

Step 3: Database Setup​

Create IAM Database User​

Configure Database Permissions​

Option 1: Using autoCreateDB Mode (recommended, if possible)​

Option 2: Using Manual Database Creation Process​

Step 4: Configure GKE Workload Identity​

Verify Node Pool Scopes​

Create Node Pool with Required Scopes (if needed)​

Enable Workload Identity​

Set Up Identity Binding​

Verification and Testing​

Verify Workload Identity​

Test Database Connection​

Helm Install​

Configure custom-values.yaml​

Deploy and Restart​

Verify Proxy Deployment​

Troubleshooting​

Common Issues​

Diagnostic Commands​

Expected Proxy Logs​