Manage user privileges for Aiven for Clickhouse® services using Terraform

Configure read and write privileges for user groups in Aiven for ClickHouse® services using Terraform. A part of Aiven's Terraform Cookbook.

Subscribe to RSS

This article shows by way of example how to set up user permissions for a Terraform project containing an Aiven for 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

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

Describe the setup

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).

Common files

Navigate to a new folder and add the following files:

provider.tf file

   terraform {
required_providers {
   aiven = {
   source  = "aiven/aiven"
   version = ">=4.0.0, < 5.0.0"
   }
}
   }

   provider "aiven" {
api_token = var.aiven_api_token
   }

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.

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
   }

*.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>"

Services.tf file

Configure the services.tf file as follows to set up the service with the database:

resource "aiven_clickhouse" "clickhouse" {
   project                 = var.project_name
   cloud_name              = "google-europe-west1"
   plan                    = "startup-16"
   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"
}
  • "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.

Writer role - read-and-write access

Configure the access-writer.tf file as follows to grant write permissions to users:

// 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
   }
}
  • "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 to grant read access to users:

// 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
   }
}
  • "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 files

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.

Next, 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.

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.

More resources