GKE - MySQL CloudSQL Conectivity Issue: ACCESS_TOKEN_SCOPE_INSUFFICIENT

[anz000]

Question

I am trying to port a Spring Boot app from AppEngine to GKE. I've got the docker images up and running, and it launches. But in the startup an attempt is made to connect to the database and it fails.

The logtrace is:

Caused by: java.lang.RuntimeException: Unable to get valid instance data within 45000 ms. Last refresh attempt failed:java.lang.RuntimeException: [my-gcloud-project:us-central1:my-gcloud-project-v1] Failed to create ephemeral certificate for the Cloud SQL instance.
        at com.google.cloud.sql.core.Refresher.getConnectionInfo(Refresher.java:114) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.core.DefaultConnectionInfoCache.getConnectionInfo(DefaultConnectionInfoCache.java:92) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.core.DefaultConnectionInfoCache.createSslSocket(DefaultConnectionInfoCache.java:101) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.core.Connector.connect(Connector.java:113) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.core.InternalConnectorRegistry.connect(InternalConnectorRegistry.java:179) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.mysql.SocketFactory.connect(SocketFactory.java:63) ~[mysql-socket-factory-connector-j-8-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.mysql.SocketFactory.connect(SocketFactory.java:45) ~[mysql-socket-factory-connector-j-8-1.15.1.jar!/:1.15.1]
        at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:62) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
        at com.mysql.cj.NativeSession.connect(NativeSession.java:120) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
        at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:935) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:805) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:438) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:189) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar!/:?]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar!/:?]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar!/:?]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) ~[HikariCP-4.0.3.jar!/:?]
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) ~[HikariCP-4.0.3.jar!/:?]
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-4.0.3.jar!/:?]
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-4.0.3.jar!/:?]
        at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:48) ~[flyway-core-8.5.13.jar!/:?]
        at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:75) ~[flyway-core-8.5.13.jar!/:?]
        at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:147) ~[flyway-core-8.5.13.jar!/:?]
        at org.flywaydb.core.Flyway.migrate(Flyway.java:124) ~[flyway-core-8.5.13.jar!/:?]
        at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-2.7.18.jar!/:2.7.18]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.31.jar!/:5.3.31]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.31.jar!/:5.3.31]
        ... 26 more
Caused by: java.util.concurrent.ExecutionException: java.lang.RuntimeException: [my-gcloud-project:us-central1:my-gcloud-project-v1] Failed to create ephemeral certificate for the Cloud SQL instance.
        at com.google.common.util.concurrent.AbstractFuture.getDoneValue(AbstractFuture.java:594) ~[guava-33.0.0-jre.jar!/:?]
        at com.google.common.util.concurrent.AbstractFuture.get(AbstractFuture.java:553) ~[guava-33.0.0-jre.jar!/:?]
        at com.google.common.util.concurrent.AbstractFuture$TrustedFuture.get(AbstractFuture.java:110) ~[guava-33.0.0-jre.jar!/:?]
        at com.google.cloud.sql.core.Refresher.handleRefreshResult(Refresher.java:196) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.core.Refresher.lambda$startRefreshAttempt$1(Refresher.java:188) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.common.util.concurrent.CombinedFuture$AsyncCallableInterruptibleTask.runInterruptibly(CombinedFuture.java:165) ~[guava-33.0.0-jre.jar!/:?]
        at com.google.common.util.concurrent.CombinedFuture$AsyncCallableInterruptibleTask.runInterruptibly(CombinedFuture.java:153) ~[guava-33.0.0-jre.jar!/:?]
        at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:76) ~[guava-33.0.0-jre.jar!/:?]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_212]
        at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_212]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) ~[?:1.8.0_212]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) ~[?:1.8.0_212]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_212]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_212]
        at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_212]
