ClickHouse User Management in Spring Boot Applications

December 07, 2025

#1

Managing users in ClickHouse helps control access, permissions, and security. Let’s integrate user management with Spring Boot.

1. Create Users in ClickHouse

CREATE USER app_user IDENTIFIED BY 'password123';
GRANT SELECT, INSERT ON iot_data TO app_user;

This defines a user with limited rights.

2. Configure Spring Boot Datasource

spring.datasource.url=jdbc:clickhouse://localhost:8123/default
spring.datasource.username=app_user
spring.datasource.password=password123

Spring Boot now connects with the created user.

3. Switch Users Dynamically

You can configure multiple datasources for different users:

@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource userDataSource() {
        return DataSourceBuilder.create()
          .url("jdbc:clickhouse://localhost:8123/default")
          .username("app_user")
          .password("password123")
          .build();
    }
}

4. Role-Based Access

ClickHouse supports roles for grouping permissions:

CREATE ROLE readonly;
GRANT SELECT ON iot_data TO readonly;
GRANT readonly TO app_user;

This makes management easier.

5. Expose User Management API

@PostMapping("/api/users")
public void createUser(@RequestBody UserDto dto) {
    jdbcTemplate.execute("CREATE USER " + dto.getUsername() + " IDENTIFIED BY '" + dto.getPassword() + "'");
}

Automate user creation from Spring Boot.