Deploying Cloud SQL for PostgreSQL on GCP with Workload identity federation and Github action

This blog is a continuation of my last blog in the series. I recommend you go through my previous blogs before this one.

In the last blog post on the series Terraform on Google Cloud, I deployed a VM instance on GCP using Terraform and automated CI pipeline using Github Actions with service account key as secret.

In this blog I will not use service account key anymore. Instead I will use GCP Workload identity federation and ‘google-github-actions/auth@v1’ Github action to deploy the Terraform code for PostgreSQL on GCP.

What is Cloud SQL for PostgreSQL?

Cloud SQL for PostgreSQL is a fully managed database service that helps you set up, maintain, manage, and administer your PostgreSQL relational databases on Google Cloud Platform.

Why use Cloud SQL for PostgreSQL instead of running it old-school?

I remember those days when we used to go through so much trouble setting up the database instances on VM, installation, HA, scalability, security, backup, monitoring and whatnot.

Cloud SQL is here to take away all that hassle and let us use the databases in a simplified way.

Here are some key advantages of using managed PostgreSQL with Cloud SQL:

• Built-in high availability with automatic failover
• Vertical and horizontal scalability options
• Security features like network isolation, encryption at rest, ability to use customer-managed encryption keys, including IAM for fine-grained access control
• Seamless integration with various Google Cloud services such as Kubernetes Engine, Compute engine, Cloud Storage, BigQuery, Dataflow, etc.
• Integrates with Google Cloud’s monitoring and logging services
• Automatic Backups and Point-in-Time Recovery

Now that we understand why, let’s talk about how. I am going to deploy PostgreSQL using industry standard, Terraform, and GitHub Actions.

Terraform code for PostgreSQL

I will use some part, such as VPC network name, project id, etc. from my 2 earlier blog on this series. I recommend you go through those first if you haven’t already.

• Getting started — deploying network, storage etc.
• Deploying VM with Github actions

We will need multiple components for this. Lets create a sql.tf file and we will write the code for individual piece, and paste all code snippets in sql.tf

Global private IP address

To connect to other end points such as GKE pods.

# Add to sql.tf
resource "google_compute_global_address" "private_ip_address" {
  provider      = google-beta
  name          = "private-ip"
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"
  prefix_length = 16
  network       = google_compute_network.main_vpc.id
  project       = var.project_id
}

VPC connector

It is a networking component in Google Cloud that enables communication between a VPC network and other network resources, such as Cloud SQL instances. It allows you to avoid exposing a public IP address for your database, you can enforce network-level access control for your database.

# Add to sql.tf
resource "google_service_networking_connection" "private_vpc_connection" {
  provider                = google-beta
  network                 = google_compute_network.main_vpc.id
  service                 = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [google_compute_global_address.private_ip_address.name]
}

Cloud SQL instance

# Add to sql.tf
resource "google_sql_database_instance" "postgres_instance" {
  name                = var.db_name
  project             = var.project_id
  region              = var.region
  database_version    = "POSTGRES_15"
  deletion_protection = false

  settings {
    tier = var.db_machine_type
    
    disk_size         = 100
    disk_type         = "PD_SSD"
    disk_autoresize   = true
    
    backup_configuration {
      enabled                        = true
      start_time                     = "02:00"
      point_in_time_recovery_enabled = true
      location                       = var.region
      
      backup_retention_settings {
        retained_backups = 7
        retention_unit   = "COUNT"
      }
    }
    
    ip_configuration {
      ipv4_enabled                                  = false
      private_network                               = google_compute_network.main_vpc.id
      enable_private_path_for_google_cloud_services = true
      require_ssl                                   = true
    }
    
    database_flags {
      name  = "log_checkpoints"
      value = "on"
    }
    
    database_flags {
      name  = "log_connections"
      value = "on"
    }
    
    database_flags {
      name  = "log_disconnections"
      value = "on"
    }
  }
  
  depends_on = [google_service_networking_connection.private_vpc_connection]
}

