Google Cloud Workload Identity Federation: A Guide to Keyless Authentication for Multi-Cloud Environments

Introduction

In today's multi-cloud landscape, managing authentication credentials across different cloud providers has become a significant security challenge. Traditional approaches using long-lived service account keys pose serious risks: they can be leaked, stolen, or compromised, and they're difficult to rotate and audit effectively.

Google Cloud Workload Identity Federation solves this problem by enabling keyless authentication. It allows workloads running outside Google Cloud—such as AWS EC2 instances, Azure VMs, GitHub Actions, or on-premises systems—to securely access Google Cloud resources without managing service account keys.

In this comprehensive guide, we'll walk through a real-world scenario and implement Workload Identity Federation step-by-step.

The Scenario: Multi-Cloud CI/CD Pipeline

Company: TechFlow Inc., a fintech startup
Challenge: Their application runs on Google Cloud (Cloud Run, Cloud SQL PostgreSQL), but their CI/CD pipelines run on GitHub Actions. They need to deploy applications and run database migrations on Cloud SQL from GitHub workflows without storing service account keys in GitHub secrets.

Current Problem:

Service account keys stored in GitHub Secrets
Keys need manual rotation every 90 days
Risk of key exposure if repository is compromised
Compliance concerns about credential management
Database credentials management complexity

Goal: Implement Workload Identity Federation to enable GitHub Actions to authenticate to Google Cloud using OIDC tokens, eliminating the need for service account keys entirely.

Architecture Overview

Here's how Workload Identity Federation works:

GitHub Actions generates an OIDC token containing claims about the workflow (repository, branch, actor)
Workload Identity Pool in Google Cloud validates the token
Workload Identity Provider maps the external identity to a Google Cloud service account
GitHub Actions receives short-lived Google Cloud credentials
Workflow uses credentials to access Google Cloud resources

┌─────────────────┐         ┌──────────────────────────┐
│  GitHub Actions │         │  Google Cloud            │
│                 │         │                          │
│  1. Get OIDC    │────────▶│  2. Workload Identity    │
│     Token       │         │     Pool validates token │
│                 │         │                          │
│  4. Receive     │◀────────│  3. Map to Service       │
│     GCP Token   │         │     Account              │
│                 │         │                          │
│  5. Access GCP  │────────▶│  6. Cloud Run, Cloud SQL │
│     Resources   │         │     (authorized access)  │
└─────────────────┘         └──────────────────────────┘

Prerequisites

Before we begin, ensure you have:

A Google Cloud project with billing enabled
gcloud CLI installed and configured
Appropriate IAM permissions:
- roles/iam.workloadIdentityPoolAdmin
- roles/iam.serviceAccountAdmin
- roles/resourcemanager.projectIamAdmin
A GitHub repository where you'll run workflows

Enable Required APIs

First, enable the necessary Google Cloud APIs:

# Set your project ID
export PROJECT_ID="your-project-id"
export PROJECT_NUMBER=$(gcloud projects describe $PROJECT_ID --format="value(projectNumber)")

# Enable required APIs
gcloud services enable iamcredentials.googleapis.com \
    cloudresourcemanager.googleapis.com \
    sts.googleapis.com \
    --project=$PROJECT_ID

Create a Service Account

Create a service account that will be impersonated by GitHub Actions:

# Create service account
gcloud iam service-accounts create github-actions-sa \
    --display-name="GitHub Actions Service Account" \
    --description="Service account for GitHub Actions workflows" \
    --project=$PROJECT_ID

# Store the service account email
export SA_EMAIL="github-actions-sa@${PROJECT_ID}.iam.gserviceaccount.com"

Grant Necessary Permissions to Service Account

Grant the service account permissions to perform required operations. For our scenario, we need Cloud Run deployment and Cloud SQL access:

# Cloud Run Admin (to deploy services)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/run.admin"

# Service Account User (to deploy as a service account)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/iam.serviceAccountUser"

# Cloud SQL Client (to connect to Cloud SQL instances)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/cloudsql.client"

# Cloud SQL Admin (to manage instances and run migrations)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/cloudsql.admin"

# Storage Admin (for artifact storage)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/storage.admin"

# Secret Manager Secret Accessor (to access database credentials)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/secretmanager.secretAccessor"

Create Workload Identity Pool

The Workload Identity Pool is a container for managing external identities:

# Create the workload identity pool
gcloud iam workload-identity-pools create "github-pool" \
    --location="global" \
    --display-name="GitHub Actions Pool" \
    --description="Identity pool for GitHub Actions workflows" \
    --project=$PROJECT_ID

# Verify creation
gcloud iam workload-identity-pools describe "github-pool" \
    --location="global" \
    --project=$PROJECT_ID

Create Workload Identity Provider

The provider configures how external tokens are validated and mapped:

# Create the OIDC provider for GitHub Actions
gcloud iam workload-identity-pools providers create-oidc "github-provider" \
    --location="global" \
    --workload-identity-pool="github-pool" \
    --display-name="GitHub Actions Provider" \
    --attribute-mapping="google.subject=assertion.sub,attribute.actor=assertion.actor,attribute.repository=assertion.repository,attribute.repository_owner=assertion.repository_owner" \
    --attribute-condition="assertion.repository_owner=='your-github-org'" \
    --issuer-uri="https://token.actions.githubusercontent.com" \
    --project=$PROJECT_ID

Understanding Attribute Mapping

The --attribute-mapping parameter is crucial. It maps claims from the GitHub OIDC token to Google Cloud attributes:

google.subject=assertion.sub: Maps the subject claim (unique identifier for the workflow)
attribute.actor=assertion.actor: Maps the GitHub user who triggered the workflow
attribute.repository=assertion.repository: Maps the repository name
attribute.repository_owner=assertion.repository_owner: Maps the repository owner

Understanding Attribute Conditions

The --attribute-condition parameter adds security by restricting which external identities can authenticate:

# Only allow specific organization
assertion.repository_owner=='techflow-inc'

# Only allow specific repository
assertion.repository=='techflow-inc/main-app'

# Allow multiple repositories
assertion.repository in ['techflow-inc/app1', 'techflow-inc/app2']

# Combine conditions
assertion.repository_owner=='techflow-inc' && assertion.repository.startsWith('techflow-inc/prod-')

Important: Replace 'your-github-org' with your actual GitHub organization or username.

Grant Service Account Impersonation Permission

Allow the Workload Identity Pool to impersonate your service account. This is where you define precisely which external identities can impersonate the service account:

# For a specific repository
gcloud iam service-accounts add-iam-policy-binding "${SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.repository/your-github-username/your-repo-name" \
    --project=$PROJECT_ID

Advanced IAM Binding Examples

Option 1: Allow any repository in your organization

gcloud iam service-accounts add-iam-policy-binding "${SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.repository_owner/your-github-org" \
    --project=$PROJECT_ID

Option 2: Allow multiple specific repositories

# First repository
gcloud iam service-accounts add-iam-policy-binding "${SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.repository/your-org/repo-1" \
    --project=$PROJECT_ID

# Second repository
gcloud iam service-accounts add-iam-policy-binding "${SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.repository/your-org/repo-2" \
    --project=$PROJECT_ID

Option 3: Allow specific branch in a repository

# This requires adding branch to attribute mapping first
gcloud iam workload-identity-pools providers update-oidc "github-provider" \
    --location="global" \
    --workload-identity-pool="github-pool" \
    --attribute-mapping="google.subject=assertion.sub,attribute.actor=assertion.actor,attribute.repository=assertion.repository,attribute.repository_owner=assertion.repository_owner,attribute.ref=assertion.ref" \
    --project=$PROJECT_ID

