Use Cloud Storage for Bulk Operations

You can either authenticate to Amazon S3 with specified or implicit authentication. To have users assume IAM roles to complete bulk operations on an S3 bucket, you can also configure assume role authentication in addition to specified or implicit.

Specified authentication

If the AUTH parameter is not provided, AWS connections default to specified and the access keys must be provided in the URI parameters.

As an example:

BACKUP DATABASE <database> INTO 's3://{bucket name}/{path in bucket}/?AWS_ACCESS_KEY_ID={access key ID}&AWS_SECRET_ACCESS_KEY={secret access key}';

Implicit authentication

If the AUTH parameter is implicit, the access keys can be omitted and the credentials will be loaded from the environment (i.e., the machines running the backup).

BACKUP DATABASE <database> INTO 's3://{bucket name}/{path}?AUTH=implicit';

You can associate an EC2 instance with an IAM role to provide implicit access to S3 storage within the IAM role's policy. In the following command, the instance example EC2 instance is associated with the example profile instance profile, giving the EC2 instance implicit access to any example profile S3 buckets.

aws ec2 associate-iam-instance-profile --iam-instance-profile Name={example profile} --region={us-east-2} --instance-id {instance example}

Assume role authentication

New in v22.2: To limit the control access to your Amazon S3 buckets, you can create IAM roles for users to assume. IAM roles do not have an association to a particular user. The role contains permissions that define the operations a user (or Principal) can complete. An IAM user can then assume a role to undertake a CockroachDB backup, restore, import, etc. As a result, the IAM user only has access to the assigned role, rather than having unlimited access to an S3 bucket.

For example, to configure a user to assume an IAM role that allows a bulk operation to an Amazon S3 bucket, take the following steps:

1. Create a role that contains a policy to interact with the S3 buckets depending on the operation your user needs to complete. See the Storage permissions section for details on the minimum permissions each CockroachDB bulk operation requires. You can create an IAM role in Amazon's Management console, under the IAM and then Policies menu. Alternately, you can use the AWS CLI.

2. If you do not already have the user that needs to assume the role, create the user. Under IAM in the Amazon console, navigate to Users and Add users. You can then add the necessary permissions by clicking on the Permissions tab. Ensure that the IAM user has sts:AssumeRole permissions attached. The following policy will give the user assume role permissions:

   {
   "Version": "2012-10-17",
   "Statement": {
       "Effect": "Allow",
       "Action": "sts:AssumeRole",
       "Resource": "arn:aws:iam::{account ID}:role/{role name}"
       }
   }
   

   The Resource here is the Amazon Resource Name (ARN) of the role you created in step 1. You can copy this from the role's Summary page.

   The sts:AssumeRole permission allows the user to obtain a temporary set of security credentials that gives them access to an S3 bucket to which they would not have access with their user-based permissions.

3. Return to your IAM role's Summary page, and click on the Trust Relationships tab. Add a trust policy into the role, which will define the users that can assume the role.

   The following trust policy provides the user the privilege to assume the role:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Principal": {
                   "AWS": "arn:aws:iam::123456789123:user/{user}"
               },
               "Action": "sts:AssumeRole"
           }
       ]
   }
   

   When creating a trust policy consider the following:

   • In the trust policy you need to include the ARN of the user that you want to assume the role under Principal. You can also include the Condition attribute to further control access to the Amazon S3 bucket. For example, this could limit the operation to a specified date range, to users with multi-factor authentication enabled, or to specific IP addresses.
     
   • If you set the Principal ARN to root, this will allow any IAM user in the account with the AssumeRole permission to access the Amazon S3 bucket as per the defined IAM role permissions.
   • When the IAM user takes on the role to perform a bulk operation, they are temporarily granted the permissions contained in the role. That is, not the permissions specified in their user profile.

4. Run the bulk operation. If using specified authentication, pass in the S3 bucket's URL with the IAM user's AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY. If using implicit authentication, specify AUTH=IMPLICIT instead. For assuming the role, pass the assumed role's ARN, which you can copy from the IAM role's summary page:

   BACKUP DATABASE movr INTO 's3://{bucket name}?AWS_ACCESS_KEY_ID={user key}&AWS_SECRET_ACCESS_KEY={user secret key}&ASSUME_ROLE=arn:aws:iam::{account ID}:role/{role name}' AS OF SYSTEM TIME '-10s';
   

   CockroachDB also supports authentication for assuming roles when taking encrypted backups. To use with an encrypted backup, pass the ASSUME_ROLE parameter to the KMS URI as well as the bucket's:

   BACKUP INTO 's3://{bucket name}?AWS_ACCESS_KEY_ID={user key}&AWS_SECRET_ACCESS_KEY={user secret key}&ASSUME_ROLE={ARN}'
   WITH kms = 'aws:///{key}?AWS_ACCESS_KEY_ID={user key}&AWS_SECRET_ACCESS_KEY={user secret key}&REGION={region}&ASSUME_ROLE={ARN}';
   

   For more information on AWS KMS URI formats, see Take and Restore Encrypted Backups.

Role chaining