Caused by: java.lang.RuntimeException: [my-gcloud-project:us-central1:my-gcloud-project-v1] Failed to create ephemeral certificate for the Cloud SQL instance.
        at com.google.cloud.sql.core.DefaultConnectionInfoRepository.addExceptionContext(DefaultConnectionInfoRepository.java:408) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.core.DefaultConnectionInfoRepository.fetchEphemeralCertificate(DefaultConnectionInfoRepository.java:278) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.core.DefaultConnectionInfoRepository.lambda$getConnectionInfo$1(DefaultConnectionInfoRepository.java:112) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.common.util.concurrent.CombinedFuture$CallableInterruptibleTask.runInterruptibly(CombinedFuture.java:196) ~[guava-33.0.0-jre.jar!/:?]
        at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:76) ~[guava-33.0.0-jre.jar!/:?]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_212]
        at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_212]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) ~[?:1.8.0_212]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) ~[?:1.8.0_212]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_212]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_212]
        at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_212]
Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 403 Forbidden
POST https://sqladmin.googleapis.com/sql/v1beta4/projects/my-gcloud-project/instances/my-gcloud-project-v1:generateEphemeralCert
{
  "code": 403,
  "details": [
    {
      "@type": "type.googleapis.com/google.rpc.ErrorInfo",
      "reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT",
      "domain": "googleapis.com",
      "metadata": {
        "method": "google.cloud.sql.v1beta4.SqlConnectService.GenerateEphemeralCert",
        "service": "sqladmin.googleapis.com"
      }
    }
  ],
  "errors": [
    {
      "domain": "global",
      "message": "Insufficient Permission",
      "reason": "insufficientPermissions"
    }
  ],
  "message": "Request had insufficient authentication scopes.",
  "status": "PERMISSION_DENIED"
}
        at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146) ~[google-api-client-2.2.0.jar!/:2.2.0]
        at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:118) ~[google-api-client-2.2.0.jar!/:2.2.0]
        at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:37) ~[google-api-client-2.2.0.jar!/:2.2.0]
        at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$3.interceptResponse(AbstractGoogleClientRequest.java:466) ~[google-api-client-2.2.0.jar!/:2.2.0]
        at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1111) ~[google-http-client-1.43.3.jar!/:1.43.3]
        at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:552) ~[google-api-client-2.2.0.jar!/:2.2.0]
        at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:493) ~[google-api-client-2.2.0.jar!/:2.2.0]
        at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:603) ~[google-api-client-2.2.0.jar!/:2.2.0]
        at com.google.cloud.sql.core.DefaultConnectionInfoRepository.fetchEphemeralCertificate(DefaultConnectionInfoRepository.java:276) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.cloud.sql.core.DefaultConnectionInfoRepository.lambda$getConnectionInfo$1(DefaultConnectionInfoRepository.java:112) ~[jdbc-socket-factory-core-1.15.1.jar!/:1.15.1]
        at com.google.common.util.concurrent.CombinedFuture$CallableInterruptibleTask.runInterruptibly(CombinedFuture.java:196) ~[guava-33.0.0-jre.jar!/:?]
        at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:76) ~[guava-33.0.0-jre.jar!/:?]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_212]
        at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_212]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) ~[?:1.8.0_212]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) ~[?:1.8.0_212]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_212]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_212]
        at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_212]

I've gone through the documentation at

• https://cloud.google.com/sql/docs/mysql/connect-kubernetes-engine#service-account-key
• https://cloud.google.com/sql/docs/mysql/connect-instance-kubernetes

My deployment.yaml looks like:

Code

apiVersion: apps/v1
kind: Deployment
metadata:
  name: my-gcloud-services
spec:
  replicas: 1
  selector:
    matchLabels:
      app: my-gcloud-services
  template:
    metadata:
      labels:
        app: my-gcloud-services
    spec:
      serviceAccountName: ksa-cloud-sql #my-gcloud-gke-service-account
      containers:
      - name: my-gcloud-services
        image: gcr.io/my-gcloud-project/my-gcloud-services:gke1
        ports:
        - containerPort: 8080  # Your application port
        env:
        - name: PORT
          value: "8080"
        - name: INSTANCE_CONNECTION_NAME
          value: my-gcloud-project:us-central1:my-gcloud-project-v1
        - name: DB_PORT
          value: "3306"
        - name: DB_USER
          valueFrom:
            secretKeyRef:
              name: sql-credentials
              key: username
        - name: DB_PASS
          valueFrom:
            secretKeyRef:
              name: sql-credentials
              key: password
        - name: DB_NAME
          valueFrom:
            secretKeyRef:
              name: sql-credentials
              key: database
      - name: cloud-sql-proxy
        image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:latest
        args:
          - "--auto-iam-authn"
          - "--port:3306"
          - "my-gcloud-project:us-central1:my-gcloud-project-v1"
        securityContext:
          runAsNonRoot: true
        resources:
          requests:
            memory: "2Gi"
            cpu: "1"
        volumeMounts:
          - name: cloudsql-instance-credentials
            mountPath: /secrets/cloudsql
            readOnly: true
      volumes:
        - name: cloudsql-instance-credentials
          secret:
            secretName: google-credentials