# Then bind for specific branch
gcloud iam service-accounts add-iam-policy-binding "${SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.ref/refs/heads/main" \
    --project=$PROJECT_ID

Get the Workload Identity Provider Resource Name

You'll need this for your GitHub Actions workflow:

gcloud iam workload-identity-pools providers describe "github-provider" \
    --location="global" \
    --workload-identity-pool="github-pool" \
    --project=$PROJECT_ID \
    --format="value(name)"

The output will look like:

projects/123456789/locations/global/workloadIdentityPools/github-pool/providers/github-provider

Save this value—you'll use it in your GitHub workflow.

Set Up Cloud SQL Instance and Secrets

Before configuring the workflow, set up your Cloud SQL instance and store credentials securely:

Create Cloud SQL Instance

# Create a PostgreSQL instance
gcloud sql instances create production-db \
    --database-version=POSTGRES_15 \
    --tier=db-custom-2-7680 \
    --region=us-central1 \
    --network=projects/$PROJECT_ID/global/networks/default \
    --no-assign-ip \
    --database-flags=cloudsql.iam_authentication=on \
    --project=$PROJECT_ID

# Create a database
gcloud sql databases create production \
    --instance=production-db \
    --project=$PROJECT_ID

# Create a PostgreSQL user
gcloud sql users create dbuser \
    --instance=production-db \
    --password=STRONG_PASSWORD_HERE \
    --project=$PROJECT_ID

Store Database Password in Secret Manager

# Enable Secret Manager API
gcloud services enable secretmanager.googleapis.com --project=$PROJECT_ID

# Create secret for database password
echo -n "STRONG_PASSWORD_HERE" | gcloud secrets create db-password \
    --data-file=- \
    --replication-policy="automatic" \
    --project=$PROJECT_ID

# Grant service account access to the secret
gcloud secrets add-iam-policy-binding db-password \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/secretmanager.secretAccessor" \
    --project=$PROJECT_ID

Alternative: Use IAM Database Authentication (Recommended)

For enhanced security, use IAM authentication instead of passwords:

# Create an IAM database user
gcloud sql users create github-actions-sa@$PROJECT_ID.iam \
    --instance=production-db \
    --type=CLOUD_IAM_SERVICE_ACCOUNT \
    --project=$PROJECT_ID

# Grant the service account Cloud SQL Client role
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/cloudsql.client"

Then connect using IAM authentication in your workflow:

# Get an IAM token for database authentication
gcloud sql generate-login-token

# Or use Cloud SQL Proxy which handles this automatically
./cloud_sql_proxy -instances=$INSTANCE_CONNECTION_NAME=tcp:5432 \
    --auto-iam-authn

Configure Cloud SQL for Private IP (Best Practice)

# Enable Private Service Access
gcloud compute addresses create google-managed-services-default \
    --global \
    --purpose=VPC_PEERING \
    --prefix-length=16 \
    --network=default \
    --project=$PROJECT_ID

# Create private connection
gcloud services vpc-peerings connect \
    --service=servicenetworking.googleapis.com \
    --ranges=google-managed-services-default \
    --network=default \
    --project=$PROJECT_ID

# Create instance with private IP only
gcloud sql instances create production-db-private \
    --database-version=POSTGRES_15 \
    --tier=db-custom-2-7680 \
    --region=us-central1 \
    --network=projects/$PROJECT_ID/global/networks/default \
    --no-assign-ip \
    --database-flags=cloudsql.iam_authentication=on \
    --project=$PROJECT_ID

Configure GitHub Actions Workflow

Now, create a GitHub Actions workflow that uses Workload Identity Federation:

name: Deploy to Cloud Run and Run Migrations

on:
  push:
    branches:
      - main

# Required for OIDC token generation
permissions:
  contents: read
  id-token: write

jobs:
  deploy:
    runs-on: ubuntu-latest
    
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      # Authenticate to Google Cloud using Workload Identity Federation
      - id: 'auth'
        name: 'Authenticate to Google Cloud'
        uses: 'google-github-actions/auth@v2'
        with:
          workload_identity_provider: 'projects/123456789/locations/global/workloadIdentityPools/github-pool/providers/github-provider'
          service_account: '[email protected]'
          token_format: 'access_token'

      # Setup Cloud SDK
      - name: 'Set up Cloud SDK'
        uses: 'google-github-actions/setup-gcloud@v2'

      # Install Cloud SQL Proxy
      - name: 'Install Cloud SQL Proxy'
        run: |
          wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
          chmod +x cloud_sql_proxy

      # Build and push Docker image
      - name: 'Build and Push to Artifact Registry'
        run: |
          gcloud auth configure-docker us-central1-docker.pkg.dev
          docker build -t us-central1-docker.pkg.dev/${{ secrets.GCP_PROJECT_ID }}/apps/myapp:${{ github.sha }} .
          docker push us-central1-docker.pkg.dev/${{ secrets.GCP_PROJECT_ID }}/apps/myapp:${{ github.sha }}

      # Run database migrations using Cloud SQL Proxy
      - name: 'Run Database Migrations'
        env:
          INSTANCE_CONNECTION_NAME: ${{ secrets.GCP_PROJECT_ID }}:us-central1:production-db
        run: |
          # Start Cloud SQL Proxy in background
          ./cloud_sql_proxy -instances=$INSTANCE_CONNECTION_NAME=tcp:5432 &
          PROXY_PID=$!
          
          # Wait for proxy to be ready
          sleep 5
          
          # Get database password from Secret Manager
          DB_PASSWORD=$(gcloud secrets versions access latest --secret="db-password")
          
          # Run migrations (example using Flyway)
          export PGPASSWORD=$DB_PASSWORD
          psql -h 127.0.0.1 -p 5432 -U dbuser -d production -f migrations/001_initial_schema.sql
          
          # Or using a migration tool like golang-migrate
          # migrate -path=./migrations -database "postgresql://dbuser:${DB_PASSWORD}@127.0.0.1:5432/production?sslmode=disable" up
          
          # Kill the proxy
          kill $PROXY_PID

      # Deploy to Cloud Run with Cloud SQL connection
      - name: 'Deploy to Cloud Run'
        run: |
          gcloud run deploy myapp \
            --image=us-central1-docker.pkg.dev/${{ secrets.GCP_PROJECT_ID }}/apps/myapp:${{ github.sha }} \
            --region=us-central1 \
            --platform=managed \
            --allow-unauthenticated \
            --add-cloudsql-instances=${{ secrets.GCP_PROJECT_ID }}:us-central1:production-db \
            --set-env-vars="DB_USER=dbuser,DB_NAME=production" \
            --set-secrets="DB_PASSWORD=db-password:latest"

      # Verify deployment and database connectivity
      - name: 'Health Check'
        run: |
          # Get the service URL
          SERVICE_URL=$(gcloud run services describe myapp --region=us-central1 --format='value(status.url)')
          
          # Check health endpoint
          curl -f ${SERVICE_URL}/health || exit 1
          
          # Verify database connection through the app
          curl -f ${SERVICE_URL}/db/ping || exit 1

Key Workflow Elements

Permissions Block: The id-token: write permission is crucial—it allows GitHub to generate OIDC tokens.
Auth Action: The google-github-actions/auth@v2 action handles the token exchange automatically.
No Secrets Required: Notice we don't store any service account keys in GitHub Secrets, only the project ID.
Cloud SQL Proxy: The proxy creates a secure tunnel to Cloud SQL, handling authentication via the service account.
Secret Manager Integration: Database passwords are stored securely and accessed at runtime.

