Skip to main content

ClickHouse Database Security as Code (Versioned)

Managing database security through ad-hoc GRANT and REVOKE commands leads to drift, and permissions scattered across migration files make it hard to answer "who can access what?"

Atlas lets you define roles, users, and permissions as code. With the versioned workflow, each change is captured in a migration file, reviewed by your team, and applied through CI/CD.

This guide covers the versioned workflow. For the declarative approach, see the declarative security guide.

Atlas Pro Feature

Roles, users, and permissions are available only to Atlas Pro users. To use this feature, run:

atlas login

Prerequisites​

  1. Docker
  2. Atlas installed on your machine (installation guide)
  3. An Atlas Pro account (run atlas login to authenticate)

Project Setup​

Start by spinning up a local ClickHouse instance using atlas tool docker:

export DATABASE_URL=$(atlas tool docker --url "docker://clickhouse/23.11" --name my-db)
Cleaning up

When you're done, stop the container with atlas tool docker kill --name my-db.

note

ClickHouse roles are server-level objects, so the connection URL should not include a database path. Atlas will manage both the demo database and the server-level roles through the same connection.

Create an atlas.hcl configuration file with roles and permissions enabled:

atlas.hcl
env "local" {
  url = getenv("DATABASE_URL")
  dev = "docker://clickhouse/23.11"
  schema {
    src = "file://schema.ch.hcl"
    mode {
      roles       = true   // Inspect and manage roles and users
      permissions = true   // Inspect and manage GRANT / REVOKE
    }
  }
  migration {
    dir = "file://migrations"
  }
}

Defining the Desired State​

The schema file is the target state Atlas diffs against the migration directory. Let's model a SaaS order management application with tiered access:

RolePurpose
app_readonlyRead-only access for reporting dashboards
app_writerRead-write access for the application backend, inherits from app_readonly
app_adminFull administrative access, inherits from app_writer
schema.ch.hcl
role "app_readonly" {
}

role "app_writer" {
  member_of = [role.app_readonly]
}

role "app_admin" {
  member_of = [role.app_writer]
}

table "users" {
  schema = schema.demo
  engine = MergeTree
  column "id" {
    type = UInt64
  }
  column "name" {
    type = String
    null = false
  }
  column "email" {
    type = String
    null = false
  }
  primary_key {
    columns = [column.id]
  }
}

table "products" {
  schema = schema.demo
  engine = MergeTree
  column "id" {
    type = UInt64
  }
  column "name" {
    type = String
    null = false
  }
  column "price" {
    type = Decimal(10,2)
    null = false
  }
  primary_key {
    columns = [column.id]
  }
}

table "orders" {
  schema = schema.demo
  engine = MergeTree
  column "id" {
    type = UInt64
  }
  column "user_id" {
    type = UInt64
    null = false
  }
  column "product_id" {
    type = UInt64
    null = false
  }
  column "total" {
    type = Decimal(10,2)
    null = false
  }
  primary_key {
    columns = [column.id]
  }
}

schema "default" {
}

schema "demo" {
}

// Read-only: SELECT on all tables
permission {
  for_each   = [table.orders, table.products, table.users]
  for        = each.value
  to         = role.app_readonly
  privileges = [SELECT]
}

// Writer: SELECT, INSERT on orders and products
permission {
  for_each   = [table.orders, table.products]
  for        = each.value
  to         = role.app_writer
  privileges = [SELECT, INSERT]
}
Users

ClickHouse database users can be mapped to roles using the user block with member_of to assign role membership, and password for authentication. See the HCL reference for details.

Generating the Initial Migration​

Run atlas migrate diff to generate the first migration file:

atlas migrate diff add_security --env local

Atlas creates a migration directory with the generated SQL and a checksum file:

migrations/
├── 20260311120000_add_security.sql
└── atlas.sum

The generated migration contains every role, grant, and table:

