Securing ClickHouse Queries with Roles and Permissions in Spring Boot

December 03, 2025

#1

ClickHouse supports role-based access control (RBAC) to secure queries. With Spring Boot, you can map user roles to database permissions easily.

1. Create Users in ClickHouse

CREATE USER app_user IDENTIFIED BY 'secret';
CREATE USER readonly_user IDENTIFIED BY 'readonly';

Each user has different privileges.

2. Define Roles

CREATE ROLE writer;
CREATE ROLE reader;

Roles simplify permission management across users.

3. Grant Privileges to Roles

GRANT SELECT ON default.* TO reader;
GRANT INSERT, SELECT ON default.* TO writer;

The reader role can only query, while writer can insert and query.

4. Assign Roles to Users

GRANT reader TO readonly_user;
GRANT writer TO app_user;

This links users to their allowed database actions.

5. Configure Spring Boot Connection

spring:
  datasource:
    url: jdbc:clickhouse://localhost:8123/default
    username: app_user
    password: secret

Spring Boot connects using a specific role’s permissions.

6. Testing Permissions

• Use app_user → can insert and read.
• Use readonly_user → can only query.
• Unauthorized actions throw errors.