Advanced Cloud SQL Workflow Patterns

Pattern 1: Using IAM Authentication (Most Secure)

name: Deploy with IAM Database Authentication

on:
  push:
    branches:
      - main

permissions:
  contents: read
  id-token: write

jobs:
  deploy:
    runs-on: ubuntu-latest
    
    steps:
      - uses: actions/checkout@v4

      - id: 'auth'
        uses: 'google-github-actions/auth@v2'
        with:
          workload_identity_provider: 'projects/123456789/locations/global/workloadIdentityPools/github-pool/providers/github-provider'
          service_account: '[email protected]'

      - uses: 'google-github-actions/setup-gcloud@v2'

      # Use Cloud SQL Auth Proxy with IAM authentication
      - name: 'Run Migrations with IAM Auth'
        run: |
          # Install Cloud SQL Auth Proxy
          curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.8.0/cloud-sql-proxy.linux.amd64
          chmod +x cloud-sql-proxy
          
          # Start proxy with automatic IAM authentication
          ./cloud-sql-proxy --auto-iam-authn ${{ secrets.GCP_PROJECT_ID }}:us-central1:production-db &
          PROXY_PID=$!
          sleep 5
          
          # Connect using IAM - no password needed!
          psql "host=127.0.0.1 port=5432 sslmode=disable user=github-actions-sa@${{ secrets.GCP_PROJECT_ID }}.iam dbname=production" \
            -c "SELECT version();"
          
          # Run migrations
          psql "host=127.0.0.1 port=5432 sslmode=disable user=github-actions-sa@${{ secrets.GCP_PROJECT_ID }}.iam dbname=production" \
            -f migrations/schema.sql
          
          kill $PROXY_PID

Pattern 2: Using Flyway for Database Migrations

      - name: 'Run Flyway Migrations'
        env:
          INSTANCE_CONNECTION_NAME: ${{ secrets.GCP_PROJECT_ID }}:us-central1:production-db
        run: |
          # Start Cloud SQL Proxy
          ./cloud_sql_proxy -instances=$INSTANCE_CONNECTION_NAME=tcp:5432 &
          PROXY_PID=$!
          sleep 5
          
          # Get database password
          DB_PASSWORD=$(gcloud secrets versions access latest --secret="db-password")
          
          # Install Flyway
          wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/10.4.1/flyway-commandline-10.4.1-linux-x64.tar.gz | tar xvz
          
          # Configure Flyway
          ./flyway-10.4.1/flyway \
            -url=jdbc:postgresql://127.0.0.1:5432/production \
            -user=dbuser \
            -password=$DB_PASSWORD \
            -locations=filesystem:./sql/migrations \
            migrate
          
          # Verify migration status
          ./flyway-10.4.1/flyway \
            -url=jdbc:postgresql://127.0.0.1:5432/production \
            -user=dbuser \
            -password=$DB_PASSWORD \
            info
          
          kill $PROXY_PID

Pattern 3: Blue-Green Database Migrations with Rollback

      - name: 'Blue-Green Migration with Rollback'
        run: |
          # Start proxy
          ./cloud_sql_proxy -instances=${{ secrets.GCP_PROJECT_ID }}:us-central1:production-db=tcp:5432 &
          PROXY_PID=$!
          sleep 5
          
          DB_PASSWORD=$(gcloud secrets versions access latest --secret="db-password")
          export PGPASSWORD=$DB_PASSWORD
          
          # Create backup before migration
          pg_dump -h 127.0.0.1 -p 5432 -U dbuser production > backup_$(date +%Y%m%d_%H%M%S).sql
          
          # Upload backup to Cloud Storage
          gcloud storage cp backup_*.sql gs://${{ secrets.GCP_PROJECT_ID }}-db-backups/
          
          # Create test database from production
          psql -h 127.0.0.1 -p 5432 -U dbuser -d postgres \
            -c "CREATE DATABASE production_test WITH TEMPLATE production;"
          
          # Test migrations on copy
          if psql -h 127.0.0.1 -p 5432 -U dbuser -d production_test -f migrations/schema.sql; then
            echo "✓ Migration test successful"
            
            # Apply to production
            psql -h 127.0.0.1 -p 5432 -U dbuser -d production -f migrations/schema.sql
            
            # Verify production migration
            if psql -h 127.0.0.1 -p 5432 -U dbuser -d production -c "SELECT COUNT(*) FROM users;" > /dev/null; then
              echo "✓ Production migration successful"
            else
              echo "✗ Production migration failed, restoring backup"
              psql -h 127.0.0.1 -p 5432 -U dbuser -d production < backup_*.sql
              exit 1
            fi
            
            # Cleanup test database
            psql -h 127.0.0.1 -p 5432 -U dbuser -d postgres \
              -c "DROP DATABASE production_test;"
          else
            echo "✗ Migration test failed, aborting"
            exit 1
          fi
          
          kill $PROXY_PID

Pattern 4: Multi-Region Database Deployment

jobs:
  deploy-multi-region:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        region: 
          - name: us-central1
            db: production-db-us
          - name: europe-west1
            db: production-db-eu
          - name: asia-southeast1
            db: production-db-asia
    
    steps:
      - uses: actions/checkout@v4
      
      - id: 'auth'
        uses: 'google-github-actions/auth@v2'
        with:
          workload_identity_provider: 'projects/123456789/locations/global/workloadIdentityPools/github-pool/providers/github-provider'
          service_account: '[email protected]'
      
      - uses: 'google-github-actions/setup-gcloud@v2'
      
      - name: 'Deploy to ${{ matrix.region.name }}'
        run: |
          # Install Cloud SQL Proxy
          curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.8.0/cloud-sql-proxy.linux.amd64
          chmod +x cloud-sql-proxy
          
          # Start proxy for this region
          ./cloud-sql-proxy ${{ secrets.GCP_PROJECT_ID }}:${{ matrix.region.name }}:${{ matrix.region.db }} &
          PROXY_PID=$!
          sleep 5
          
          # Get region-specific password
          DB_PASSWORD=$(gcloud secrets versions access latest --secret="db-password-${{ matrix.region.name }}")
          export PGPASSWORD=$DB_PASSWORD
          
          # Run common migrations
          psql -h 127.0.0.1 -p 5432 -U dbuser -d production \
            -f migrations/common/schema.sql
          
          # Run region-specific configurations
          psql -h 127.0.0.1 -p 5432 -U dbuser -d production \
            -v region=${{ matrix.region.name }} \
            -f migrations/regional/config.sql
          
          kill $PROXY_PID
          
          # Deploy Cloud Run in this region
          gcloud run deploy myapp-${{ matrix.region.name }} \
            --image=us-central1-docker.pkg.dev/${{ secrets.GCP_PROJECT_ID }}/apps/myapp:${{ github.sha }} \
            --region=${{ matrix.region.name }} \
            --add-cloudsql-instances=${{ secrets.GCP_PROJECT_ID }}:${{ matrix.region.name }}:${{ matrix.region.db }} \
            --set-env-vars="REGION=${{ matrix.region.name }},DB_NAME=production"

Advanced Configuration - Multiple Environments

For production deployments, you'll likely need different service accounts for different environments:

# Create production service account
gcloud iam service-accounts create github-actions-prod-sa \
    --display-name="GitHub Actions Production SA" \
    --project=$PROJECT_ID

export PROD_SA_EMAIL="github-actions-prod-sa@${PROJECT_ID}.iam.gserviceaccount.com"

# Bind only to main branch
gcloud iam service-accounts add-iam-policy-binding "${PROD_SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.ref/refs/heads/main" \
    --project=$PROJECT_ID

