Manage user privileges for Terraform-deployed ClickHouse® services

This article shows by way of example how to set up user permissions for a Terraform project containing a managed ClickHouse® service. It details how to grant

• Read-and-write access for users who need to write to the database (writer role)

• Read-only access for users who need to read from the database (analyst role).

Prerequisites

• Download and install Terraform.

• Sign up for Aiven if you haven’t already.

• Generate an authentication token.

See also

For information on what types of access you can grant to your project, see Project members and roles.

Let’s cook!

Imagine that you are collecting IoT measurements from thousands of sensors and these metrics are populated in Apache Kafka® topic iot_measurements.

You may wish to create an Aiven for ClickHouse® service along with a database containing IoT sensor measurements and correct permissions for two roles: the writer role (allowed to insert data) and the analyst role (allowed to query data).

Configure common files

Expand to check out the relevant common files needed for this recipe.

Navigate to a new folder and add the following files:

1. provider.tf file

terraform {
  required_providers {
    aiven = {
      source  = "aiven/aiven"
      version = "~> 3.10.0"
    }
  }
}

provider "aiven" {
  api_token = var.aiven_api_token
}

Tip

You can set environment variable TF_VAR_aiven_api_token for the api_token property so that you don’t need to pass the -var-file flag when executing Terraform commands.

2. variables.tf file

Use it for defining the variables to avoid including sensitive information in source control. The variables.tf file defines the API token, the project name to use, and the prefix for the service name:

variable "aiven_api_token" {
  description = "Aiven console API token"
  type        = string
}

variable "project_name" {
  description = "Aiven console project name"
  type        = string
}

3. *.tfvars file

Use it to indicate the actual values of variables so that they can be passed (with the -var-file= flag) to Terraform during runtime and excluded later on. Configure the var-values.tfvars file as follows:

aiven_api_token     = "<YOUR-AIVEN-AUTHENTICATION-TOKEN-GOES-HERE>"
project_name        = "<YOUR-AIVEN-CONSOLE-PROJECT-NAME-GOES-HERE>"

Set up the service with the database

Configure the services.tf file as follows:

resource "aiven_clickhouse" "clickhouse" {
  project                 = var.project_name
  cloud_name              = "google-europe-west1"
  plan                    = "startup-beta-16" // A special plan name for the product being in beta
  service_name            = "clickhouse-gcp-eu"
  maintenance_window_dow  = "monday"
  maintenance_window_time = "10:00:00"
}

resource "aiven_clickhouse_database" "measurements" {
  project                 = var.project_name
  service_name            = aiven_clickhouse.clickhouse.service_name
  name                    = "iot_measurements"
}

Expected result

• "aiven_clickhouse" resource creates an Aiven for ClickHouse service with the project name, the cloud name (provider, region, zone), the service plan, and the service name as specified in the services.tf file.

• "aiven_clickhouse_database" resource creates a database with the project name, the service name, and the database name as specified in the services.tf file.

Grant user permissions

Writer role - read-and-write access

Configure the access-writer.tf file as follows:

// ETL user with write permissions to the IoT measurements DB
resource "aiven_clickhouse_user" "etl" {
  project      = var.project_name
  service_name = aiven_clickhouse.clickhouse.service_name
  username     = "etl"
}

// Writer role that will be granted insert privilege to the measurements DB
resource "aiven_clickhouse_role" "writer" {
  project      = var.project_name
  service_name = aiven_clickhouse.clickhouse.service_name
  role         = "writer"
}

// Writer role's privileges
resource "aiven_clickhouse_grant" "writer_role" {
  project      = aiven_clickhouse.clickhouse.project
  service_name = aiven_clickhouse.clickhouse.service_name
  role         = aiven_clickhouse_role.writer.role

  privilege_grant {
    privilege = "INSERT"
    database  = aiven_clickhouse_database.measurements.name
    table     = "*"
  }

  privilege_grant {
    privilege = "SELECT"
    database  = aiven_clickhouse_database.measurements.name
    table     = "*"
  }
}

// Grant the writer role to the ETL user
resource "aiven_clickhouse_grant" "etl_user" {
  project      = aiven_clickhouse.clickhouse.project
  service_name = aiven_clickhouse.clickhouse.service_name
  user         = aiven_clickhouse_user.etl.username

  role_grant {
    role = aiven_clickhouse_role.writer.role
  }
}

Expected result

• "aiven_clickhouse_user" resource creates a user that can connect to the cluster.

• "aiven_clickhouse_role" resources creates a role that can be granted fine-grained privileges at the table level.

• "aiven_clickhouse_grant"."writer_role" resource specifies the privileges and the scope of their application for the writer role using the privilege_grant nested configuration.

• "aiven_clickhouse_grant"."etl_user" assigns the writer role to the etl user.

Analyst role - read access

Configure the access-analyst.tf file as follows:

// Analyst user with read-only access to the IoT measurements DB
resource "aiven_clickhouse_user" "analyst" {
  project      = var.project_name
  service_name = aiven_clickhouse.clickhouse.service_name
  username     = "analyst"
}

// Reader role that will be granted insert privilege to the measurements DB
resource "aiven_clickhouse_role" "reader" {
  project      = var.project_name
  service_name = aiven_clickhouse.clickhouse.service_name
  role         = "reader"
}

// Reader role's privileges
resource "aiven_clickhouse_grant" "reader_role" {
  project      = aiven_clickhouse.clickhouse.project
  service_name = aiven_clickhouse.clickhouse.service_name
  role         = aiven_clickhouse_role.reader.role

  privilege_grant {
    privilege = "SELECT"
    database  = aiven_clickhouse_database.measurements.name
    table     = "*"
  }
}

// Grant the reader role to the Analyst user
resource "aiven_clickhouse_grant" "analyst_user" {
  project      = aiven_clickhouse.clickhouse.project
  service_name = aiven_clickhouse.clickhouse.service_name
  user         = aiven_clickhouse_user.analyst.username

  role_grant {
    role = aiven_clickhouse_role.reader.role
  }
}

Expected result

• "aiven_clickhouse_user" resource creates a user that can connect to the cluster.

• "aiven_clickhouse_role" resources creates a role that can be granted fine-grained privileges at the table level.

• "aiven_clickhouse_grant"."reader_role" resource specifies the privileges and the scope of their application for the reader’s role using the privilege_grant nested configuration.

• "aiven_clickhouse_grant"."analyst_user" assigns the writer role to the analyst user.

Execute the Terraform files

Expand to check out how to execute the Terraform files.

1. Run the following command:

terraform init

The init command performs initialization operations to prepare the working directory for use with Terraform. For this recipe, init automatically finds, downloads, and installs the necessary Aiven Terraform Provider plugins.

2. Run the following command:

terraform plan -var-file=var-values.tfvars

The plan command creates an execution plan and shows the resources to be created (or modified). This command doesn’t actually create any resources but gives you a heads-up on what’s going to happen.

3. If the output of terraform plan looks as expected, run the following command:

terraform apply -var-file=var-values.tfvars

The terraform apply command creates (or modifies) your infrastructure resources.