Sign in Subscribe

Managing CockroachDB as Code with Terraform

Mike Bookham

• 8 min read
Managing CockroachDB as Code with Terraform

Managing everything as code ensures no configuration drift ensuring predictable results. This is critical when managing applications at scale. Cockroach Dedicated is a Database as a Service (DBaaS) offering that allows companies the ability to outsource the day to day delivery and operations of a relational database that is reliable and able to scale to meet the needs of the cloud era. For CockroachDB to be regarded as a managed service ready for integration with other modern tools in the DevOps Engineers toolbox it needs to meet certain requirements. One of those requirements is to be able to integrate with provisioning tools used to deploy other components in the software stack. One such tool is Terraform, this is a tool that is able to use API’s of many infrastructure providers to deploy infrastructure. Our previous blog back in October 2022 covered the release of the CockroachDB Terraform provider. Since then a number of new feature have been added, these include the ability to retrieve the database connection string and certificate, the ability encrypt your data with your own encryption keys (CMEK). Along with some more operational focused features like the ability to configure maintenance windows. This defines when Cockroach Labs can perform maintenance tasks like upgrades. Also an integration with Datadog of visualising your metrics

To enable you to follow along we will provide all the steps required to deploy a cluster into Cockroach Cloud with the new features enabled.

Deploy CockroachDB Dedicated with Terraform

Similar to the previous blog there is a requirement for software to be installed. First as the code will be held in a GitHub repository the Git CLI is needed. Once the code has been retrieved the Terraform binary will be required to execute the code. The final element is the API key for Cockroach Cloud that will allow Terraform to provision the required features.

List of required prerequisites:

Step 1: Clone the git repository

git clone https://github.com/cockroachlabs-field/cockroachdb-terraform-dedicated-july2023-example.git

Step 2: Grab your API key

Before we get into the Terraform code and the resources it creates we need to add the Cockroach API key as a local environment variable. You can create one of these in the Cockroach Cloud UI by firstly adding a service account, which then allows you to generate a key associated with it.


Once a service account is created and an API key generated we export that into our environment for Terraform to use.

export COCKROACH_API_KEY=<YOUR_API_KEY>


Step 3: Prepare your variables

The new CockroachDB Cloud Terraform provider requires the presence of the environment variable to be able to create the required resources within the cloud platform. Now let’s take a closer look at the Terraform code itself.

Within the repository there is a file called terraform.tfvars.example . This file is an example of the variables that you can pass to the Terraform run to define the configuration. Make a copy of this file and call it terraform.tfvars then update the values as required for your own deployment. As this is just an example, we’re just going to create this file locally and store the variables in plain text. For production environments these values should not be checked into the code repository and if using CI/CD should be passed in using pipeline secrets or stored in a third party tool such as Vault.

cluster_name = "dsmbcrdbtftexample2"

sql_user_name = "mbds"

sql_user_password ="ThisIsASuperSafePassword"

cloud_provider = "AWS"

cloud_provider_region = ["eu-west-2"]

cluster_nodes = "3"

storage_gib = "150"

machine_type = "m6i.xlarge"

region = "eu-west-2"

datadog_site = "US1"

datadog_api_key = "redacted"

Step 4: Connection String and Certificate

To connect your applications to your CockroachDB Dedicated cluster you will need a connection string and certificate. With these you will be able to connect securely to your cluster. The connection string is made up of a number of components. The user you are connecting with, the cluster name and short identifier. The region and the hostname make up the final parts of the cluster address. This is followed by the location of the certificate required to communicate with the cluster. There is an example below.

'postgresql://<user>@<cluster-name>-<short-id>.<region>.<host>:26257/<database>?sslmode=verify-full&sslrootcert='$HOME'/Library/CockroachCloud/certs/<cluster-name>-ca.crt'


To retrieve this information with Terraform you need to create a data resource in your terraform code to retrieve it. Below is an example on how to do this.

data "cockroach_connection_string" "cockroach" {

  id       = cockroach_cluster.cockroach.id

  sql_user = cockroach_sql_user.cockroach.name

  database = cockroach_cluster.cockroach.id

}


With this, you can then create an output in your output.tf file to display the connection string when the terraform runs, or output to a variable to use elsewhere. Below is an example of outputting the connection string

output "connection_string" {

    value = module.cockroach-dedicated.connection_string

}


Alongside the connection string, in order to connect to your cluster, you’ll also need to output the cluster certificate, you can output this as a file within the terraform with the code snippet below.

resource "local_file" "cluster_cert" {

  filename = "${path.root}/cert.pem"

  content = data.cockroach_cluster_cert.cockroach.cert

}


If required you can also output this as a variable or text output using the output snippet below

output "cert" {

  value = data.cockroach_cluster_cert.cockroach.cert

}


Step 5: Customer Managed Encryption Keys (CMEK)

Customer-Managed Encryption Keys (CMEK) allow you to protect data at rest in a CockroachDB Dedicated advanced private cluster using a cryptographic key that is entirely within your control, hosted in a supported cloud provider key-management system (KMS). This key is called the CMEK key.