# Create staging service account
gcloud iam service-accounts create github-actions-staging-sa \
    --display-name="GitHub Actions Staging SA" \
    --project=$PROJECT_ID

export STAGING_SA_EMAIL="github-actions-staging-sa@${PROJECT_ID}.iam.gserviceaccount.com"

# Bind to develop branch
gcloud iam service-accounts add-iam-policy-binding "${STAGING_SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.ref/refs/heads/develop" \
    --project=$PROJECT_ID

Then in your workflow:

- id: 'auth'
  name: 'Authenticate to Google Cloud'
  uses: 'google-github-actions/auth@v2'
  with:
    workload_identity_provider: 'projects/123456789/locations/global/workloadIdentityPools/github-pool/providers/github-provider'
    service_account: ${{ github.ref == 'refs/heads/main' && '[email protected]' || '[email protected]' }}

Verification and Testing

Test 1: Verify Token Exchange

Create a simple test workflow:

name: Test Workload Identity

on:
  workflow_dispatch:

permissions:
  id-token: write
  contents: read

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - id: 'auth'
        uses: 'google-github-actions/auth@v2'
        with:
          workload_identity_provider: 'projects/123456789/locations/global/workloadIdentityPools/github-pool/providers/github-provider'
          service_account: '[email protected]'
      
      - name: 'Verify Authentication'
        run: |
          gcloud auth list
          gcloud projects describe ${{ secrets.GCP_PROJECT_ID }}

Test 2: Audit Logs

Check Cloud Audit Logs to verify authentication:

gcloud logging read "protoPayload.methodName=GenerateAccessToken" \
    --limit=10 \
    --format=json \
    --project=$PROJECT_ID

Look for entries showing GitHub Actions successfully exchanging tokens.

Security Best Practices

1. Principle of Least Privilege

Grant only the minimum permissions needed:

# Instead of broad roles, use specific permissions
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/run.developer"  # Instead of run.admin

gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/cloudsql.client"  # Instead of cloudsql.admin for read-only access

2. Use Attribute Conditions Strictly

Always restrict which external identities can authenticate:

# Bad: Too permissive
--attribute-condition=""

# Good: Specific organization
--attribute-condition="assertion.repository_owner=='your-org'"

# Better: Specific repositories and branches
--attribute-condition="assertion.repository_owner=='your-org' && assertion.repository.startsWith('your-org/prod-') && assertion.ref=='refs/heads/main'"

3. Separate Service Accounts by Environment

Never use the same service account for production and staging:

✗ github-actions-sa → All environments
✓ github-actions-prod-sa → Production only
✓ github-actions-staging-sa → Staging only
✓ github-actions-dev-sa → Development only

4. Enable Audit Logging

Ensure Data Access audit logs are enabled:

# Create audit config
cat > audit-config.yaml <<EOF
auditConfigs:
- auditLogConfigs:
  - logType: ADMIN_READ
  - logType: DATA_READ
  - logType: DATA_WRITE
  service: iam.googleapis.com
EOF

gcloud projects set-iam-policy $PROJECT_ID audit-config.yaml

5. Monitor Token Exchange Activity

Set up monitoring alerts for suspicious activity:

# Create a log-based metric
gcloud logging metrics create workload-identity-failures \
    --description="Failed workload identity token exchanges" \
    --log-filter='protoPayload.methodName="GenerateAccessToken"
    AND protoPayload.status.code!=0'

# Create alert policy
gcloud alpha monitoring policies create \
    --notification-channels=CHANNEL_ID \
    --display-name="Workload Identity Failures" \
    --condition-display-name="High failure rate" \
    --condition-threshold-value=5 \
    --condition-threshold-duration=60s

6. Cloud SQL Security Best Practices

Implement these security measures for Cloud SQL:

# Enable automatic backups
gcloud sql instances patch production-db \
    --backup-start-time=03:00 \
    --enable-bin-log \
    --project=$PROJECT_ID

# Enable point-in-time recovery
gcloud sql instances patch production-db \
    --enable-point-in-time-recovery \
    --project=$PROJECT_ID

# Require SSL for all connections
gcloud sql instances patch production-db \
    --require-ssl \
    --project=$PROJECT_ID

# Enable database flags for security
gcloud sql instances patch production-db \
    --database-flags=\
cloudsql.iam_authentication=on,\
log_checkpoints=on,\
log_connections=on,\
log_disconnections=on,\
log_lock_waits=on,\
log_statement=ddl,\
log_min_duration_statement=1000 \
    --project=$PROJECT_ID

# Restrict network access (private IP only)
gcloud sql instances patch production-db \
    --no-assign-ip \
    --network=projects/$PROJECT_ID/global/networks/default \
    --project=$PROJECT_ID

Database User Security:

-- Revoke public schema privileges
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE production FROM PUBLIC;

-- Create read-only role for analytics
CREATE ROLE analytics_readonly;
GRANT CONNECT ON DATABASE production TO analytics_readonly;
GRANT USAGE ON SCHEMA public TO analytics_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_readonly;

-- Create application role with limited privileges
CREATE ROLE app_user;
GRANT CONNECT ON DATABASE production TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Assign IAM service account to role
GRANT app_user TO "[email protected]";

Automated Security Scanning:

# Add to your GitHub workflow
- name: 'Scan Database for Security Issues'
  run: |
    # Check for overly permissive grants
    psql -h 127.0.0.1 -p 5432 -U dbuser -d production <<EOF
    SELECT 
      grantee, 
      string_agg(privilege_type, ', ') as privileges,
      table_schema,
      table_name
    FROM information_schema.table_privileges
    WHERE grantee = 'PUBLIC'
    GROUP BY grantee, table_schema, table_name;
    EOF
    
    # Check for weak passwords (if not using IAM auth)
    psql -h 127.0.0.1 -p 5432 -U dbuser -d production <<EOF
    SELECT usename 
    FROM pg_shadow 
    WHERE passwd IS NULL OR passwd = '';
    EOF

7. Secrets Rotation Strategy

Automate credential rotation for enhanced security:

# Create a rotation script
cat > rotate-db-password.sh <<'EOF'
#!/bin/bash
set -e

PROJECT_ID=$1
INSTANCE_NAME=$2
DB_USER=$3
SECRET_NAME=$4

# Generate new password
NEW_PASSWORD=$(openssl rand -base64 32)

# Update Cloud SQL user
gcloud sql users set-password $DB_USER \
    --instance=$INSTANCE_NAME \
    --password=$NEW_PASSWORD \
    --project=$PROJECT_ID

# Update Secret Manager
echo -n "$NEW_PASSWORD" | gcloud secrets versions add $SECRET_NAME \
    --data-file=- \
    --project=$PROJECT_ID

# Disable old secret versions (after grace period)
OLD_VERSIONS=$(gcloud secrets versions list $SECRET_NAME \
    --filter="state:ENABLED" \
    --format="value(name)" \
    --sort-by="~createTime" \
    --limit=5 \
    | tail -n +3)

for VERSION in $OLD_VERSIONS; do
    gcloud secrets versions disable $VERSION --secret=$SECRET_NAME --project=$PROJECT_ID
done

echo "Password rotated successfully"
EOF

chmod +x rotate-db-password.sh

# Schedule rotation with Cloud Scheduler
gcloud scheduler jobs create http db-password-rotation \
    --schedule="0 0 1 * *" \
    --uri="https://your-cloud-function-url/rotate-password" \
    --http-method=POST \
    --oidc-service-account-email=$SA_EMAIL \
    --project=$PROJECT_ID

Troubleshooting Common Issues

Issue 1: "Permission denied" when exchanging token

