GKE workload identity with spring boot

Question

I am trying to connect my spring boot application running in GKE to a cloudSQL database in GC. The suggested approach by google is via workload identity.

I have followed these instructions in addition to adding the cloud admin role to the service account and I have even run the test pod to make sure that my pod runs with the correct service account. I then run my deployment like this.

apiVersion: "apps/v1"
kind: "Deployment"
metadata:
  name: "hello-world"
  namespace: my-dev
  labels:
    app: "hello-world"
spec:
  replicas: 1
  selector:
    matchLabels:
      app: "hello-world"
  template:
    metadata:
      labels:
        app: "hello-world"
    spec:
      containers:
        - name: "hello-container"
          image: "my-region-docker.pkg.dev/my-project/my-repo/my-tag"
          ports:
            - containerPort: 8080
      serviceAccountName: new-service-account
      nodeSelector:
        iam.gke.io/gke-metadata-server-enabled: "true"

My understanding is that once we do that we do not need username/password to connect to the database since the service account is authorized for the connection.

However, I cannot make that work in the spring boot side. I have tried multiple combinations of from this page

spring.datasource.url=jdbc:postgresql:///DATABASE_NAME?cloudSqlInstance=INSTANCE_CONNECTION_NAME&socketFactory=com.google.cloud.sql.postgres.SocketFactory

and

spring.cloud.gcp.sql.*

properties but I have not managed to make it connect.

Is my assumption of not needing username/password wrong or is my configuration wrong?

Properties used

[email protected]
spring.datasource.password=mockpassword # this is the actual value that I placed since you told me not to leave empty
spring.datasource.url=jdbc:postgresql:///springdb?cloudSqlInstance=project-id:region:mydb&socketFactory=com.google.cloud.sql.postgres.SocketFactory&enableIamAuth=true

Answer 1

How I made it work.

First I followed the steps for workload identity

You must add the service account from the workload identity to the database, that can be done both via gcloud or the console.

Instructions how to configure database to a accept IAM authentication.

Also, the database needs to have the appropriate flag, if it does not in the user section there will be an exclamation mark, if you click it you can add it.

Instructions how to add service account to database

So far so good, however the user does not have permissions to the database and my database is private so I cannot connect to it from outside so we have to connect via a VM in the same network to do the GRANT.

Instructions how to execute the GRANT for a user

I followed these instructions with a small modification at the end instead of

psql "host=127.0.0.1 port=5432 sslmode=disable dbname=DB_NAME user=postgres"

you can connect without specifying a database

psql "host=127.0.0.1 port=5432 sslmode=disable user=postgres"

now I just run

grant all privileges on database "MY_DB" to "[email protected]";

Beware this worked with Postgres 12, but it failed with 15 you can check this post for 15

Finally configure the spring boot application, according to this guide that @Jonathan provided. I tried many dependencies, but at the end I just needed this one.

<dependency>
    <groupId>com.google.cloud.sql</groupId>
    <artifactId>postgres-socket-factory</artifactId>
    <version>1.14.1</version>
</dependency>

And then again as @Jonathan said the properties need to be set as following. the password is not used, but it must not be empty!

spring.datasource.username=<SHORTENED_SERVICE_ACCOUNT_NAME>
spring.datasource.password=ANYTHING_BUT_EMPTY
spring.datasource.url=jdbc:postgresql:///DATABASE_NAME?cloudSqlInstance=INSTANCE_CONNECTION_NAME&socketFactory=com.google.cloud.sql.postgres.SocketFactory&enableIamAuth=true

Answer 2

It looks like your database configuration needs Cloud SQL JDBC Connector to use IAM Authentication and workload identity.

First, add &enableIamAuth=true to your JDBC URL. It should look like this:

spring.datasource.url=jdbc:postgresql:///DATABASE_NAME?cloudSqlInstance=INSTANCE_CONNECTION_NAME&socketFactory=com.google.cloud.sql.postgres.SocketFactory&enableIamAuth=true

Also, You will also need to set the database properties for user and password. username needs to be the shortened version of the Google Service Account email. password must not be blank or the Postgres JDBC driver will throw an exception, but because you set enableIamAuth=true, the password value will not be used.

spring.datasource.username=<SHORTENED_SERVICE_ACCOUNT_NAME>
spring.datasource.password=password

Replace <SHORTENED_SERVICE_ACCOUNT_NAME> with the shortened service account name used by your Postgres database.

You must shorten the full IAM user email into a database username. Due to different constraints on allowed characters in the database username, Mysql and Postgres differ in how they shorten an IAM email address into a database username.

• Mysql: Truncate the IAM email removing the @ and everything that follows.
• Postgres: If the IAM email ends with .gserviceaccount.com, remove the .gserviceaccount.com suffix from the email.

For example, if the full IAM user account is [email protected], then the shortened database username would be my-sa for Mysql, and [email protected] for Postgres.