0

This is driving me crazy, been trying to get this to work for 3 days now: I'm trying to connect a kubernetes deployment to my Cloud SQL database in GCP.

Here's what I've done so far:

  1. Set up the cloud SQL proxy to work as a sidecar in my deployment
  2. Created a GKE service account and attached it to my deployment
  3. Bound the GKE service account to my GCP service account
  4. Edited to the service account (to what I can tell) is owner permission

Yet what I run the deployment in GKE I still get:

the default Compute Engine service account is not configured with sufficient permissions to access the Cloud SQL API from this VM. Please create a new VM with Cloud SQL access (scope) enabled under "Identity and API access". Alternatively, create a new "service account key" and specify it using the -credential_file parameter

How can I fix this? I can't find any documentation on how to set up the service account to have the correct permissions with Cloud SQL or how to debug this issue. Every single tutorial I can find ends with "bind your service account" and then stops. Nothing that describes what permissions are needed, and nothing about how to actually connect to the DB from my code (how would my code talk to the proxy?).

Please help

Rob
  • 7,028
  • 15
  • 63
  • 95
  • The fact that the error tells you that you are using the default (GCP) SA shows that you are not authenticating with your created (GCP) SA. Keep in mind this SA should be used by the proxy, not your application. Tbh all of your questions are handled in the official documentation https://cloud.google.com/sql/docs/mysql/connect-kubernetes-engine which doesn't just say 'bind your service account', it also explains 3 different ways to authenticate, you seem to have only tried identity federation (which is the recommended way). Maybe specifically ask what step you have issues with in that doc. – somethingsomething Aug 26 '22 at 19:12
  • Thank you, your comments on the error are very helpful. I've gone through that article several times already but I'm clearly missing something. I've added the service account to my deployment spec like this, "serviceAccountName: lumivest-node-service-account" so I'm not sure why it's saying that it's using the default service account still. Do you know if the fact that I'm using namespaces might be causing the issue? – Rob Aug 26 '22 at 19:45
  • @somethingsomething do you know where I can find docs on how to properly set up the GSA to connect to Cloud SQL? The document completely glosses over that part (what roles does it need, etc) – Rob Aug 26 '22 at 20:28

2 Answers2

3

FINALLY got it to work!

Two major pieces that the main article on this (cloud.google.com/sql/docs/mysql/connect-kubernetes-engine) glosses over:

  1. Properly setting up workload identity, for which I found these links to be very helpful: a) https://cloud.google.com/kubernetes-engine/docs/how-to/workload-identity b) https://www.youtube.com/watch?v=l-nws1e4B8M

  2. To connect to the DB you have to have your code use the DB host 127.0.0.1

Rob
  • 7,028
  • 15
  • 63
  • 95
0

For anybody who is looking for a summarization:

You could find a quickstart here: https://cloud.google.com/sql/docs/postgres/connect-instance-kubernetes

And detailed documentation here: https://cloud.google.com/sql/docs/postgres/connect-kubernetes-engine

Basically, if you choose Cloud SQL Auth proxy + Workload Identity, there are some things you need to check when it went wrong:

On GKE side: code example

  1. Make sure Workload Identity is enabled (both the cluster and the node pool)

  2. Check if Kubernetes Service Account is created for your application

  3. Create your own IAM Service Account that is on behalf of your application.

gcloud iam service-accounts create GSA_NAME --project=GSA_PROJECT

  1. Ensure that your IAM service account has the roles you need, here is Cloud SQL Client IAM role

gcloud projects add-iam-policy-binding PROJECT_ID
--member "serviceAccount:GSA_NAME@GSA_PROJECT.iam.gserviceaccount.com"
--role "roles/cloudsql.client"

  1. Bound the Kubernetes Service Account to the IAM service account (in other words: allow the Kubernetes Service Account to impersonate the IAM Service Account) by:
  • Enabling the IAM binding between your Kubernetes Service Account and IAM service account
  • Adding an annotation to your Kubernetes Service Account so Workload Identity knows which IAM Service Account to use

  1. Make sure to specify the Kubernetes Service Account for the application in deployment.yaml. Under spec.template.spec.serviceAccountName here. By setting this, your deployment is on behalf of Kubernetes Service Account that impersonate the IAM Service Account, so the sidecar Cloud SQL Auth proxy in this deployment has the Cloud SQL Client IAM role (from IAM Service Account) to establish connections to Cloud SQL

  2. Check Cloud SQL Auth proxy is also in your application deployment.yaml. --name: cloud-sql-proxy here. Replace <INSTANCE_CONNECTION_NAME> by your Cloud SQL instance connection name and <DB_PORT> to a number like: 5432. This one will run a sidecar in your pod along with your application, and this sidecar will open a Cloud SQL Auth Proxy at localhost:5432 (5432 is DB_PORT you specified above, and Cloud SQL Auth Proxy authenticate itself to Cloud SQL by Cloud SQL Client IAM role above). So in the application, your configuration property for the database URL should be: jdbc:postgresql://localhost:5432.

On Application side:

  • Make sure your data properties are configured right.

url: jdbc:postgresql://localhost:5432

username: your_username

password: your_password

database: your_database

Linh Vu
  • 736
  • 3
  • 7