How to manage CockroachDB as Code with Terraform

How to manage CockroachDB as Code with Terraform
[ Webinar ]

How to build a payment application that scales to infinity

Join Webinar

Managing all infrastructure as code is a way of ensuring that no configuration drift occurs and that performance is as expected. This is critical when you’re managing applications at scale where the impact of configuration drift is exponential. In this tutorial blog we’ll demonstrate how CockroachDB Dedicated (our DBaaS) can be managed as code via our integration with Terraform. With this integration you are able to integrate your database with the other elements of the software stack you are deploying. And then you can use pipeline tools to deploy your application as a complete stack including the database.

We wrote a blog back in October 2022 to cover the release of the CockroachDB Terraform provider. Since then a number of new feature have been added, including:

•  the ability to retrieve the database connection string and certificate

•  the ability to encrypt your data with your own encryption keys (CMEK)

There are also some operational focused features like the ability to configure maintenance windows which defines when Cockroach Labs can perform maintenance tasks like upgrades. And there’s an integration with Datadog for 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.

How to 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 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 is 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 8: 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 9: 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 10: 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 have 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.

About the authors

Dan Sheldon github link linkedin link

Dan Sheldon is a former Kubernetes Consultant turned Sales Engineer, with an extensive background of delivering multiple cloud based projects. When he's not building things in the cloud he's often found building Lego Technic.

Keep Reading

Performance Benefits of NOT NULL Constraints on Foreign Key Reference Columns

NOT NULL constraints are a ubiquitous tool for upholding data integrity. However, their performance benefits are less …

Read more
How to choose the right metadata store

Choosing the right metadata store should be contingent on your system architecture. There is no definitive ‘right …

How to run a database in Kubernetes

About 75 percent of container orchestration is done in Kubernetes. But the popularity of the k8s platform doesn’t …

Read more