deletion_protection = false — use this option when deploying with terraform. If you use deletion_protection = true, you won’t be able destroy the instance with terraform destroy.

enable_private_path_for_google_cloud_services = true — so your db instance can talk to other Google Cloud services via private ip

Username and password

For authentication

# Add to sql.tf
resource "random_password" "postgres_password" {
  length  = 16
  special = true
}

resource "google_sql_user" "postgres_user" {
  name     = "postgres"
  instance = google_sql_database_instance.postgres_instance.name
  password = random_password.postgres_password.result
  project  = var.project_id
}

# Create application database
resource "google_sql_database" "app_database" {
  name     = "appdb"
  instance = google_sql_database_instance.postgres_instance.name
  project  = var.project_id
}

Add to variables.tf

variable "db_name" {
  type        = string
  description = "name of the postgresql instance"
  default     = "postgres-db"
}

variable "db_machine_type" {
  type        = string
  description = "machine type for db instance"
  default     = "db-f1-micro"
  validation {
    condition = contains([
      "db-f1-micro", "db-g1-small", "db-n1-standard-1", 
      "db-n1-standard-2", "db-n1-highmem-2", "db-n2-standard-2"
    ], var.db_machine_type)
    error_message = "Database machine type must be a valid Cloud SQL machine type."
  }
}

There are different types of instances available on GCP for Cloud SQL — db-f1-micro, db-g1-small, db-n1-standard, db-n1-highmem, db-n2-standard, db-n2-highmem, db-custom-#

Add to terraform.tfvars

db_name         = "postgres-db"
db_machine_type = "db-f1-micro"

Add to providers.tf

Make sure you have the google-beta provider configured:

terraform {
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 5.0"
    }
    google-beta = {
      source  = "hashicorp/google-beta"
      version = "~> 5.0"
    }
    random = {
      source  = "hashicorp/random"
      version = "~> 3.1"
    }
  }
  required_version = ">= 1.0"
}

provider "google-beta" {
  project = var.project_id
  region  = var.region
}

provider "random" {}

Your terraform configuration is ready. Now lets get to CI part using Github Actions and Workload identity federation — Keyless CI

Let’s talk about Workload identity federation in GCP

Workload Identity Federation in GCP enables the integration of GCP services with external identity providers (IDPs) using the OpenID Connect (OIDC) standard. It allows you to use external identities to authenticate and authorize access to GCP resources.

How to use it to authorize Github Action?

We need 3 components:

1. Workload Identity Pool
2. Workload Identity Provider
3. Service Account with required permissions
4. auth(Service Account) Github Action

I will be deploying it using terraform and Github workflow from last blog post where I used service account credentials stored in GitHub secrets.

I will create Workload Identity Pool and Identity Provider using google terraform module.

Create a file wip.tf and add below lines.

Service account creation

Add below lines for service account creation into wip.tf

# Service account for GitHub Actions
resource "google_service_account" "github_actions_sa" {
  project      = var.project_id
  account_id   = "github-actions-sa"
  display_name = "GitHub Actions Service Account"
  description  = "Service account for GitHub Actions workflows"
}

# Grant necessary permissions to the service account
resource "google_project_iam_member" "github_actions_permissions" {
  for_each = toset([
    "roles/compute.admin",
    "roles/storage.admin", 
    "roles/cloudsql.admin",
    "roles/servicenetworking.networksAdmin",
    "roles/iam.serviceAccountUser"
  ])
  
  project = var.project_id
  role    = each.value
  member  = "serviceAccount:${google_service_account.github_actions_sa.email}"
}

Workload Identity Pool and Provider

Add below lines of code to create Workload Identity Pool and Identity Provider into wip.tf.

module "gh_oidc" {
  source      = "terraform-google-modules/github-actions-runners/google//modules/gh-oidc"
  version     = "~> 3.1"
  project_id  = var.project_id
  pool_id     = var.workload_identity_pool_name
  pool_display_name = var.workload_identity_pool_display_name
  provider_id = var.workload_identity_provider_name
  