Symptom:

Error: google-github-actions/auth failed with: failed to generate Google Cloud access token: 
(400) {"error":"invalid_target","error_description":"The provided target service account is invalid"}

Solution: Verify the IAM binding:

gcloud iam service-accounts get-iam-policy $SA_EMAIL \
    --project=$PROJECT_ID \
    --flatten="bindings[].members" \
    --filter="bindings.role:roles/iam.workloadIdentityUser"

Ensure the principal matches your repository exactly.

Issue 2: "Attribute condition not satisfied"

Symptom:

Error: (403) Attribute condition not satisfied

Solution: Check your attribute condition matches the token claims:

# Update condition to be more permissive temporarily for debugging
gcloud iam workload-identity-pools providers update-oidc "github-provider" \
    --location="global" \
    --workload-identity-pool="github-pool" \
    --attribute-condition="" \
    --project=$PROJECT_ID

Then examine the actual token claims in audit logs and adjust your condition.

Issue 3: Missing `id-token: write` permission

Symptom:

Error: Unable to get ACTIONS_ID_TOKEN_REQUEST_URL env variable

Solution: Add to your workflow:

permissions:
  id-token: write
  contents: read

Issue 4: Insufficient permissions for service account

Symptom:

Error: (403) Permission denied on resource

Solution: Review and grant required roles:

# Check current roles
gcloud projects get-iam-policy $PROJECT_ID \
    --flatten="bindings[].members" \
    --filter="bindings.members:serviceAccount:${SA_EMAIL}"

# Add missing role
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/REQUIRED_ROLE"

Issue 5: Cloud SQL Proxy Connection Timeout

Symptom:

Error: couldn't connect to <instance>: dial tcp 127.0.0.1:5432: connect: connection refused

Solution: Ensure the proxy has started and the service account has cloudsql.client role:

# Verify service account has Cloud SQL Client role
gcloud projects get-iam-policy $PROJECT_ID \
    --flatten="bindings[].members" \
    --filter="bindings.members:serviceAccount:${SA_EMAIL}" \
    --filter="bindings.role:roles/cloudsql.client"

# Grant if missing
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/cloudsql.client"

# In your workflow, add more wait time
./cloud_sql_proxy -instances=$INSTANCE &
sleep 10  # Increase from 5 to 10 seconds

# Or check proxy logs
./cloud_sql_proxy -instances=$INSTANCE -verbose &

Issue 6: IAM Database Authentication Failures

Symptom:

psql: error: connection to server at "127.0.0.1", port 5432 failed: 
FATAL: password authentication failed for user "[email protected]"

Solution: Verify IAM user exists and has proper grants:

# Check if IAM user exists
gcloud sql users list --instance=production-db --project=$PROJECT_ID

# Create IAM user if missing
gcloud sql users create github-actions-sa@$PROJECT_ID.iam \
    --instance=production-db \
    --type=CLOUD_IAM_SERVICE_ACCOUNT \
    --project=$PROJECT_ID

# Grant database permissions (connect via Cloud SQL Proxy first)
psql "host=127.0.0.1 port=5432 user=postgres dbname=production" <<EOF
GRANT CONNECT ON DATABASE production TO "github-actions-sa@$PROJECT_ID.iam";
GRANT USAGE ON SCHEMA public TO "github-actions-sa@$PROJECT_ID.iam";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "github-actions-sa@$PROJECT_ID.iam";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "github-actions-sa@$PROJECT_ID.iam";
EOF

Issue 7: Secret Manager Access Denied

Symptom:

Error: gcloud secrets versions access latest --secret="db-password"
ERROR: (gcloud.secrets.versions.access) PERMISSION_DENIED

Solution: Grant Secret Manager access:

# Grant access to specific secret
gcloud secrets add-iam-policy-binding db-password \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/secretmanager.secretAccessor" \
    --project=$PROJECT_ID

# Or grant project-wide access (less secure)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/secretmanager.secretAccessor"

Issue 8: Cloud SQL Instance Not Found

Symptom:

Error: instance does not exist or you are not authorized to access it

Solution: Verify instance name and permissions:

# List all Cloud SQL instances
gcloud sql instances list --project=$PROJECT_ID

# Describe specific instance
gcloud sql instances describe production-db --project=$PROJECT_ID

# Ensure correct connection name format: PROJECT_ID:REGION:INSTANCE_NAME
# Example: my-project:us-central1:production-db

Issue 9: Migration Script Fails Silently

Symptom: Workflow succeeds but migrations don't apply.

Solution: Add explicit error handling:

- name: 'Run Migrations with Error Handling'
  run: |
    set -e  # Exit on any error
    set -o pipefail  # Catch errors in pipes
    
    ./cloud_sql_proxy -instances=$INSTANCE_CONNECTION_NAME=tcp:5432 &
    PROXY_PID=$!
    
    # Ensure proxy cleanup on exit
    trap "kill $PROXY_PID 2>/dev/null || true" EXIT
    
    sleep 5
    
    DB_PASSWORD=$(gcloud secrets versions access latest --secret="db-password")
    export PGPASSWORD=$DB_PASSWORD
    
    # Run migration with verbose output
    psql -h 127.0.0.1 -p 5432 -U dbuser -d production \
      -v ON_ERROR_STOP=1 \
      -f migrations/schema.sql \
      2>&1 | tee migration.log
    
    # Check exit code explicitly
    if [ ${PIPESTATUS[0]} -ne 0 ]; then
      echo "Migration failed!"
      cat migration.log
      exit 1
    fi

Issue 10: Connection Pool Exhaustion

Symptom:

Error: remaining connection slots are reserved for non-replication superuser connections

Solution: Implement connection pooling and limits:

- name: 'Use Connection Pooling'
  run: |
    # Increase max_connections on Cloud SQL instance
    gcloud sql instances patch production-db \
      --database-flags=max_connections=100 \
      --project=$PROJECT_ID
    
    # Or use PgBouncer for connection pooling
    cat > pgbouncer.ini <<EOF
[databases]
production = host=127.0.0.1 port=5432 dbname=production

[pgbouncer]
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20
reserve_pool_size = 5
EOF
    
    pgbouncer -d pgbouncer.ini
    
    # Connect through PgBouncer on port 6432
    psql -h 127.0.0.1 -p 6432 -U dbuser -d production -f migrations/schema.sql

Advanced Topics

Complete Infrastructure Provisioning with gcloud

Here's a complete script to provision all infrastructure using gcloud commands:

#!/bin/bash
# complete-setup.sh - Complete Workload Identity Federation setup script

set -e  # Exit on error

# Configuration variables
export PROJECT_ID="your-project-id"
export GITHUB_ORG="your-github-org"
export GITHUB_REPO="your-repo-name"
export REGION="us-central1"
export DB_INSTANCE_NAME="production-db"
export DB_NAME="production"
export DB_USER="dbuser"

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

echo -e "${GREEN}Starting Workload Identity Federation setup...${NC}"

# Step 1: Enable required APIs
echo -e "${YELLOW}Enabling required APIs...${NC}"
gcloud services enable \
    iamcredentials.googleapis.com \
    cloudresourcemanager.googleapis.com \
    sts.googleapis.com \
    sqladmin.googleapis.com \
    secretmanager.googleapis.com \
    run.googleapis.com \
    cloudbuild.googleapis.com \
    artifactregistry.googleapis.com \
    --project=$PROJECT_ID

# Get project number
export PROJECT_NUMBER=$(gcloud projects describe $PROJECT_ID --format="value(projectNumber)")
echo -e "${GREEN}Project Number: $PROJECT_NUMBER${NC}"

