Cloud SQL + IAM database authentication (ruby)

Cloud SQL + IAM database authentication (ruby).md

Cloud SQL + IAM database authentication (ruby)

docker-compose.yml:

services:
  app:
    image: alpine:3.19
    command: sleep infinity
    init: true
    volumes:
      - .:/app
    depends_on:
      - proxy

  proxy:
    image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.6.0
    command:
      --address 0.0.0.0
      --credentials-file /credentials.json
      --auto-iam-authn
      PROJECT_ID:europe-central2:test-sql
    volumes:
      - ./credentials.json:/credentials.json

main.tf:

provider "google" {
  project = "PROJECT_ID"
}

resource "google_sql_database_instance" "test-sql" {
  # deletion_protection = false
  name = "test-sql"
  region = "europe-central2"
  database_version = "POSTGRES_15"
  settings {
    tier = "db-f1-micro"
    database_flags {
      name = "cloudsql.iam_authentication"
      value = "on"
    }
  }
}

resource "google_service_account" "test-sql" {
  account_id = "test-sql"
}

resource "google_project_iam_member" "test-sql-client" {
  project = "PROJECT_ID"
  role = "roles/cloudsql.client"
  member = "serviceAccount:${google_service_account.test-sql.email}"
  condition {
    title = "test-sql"
    expression = "resource.name == 'projects/PROJECT_ID/instances/test-sql' && resource.service == 'sqladmin.googleapis.com'"
  }
}

resource "google_project_iam_member" "test-sql-instance-user" {
  project = "PROJECT_ID"
  role = "roles/cloudsql.instanceUser"
  member = "serviceAccount:${google_service_account.test-sql.email}"
  condition {
    title = "test-sql"
    expression = "resource.name == 'projects/PROJECT_ID/instances/test-sql' && resource.service == 'sqladmin.googleapis.com'"
  }
}

resource "google_sql_user" "test-sql-iam" {
  name = "test-sql@PROJECT_ID.iam"
  instance = google_sql_database_instance.test-sql.name
  type = "CLOUD_IAM_SERVICE_ACCOUNT"
}

a.rb:

require 'pg'
PG.connect(host: 'proxy', user: 'test-sql@PROJECT_ID.iam', dbname: 'postgres')

// replace PROJECT_ID
$ docker run --rm -itv "$PWD:/app" -w /app google/cloud-sdk:457.0.0-alpine
/app # gcloud auth login --update-adc
/app # apk add terraform
/app # terraform init
/app # terraform apply; echo -e '\a'
// create the service account key
$ mv ... credentials.json

$ docker compose up -d
$ docker compose exec app sh
/ # apk add postgresql-client
/ # psql -h proxy -U test-sql@PROJECT_ID.iam postgres
psql (16.2, server 15.5)
Type "help" for help.

postgres=> 
/ # apk add build-base ruby-dev postgresql-dev
/ # gem install pg -v 1.5.6
/ # ruby app/a.rb
#<PG::Connection:0x00007c9519dc4a58 host=proxy port=5432 user=test-sql@PROJECT_ID.iam>