  sa_mapping = {
    (google_service_account.github_actions_sa.account_id) = {
      sa_name   = google_service_account.github_actions_sa.name
      attribute = "attribute.repository/your-github-username/your-repo-name"
    }
  }
}

Add the variables to variables.tf

Add the variables to variables.tf and set default values for them:

variable "workload_identity_pool_name" {
  type        = string
  description = "Name of the Workload Identity Pool."
  default     = "github-pool"
}

variable "workload_identity_pool_display_name" {
  type        = string
  description = "Display name of Workload Identity Pool."
  default     = "GitHub Actions Pool"
}

variable "workload_identity_provider_name" {
  type        = string
  description = "Name of the Workload Identity Provider."
  default     = "github-provider"
}

variable "project_number" {
  type        = string
  description = "GCP project number"
  # Get this from: gcloud projects describe PROJECT_ID --format="value(projectNumber)"
}

variable "github_repository" {
  type        = string
  description = "GitHub repository in format: username/repo-name"
}

Add to terraform.tfvars

project_number    = "123456789012"  # Replace with your project number
github_repository = "your-username/your-repo-name"  # Replace with your repo

Push the code to main branch and it will trigger the build and deploy the resources.

You can also choose to create workload identity pool and provider using gcloud commands, I will talk about it in my next blog post.

Now lets use Workload Identity Federation in GitHub Actions

Now that we have setup Workload identity federation, lets use this to make our Github Action workflow keyless, and secure.

I will use the same workflow from last blog and will change the authentication part to leverage Workload identity federation.

Update your .github/workflows/deploy.yml:

name: Terraform Deployment with PostgreSQL

on:
  push:
    branches:
      - main
      - develop
  pull_request:
    branches:
      - main

env:
  PROJECT_ID: your-project-id      # Replace with your project ID
  PROJECT_NUMBER: "123456789012"   # Replace with your project number
  WIF_PROVIDER: "projects/123456789012/locations/global/workloadIdentityPools/github-pool/providers/github-provider"
  WIF_SERVICE_ACCOUNT: "github-actions-sa@your-project-id.iam.gserviceaccount.com"

jobs:
  terraform:
    name: Terraform Plan and Apply
    runs-on: ubuntu-latest
    
    permissions:
      contents: read
      id-token: write
      pull-requests: write

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4
      
      - id: auth
        name: Authenticate to Google Cloud
        uses: google-github-actions/auth@v1
        with:
          create_credentials_file: true
          workload_identity_provider: ${{ env.WIF_PROVIDER }}
          service_account: ${{ env.WIF_SERVICE_ACCOUNT }}

      - name: Set up Cloud SDK
        uses: google-github-actions/setup-gcloud@v1
        with:
          version: latest
          
      - name: Setup Terraform
        uses: hashicorp/setup-terraform@v3
        with:
          terraform_version: 1.6.0

      - name: Terraform Format Check
        id: fmt
        run: terraform fmt -check -diff -recursive
        continue-on-error: true

      - name: Terraform Initialize
        id: init
        run: terraform init

      - name: Terraform Validation
        id: validate
        run: terraform validate

      - name: Terraform Plan
        id: plan
        run: |
          terraform plan -input=false -out=tfplan -detailed-exitcode
        continue-on-error: true

      - name: Comment PR with Plan
        if: github.event_name == 'pull_request'
        uses: actions/github-script@v7
        with:
          script: |
            const output = `#### Terraform Format and Style 🖌\`${{ steps.fmt.outcome }}\`
            #### Terraform Initialization ⚙️\`${{ steps.init.outcome }}\`
            #### Terraform Validation 🤖\`${{ steps.validate.outcome }}\`
            #### Terraform Plan 📖\`${{ steps.plan.outcome }}\`

            <details><summary>Show Plan</summary>

            \`\`\`terraform
            ${{ steps.plan.outputs.stdout }}
            \`\`\`

            </details>

            *Pusher: @${{ github.actor }}, Action: \`${{ github.event_name }}\`*`;

            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: output
            })

      - name: Terraform Apply
        if: github.ref == 'refs/heads/main' && github.event_name == 'push'
        run: terraform apply -input=false tfplan

      - name: Output Database Information
        if: github.ref == 'refs/heads/main' && github.event_name == 'push'
        run: |
          echo "## 🗃️ Database Deployed Successfully!" >> $GITHUB_STEP_SUMMARY
          echo "| Resource | Value |" >> $GITHUB_STEP_SUMMARY
          echo "|----------|-------|" >> $GITHUB_STEP_SUMMARY
          echo "| Database Instance | $(terraform output -raw db_instance_name) |" >> $GITHUB_STEP_SUMMARY
          echo "| Connection Name | $(terraform output -raw db_connection_name) |" >> $GITHUB_STEP_SUMMARY
          echo "| Private IP | $(terraform output -raw db_private_ip) |" >> $GITHUB_STEP_SUMMARY

      - name: Clean up
        if: always()
        run: rm -f tfplan