Beyond a user assuming a role, it is also possible to "chain" roles to create a path for users to assume roles to particular operations. Role chaining allows a user to assume a role through an intermediate role(s) instead of the user directly assuming a role. In this way, the role chain passes the request for access to the final role in the chain. Role chaining could be useful when a third-party organization needs access to your Amazon S3 bucket to complete a bulk operation. Or, your organization could grant roles based on limited-privilege levels.

Assuming the role follows the same approach outlined in the previous section. The additional required step to chain roles is to ensure that the ARN of role A, which is assuming role B, is present in role B's trust policy with the sts:AssumeRole action.

The role B's trust policy must contain:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::{account-A-ID}:role/{role A name}"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

In a chain of three roles, role C's trust policy needs to include role B in the same way. For example, to chain three roles so that a user could assume role C, it is necessary to verify the following:

When passing a chained role into BACKUP, it will follow this pattern:

BACKUP DATABASE movr INTO "s3://{bucket name}?AWS_ACCESS_KEY_ID={user's key}&AWS_SECRET_ACCESS_KEY={user's secret key}&ASSUME_ROLE={role A ARN},{role B ARN},{role C ARN}" AS OF SYSTEM TIME '-10s';

Each chained role is listed separated by a ,. You can copy the ARN of the role from its summary page.

The AUTH parameter passed to the file URL must be set to either specified or implicit. The default behavior is specified in v21.2+. The following sections describe how to set up each authentication method.

Specified authentication

To access the storage bucket with specified credentials, it's necessary to create a service account and add the service account address to the permissions on the specific storage bucket.

The JSON credentials file for authentication can be downloaded from the Service Accounts page in the Google Cloud Console and then base64-encoded:

cat gcs_key.json | base64

Pass the encoded JSON object to the CREDENTIALS parameter:

BACKUP DATABASE <database> INTO 'gs://{bucket name}/{path}?AUTH=specified&CREDENTIALS={encoded key}';

Implicit authentication

For CockroachDB instances that are running within a Google Cloud Environment, environment data can be used from the service account to implicitly access resources within the storage bucket.

For CockroachDB clusters running in other environments, implicit authentication access can still be set up manually with the following steps:

1. Create a service account and add the service account address to the permissions on the specific storage bucket.

2. Download the JSON credentials file from the Service Accounts page in the Google Cloud Console to the machines that CockroachDB is running on. (Since this file will be passed as an environment variable, it does not need to be base64-encoded.) Ensure that the file is located in a path that CockroachDB can access.

3. Create an environment variable instructing CockroachDB where the credentials file is located. The environment variable must be exported on each CockroachDB node:

   export GOOGLE_APPLICATION_CREDENTIALS="/{cockroach}/gcs_key.json"
   

   Alternatively, to pass the credentials using systemd, use systemctl edit cockroach.service to add the environment variable Environment="GOOGLE_APPLICATION_CREDENTIALS=gcs-key.json" under [Service] in the cockroach.service unit file. Then, run systemctl daemon-reload to reload the systemd process. Restart the cockroach process on each of the cluster's nodes with systemctl restart cockroach, which will reload the configuration files.

   To pass the credentials using code, see Google's Authentication documentation.

4. Run a backup (or other bulk operation) to the storage bucket with the AUTH parameter set to implicit:

   BACKUP DATABASE <database> INTO 'gs://{bucket name}/{path}?AUTH=implicit';
   

To access Azure storage containers, it is sometimes necessary to url encode the account key since it is base64-encoded and may contain +, /, = characters. For example:

BACKUP DATABASE <database> INTO 'azure://{container name}/{path}?AZURE_ACCOUNT_NAME={account name}&AZURE_ACCOUNT_KEY={url-encoded key}';

If your environment requires an HTTP or HTTPS proxy server for outgoing connections, you can set the standard HTTP_PROXY and HTTPS_PROXY environment variables when starting CockroachDB. You can create your own HTTP server with NGINX. A custom root CA can be appended to the system's default CAs by setting the cloudstorage.http.custom_ca cluster setting, which will be used when verifying certificates from HTTPS URLs.

If you cannot run a full proxy, you can disable external HTTP(S) access (as well as custom HTTP(S) endpoints) when importing by using the --external-io-disable-http flag.

A custom root CA can be appended to the system's default CAs by setting the cloudstorage.http.custom_ca cluster setting, which will be used when verifying certificates from an S3-compatible service.

These actions are the minimum access permissions to be set in an Amazon S3 bucket policy:

See Policies and Permissions in Amazon S3 for detail on setting policies and permissions in Amazon S3.

An example S3 bucket policy for a backup:

{
    "Version": "2012-10-17",
    "Id": "Example_Policy",
    "Statement": [
        {
            "Sid": "ExampleStatement01",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::{ACCOUNT_ID}:user/{USER}"
            },
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::{BUCKET_NAME}",
                "arn:aws:s3:::{BUCKET_NAME}/*"
            ]
        }
    ]
}

In Google Cloud Storage, you can grant users roles that define their access level to the storage bucket. For the purposes of running CockroachDB operations to your bucket, the following table lists the permissions that represent the minimum level required for each operation. GCS provides different levels of granularity for defining the roles in which these permissions reside. You can assign roles that already have these permissions configured, or make your own custom roles that include these permissions.

For more detail about Predefined, Basic, and Custom roles, see IAM roles for Cloud Storage.

For guidance on adding a user to a bucket's policy, see Add a principal to a bucket-level policy.