Take and Restore Encrypted Backups

On this page

This doc provides information about how to take and restore encrypted backups in the following ways:

• Using AWS Key Management Service (KMS)
• Using Google Cloud Key Management Service (KMS)
• Using a passphrase

Supported products

The feature described on this page is available in CockroachDB Dedicated, CockroachDB Serverless, and CockroachDB Self-Hosted clusters when you are running self-managed backups. For a full list of features, see Backup and restore product support.

Use Key Management Service

You can encrypt full or incremental backups with AWS or Google Cloud Key Management Service (KMS) by using the kms option. Files written by the backup (BACKUP manifest and data files) are encrypted using a 256-bit crypto-random generated data key. This data key is encrypted with the provided KMS URI(s) and stored alongside the BACKUP data in an ENCRYPTION_INFO file, which is used when restoring the backed-up data.

On RESTORE, CockroachDB reads the ENCRYPTION_INFO file and attempts to decrypt the encrypted data key using the KMS URI provided in the RESTORE statement. Once CockroachDB successfully obtains the unencrypted data key, the BACKUP manifest and data files will be decrypted and the restoration will proceed. Similarly, the same KMS URI is needed to decrypt the file to list the contents of the backup when using SHOW BACKUP.

When used with incremental backups, the kms option is applied to all the backup file URLs, which means each incremental must include at least one of the KMS URIs used to take the full backup. It can be any subset of the original URIs, but you cannot include any new KMS URIs. Similarly, when used with locality-aware backups, the KMS URI provided is applied to files in all localities.

For more information about AWS KMS, see the documentation. For more information about Google Cloud KMS, see the documentation.

Generate a KMS key

Before you can use a KMS to encrypt a CockroachDB backup, you must first generate a KMS key. This is the key generated by the cloud provider and it never leaves the KMS. It contains key-related metadata and key material to encrypt/decrypt other data. The key material can never be exported, deleted, or extracted. CockroachDB expects the key to be symmetric (256 bit).

CockroachDB also supports multi-region encryption for your backup. At the time of BACKUP, you can provide multiple KMS URIs, each referencing a KMS key in a different region. This allows CockroachDB to save multiple versions of the encrypted data key used to encrypt the backup data, one per KMS URI. With these encrypted versions of the data key stored alongside the encrypted backup data, a user can RESTORE the encrypted data using any one of the KMS URIs that were supplied during backup. In the case of a single KMS region outage, the data can be decrypted with any of the KMS keys from the other regions.

Add a new KMS key to an existing backup

To add a new KMS key to an existing backup, use the ALTER BACKUP statement. ALTER BACKUP allows for new KMS encryption keys to be applied to an existing chain of encrypted backups (full and incremental). Once completed, subsequent BACKUP, RESTORE, and SHOW BACKUP statements can use any of the existing or new KMS URIs to decrypt the backup.

For examples on adding a new KMS key to an existing backup, see the ALTER BACKUP examples.

URI formats

AWS KMS URI format

The AWS KMS URI must use the following format:

aws:///{key}?AUTH={auth_type>}&REGION={region}

The AWS URI requires the following:

See AWS's KMS keys documentation for guidance on creating an AWS KMS key.

Google Cloud KMS URI format

The Google Cloud KMS URI must use the following format:

gs:///projects/{project name}/locations/{location}/keyRings/{key ring name}/cryptoKeys/{key name}?AUTH={auth_type}

The Google Cloud URI requires the following:

See Google Cloud's customer-managed encryption key documentation for guidance on creating a KMS key.

Examples

• Take an encrypted backup
• Take a backup with multi-region encryption
• Restore from an encrypted backup

The following examples provide connection strings to Amazon S3 and Google Cloud Storage. For guidance using other authentication parameters, read Use Cloud Storage for Bulk Operations.

> BACKUP INTO 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    WITH kms = 'aws:///{key}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}&REGION=us-east-1';

        job_id       |  status   | fraction_completed | rows | index_entries |  bytes
---------------------+-----------+--------------------+------+---------------+----------
  594193600274956289 | succeeded |                  1 | 2689 |          1217 | 1420108
(1 row)

> BACKUP INTO 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    WITH KMS=(
      'aws:///{key}?AUTH=implicit&REGION=us-east-1',
      'aws:///{key}?AUTH=implict&REGION=us-west-1'
    );

        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
  594471427115220993 | succeeded |                  1 |   20 |             2 |  1026
(1 row)

> RESTORE FROM LATEST IN 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    WITH kms = 'aws:///{key}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}&REGION=us-east-1';

        job_id       |  status   | fraction_completed | rows | index_entries |  bytes