You can manage your CMEK keys using one or more of the following services:

  • Amazon Web Services (AWS) KMS
  • Google Cloud Platform (GCP) KMS

Azure will be added to this list in the Autumn of 2023 once this cloud provider goes General Availability.

Customer-Managed Encryption Keys (CMEK) for CockroachDB Dedicated advanced allows the customer to delegate responsibility for the work of encrypting their cluster data to CockroachDB Cloud, while maintaining the ability to completely revoke CockroachDB Cloud's access. To configure this using Terraform you need to create the following resources.

resource "aws_iam_role" "example" {

  name = "cmek_test_role_ds"


  assume_role_policy = jsonencode({

    "Version" : "2012-10-17",

    "Statement" : [

      {

        "Effect" : "Allow",

        "Action" : "sts:AssumeRole",

        "Principal" : {

          "AWS" : cockroach_cluster.cockroach.account_id

        }

      }

    ]

  })

}


data "aws_iam_user" "example" {

  user_name = "dsheldon"

}


resource "aws_kms_key" "example" {

  policy = jsonencode({

    "Version" : "2012-10-17",

    "Statement" : [

      {

        "Effect" : "Allow",

        "Action" : "kms:*",

        "Principal" : {

          "AWS" : [

            aws_iam_role.example.arn,

            data.aws_iam_user.example.arn

          ]

        },

        "Resource" : "*"

      }

    ]

  })

  multi_region = true

}


resource "cockroach_cmek" "example" {

  id = cockroach_cluster.cockroach.id

  regions = /*concat(*/ [

    {

      region : "eu-west-2"

      key : {

        auth_principal : aws_iam_role.example.arn

        type : "AWS_KMS"

        uri : aws_kms_key.example.arn

      }

    }

  ]

}



Step 6: Maintenance Windows

Within Cockroach Cloud you can view and manage the patch upgrade window for your cluster. To help keep your clusters updated while minimizing disruption and downtime, set a window of time when your cluster is experiencing the lowest traffic. You are also able to configure this using Terraform.

resource "cockroach_maintenance_window" "example" {

  id              = cockroach_cluster.cockroach.id

  offset_duration = var.offset_duration

  window_duration = var.window_duration

}


Step 7: Operations with Datadog

Cockroach Cloud allows for the export of metrics to external monitoring tools like Datadog. By doing this you can collate metrics for other sources to give you an end to end view of the service you are trying to deliver. This is critical in maintaining your prescribed SLA to your customers, whoever that may be. This could be external users of the platform or an internal system where the customers are internal members of staff. To configure and manage metrics export for your CockroachDB Dedicated cluster, use the metricexport endpoint for Datadog. Access to the metricexport endpoints requires a valid CockroachDB Cloud service account with the appropriate permissions (admin privilege or Cluster Admin role). To configure this using Terraform use the following code example to help.

resource "cockroach_metric_export_datadog_config" "example" {

  id      = cockroach_cluster.cockroach.id

  site    = var.datadog_site

  api_key = var.datadog_api_key

}


Step 4: Initialize your Terraform

To prepare our directory containing the terraform code we have just cloned from the GitHub repository we must run terraform init. By running this command it prepares our directory with all the required components that are defined in our code. For example downloading any Terraform providers or modules that are externally hosted. In this blog that will be the CockroachDB Cloud provider.

terraform init


Step 5: Check your planned outcome

Now we are able to move to the next stage which is to run a terraform plan. The terraform plan command creates an execution plan, which lets you preview the changes that Terraform plans to make to your infrastructure. When Terraform creates a plan it:

  • Reads the current state of any already-existing remote objects to make sure that the Terraform state is up-to-date.
  • Compares the current configuration to the prior state and notes any differences.
  • Proposes a set of change actions that should, if applied, make the remote objects match the configuration.

terraform plan


The plan command alone does not actually carry out the proposed changes. You can use this command to check whether the proposed changes match what you expected before you apply the changes or share your changes with your team for broader review.

If Terraform detects that no changes are needed to resource instances or to root module output values, terraform plan will report that no actions need to be taken.

Step 6: Deploy your infrastructure

To build our cluster there is one final step to complete. Once we are happy with our terraform plan and the resources that it is going to create, then terraform apply can be executed.

terraform apply -auto-approve


This will create all the resources that are defined in the terraform code in the repository. It will now take a number of minutes as Terraform instructs the APIs of AWS and Cockroach Cloud.

Validate your infrastructure

After a while (up to 30 mins) depending on the time of day. Your cluster will become visible in the Cockroach Cloud UI where you can observe and monitor the database.

IMPORTANT - An important thing to remember is that the cluster is now being managed by Terraform and changes should be avoided in the UI.

Continuous Improvement…

Since our previous blog post there has been significant developments with the CockroachDB Terraform Provider. From its release it is maturing quickly to cover more and more of the Cockroach Cloud capabilities. As time progresses the provider is becoming more closely aligned to the features available in the Cockroach Cloud UI. The benefit of using Terraform to configure your CockroachDB cluster is that you are able to integrate your database with the other elements of the software stack you are deploying. You will be able use pipeline tools to deploy your application as a complete stack including the database.

Sign up for more like this.

Enter your email
Subscribe