CockroachDB & GKE: AUTH=implicit Backups

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.

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.

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": "[e-mail]",
    "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.