Additional Details

My application.yml looks like:

spring:
  datasource:
    url: jdbc:mysql://google/my-gcloud-database?cloudSqlInstance=my-gcloud-project:us-central1:my-gcloud-project-v1&socketFactory=com.google.cloud.sql.mysql.SocketFactory
    username: my-user
    password: my-password
..

I'm lost in the pile of documentation and tutorials to get GKE to get access to MySQL instance.

[enocom]

Two things:

1. You're using the Cloud SQL Java Connector and the Cloud SQL Proxy. In fact, only one is necessary. Either use the Proxy with a standard database driver, or drop the Proxy and use the Cloud SQL Java Connector.
2. Instead of using a credentials file, I recommend using Workload Identity. This will simplify the connection path. For using a credentials file, though, see the Java Connector's docs. You'll also need to ensure your VMs have the Cloud SQL Admin API OAuth2 scope, but only if your GKE VMs are using the default Compute Service Account.

[anz000]

Yeah, I was trying both approach and failed with both. I feel like I'm missing one or two things with either approach. if using Workload Identity is preferred, I can try to use it.

When I build the cluster, if I assign a service account (from AppEngine - that works with access to Cloud SQL on AppEngine instances), then it works. When I don't assign any service account, it falls back to the default compute engine service account, and I run into the issue. Both service account have the same roles, but the compute-engine SA has some weird IAM issues.

Update application.yml to use 127.0.0.1 for the database:

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/db?serverTimezone=UTC
    username: my-db-username
    password: my-db-password

..

Here's the updated deployment.yaml file

apiVersion: apps/v1
kind: Deployment
metadata:
  name: my-gcloud-services
spec:
  replicas: 1
  selector:
    matchLabels:
      app: my-gcloud-services
  template:
    metadata:
      labels:
        app: my-gcloud-services
    spec:
      serviceAccountName: ksa-cloud-sql #my-gcloud-gke-service-account
      containers:
      - name: my-gcloud-services
        image: gcr.io/my-gcloud-project/my-gcloud-services:gke1
        ports:
        - containerPort: 8080  # Your application port
        env:
        - name: PORT
          value: "8080"
        - name: INSTANCE_CONNECTION_NAME
          value: my-gcloud-project:us-central1:my-gcloud-project-v1
        - name: DB_HOST
          value: "127.0.0.1"
        - name: DB_PORT
          value: "3306"
        - name: DB_USER
          valueFrom:
            secretKeyRef:
              name: sql-credentials
              key: username
        - name: DB_PASS
          valueFrom:
            secretKeyRef:
              name: sql-credentials
              key: password
        - name: DB_NAME
          valueFrom:
            secretKeyRef:
              name: sql-credentials
              key: database
      - name: cloud-sql-proxy
        image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.8.0
        args:
          - "--structured-logs"
          - "--port=3306"
          - "my-gcloud-project:us-central1:my-gcloud-project-v1"
        securityContext:
          runAsNonRoot: true

[enocom]

Looks good to me. I'd suggest using a non-default service account. Nonetheless, for the sake of completeness, you can use the default service account and then under Node pools -> Security (for standard clusters), enable the Cloud SQL Admin API access:

[anz000]

I was using gcloud. Adding the scope --scopes=https://www.googleapis.com/auth/cloud-platform when creating the cluster fixes the issue.

I appreciate your help.

[enocom]

Glad to hear it.