# Step 2: Create Workload Identity Pool
echo -e "${YELLOW}Creating Workload Identity Pool...${NC}"
if gcloud iam workload-identity-pools describe "github-pool" \
    --location="global" \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Pool already exists, skipping...${NC}"
else
    gcloud iam workload-identity-pools create "github-pool" \
        --location="global" \
        --display-name="GitHub Actions Pool" \
        --description="Identity pool for GitHub Actions workflows" \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Pool created${NC}"
fi

# Step 3: Create Workload Identity Provider
echo -e "${YELLOW}Creating Workload Identity Provider...${NC}"
if gcloud iam workload-identity-pools providers describe "github-provider" \
    --location="global" \
    --workload-identity-pool="github-pool" \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Provider already exists, skipping...${NC}"
else
    gcloud iam workload-identity-pools providers create-oidc "github-provider" \
        --location="global" \
        --workload-identity-pool="github-pool" \
        --display-name="GitHub Actions Provider" \
        --attribute-mapping="google.subject=assertion.sub,attribute.actor=assertion.actor,attribute.repository=assertion.repository,attribute.repository_owner=assertion.repository_owner,attribute.ref=assertion.ref" \
        --attribute-condition="assertion.repository_owner=='${GITHUB_ORG}'" \
        --issuer-uri="https://token.actions.githubusercontent.com" \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Provider created${NC}"
fi

# Step 4: Create Service Accounts
echo -e "${YELLOW}Creating service accounts...${NC}"

# Production service account
if gcloud iam service-accounts describe github-actions-prod-sa@${PROJECT_ID}.iam.gserviceaccount.com \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Production SA already exists, skipping...${NC}"
else
    gcloud iam service-accounts create github-actions-prod-sa \
        --display-name="GitHub Actions Production SA" \
        --description="Production deployment service account" \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Production SA created${NC}"
fi

# Staging service account
if gcloud iam service-accounts describe github-actions-staging-sa@${PROJECT_ID}.iam.gserviceaccount.com \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Staging SA already exists, skipping...${NC}"
else
    gcloud iam service-accounts create github-actions-staging-sa \
        --display-name="GitHub Actions Staging SA" \
        --description="Staging deployment service account" \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Staging SA created${NC}"
fi

export PROD_SA_EMAIL="github-actions-prod-sa@${PROJECT_ID}.iam.gserviceaccount.com"
export STAGING_SA_EMAIL="github-actions-staging-sa@${PROJECT_ID}.iam.gserviceaccount.com"

# Step 5: Grant IAM permissions to service accounts
echo -e "${YELLOW}Granting IAM permissions...${NC}"

# Production permissions
for ROLE in "roles/run.admin" "roles/iam.serviceAccountUser" "roles/cloudsql.client" \
            "roles/storage.admin" "roles/secretmanager.secretAccessor"; do
    gcloud projects add-iam-policy-binding $PROJECT_ID \
        --member="serviceAccount:${PROD_SA_EMAIL}" \
        --role="$ROLE" \
        --condition=None \
        --quiet
done

# Staging permissions (more limited)
for ROLE in "roles/run.developer" "roles/cloudsql.client" \
            "roles/secretmanager.secretAccessor"; do
    gcloud projects add-iam-policy-binding $PROJECT_ID \
        --member="serviceAccount:${STAGING_SA_EMAIL}" \
        --role="$ROLE" \
        --condition=None \
        --quiet
done

echo -e "${GREEN}✓ IAM permissions granted${NC}"

# Step 6: Bind workload identity to service accounts
echo -e "${YELLOW}Binding workload identity...${NC}"

# Production: main branch only
gcloud iam service-accounts add-iam-policy-binding "${PROD_SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.ref/refs/heads/main" \
    --project=$PROJECT_ID \
    --quiet

# Staging: develop branch
gcloud iam service-accounts add-iam-policy-binding "${STAGING_SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.ref/refs/heads/develop" \
    --project=$PROJECT_ID \
    --quiet

echo -e "${GREEN}✓ Workload identity bindings created${NC}"

# Step 7: Create Artifact Registry repository
echo -e "${YELLOW}Creating Artifact Registry repository...${NC}"
if gcloud artifacts repositories describe apps \
    --location=$REGION \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Repository already exists, skipping...${NC}"
else
    gcloud artifacts repositories create apps \
        --repository-format=docker \
        --location=$REGION \
        --description="Docker images for applications" \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Artifact Registry created${NC}"
fi

# Step 8: Create Cloud SQL instance
echo -e "${YELLOW}Creating Cloud SQL instance (this may take 5-10 minutes)...${NC}"
if gcloud sql instances describe $DB_INSTANCE_NAME \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Cloud SQL instance already exists, skipping...${NC}"
else
    gcloud sql instances create $DB_INSTANCE_NAME \
        --database-version=POSTGRES_15 \
        --tier=db-custom-2-7680 \
        --region=$REGION \
        --network=projects/$PROJECT_ID/global/networks/default \
        --no-assign-ip \
        --database-flags=cloudsql.iam_authentication=on,log_connections=on,log_disconnections=on \
        --backup-start-time=03:00 \
        --enable-bin-log \
        --enable-point-in-time-recovery \
        --maintenance-window-day=SUN \
        --maintenance-window-hour=4 \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Cloud SQL instance created${NC}"
fi

# Step 9: Create database
echo -e "${YELLOW}Creating database...${NC}"
if gcloud sql databases describe $DB_NAME \
    --instance=$DB_INSTANCE_NAME \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Database already exists, skipping...${NC}"
else
    gcloud sql databases create $DB_NAME \
        --instance=$DB_INSTANCE_NAME \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Database created${NC}"
fi

# Step 10: Create IAM database users
echo -e "${YELLOW}Creating IAM database users...${NC}"

# Production IAM user
if gcloud sql users describe "${PROD_SA_EMAIL}" \
    --instance=$DB_INSTANCE_NAME \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Production IAM user already exists, skipping...${NC}"
else
    gcloud sql users create "${PROD_SA_EMAIL}" \
        --instance=$DB_INSTANCE_NAME \
        --type=CLOUD_IAM_SERVICE_ACCOUNT \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Production IAM user created${NC}"
fi

# Staging IAM user
if gcloud sql users describe "${STAGING_SA_EMAIL}" \
    --instance=$DB_INSTANCE_NAME \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Staging IAM user already exists, skipping...${NC}"
else
    gcloud sql users create "${STAGING_SA_EMAIL}" \
        --instance=$DB_INSTANCE_NAME \
        --type=CLOUD_IAM_SERVICE_ACCOUNT \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Staging IAM user created${NC}"
fi

# Step 11: Create secrets in Secret Manager
echo -e "${YELLOW}Creating secrets...${NC}"

# Generate a strong password (as fallback)
DB_PASSWORD=$(openssl rand -base64 32)

if gcloud secrets describe db-password \
    --project=$PROJECT_ID &>/dev/null; then
    echo -e "${GREEN}Secret already exists, skipping...${NC}"
else
    echo -n "$DB_PASSWORD" | gcloud secrets create db-password \
        --data-file=- \
        --replication-policy="automatic" \
        --project=$PROJECT_ID
    echo -e "${GREEN}✓ Database password secret created${NC}"
fi

# Grant access to secrets
gcloud secrets add-iam-policy-binding db-password \
    --member="serviceAccount:${PROD_SA_EMAIL}" \
    --role="roles/secretmanager.secretAccessor" \
    --project=$PROJECT_ID \
    --quiet