migrations/20260311120000_add_security.sql
-- Create role "app_readonly"
CREATE ROLE `app_readonly`;
-- Create role "app_writer"
CREATE ROLE `app_writer`;
-- Grant role "app_readonly" to "app_writer"
GRANT `app_readonly` TO `app_writer`;
-- Create role "app_admin"
CREATE ROLE `app_admin`;
-- Grant role "app_writer" to "app_admin"
GRANT `app_writer` TO `app_admin`;
-- Add new schema named "demo"
CREATE DATABASE `demo` ENGINE Atomic;
-- Create "orders" table
CREATE TABLE `demo`.`orders` (
  `id` UInt64,
  `user_id` UInt64,
  `product_id` UInt64,
  `total` Decimal(10, 2)
) ENGINE = MergeTree
PRIMARY KEY (`id`) ORDER BY (`id`) SETTINGS index_granularity = 8192;
-- Grant on table "orders" to "app_readonly"
GRANT SELECT ON `demo`.`orders` TO `app_readonly`;
-- Grant on table "orders" to "app_writer"
GRANT INSERT, SELECT ON `demo`.`orders` TO `app_writer`;
-- Create "products" table
CREATE TABLE `demo`.`products` (
  `id` UInt64,
  `name` String,
  `price` Decimal(10, 2)
) ENGINE = MergeTree
PRIMARY KEY (`id`) ORDER BY (`id`) SETTINGS index_granularity = 8192;
-- Grant on table "products" to "app_readonly"
GRANT SELECT ON `demo`.`products` TO `app_readonly`;
-- Grant on table "products" to "app_writer"
GRANT INSERT, SELECT ON `demo`.`products` TO `app_writer`;
-- Create "users" table
CREATE TABLE `demo`.`users` (
  `id` UInt64,
  `name` String,
  `email` String
) ENGINE = MergeTree
PRIMARY KEY (`id`) ORDER BY (`id`) SETTINGS index_granularity = 8192;
-- Grant on table "users" to "app_readonly"
GRANT SELECT ON `demo`.`users` TO `app_readonly`;

Applying Migrations​

Apply to the target database:

atlas migrate apply --env local
Migrating to version 20260311120000 (1 migration in total):

  -- migrating version 20260311120000
    -> CREATE ROLE `app_readonly`
    -> CREATE ROLE `app_writer`
    -> GRANT `app_readonly` TO `app_writer`
    -> CREATE ROLE `app_admin`
    -> GRANT `app_writer` TO `app_admin`
    -> CREATE DATABASE `demo` ENGINE Atomic
    -> CREATE TABLE `demo`.`orders` ( ... )
    -> GRANT SELECT ON `demo`.`orders` TO `app_readonly`
    -> GRANT INSERT, SELECT ON `demo`.`orders` TO `app_writer`
    -> CREATE TABLE `demo`.`products` ( ... )
    -> GRANT SELECT ON `demo`.`products` TO `app_readonly`
    -> GRANT INSERT, SELECT ON `demo`.`products` TO `app_writer`
    -> CREATE TABLE `demo`.`users` ( ... )
    -> GRANT SELECT ON `demo`.`users` TO `app_readonly`
  -- ok

  -------------------------
  -- 1 migration
  -- 14 sql statements

With Atlas Cloud, push your migration directory and deploy from any CI/CD platform:

atlas migrate push app --env local

Making Incremental Changes​

When requirements change, update the schema file and generate a new migration. Atlas computes only the diff. For example, adding an app_support role for the support team:

Update the schema file:

schema.ch.hcl
role "app_support" {
  member_of = [role.app_readonly]
}

// Support can view and update orders
permission {
  to         = role.app_support
  for        = table.orders
  privileges = [SELECT, ALTER_UPDATE]
}

Generate the incremental migration:

atlas migrate diff add_support_role --env local

Atlas generates only what changed - the new role and its grants:

migrations/20260311130000_add_support_role.sql
-- Create role "app_support"
CREATE ROLE `app_support`;
-- Grant role "app_readonly" to "app_support"
GRANT `app_readonly` TO `app_support`;
-- Grant on table "orders" to "app_support"
GRANT ALTER UPDATE, SELECT ON `demo`.`orders` TO `app_support`;

The migration directory now holds both files. The sum of all migrations represents the current security state:

migrations/
├── 20260311120000_add_security.sql
├── 20260311130000_add_support_role.sql
└── atlas.sum

Next Steps​

Have questions? Feedback? Find our team on our Discord server or schedule a demo.