New Terraform provider: Manage ClickHouse database users, roles, and privileges with code

We’re excited to announce the general availability of our new Terraform provider, clickhousedbops, to help customers manage database users, roles, and grant roles and privileges. This new provider supports ClickHouse instances running on both ClickHouse Cloud or self hosted.

We have already seen adoption from many ClickHouse Cloud customers for using Terraform to programmatically automate the configuration and management of their ClickHouse services. These customers see major ease of use benefits using Terraform to manage the state of all their ClickHouse services across different environments. This Terraform functionality has been supported with our existing Clickhouse Terraform provider for managing and deploying services, private endpoints, and Transparent Data Encryption keys for services. Now customers can extend their Terraform use cases with our clickhousedbops provider. After defining a direct connection to their clickhouse instance, customers can build databases and then define users and roles with privilege grants on that database’s tables or columns.

Imagine you are an engineering team lead and you are interested in building a new ClickHouse database so your team can store and analyze your business unit’s email and phone log data. You already manage all your other infrastructure programmatically using Terraform and so you are interested in doing the same with ClickHouse.

You want to first login to your ClickHouse Cloud account. Then once you login, you can create an API token and secret from your ClickHouse cloud organization in order to allow terraform to connect.

Now you want to define a new ClickHouse Cloud service called “Log Analytics” using Terraform. You can create a main.tf file to configure the Clickhouse provider. You want to define your new ClickHouse Cloud service resource in the AWS us-east-1 region named “Log Analytics” in the same file.

Note: We do not recommend storing credentials in your terraform files, see docs here for help creating a separate variables file.

Main.tf file

terraform {
  required_providers {
    clickhouse = {
      source  = "ClickHouse/clickhouse"
    }
   }
}

locals {
  # Please don't do this :)
  password = "test"
}

# Configuration-based authentication, replace with your own values
provider "clickhouse" {
  organization_id = ""
  token_key       = ""
  token_secret    = ""
}

resource "clickhouse_service" "service" {
  name           = "Log Analytics"
  cloud_provider = "aws"
  region         = "us-east-1"
  idle_scaling   = true
  min_replica_memory_gb  = 8
  max_replica_memory_gb  = 16
  num_replicas = 3
  idle_timeout_minutes = 5

  # for this example this service will be open to all internet connections
  ip_access = [
    {
      source      = "0.0.0.0/0"
      description = "Test IP"
    }
  ]
  password_hash = sha256(local.password)
}

Now that you have your new service defined you want to add your team to a new database called “logs” in the new “Log Analytics” service you created. The following are the requirements for your team to define in Terraform:

1. Your team has 3 users - Ryan, Jill, and Carol.
2. Ryan is responsible for running analytic queries only on the email logs table in the database, but does not need read access to any other tables.
3. Jill and Carol are responsible for running analytic queries only on the phone logs table in the database, but do not need read access to any other tables.

In order to satisfy these requirements, you decide to use Terraform and the new Clickhousedbops provider to configure these users, roles, and grant privileges. First you add the terraform provider to the required_providers block at the top of the same main.tf terraform file.

terraform {
  required_providers {
    clickhouse = {
      source  = "ClickHouse/clickhouse"
    }
    clickhousedbops = {
      source  = "ClickHouse/clickhousedbops"
    }
   }
}
...

Next, you configure the provider and the resources for database, users, roles, and grant privileges according to the requirements. These resources are appended at the end of the same main.tf file.

provider "clickhousedbops" {
  protocol = "nativesecure"      
  host = clickhouse_service.service.endpoints.nativesecure.host
  port = clickhouse_service.service.endpoints.nativesecure.port
  auth_config = {
    strategy = "password"
    username = "default"
    password = local.password
  }
}
resource "clickhousedbops_database" "logs" {
  name = "logs"
  comment = "Database for logs"
}

resource "clickhousedbops_user" "ryan" {
  name = "ryan"
  # You'll want to generate the password and feed it here instead of hardcoding.
  password_sha256_hash_wo = sha256("test1")
  password_sha256_hash_wo_version = 1
}


resource "clickhousedbops_user" "jill" {
  name = "jill"
  # You'll want to generate the password and feed it here instead of hardcoding.
  password_sha256_hash_wo = sha256("test2")
  password_sha256_hash_wo_version = 1
}