gcloud secrets add-iam-policy-binding db-password \
    --member="serviceAccount:${STAGING_SA_EMAIL}" \
    --role="roles/secretmanager.secretAccessor" \
    --project=$PROJECT_ID \
    --quiet

# Step 12: Enable audit logging
echo -e "${YELLOW}Configuring audit logging...${NC}"
cat > /tmp/audit-config.json <<EOF
{
  "auditConfigs": [
    {
      "service": "iam.googleapis.com",
      "auditLogConfigs": [
        {"logType": "ADMIN_READ"},
        {"logType": "DATA_READ"},
        {"logType": "DATA_WRITE"}
      ]
    },
    {
      "service": "cloudsql.googleapis.com",
      "auditLogConfigs": [
        {"logType": "ADMIN_READ"},
        {"logType": "DATA_READ"},
        {"logType": "DATA_WRITE"}
      ]
    }
  ]
}
EOF

# Note: Full audit config merge would require getting current policy first
echo -e "${YELLOW}Note: Audit logging configuration template created at /tmp/audit-config.json${NC}"
echo -e "${YELLOW}Apply manually if needed to preserve existing policies${NC}"

# Step 13: Create monitoring alerts
echo -e "${YELLOW}Creating monitoring metrics and alerts...${NC}"

# Workload Identity failures
gcloud logging metrics create workload-identity-failures \
    --description="Failed workload identity token exchanges" \
    --log-filter='protoPayload.methodName="GenerateAccessToken" AND protoPayload.status.code!=0' \
    --project=$PROJECT_ID &>/dev/null || echo -e "${YELLOW}Metric already exists${NC}"

# Cloud SQL connection failures
gcloud logging metrics create cloudsql-connection-failures \
    --description="Failed Cloud SQL connections" \
    --log-filter='resource.type="cloudsql_database" AND severity="ERROR"' \
    --project=$PROJECT_ID &>/dev/null || echo -e "${YELLOW}Metric already exists${NC}"

echo -e "${GREEN}✓ Monitoring metrics created${NC}"

# Step 14: Output important information
echo -e "${GREEN}========================================${NC}"
echo -e "${GREEN}Setup Complete!${NC}"
echo -e "${GREEN}========================================${NC}"
echo ""
echo -e "${YELLOW}Important Information:${NC}"
echo ""
echo "Workload Identity Provider:"
echo "  projects/$PROJECT_NUMBER/locations/global/workloadIdentityPools/github-pool/providers/github-provider"
echo ""
echo "Service Accounts:"
echo "  Production: $PROD_SA_EMAIL"
echo "  Staging: $STAGING_SA_EMAIL"
echo ""
echo "Cloud SQL Instance:"
echo "  Connection Name: $PROJECT_ID:$REGION:$DB_INSTANCE_NAME"
echo "  Database: $DB_NAME"
echo ""
echo "Artifact Registry:"
echo "  Repository: $REGION-docker.pkg.dev/$PROJECT_ID/apps"
echo ""
echo -e "${YELLOW}Next Steps:${NC}"
echo "1. Copy the Workload Identity Provider string above to your GitHub workflow"
echo "2. Add PROJECT_ID to your GitHub repository secrets"
echo "3. Configure database grants by connecting to Cloud SQL"
echo "4. Test the workflow with a deployment to develop branch (staging)"
echo "5. After validation, deploy to main branch (production)"
echo ""
echo -e "${GREEN}Save this output for reference!${NC}"

Using the Provisioning Script

Save the script and run it:

# Make executable
chmod +x complete-setup.sh

# Run with your configuration
./complete-setup.sh

Incremental Updates with gcloud

For updating specific components:

Update Workload Identity Provider:

# Add new attribute mapping
gcloud iam workload-identity-pools providers update-oidc "github-provider" \
    --location="global" \
    --workload-identity-pool="github-pool" \
    --attribute-mapping="google.subject=assertion.sub,attribute.actor=assertion.actor,attribute.repository=assertion.repository,attribute.repository_owner=assertion.repository_owner,attribute.ref=assertion.ref,attribute.environment=assertion.environment" \
    --project=$PROJECT_ID

# Update attribute condition
gcloud iam workload-identity-pools providers update-oidc "github-provider" \
    --location="global" \
    --workload-identity-pool="github-pool" \
    --attribute-condition="assertion.repository_owner=='your-org' && assertion.repository.startsWith('your-org/prod-')" \
    --project=$PROJECT_ID

Add New Service Account Binding:

# Bind new repository
gcloud iam service-accounts add-iam-policy-binding "${PROD_SA_EMAIL}" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.repository/your-org/new-repo" \
    --project=$PROJECT_ID

Update Cloud SQL Configuration:

# Increase instance size
gcloud sql instances patch production-db \
    --tier=db-custom-4-15360 \
    --project=$PROJECT_ID

# Add database flags
gcloud sql instances patch production-db \
    --database-flags=cloudsql.iam_authentication=on,max_connections=200,shared_buffers=2GB \
    --project=$PROJECT_ID

# Enable high availability
gcloud sql instances patch production-db \
    --availability-type=REGIONAL \
    --project=$PROJECT_ID

Cross-Project Access

Enable a service account in one project to be impersonated from another:

# Project A: Create workload identity pool
export PROJECT_A="project-a-id"
export PROJECT_B="project-b-id"
export PROJECT_A_NUMBER=$(gcloud projects describe $PROJECT_A --format="value(projectNumber)")

# Create pool in Project A
gcloud iam workload-identity-pools create "cross-project-pool" \
    --location="global" \
    --display-name="Cross-Project Pool" \
    --project=$PROJECT_A

# Create provider in Project A
gcloud iam workload-identity-pools providers create-oidc "cross-project-provider" \
    --location="global" \
    --workload-identity-pool="cross-project-pool" \
    --display-name="Cross-Project Provider" \
    --attribute-mapping="google.subject=assertion.sub,attribute.repository=assertion.repository" \
    --attribute-condition="assertion.repository_owner=='your-org'" \
    --issuer-uri="https://token.actions.githubusercontent.com" \
    --project=$PROJECT_A

# Create service account in Project B
gcloud iam service-accounts create cross-project-sa \
    --display-name="Cross-Project SA" \
    --project=$PROJECT_B

# Grant impersonation from Project A pool to Project B service account
gcloud iam service-accounts add-iam-policy-binding \
    "cross-project-sa@${PROJECT_B}.iam.gserviceaccount.com" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_A_NUMBER}/locations/global/workloadIdentityPools/cross-project-pool/attribute.repository/your-org/your-repo" \
    --project=$PROJECT_B

# Grant Project B service account permissions in Project B
gcloud projects add-iam-policy-binding $PROJECT_B \
    --member="serviceAccount:cross-project-sa@${PROJECT_B}.iam.gserviceaccount.com" \
    --role="roles/cloudsql.client" \
    --project=$PROJECT_B

Cleanup Script

To remove all resources:

#!/bin/bash
# cleanup.sh - Remove all Workload Identity Federation resources

export PROJECT_ID="your-project-id"
export PROJECT_NUMBER=$(gcloud projects describe $PROJECT_ID --format="value(projectNumber)")

echo "Removing service account bindings..."
gcloud iam service-accounts remove-iam-policy-binding \
    "github-actions-prod-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
    --role="roles/iam.workloadIdentityUser" \
    --member="principalSet://iam.googleapis.com/projects/${PROJECT_NUMBER}/locations/global/workloadIdentityPools/github-pool/attribute.ref/refs/heads/main" \
    --project=$PROJECT_ID

echo "Deleting workload identity provider..."
gcloud iam workload-identity-pools providers delete "github-provider" \
    --location="global" \
    --workload-identity-pool="github-pool" \
    --project=$PROJECT_ID \
    --quiet

