In GCP CloudSQL how do I reset credentials for GKE after deleting secrets

Question

While bringing up a new cluster I accidentally deleted the secrets for cloudsql-oauth-credentials in a staging cluster/project. Is there a way to re-obtain and install these from "gcloud" or the console for cloudSQL? I may have a copy of the original that looks like this (private stuff removed):

{                                                                                                                                                                                                                                                                                                                                                                                          
  "type": "service_account",
  "project_id": "able-XXXXX-XXXXX",
  "private_key_id": "8adcffXXXX",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvwIXXXXXXXXXX==\n-----END PRIVATE KEY-----\n",
  "client_email": "xxxx-service-account-sql-cli@able-xxxx.iam.gserviceaccount.com",
  "client_id": "10905637232xxxxx",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/notify-service-account-sql-cli%40ablexxxxx.iam.gserviceaccount.com"
}

I'm hoping I can use that with:

kubectl create  secret generic cloudsql-oauth-credentials --from-literal="credentials.json=`cat build/cloudsql-oauth-credentials.json`"

Note: this is using the standard sidecar proxy config on GCP for GKE deployments.

Reinstalling the secret appears to be working for the original cluster. For the new cluster however, I see "googleapi: Error 403: The client is not authorized to make this request., notAuthorized" (fwiw) — Charles Thayer, Dec 11 '18 at 00:14
Your use case would need further troubleshooting and investigation. I would suggest that you open a new Issue in the [Public Issue Tracker](https://cloud.google.com/support/docs/issue-trackers), and a GCP specialist will investigate it more deeply, as they need to take a look at the new cluster and verify its settings. — Mahmoud Sharif, Dec 14 '18 at 14:18

Charles Thayer · Answer 1 · 2019-05-14T06:15:01.960

Follow-up, After much confusion I found that I was connected to the wrong container within my pod, which is why I couldn't find the secrets for the cloudsql credentials. I was able to find the credentials in my pod via a volume mount like this:

kubectl exec engine-cron-prod-deployment-788ddb4b8-bxmz9 -c postgres-proxy -it -- /bin/sh
/ # ls /secrets/cloudsql/                                                                                                                                                                                                                                                                  
credentials.json                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
/ # cat /secrets/cloudsql/credentials.json                                                                                                                                                                                                                                                 
{                                                                                                                                                                                                                                                                                          
  "type": "service_account",
  [..stuff deleted..]

This result matched the file I had saved so my keyfile.json (aka cloudsql-oauth-credentials.json) was the right one.

Just to be clear, the sidecar pattern in my deployment yaml looks something like this:

spec:
  volumes:
  - name: ssl-certs
    hostPath:
      path: /etc/ssl/certs
  - name: cloudsql-oauth-credentials
    secret:
      secretName: cloudsql-oauth-credentials
  - name: cloudsql
    emptyDir:
  containers:
  - name: postgres-proxy
    image: gcr.io/cloudsql-docker/gce-proxy:1.09
    imagePullPolicy: Always
    command: ["/cloud_sql_proxy",
              "--dir=/cloudsql",
              "-instances=@@PROJECT@@:us-central1:@@DBINST@@=tcp:5432",
              "-credential_file=/secrets/cloudsql/credentials.json"]
    volumeMounts:
      - name: cloudsql-oauth-credentials
        mountPath: /secrets/cloudsql
        readOnly: true
      - name: ssl-certs
        mountPath: /etc/ssl/certs
      - name: cloudsql
        mountPath: /cloudsql

Conclusions:

Regardless, one could always delete the service account and create a new one to get the credentials, then add that account to the right roles (for cloudsql) and start again, though that would somewhat painful and time consuming.
One can re-use these credentials with other GKE clusters to connect to the same cloudsql DB, or one can create new service accounts with the same roles but a separate set of credentials.

Edit: For completeness, one can also retrieve and store their secret for safe keeping as a backup. By using get -o json you'll recover the credentials.json as base64 encoded text.

$kubectl get -o json secret cloudsql-oauth-credentials                                                                                                                                                                                                                                                                                               
{                                                                                                                                                                                                                                                                                                                                                                                       
    "apiVersion": "v1",
    "data": {
        "credentials.json": "ewogICJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsCiAgInByb2plY3RfaWQiOiAiYW...."
    },
    "kind": "Secret",
    "metadata": {
        "creationTimestamp": "2019-01-03T01:32:49Z",
        "name": "cloudsql-oauth-credentials",
        "namespace": "default",
        "resourceVersion": "12078",
        "selfLink": "/api/v1/namespaces/default/secrets/cloudsql-oauth-credentials",
        "uid": "7af2bdde-0ef7-11e9-92bd-123123123123"
    },
    "type": "Opaque"
}

That base64 text can easily be decoded and saved:

$ base64 -d < credentials.json.b64 | tee credentials.json
{                                                                                                                                                                                                                                                                                                                                                                                       
  "type": "service_account",
  "project_id": "xxx-xxx-xxx",
  "private_key_id": "abc123abc123abc123abc123abc123abc123",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvwIBADANBgkqhkiG9...==\n-----END PRIVATE KEY-----\n",
  "client_email": "xxx-service-account-sql-cli@xxx-xxx-xxx.iam.gserviceaccount.com",
  "client_id": "321321321321321321",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/xxx-xxx-account-sql-cli%40xxx-xxx-xxx.iam.gserviceaccount.com"
}

In GCP CloudSQL how do I reset credentials for GKE after deleting secrets

1 Answers1