resource "clickhousedbops_user" "carol" {
  name = "carol"
  # You'll want to generate the password and feed it here instead of hardcoding.
  password_sha256_hash_wo = sha256("test3")
  password_sha256_hash_wo_version = 1
}

resource "clickhousedbops_role" "reader_email_logs" {
  name = "reader_email_logs"
}

resource "clickhousedbops_role" "reader_phone_logs" {
  name = "reader_phone_logs"
}

resource "clickhousedbops_grant_privilege" "grant_select_on_email_logs_to_user" {
  privilege_name    = "SELECT"
  database_name     = "logs"
  table_name        = "email_logs"
  grantee_role_name = clickhousedbops_role.reader_email_logs.name
  grant_option      = true
}

resource "clickhousedbops_grant_privilege" "grant_select_on_phone_logs_to_user" {
  privilege_name    = "SELECT"
  database_name     = "logs"
  table_name        = "phone_logs"
  grantee_role_name = clickhousedbops_role.reader_phone_logs.name
  grant_option      = true
}

resource "clickhousedbops_grant_role" "role_to_user_ryan" {
  role_name         = clickhousedbops_role.reader_email_logs.name
  grantee_user_name = clickhousedbops_user.ryan.name
}

resource "clickhousedbops_grant_role" "role_to_user_jill" {
  role_name         = clickhousedbops_role.reader_phone_logs.name
  grantee_user_name = clickhousedbops_user.jill.name
}

resource "clickhousedbops_grant_role" "role_to_user_carol" {
  role_name         = clickhousedbops_role.reader_phone_logs.name
  grantee_user_name = clickhousedbops_user.carol.name
}

Finally, in order to deploy your terraform configuration into your ClickHouse Cloud organization, run “terraform init” to download the providers and then “terraform apply. In a few minutes you will have the clickhouse service and the database resources created.

% terraform apply
Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # clickhouse_service.service will be created
  + resource "clickhouse_service" "service" {
      + backup_configuration        = (known after apply)
      + cloud_provider              = "aws"
      + endpoints                   = (known after apply)
      + iam_role                    = (known after apply)
      + id                          = (known after apply)
      + idle_scaling                = true
      + idle_timeout_minutes        = 5
...
clickhouse_service.service: Creating...
clickhouse_service.service: Still creating... [10s elapsed]
...
clickhouse_service.service: Creation complete after 6m36s 
...
clickhousedbops_role.reader_email_logs: Creating...
clickhousedbops_role.reader_phone_logs: Creating...
clickhousedbops_database.logs: Creating...
clickhousedbops_user.ryan: Creating...
clickhousedbops_user.carol: Creating...
clickhousedbops_user.jill: Creating...
...
Apply complete! Resources: 12 added, 0 changed, 0 destroyed.

After this you can use the ClickHouse Client CLI to login as the default user and create two tables “email_logs” and “phone_logs” in this logs database, load the intended data into each, and then confirm that the correct users were created and that each user has the correct role and database privileges.

Tip: connect to the database and run the SQL command “SELECT * FROM system.role_grants” to see all database users and roles for each.

Congratulations! You have successfully used both of the officially supported ClickHouse Terraform providers for configuring your ClickHouse Cloud service, database, users, roles, and database privileges.

Now let’s say that you are not a ClickHouse Cloud customer (free trial offered here!). This new clickhousedbops provider also has support for our customers who want to use Terraform for managing their self-hosted ClickHouse database users, roles, and grant privileges in their own self hosted cloud or on-prem environment.

To support these open source ClickHouse deployments, if users have multiple replicas then a “cluster_name” variable (database resource example) can be defined in each of the clickhousedbops resource definitions. This will create the resources in all of the replicas in that cluster. If omitted, that resource will be created on the replica hit by the query. We recommend that this should always be set when hitting a cluster with more than one replica.

Note: This field must be left null when using a ClickHouse Cloud cluster. When using a self hosted ClickHouse instance, this field should only be set for non database resources when there is more than one replica and you are not using 'replicated' storage for user_directory.

You would define your clickhousedbops database resource as such:

resource "clickhousedbops_database" "logs" {
  name = "logs"
  cluster_name = "cluster1"
  comment = "Database for logs"
}

We hope our new Terraform support makes managing ClickHouse infrastructure easier and more reliable. We encourage users who are interested to try out our terraform providers and give us feedback directly through opening an issue in either GitHub repository - ClickHouse or Clickhousedops.