GCP Cloud SQL auth failure

[hulto]

Work around

1. Terraform apply
2. Hit error
3. Download connect auth proxy https://cloud.google.com/sql/docs/mysql/connect-auth-proxy
4. Run it with correct SQL "Connection name". Eg. ./cloud-sql-proxy ccdc-red-team-infra:us-east4:tavern-db2
5. Login with mysql

$ mysql --get-server-public-key -h 127.0.0.1 -u tavern -p
Enter password: 

6. Paste password from cloud run YAML to login
   
7. Re-apply terraform

Describe the bug
When deploying Realm with Terraform the container fails to start with error:

2024/10/25 15:58:08 fatal error: failed to initialize graph schema: mysql: querying mysql version Error 1045 (28000): Access denied for user 'tavern2'@'cloudsqlproxy~34.34.234.112' (using password: YES)

To Reproduce
Follow terraform setup.
Terraform apply.

Expected behavior
Realm should be able to authenticate to the sql database.

Screenshots

Additional context
Able to reproduce locally using the cloud sql auth proxy https://cloud.google.com/sql/docs/mysql/connect-auth-proxy

Testing a manually created user tavern2 and an automated user created tavern-auto

The manually created user is able to authenticate the automatically created one is not (even after updating TF to explicitly allow "%")

[hulto]

Without changing anything.

Work around:

1. Terraform apply and get error.

2. Validate with cloud SQL connector

$ ./cloud-sql-proxy ccdc-red-team-infra:us-east4:tavern-db2
2024/10/25 12:49:04 Authorizing with Application Default Credentials
2024/10/25 12:49:05 [ccdc-red-team-infra:us-east4:tavern-db2] Listening on 127.0.0.1:3306
2024/10/25 12:49:05 The proxy has started successfully and is ready for new connections!

$ mysql --get-server-public-key -h 127.0.0.1 -u tavern -p     
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 8.0.31-google (Google)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ^DBye

3. Be surprised it worked....

4. Try re-deploying tavern cloud run > tavern > deploy revision > change max scale > delpoy

5. Be suprised it worked...

6. re-run Terraform apply to finish the build....

[hulto]

Manually removing the SQL user and cloud run service and re-running to try reproducing the error.
Re-running TF to create both.

Get the error.

Waited 10 seconds.

Able to connect with cloud proxy.
Terraform works now.

[hulto]

Testing without connecting via auth proxy in case that's somehow changing things.
Tried 3 times over a 8 minute window and no success.

Testing with auth proxy terraform works immediately after.

[hulto]

Is cloud sql connector changing / fixing IAM permissions to connect to the DB?
Weird because the connector auths with your users IAM role not the service account.
Plus the service account is Editor.

[hulto]

Tried updating to cloud_run_v2_service with the same issues.

resource "google_cloud_run_v2_service" "tavern" {
  name     = "tavern"
  location = var.gcp_region
  ingress  = "INGRESS_TRAFFIC_ALL"

  traffic {
    type    = "TRAFFIC_TARGET_ALLOCATION_TYPE_LATEST"
    percent = 100
  }

  template {
    containers {
      name  = local.tavern_container_name
      image = var.tavern_container_image
      ports {
        container_port = 80
      }
      env {
        name  = "MYSQL_NET"
        value = "unix"
      }
      env {
        name  = "MYSQL_USER"
        value = google_sql_user.tavern-user.name
      }
      env {
        name  = "MYSQL_PASSWD"
        value = google_sql_user.tavern-user.password
      }
      env {
        name  = "MYSQL_DB"
        value = google_sql_database.tavern-db.name
      }
      env {
        name  = "MYSQL_ADDR"
        value = format("/cloudsql/%s", google_sql_database_instance.tavern-sql-instance.connection_name)
      }
      env {
        name  = "OAUTH_CLIENT_ID"
        value = var.oauth_client_id
      }
      env {
        name  = "OAUTH_CLIENT_SECRET"
        value = var.oauth_client_secret
      }
      env {
        name  = "OAUTH_DOMAIN"
        value = format("https://%s", var.oauth_domain)
      }
      env {
        name  = "ENABLE_METRICS"
        value = var.enable_metrics ? "1" : ""
      }

    }

    // Only create prometheus sidecar if metrics enabled
    dynamic "containers" {
      for_each = var.enable_metrics ? [{
        image = "us-docker.pkg.dev/cloud-ops-agents-artifacts/cloud-run-gmp-sidecar/cloud-run-gmp-sidecar:1.0.0"
        name  = local.prometheus_container_name
      }] : []
      content {
        name  = containers.value.name
        image = containers.value.image
      }
    }

    volumes {
      name = "cloudsql"
      cloud_sql_instance {
        instances = [google_sql_database_instance.tavern-sql-instance.connection_name]
      }
    }

    scaling {
      min_instance_count = var.min_scale
      max_instance_count = var.max_scale
    }

    session_affinity = true

    annotations = {
      for k, v in {
        "run.googleapis.com/client-name"            = "terraform"
        "run.googleapis.com/container-dependencies" = var.enable_metrics ? jsonencode({ "${local.prometheus_container_name}" = [local.tavern_container_name] }) : ""
      } : k => v if v != ""
    }
  }
}

[hulto]

Just establishing a connection with cloud-sql-proxy or netcatting to it is not sufficient to resolve the issue.
You need to authenticate with a valid login.

[hulto]

Following this guide was able to get a test app to connect to the tavern-db.
https://cloud.google.com/sql/docs/mysql/connect-instance-cloud-run#go

        env:
        - name: INSTANCE_UNIX_SOCKET
          value: /cloudsql/ccdc-red-team-infra:us-east4:tavern-db
        - name: INSTANCE_CONNECTION_NAME
          value: ccdc-red-team-infra:us-east4:tavern-db
        - name: DB_NAME
          value: quickstart-db # A different DB on the server than realm uses.
        - name: DB_USER
          value: tavern
        - name: DB_PASS
          value: [REDACTED]

Once the app deployed I browsed to it triggering a DB connection.
And then the terraform deploy worked.

So the issues is fixed anytime the cloud-sql-proxy connection is established and cloud run containers can create that connection correctly the first try.

[hulto]

Test apps unix socket connection code.
https://github.com/GoogleCloudPlatform/golang-samples/blob/main/cloudsql/mysql/database-sql/connect_unix.go

• Verified that the tavern ConfigureMySQLFromEnv returns the correct DSN

When sample app only has unix socket auth (No INSTANCE_CONNECTION_NAME) it also fails.