---------------------+-----------+--------------------+------+---------------+----------
  594193600274956291 | succeeded |                  1 | 2689 |          1217 | 1420108
(1 row)

> BACKUP INTO 'gs://{BUCKET NAME}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
    WITH kms = 'gs:///projects/{project name}/locations/us-east1/keyRings/{key ring name}/cryptoKeys/{key name}?AUTH=specified&CREDENTIALS={encoded key}';

        job_id       |  status   | fraction_completed | rows | index_entries |  bytes
---------------------+-----------+--------------------+------+---------------+----------
  594193600274956289 | succeeded |                  1 | 2689 |          1217 | 1420108
(1 row)

> BACKUP INTO 'gs://{BUCKET NAME}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
    WITH KMS=(
      'gs:///projects/{project name}/locations/us-east1/keyRings/{key ring name}/cryptoKeys/{key name}?AUTH=specified&CREDENTIALS={encoded key}',
      'gs:///projects/{project name}/locations/us-west1/keyRings/{key ring name}/cryptoKeys/{key name}?AUTH=specified&CREDENTIALS={encoded key}'
    );

        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
  594471427115220993 | succeeded |                  1 |   20 |             2 |  1026
(1 row)

> RESTORE FROM LATEST IN 'gs://{BUCKET NAME}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
    WITH kms = 'gs:///projects/{project name}/locations/us-east1/keyRings/{key ring name}/cryptoKeys/{key name}?AUTH=specified&CREDENTIALS={encoded key}';

        job_id       |  status   | fraction_completed | rows | index_entries |  bytes
---------------------+-----------+--------------------+------+---------------+----------
  594193600274956291 | succeeded |                  1 | 2689 |          1217 | 1420108
(1 row)

Use a passphrase

You can encrypt full or incremental backups with a passphrase by using the encryption_passphrase option. Files written by the backup (including BACKUP manifests and data files) are encrypted using the specified passphrase to derive a key. To restore the encrypted backup, the same encryption_passphrase option (with the same passphrase) must be included in the RESTORE statement.

When used with incremental backups, the encryption_passphrase option is applied to all the backup file URLs, which means the same passphrase must be used when appending another incremental backup to an existing backup. Similarly, when used with locality-aware backups, the passphrase provided is applied to files in all localities.

Encryption is done using AES-256-GCM, and GCM is used to both encrypt and authenticate the files. A random salt is used to derive a once-per-backup AES key from the specified passphrase, and then a random initialization vector is used per-file. CockroachDB uses PBKDF2 with 64,000 iterations for the key derivation.

For an example of an encrypted backup, see Create an encrypted backup.

The following examples make use of:

• Amazon S3 connection strings. For guidance on connecting to other storage options or using other authentication parameters instead, read Use Cloud Storage.
• The default AUTH=specified parameter. For guidance on using AUTH=implicit authentication with Amazon S3 buckets instead, read Use Cloud Storage for Bulk Operations — Authentication.

Also, note the following features for connecting and authenticating to cloud storage:

• New in v22.2: External connections, which allow you to represent an external storage or sink URI. You can then specify the external connection's name in statements rather than the provider-specific URI. For detail on using external connections, see the CREATE EXTERNAL CONNECTION page.
• New in v22.2: Assume role authentication, which allows you to limit the control specific users have over your storage buckets. See Assume role authentication for more information.

Take an encrypted backup using a passphrase

To take an encrypted backup, use the encryption_passphrase option:

> BACKUP INTO 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' WITH encryption_passphrase = 'password123';

        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+---------
  543214409874014209 | succeeded |                  1 | 2597 |          1028 | 467701
(1 row)

To restore, use the same encryption_passphrase. See the example below for more details.

Restore from an encrypted backup using a passphrase

To decrypt an encrypted backup, use the encryption_passphrase option option and the same passphrase that was used to create the backup.

For example, the encrypted backup created in the previous example can be restored with:

> RESTORE FROM LATEST IN 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' WITH encryption_passphrase = 'password123';

        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+---------
  543217488273801217 | succeeded |                  1 | 2597 |          1028 | 467701
(1 row)

To restore from a specific backup, use RESTORE FROM {subdirectory} IN ....

See also

• BACKUP
• RESTORE
• ALTER BACKUP
• Take Full and Incremental Backups
• Take and Restore Locality-aware Backups
• Take Backups with Revision History and Restore from a Point-in-time
• IMPORT
• Use the Built-in SQL Client
• cockroach Commands Overview

Was this helpful?