spring-bootkubernetesgoogle-kubernetes-enginegoogle-cloud-sqlidentity-management

Connecting Google Cloud SQL using Google Kubernetes: Insufficient Permission - GenerateEphemeralCert

I am trying to connect to MySQL CLoud SQL using GKE. I am using Spring Boot. But I cant establish the connection to the database.

I followed information from https://cloud.google.com/sql/docs/postgres/connect-kubernetes-engine

I've created the service account and service account name and bindings as given in the documentation. I even went ahead and gave a "Owner" IAM role for the service account, but the problem still persists. I'm pretty sure that the db credential secrets are correct, and can access the database in appengine and through db clients.

I get the following error:

{
  "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"
}

Full-stack trace:

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]

Deployment yaml file: (Edited - added the sql-cloud-proxy side-car pattern)

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

The application has data connection config in application.yml

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

Any pointers leading to a solution would be much appreciated. Thanks.

Solution

  • One thing you can double check is the host VM's access scope. Steps:

    enter image description here

    Eno also gave very good suggestions in GitHub. Copied it here (https://github.com/GoogleCloudPlatform/cloud-sql-proxy/issues/2104#issuecomment-1906512533) to share with those who are interested.