Add database outputs

Add these outputs to your outputs.tf:

# Database outputs
output "db_instance_name" {
  value       = google_sql_database_instance.postgres_instance.name
  description = "Name of the PostgreSQL instance"
}

output "db_connection_name" {
  value       = google_sql_database_instance.postgres_instance.connection_name
  description = "Connection name for the PostgreSQL instance"
}

output "db_private_ip" {
  value       = google_sql_database_instance.postgres_instance.private_ip_address
  description = "Private IP address of the PostgreSQL instance"
}

output "db_public_ip" {
  value       = google_sql_database_instance.postgres_instance.public_ip_address
  description = "Public IP address of the PostgreSQL instance (if enabled)"
}

# Sensitive output for password (use carefully)
output "db_password" {
  value       = random_password.postgres_password.result
  description = "Generated password for postgres user"
  sensitive   = true
}

Testing your deployment

This is it. Now commit and push your changes:

git add .
git commit -m "Add PostgreSQL deployment with Workload Identity Federation"
git push origin main

This will trigger the workflow and your all terraform resources will be deployed.

Verify your database deployment

Once deployed, you can verify your database:

# List Cloud SQL instances
gcloud sql instances list

# Get instance details
gcloud sql instances describe postgres-db

# Connect to the database (from a VM in the same VPC)
gcloud sql connect postgres-db --user=postgres

Connect from your VM

You can connect to the database from the VM we deployed in Part 2:

# SSH into your VM
gcloud compute ssh demo-dev-vm --zone=us-central1-b

# Install PostgreSQL client
sudo apt-get update
sudo apt-get install -y postgresql-client

# Connect to the database using private IP
psql -h PRIVATE_IP_ADDRESS -U postgres -d appdb

Benefits of Workload Identity Federation

Security advantages:

• No service account keys stored in GitHub secrets
• Short-lived tokens instead of long-lived keys
• Automatic token rotation handled by Google
• Audit trails for all authentication events

Operational benefits:

• No key management overhead
• Automatic security with OIDC standards
• Integration with existing GitHub workflows
• Scalable across multiple repositories

What we built

In this blog, we successfully:

✅ Deployed Cloud SQL PostgreSQL with private networking
✅ Configured VPC peering for secure database access
✅ Implemented Workload Identity Federation for keyless authentication
✅ Automated deployment with GitHub Actions
✅ Set up proper backup and monitoring configurations

Next up in Part 4

In the next blog, I will use Cloud Functions to rotate the service account key stored in Secret Manager and automate secret management.

Thank you for reading, I hope this post has added some value to you.

Connect with me:

• LinkedIn for more Google Cloud, Terraform, Python and other DevOps tools content

Tags: #GoogleCloud #Terraform #PostgreSQL #GitHubActions #DevOps #Security #CloudSQL #WorkloadIdentityFederation