ClickHouse Role-Based Access Control in Spring Boot Applications

December 08, 2025

#1

Role-based access control (RBAC) in ClickHouse ensures secure and organized database access. Let’s set up RBAC in a Spring Boot project.

1. Create Roles in ClickHouse

CREATE ROLE reader;
CREATE ROLE writer;
GRANT SELECT ON iot_data TO reader;
GRANT INSERT ON iot_data TO writer;

Roles define specific privileges.

2. Create Users and Assign Roles

CREATE USER user_read IDENTIFIED BY 'read123';
CREATE USER user_write IDENTIFIED BY 'write123';
GRANT reader TO user_read;
GRANT writer TO user_write;

Users inherit permissions from roles.

3. Configure Datasource in Spring Boot

spring.datasource.url=jdbc:clickhouse://localhost:8123/default
spring.datasource.username=user_read
spring.datasource.password=read123

Spring Boot now connects with a role-bound user.

4. Multiple Datasource Configurations

Switch roles dynamically in code:

@Bean
public DataSource writerDataSource() {
    return DataSourceBuilder.create()
      .url("jdbc:clickhouse://localhost:8123/default")
      .username("user_write")
      .password("write123")
      .build();
}

5. Expose APIs with Role Mapping

Integrate Spring Security to route requests to the correct datasource based on the logged-in user’s role.