echo "Deleting workload identity pool..."
gcloud iam workload-identity-pools delete "github-pool" \
    --location="global" \
    --project=$PROJECT_ID \
    --quiet

echo "Deleting service accounts..."
gcloud iam service-accounts delete "github-actions-prod-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
    --project=$PROJECT_ID \
    --quiet

gcloud iam service-accounts delete "github-actions-staging-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
    --project=$PROJECT_ID \
    --quiet

echo "Cleanup complete!"

Token Lifetime and Caching

Understand token lifetimes:

OIDC tokens from GitHub: Valid for 10 minutes
GCP access tokens: Valid for 1 hour by default
The auth action caches tokens: Reuses valid tokens within the workflow

For long-running jobs, tokens are automatically refreshed.

Performance Optimization

1. Cache Authentication Step

The google-github-actions/auth action automatically caches credentials. Ensure subsequent steps reuse them:

steps:
  - id: 'auth'
    uses: 'google-github-actions/auth@v2'
    with:
      workload_identity_provider: '...'
      service_account: '...'
      token_format: 'access_token'  # Cache access token
      access_token_lifetime: '3600s'  # 1 hour

2. Parallel Job Authentication

Each job needs separate authentication:

jobs:
  deploy-frontend:
    runs-on: ubuntu-latest
    steps:
      - id: 'auth'
        uses: 'google-github-actions/auth@v2'
        # ... auth config
  
  deploy-backend:
    runs-on: ubuntu-latest
    steps:
      - id: 'auth'
        uses: 'google-github-actions/auth@v2'
        # ... same auth config

Both jobs authenticate independently in parallel.

Monitoring and Observability

Create Custom Dashboard

Monitor Workload Identity Federation usage:

# Create log-based metrics
gcloud logging metrics create wif-token-exchanges \
    --description="Workload Identity token exchanges" \
    --log-filter='protoPayload.methodName="GenerateAccessToken"'

gcloud logging metrics create wif-successful-exchanges \
    --description="Successful WIF exchanges" \
    --log-filter='protoPayload.methodName="GenerateAccessToken" AND protoPayload.status.code=0'

gcloud logging metrics create wif-failed-exchanges \
    --description="Failed WIF exchanges" \
    --log-filter='protoPayload.methodName="GenerateAccessToken" AND protoPayload.status.code!=0'

Query Recent Authentications

# Last 10 authentication attempts
gcloud logging read \
    'protoPayload.methodName="GenerateAccessToken"
    AND resource.labels.service_account_id:"github-actions-sa"' \
    --limit=10 \
    --format='table(timestamp,protoPayload.authenticationInfo.principalEmail,protoPayload.status.code)' \
    --project=$PROJECT_ID

Cost Considerations

Workload Identity Federation has no additional cost:

✓ Token exchange operations: Free
✓ Workload Identity Pools: Free
✓ Workload Identity Providers: Free
✓ IAM operations: Free (within quota)

You only pay for:

Resources accessed by the service account (Cloud Run, BigQuery, etc.)
Cloud Audit Logs storage (if enabled for Data Access logs)

Migration from Service Account Keys

If you're currently using service account keys, here's the migration path:

Step 1: Set up Workload Identity Federation (as shown above)

Step 2: Update workflows to use both methods temporarily

- id: 'auth'
  uses: 'google-github-actions/auth@v2'
  with:
    workload_identity_provider: 'projects/.../providers/github-provider'
    service_account: '[email protected]'
    # Fallback to key if WIF fails during migration
    credentials_json: ${{ secrets.GCP_SA_KEY }}

Step 3: Test thoroughly in staging

Step 4: Remove the fallback

- id: 'auth'
  uses: 'google-github-actions/auth@v2'
  with:
    workload_identity_provider: 'projects/.../providers/github-provider'
    service_account: '[email protected]'
    # No fallback - fully migrated to WIF

Step 5: Delete service account keys

# List keys
gcloud iam service-accounts keys list \
    --iam-account=$SA_EMAIL \
    --project=$PROJECT_ID

# Delete each key
gcloud iam service-accounts keys delete KEY_ID \
    --iam-account=$SA_EMAIL \
    --project=$PROJECT_ID

Step 6: Remove GitHub Secrets

Delete the GCP_SA_KEY secret from your repository settings.

Conclusion

Workload Identity Federation represents a significant security improvement over traditional service account keys. By implementing the steps in this guide, you've:

✓ Eliminated long-lived credentials from your CI/CD pipeline
✓ Reduced the risk of credential leakage
✓ Simplified credential rotation (it's now automatic)
✓ Improved audit trail with detailed token exchange logs
✓ Implemented fine-grained access control based on repository, branch, and actor
✓ Secured database access with Cloud SQL IAM authentication
✓ Automated database migrations without storing database credentials

Google Cloud Platform Google Cloud Workload Identity Federation: A Guide to Keyless Authentication for Multi-Cloud Environments

Introduction

The Scenario: Multi-Cloud CI/CD Pipeline

Architecture Overview

Prerequisites

Enable Required APIs

Create a Service Account

Grant Necessary Permissions to Service Account

Create Workload Identity Pool

Create Workload Identity Provider

Understanding Attribute Mapping

Understanding Attribute Conditions

Grant Service Account Impersonation Permission

Advanced IAM Binding Examples

Get the Workload Identity Provider Resource Name

Set Up Cloud SQL Instance and Secrets

Create Cloud SQL Instance

Store Database Password in Secret Manager

Alternative: Use IAM Database Authentication (Recommended)

Configure Cloud SQL for Private IP (Best Practice)

Configure GitHub Actions Workflow

Key Workflow Elements

Advanced Cloud SQL Workflow Patterns

Pattern 1: Using IAM Authentication (Most Secure)

Pattern 2: Using Flyway for Database Migrations

Pattern 3: Blue-Green Database Migrations with Rollback

Pattern 4: Multi-Region Database Deployment

Advanced Configuration - Multiple Environments

Verification and Testing

Test 1: Verify Token Exchange

Test 2: Audit Logs

Security Best Practices

1. Principle of Least Privilege

2. Use Attribute Conditions Strictly

3. Separate Service Accounts by Environment

4. Enable Audit Logging

5. Monitor Token Exchange Activity

6. Cloud SQL Security Best Practices

7. Secrets Rotation Strategy

Troubleshooting Common Issues

Issue 1: "Permission denied" when exchanging token

Issue 2: "Attribute condition not satisfied"

Issue 3: Missing id-token: write permission

Issue 4: Insufficient permissions for service account

Issue 5: Cloud SQL Proxy Connection Timeout

Issue 6: IAM Database Authentication Failures

Issue 7: Secret Manager Access Denied

Issue 8: Cloud SQL Instance Not Found

Issue 9: Migration Script Fails Silently

Issue 10: Connection Pool Exhaustion

Advanced Topics

Complete Infrastructure Provisioning with gcloud

Using the Provisioning Script

Incremental Updates with gcloud

Cross-Project Access

Cleanup Script

Token Lifetime and Caching

Performance Optimization

1. Cache Authentication Step

2. Parallel Job Authentication

Monitoring and Observability

Create Custom Dashboard

Query Recent Authentications

Cost Considerations

Migration from Service Account Keys

Step 1: Set up Workload Identity Federation (as shown above)

Step 2: Update workflows to use both methods temporarily

Step 3: Test thoroughly in staging

Step 4: Remove the fallback

Step 5: Delete service account keys

Step 6: Remove GitHub Secrets

Conclusion

Issue 3: Missing `id-token: write` permission