Failed to call sqladmin.googleapi from Spring cloud GCP postgresql when using service account

[at9pm]

I am connecting a Springboot application on my local computer to GCP Cloud SQL, using Spring cloud dependency com.google.cloud:spring-cloud-gcp-starter-sql-postgresql:4.8.2

The dependency is trying to call below endpoint and got 401 unauthorized.

https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/connect/generateEphemeralCert

I am calling with a service account with roles Cloud SQL Admin, Cloud SQL Client, Cloud SQL Editor, Cloud SQL Instance User. Downloaded the json key file and put it under my resource folder. Configured application.properties as below.

  datasource:
    driver-class-name: org.postgresql.Driver
    username: username
    password: password
  cloud:
    gcp:
      sql:
        database-name: database-name
        instance-connection-name: instance-connection-name
        credentials:
          location: file:credential.json
          scopes: DEFAULT_SCOPES,https://www.googleapis.com/auth/sqlservice.admin,https://www.googleapis.com/auth/cloud-platform

When I startup my Springboot application, it is saying

java.lang.RuntimeException: Failed to create ephemeral certificate for the Cloud SQL instance.
    at com.google.cloud.sql.core.SqlAdminApiFetcher.addExceptionContext(SqlAdminApiFetcher.java:380)
    at com.google.cloud.sql.core.SqlAdminApiFetcher.fetchEphemeralCertificate(SqlAdminApiFetcher.java:277)
    at com.google.cloud.sql.core.SqlAdminApiFetcher.lambda$getInstanceData$1(SqlAdminApiFetcher.java:117)
    at com.google.common.util.concurrent.CombinedFuture$CallableInterruptibleTask.runInterruptibly(CombinedFuture.java:196)
    at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:74)
    ... 6 common frames omitted
Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 401 Unauthorized
POST https://sqladmin.googleapis.com/sql/v1beta4/projects/my-project-id/instances/my-sql-instance:generateEphemeralCert
    at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:118)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:37)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$3.interceptResponse(AbstractGoogleClientRequest.java:466)
    at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1111)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:552)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:493)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:603)
    at com.google.cloud.sql.core.SqlAdminApiFetcher.fetchEphemeralCertificate(SqlAdminApiFetcher.java:275)
    ... 9 common frames omitted

I also tried to add the dependency com.google.cloud:spring-cloud-gcp-starter:4.8.2 and configure credentials and project-id under spring.cloud.gcp, but it does not help at all. I am new to GCP. I also tried to open up firewall rule, also does not help.

[ms4446]

The "401 Unauthorized" error message indicates that the service account lacks the necessary permissions to access the Cloud SQL Admin API.

To address this, ensure the service account is assigned the following roles:

• Cloud SQL Admin
• Cloud SQL Client
• Cloud SQL Editor
• Cloud SQL Instance User

You can assign these roles to the service account via the Cloud IAM console or using the gcloud command-line tool.

After granting the required roles, you should be able to connect to your Cloud SQL instance using the Spring Cloud GCP starter.

If the issue persists:

1. Verify the service account JSON key file's location. By default, it should be in src/main/resources.
2. Ensure the service account JSON key file isn't corrupted. Consider downloading a fresh copy.
3. Confirm the service account email address is correct. This can be found in the Cloud IAM console.

[at9pm]

I suspect either there is something wrong for the dependency com.google.cloud:spring-cloud-gcp-starter-sql-postgresql:4.8.2 when using the key file to do authentication, or another explanation is the dependency cannot read my key file but not throwing out any message that it cannot read the key.

I tried another approach by using gcloud cli for ADC, which is working.

[ms4446]

There are a few things you can try to troubleshoot the issue with the com.google.cloud:spring-cloud-gcp-starter-sql-postgresql:4.8.2 dependency:

1. Key File Location: Ensure that the key file is in the correct location. The default location is src/main/resources.

2. Key File Integrity: Verify that the key file is not corrupted. Consider downloading a fresh copy of the key file from the GCP Console.

3. Service Account Email: Ensure that the service account email address is correct. You can find the service account email address in the Cloud IAM console.

4. Credentials Property: Try specifying the path to your key file in your application.properties:

   spring.cloud.gcp.sql.credentials.location=file:/path/to/key/file

   Replace /path/to/key/file with the actual path to your service account key file.

5. Using gcloud for Authentication: If you're still having issues, you can use the gcloud command-line tool to generate an access token. Once you have the token, you can use it in your application. Here's a general approach:

   • First, generate the access token using the command line:
     gcloud auth print-access-token
   • Capture the output token and use it in your Java application as needed. Note that this token has a limited lifespan, so it's best suited for debugging purposes or short-lived operations.

Remember to always handle access tokens with care, as they grant access to your GCP resources. Avoid hardcoding them or exposing them in logs or error messages.