CockroachDB & GKE: AUTH=implicit Backups
Published: 2022-01-20 03:34:59

Let’s look at taking backups on a CockroachDB cluster running on Google Kubernetes Engine.  For this discussion we are assuming that you have a functioning GKE environment and have installed CockroachDB using a statefulset template similar to the one found [here](https://github.com/cockroachdb/cockroach/blob/master/cloud/kubernetes/cockroachdb-statefulset.yaml).  

Since we are running on GKE we want to backup our CockroachDB data to a Google Cloud Storage bucket.  The following example backup command will backup the _example_db_ to the _backup_ directory in the _test_crl_ Google bucket. 

```
root@cockroachdb-public:26257/defaultdb> BACKUP DATABASE example_db TO 'gs://test_crl/backup?AUTH=xxxx' AS OF SYSTEM TIME '-10s';
```

The unanswered question with this command is how do we authorize the CockroachDB backup command to use the Google Cloud Storage bucket.  We need to pass the Google Cloud service account credentials associated with the GCP instances running the Cockroach nodes to the backup command.  We have two options to do this.

The first option is to use **AUTH=specified** in the backup command.  This is accomplished by base64 encoding the Google service account JSON key file and including that encoded value in the backup command.  Using this approach the backup command would look like

```
BACKUP DATABASE example_db TO 'gs://test_crl/backup?AUTH=specified&CREDENTIALS=really_long_base64_encoded_string' AS OF SYSTEM TIME '-10s';
```

This works fine, but is not very easy to maintain. Also, for those organizations with stringent security requirements this solution will likely run afoul of an InfoSec review.  

The second and recommended option is to use **AUTH=implicit** in the backup command.  This approach allows all the individual Kubernetes pods running CockroachDB to access the service account credentials as an environment variable as outlined in the GCP [documentation](https://cloud.google.com/docs/authentication/production#providing_credentials_to_your_application).

To implement AUTH=implicit we will create a Kubernetes secret on GKE and then modify the Kubernetes CockroachDB statefulset to populate the environment variable and mount the secret to all the CockroachDB pods.  

Begin by creating a Kubernetes secret manifest file named `secret_auth_implicit.yaml` 

```
apiVersion: v1
kind: Secret
metadata:
  name: googlesecret
type: Opaque
stringData:
  googlebucket.json: |
    {
    "type": "service_account",
    "project_id": "cockroach-****",
    "private_key_id": "****",
    "private_key": "-----BEGIN PRIVATE KEY-----\n*****\n-----END PRIVATE KEY-----\n",
    "client_email": "626923887835-compute@developer.gserviceaccount.com",
    "client_id": "*****",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/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/626923887835-compute%40developer.gserviceaccount.com"
    }
```

Basically everything between the curly braces is the contents of the service account JSON key file.  Details have been redacted in the above example.  Now, apply this yaml file to create the Kubernetes secret 

`kubectl apply -f secret_auth_implicit.yaml`

Next add the following directives to the `env:`, `volumeMounts:` and `volumes:` sections of the CockroachDB statefulset configuration
        
```
        env:
        - name: GOOGLE_APPLICATION_CREDENTIALS
          value: /cockroach/google/googlebucket.json

        volumeMounts:
        - mountPath: /cockroach/google
          name: secretconfig

      volumes:
      - name: secretconfig
        secret:
          defaultMode: 420
          secretName: googlesecret
```

The environment variable specified in the `env:`  section _must_ be named `GOOGLE_APPLICATION_CREDENTIALS` and the value `googlebucket.json` must match up to what is defined in the secret as the name of the _stringData_.

Apply the statefulset change and connect to one of the CockroachDB nodes and verify the configuration.  First display the GOOGLE_APPLICATION_CREDENTIALS environment variable

```
[root@cockroachdb-0 cockroach]# echo $GOOGLE_APPLICATION_CREDENTIALS
/cockroach/google/googlebucket.json
```

Next cat the googlebucket.json file in the /cockroach/google/ directory and verify its contents

```
[root@cockroachdb-0 google]# pwd
/cockroach/google
[root@cockroachdb-0 google]# cat googlebucket.json
{
"type": "service_account",
"project_id": "cockroach-xxxx",
.
.
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/626923887835-compute%40developer.gserviceaccount.com"
}
```

Once the statefulset configuration has been verified, run the backup using AUTH=implicit 
 

```
root@cockroachdb-public:26257/defaultdb> BACKUP DATABASE example_db INTO 'gs://test_crl/backup?AUTH=implicit';
        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
  719317784274370562 | succeeded |                  1 |    5 |             0 |    95
(1 row)
```

Success!  

For more information on CockroachDB backups see the online documentation [here](https://www.cockroachlabs.com/docs/stable/backup.html?filters=gcs).