Secure your ClickHouse deployment with proper user authentication, role-based access control, and production-grade security policies. Learn to create users, manage roles, implement quota systems, and monitor access patterns for enterprise environments.

Prerequisites

ClickHouse server installed and running
Root or sudo access
Basic understanding of XML configuration
Network access to ClickHouse ports

What this solves

ClickHouse default installations allow unrestricted access to all databases and operations, creating significant security risks in production environments. This tutorial implements proper user authentication, role-based access control (RBAC), and security policies to protect sensitive analytical data and prevent unauthorized access or resource abuse.

Step-by-step configuration

Update system packages and verify ClickHouse installation

Ensure your system is updated and ClickHouse is properly installed before configuring security settings.

sudo apt update && sudo apt upgrade -y
sudo systemctl status clickhouse-server

sudo dnf update -y
sudo systemctl status clickhouse-server

Create secure directory structure for user configurations

Organize ClickHouse user and role configurations in a dedicated directory with proper permissions for security and maintainability.

sudo mkdir -p /etc/clickhouse-server/users.d
sudo mkdir -p /etc/clickhouse-server/config.d
sudo chown clickhouse:clickhouse /etc/clickhouse-server/users.d
sudo chown clickhouse:clickhouse /etc/clickhouse-server/config.d
sudo chmod 750 /etc/clickhouse-server/users.d
sudo chmod 750 /etc/clickhouse-server/config.d

Configure password encryption and security settings

Enable secure password storage with SHA-256 encryption and configure session timeout policies to enhance authentication security.

<clickhouse>
    <password_complexity>
        <rule>
            <pattern>.{12,}</pattern>
            <message>Password must be at least 12 characters</message>
        </rule>
        <rule>
            <pattern>.\d.</pattern>
            <message>Password must contain at least one digit</message>
        </rule>
        <rule>
            <pattern>.[A-Z].</pattern>
            <message>Password must contain at least one uppercase letter</message>
        </rule>
    </password_complexity>
    <session_timeout>3600</session_timeout>
    <max_sessions_for_user>10</max_sessions_for_user>
    <listen_host>127.0.0.1</listen_host>
    <listen_host>203.0.113.10</listen_host>
</clickhouse>

Create admin user with full privileges

Configure a dedicated administrative user with secure password hash and complete database access for system management operations.

echo -n 'AdminPass123!' | sha256sum

<clickhouse>
    <users>
        <admin_user>
            <password_sha256_hex>c8b8f5e8a5c4d7b2f9e3a1d6c4b8f5e8a5c4d7b2f9e3a1d6c4b8f5e8a5c4d7b2</password_sha256_hex>
            <profile>admin_profile</profile>
            <quota>admin_quota</quota>
            <networks>
                <ip>127.0.0.1</ip>
                <ip>203.0.113.0/24</ip>
            </networks>
            <grants>
                <query>GRANT ALL ON .</query>
                <query>GRANT ROLE admin_role</query>
            </grants>
        </admin_user>
    </users>
    <profiles>
        <admin_profile>
            <max_memory_usage>10000000000</max_memory_usage>
            <use_uncompressed_cache>1</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
            <max_query_size>1000000</max_query_size>
            <max_concurrent_queries_for_user>100</max_concurrent_queries_for_user>
        </admin_profile>
    </profiles>
    <quotas>
        <admin_quota>
            <interval>
                <duration>3600</duration>
                <queries>10000</queries>
                <errors>100</errors>
                <result_rows>100000000</result_rows>
                <read_rows>100000000</read_rows>
                <execution_time>3600</execution_time>
            </interval>
        </admin_quota>
    </quotas>
</clickhouse>

Configure RBAC roles for different access levels

Create granular roles for read-only access, data analysts, and application users with specific permissions and resource limits.

<clickhouse>
    <roles>
        <readonly_role>
            <grants>
                <query>GRANT SELECT ON analytics_db.*</query>
                <query>GRANT SHOW TABLES ON analytics_db.*</query>
                <query>GRANT SHOW DATABASES</query>
            </grants>
        </readonly_role>
        <analyst_role>
            <grants>
                <query>GRANT SELECT, CREATE TABLE ON analytics_db.*</query>
                <query>GRANT CREATE TEMPORARY TABLE</query>
                <query>GRANT CREATE VIEW ON analytics_db.*</query>
                <query>GRANT DROP TABLE ON analytics_db.temp_*</query>
            </grants>
        </analyst_role>
        <app_role>
            <grants>
                <query>GRANT SELECT, INSERT ON app_db.*</query>
                <query>GRANT OPTIMIZE TABLE ON app_db.*</query>
            </grants>
        </app_role>
        <admin_role>
            <grants>
                <query>GRANT ALL ON .</query>
                <query>GRANT SYSTEM ON CLUSTER *</query>
                <query>GRANT CREATE USER, DROP USER, ALTER USER</query>
            </grants>
        </admin_role>
    </roles>
</clickhouse>

Create application users with restricted access

Configure specialized users for different application tiers with appropriate resource quotas and network restrictions.

echo -n 'AnalystSecure456' | sha256sum
echo -n 'AppUserPass789' | sha256sum

<clickhouse>
    <users>
        <analyst_user>
            <password_sha256_hex>a7b2c3d4e5f6789012345678901234567890abcdef1234567890abcdef123456</password_sha256_hex>
            <profile>analyst_profile</profile>
            <quota>analyst_quota</quota>
            <networks>
                <ip>203.0.113.0/24</ip>
            </networks>
            <grants>
                <query>GRANT ROLE analyst_role</query>
            </grants>
            <default_database>analytics_db</default_database>
        </analyst_user>
        <app_user>
            <password_sha256_hex>b8c9d0e1f2a3456789012345678901234567890abcdef1234567890abcdef1234</password_sha256_hex>
            <profile>app_profile</profile>
            <quota>app_quota</quota>
            <networks>
                <ip>203.0.113.100/32</ip>
                <ip>203.0.113.101/32</ip>
            </networks>
            <grants>
                <query>GRANT ROLE app_role</query>
            </grants>
            <default_database>app_db</default_database>
        </app_user>
        <readonly_user>
            <password_sha256_hex>d2e3f4a5b67890123456789012345678901234abcdef567890123456789012345</password_sha256_hex>
            <profile>readonly_profile</profile>
            <quota>readonly_quota</quota>
            <networks>
                <ip>203.0.113.0/24</ip>
            </networks>
            <grants>
                <query>GRANT ROLE readonly_role</query>
            </grants>
            <readonly>1</readonly>
        </readonly_user>
    </users>
</clickhouse>

Configure user profiles with resource limits

Define performance profiles that control memory usage, query complexity, and execution timeouts for different user types.

<clickhouse>
    <profiles>
        <analyst_profile>
            <max_memory_usage>2000000000</max_memory_usage>
            <max_query_size>262144</max_query_size>
            <max_ast_depth>1000</max_ast_depth>
            <max_ast_elements>50000</max_ast_elements>
            <max_execution_time>300</max_execution_time>
            <max_concurrent_queries_for_user>5</max_concurrent_queries_for_user>
            <readonly>0</readonly>
            <allow_ddl>1</allow_ddl>
        </analyst_profile>
        <app_profile>
            <max_memory_usage>1000000000</max_memory_usage>
            <max_query_size>65536</max_query_size>
            <max_execution_time>60</max_execution_time>
            <max_concurrent_queries_for_user>20</max_concurrent_queries_for_user>
            <readonly>0</readonly>
            <allow_ddl>0</allow_ddl>
            <max_rows_to_read>1000000</max_rows_to_read>
            <max_bytes_to_read>1000000000</max_bytes_to_read>
        </app_profile>
        <readonly_profile>
            <max_memory_usage>500000000</max_memory_usage>
            <max_query_size>32768</max_query_size>
            <max_execution_time>120</max_execution_time>
            <max_concurrent_queries_for_user>3</max_concurrent_queries_for_user>
            <readonly>1</readonly>
            <allow_ddl>0</allow_ddl>
            <max_result_rows>10000</max_result_rows>
        </readonly_profile>
    </profiles>
</clickhouse>

Implement quota system for resource management

Configure quota policies that limit resource consumption per user type to prevent system overload and ensure fair resource allocation.

<clickhouse>
    <quotas>
        <analyst_quota>
            <interval>
                <duration>3600</duration>
                <queries>1000</queries>
                <errors>50</errors>
                <result_rows>10000000</result_rows>
                <read_rows>50000000</read_rows>
                <execution_time>7200</execution_time>
            </interval>
            <interval>
                <duration>86400</duration>
                <queries>10000</queries>
                <errors>200</errors>
                <result_rows>100000000</result_rows>
                <read_rows>500000000</read_rows>
                <execution_time>28800</execution_time>
            </interval>
        </analyst_quota>
        <app_quota>
            <interval>
                <duration>3600</duration>
                <queries>10000</queries>
                <errors>100</errors>
                <result_rows>1000000</result_rows>
                <read_rows>10000000</read_rows>
                <execution_time>3600</execution_time>
            </interval>
        </app_quota>
        <readonly_quota>
            <interval>
                <duration>3600</duration>
                <queries>100</queries>
                <errors>10</errors>
                <result_rows>100000</result_rows>
                <read_rows>1000000</read_rows>
                <execution_time>600</execution_time>
            </interval>
        </readonly_quota>
    </quotas>
</clickhouse>

Disable default user and enable authentication

Remove the insecure default user and enforce authentication requirements for all database connections.

<clickhouse>
    <users>
        <default remove="1"/>
    </users>
</clickhouse>

Configure audit logging for security monitoring

Enable comprehensive audit logging to track user activities, failed authentication attempts, and privilege escalations.

<clickhouse>
    <query_log>
        <database>system</database>
        <table>query_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_log>
    <query_thread_log>
        <database>system</database>
        <table>query_thread_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_thread_log>
    <session_log>
        <database>system</database>
        <table>session_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </session_log>
    <part_log>
        <database>system</database>
        <table>part_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </part_log>
</clickhouse>

Set secure file permissions on configuration files

Protect user credentials and configuration files by setting restrictive permissions that prevent unauthorized access.

Never use chmod 777. It gives every user on the system full access to your files. Instead, fix ownership with chown and use minimal permissions.

sudo chown clickhouse:clickhouse /etc/clickhouse-server/users.d/*.xml
sudo chown clickhouse:clickhouse /etc/clickhouse-server/config.d/*.xml
sudo chmod 640 /etc/clickhouse-server/users.d/*.xml
sudo chmod 640 /etc/clickhouse-server/config.d/*.xml
sudo chmod 750 /etc/clickhouse-server/users.d
sudo chmod 750 /etc/clickhouse-server/config.d

Restart ClickHouse and verify configuration

Apply the new security configuration and confirm that ClickHouse starts successfully with authentication enabled.

sudo systemctl restart clickhouse-server
sudo systemctl status clickhouse-server
sudo tail -n 50 /var/log/clickhouse-server/clickhouse-server.log

Test authentication and user access

Test admin user authentication

Verify that the administrative user can connect and perform privileged operations.

clickhouse-client --user admin_user --password
Enter: AdminPass123!
SHOW USERS;
SHOW ROLES;
SELECT * FROM system.quotas;

Test role-based access restrictions

Confirm that users have appropriate access levels based on their assigned roles and profiles.

# Test analyst user
clickhouse-client --user analyst_user --password
Enter: AnalystSecure456
SHOW DATABASES;
CREATE DATABASE IF NOT EXISTS analytics_db;
USE analytics_db;
CREATE TABLE test_table (id UInt32, name String) ENGINE = Memory;
\q

Test readonly user
clickhouse-client --user readonly_user --password
Should fail to create tables
CREATE TABLE fail_table (id UInt32) ENGINE = Memory;

Configure monitoring and alerting

Create monitoring queries for security events

Set up queries to detect suspicious activities, quota violations, and authentication failures for ongoing security monitoring.

clickhouse-client --user admin_user --password

-- Monitor failed login attempts
SELECT 
    user,
    client_hostname,
    client_address,
    count() as failed_attempts,
    max(event_time) as last_attempt
FROM system.session_log 
WHERE type = 'LoginFailure' 
  AND event_date >= today() - 1
GROUP BY user, client_hostname, client_address
ORDER BY failed_attempts DESC;

-- Monitor quota usage
SELECT 
    quota_name,
    user_name,
    queries,
    max_queries,
    round(queries / max_queries * 100, 2) as usage_percent
FROM system.quota_usage 
WHERE usage_percent > 80;

-- Monitor privileged operations
SELECT 
    user,
    query,
    event_time,
    client_address
FROM system.query_log 
WHERE query ILIKE '%CREATE USER%' 
   OR query ILIKE '%DROP USER%' 
   OR query ILIKE '%GRANT%'
   AND event_date >= today() - 1
ORDER BY event_time DESC;

Verify your setup

# Check ClickHouse service status
sudo systemctl status clickhouse-server

Verify users are configured
clickhouse-client --user admin_user --password -q "SHOW USERS"

Check roles configuration
clickhouse-client --user admin_user --password -q "SHOW ROLES"

Test authentication enforcement
clickhouse-client -q "SELECT 1" # Should fail without credentials

Verify audit logging is active
clickhouse-client --user admin_user --password -q "SELECT count() FROM system.query_log WHERE event_date = today()"

Check quota enforcement
clickhouse-client --user readonly_user --password -q "SELECT user_name, quota_name FROM system.quota_usage WHERE user_name = 'readonly_user'"

Common issues

Symptom	Cause	Fix
Authentication required error	Default user disabled without creating replacement	Create admin user before disabling default user
Configuration not loaded	XML syntax errors or file permissions	Check logs with `sudo tail /var/log/clickhouse-server/clickhouse-server.err.log`
User cannot connect remotely	Network restrictions in user config	Add client IP to user's networks section
Permission denied on config files	Incorrect file ownership or permissions	Fix with `sudo chown clickhouse:clickhouse` and `chmod 640`
Quota exceeded errors	Resource limits too restrictive	Adjust quota values or reset with `SYSTEM RELOAD USERS`
Role assignments not working	Role not granted to user properly	Use `GRANT ROLE role_name TO user_name` syntax

Next steps

Automated install script

Run this to automate the entire setup

install.sh

#!/usr/bin/env bash

set -euo pipefail

# ClickHouse RBAC Security Configuration Script
# Configures users, roles, and access control for production environments

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Default values
ADMIN_PASSWORD="${1:-AdminPass123!}"
CLICKHOUSE_HOST="${2:-127.0.0.1}"

# Usage information
usage() {
    echo "Usage: $0 [admin_password] [clickhouse_host]"
    echo "  admin_password: Password for admin user (default: AdminPass123!)"
    echo "  clickhouse_host: ClickHouse server IP (default: 127.0.0.1)"
    exit 1
}

# Cleanup function for rollback
cleanup() {
    echo -e "${RED}[ERROR] Script failed. Rolling back changes...${NC}"
    systemctl stop clickhouse-server 2>/dev/null || true
    rm -f /etc/clickhouse-server/users.d/security.xml 2>/dev/null || true
    rm -f /etc/clickhouse-server/config.d/security.xml 2>/dev/null || true
    systemctl start clickhouse-server 2>/dev/null || true
    exit 1
}

trap cleanup ERR

# Check if running as root
if [[ $EUID -ne 0 ]]; then
    echo -e "${RED}This script must be run as root${NC}"
    exit 1
fi

# Auto-detect distribution
echo -e "${YELLOW}[1/8] Detecting distribution...${NC}"
if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian) 
            PKG_MGR="apt"
            PKG_UPDATE="apt update"
            PKG_INSTALL="apt install -y"
            PKG_UPGRADE="apt upgrade -y"
            ;;
        almalinux|rocky|centos|rhel|ol|fedora) 
            PKG_MGR="dnf"
            PKG_UPDATE="dnf check-update || true"
            PKG_INSTALL="dnf install -y"
            PKG_UPGRADE="dnf upgrade -y"
            ;;
        amzn) 
            PKG_MGR="yum"
            PKG_UPDATE="yum check-update || true"
            PKG_INSTALL="yum install -y"
            PKG_UPGRADE="yum upgrade -y"
            ;;
        *) 
            echo -e "${RED}Unsupported distribution: $ID${NC}"
            exit 1
            ;;
    esac
    echo -e "${GREEN}Detected: $PRETTY_NAME${NC}"
else
    echo -e "${RED}Cannot detect distribution${NC}"
    exit 1
fi

# Update system packages
echo -e "${YELLOW}[2/8] Updating system packages...${NC}"
$PKG_UPDATE
$PKG_UPGRADE

# Check ClickHouse installation
echo -e "${YELLOW}[3/8] Checking ClickHouse installation...${NC}"
if ! command -v clickhouse-server &> /dev/null; then
    echo -e "${RED}ClickHouse not found. Please install ClickHouse first.${NC}"
    exit 1
fi

if ! systemctl is-active --quiet clickhouse-server; then
    systemctl start clickhouse-server
fi

echo -e "${GREEN}ClickHouse is running${NC}"

# Create secure directory structure
echo -e "${YELLOW}[4/8] Creating secure directory structure...${NC}"
mkdir -p /etc/clickhouse-server/users.d
mkdir -p /etc/clickhouse-server/config.d

# Set proper ownership and permissions
chown clickhouse:clickhouse /etc/clickhouse-server/users.d
chown clickhouse:clickhouse /etc/clickhouse-server/config.d
chmod 750 /etc/clickhouse-server/users.d
chmod 750 /etc/clickhouse-server/config.d

echo -e "${GREEN}Directory structure created${NC}"

# Generate password hash
echo -e "${YELLOW}[5/8] Generating secure password hash...${NC}"
PASSWORD_HASH=$(echo -n "$ADMIN_PASSWORD" | sha256sum | cut -d' ' -f1)
echo -e "${GREEN}Password hash generated${NC}"

# Configure security settings
echo -e "${YELLOW}[6/8] Configuring security settings...${NC}"
cat > /etc/clickhouse-server/config.d/security.xml << EOF
<clickhouse>
    <password_complexity>
        <rule>
            <pattern>.{12,}</pattern>
            <message>Password must be at least 12 characters</message>
        </rule>
        <rule>
            <pattern>.*\d.*</pattern>
            <message>Password must contain at least one digit</message>
        </rule>
        <rule>
            <pattern>.*[A-Z].*</pattern>
            <message>Password must contain at least one uppercase letter</message>
        </rule>
    </password_complexity>
    <session_timeout>3600</session_timeout>
    <max_sessions_for_user>10</max_sessions_for_user>
    <listen_host>127.0.0.1</listen_host>
    <listen_host>$CLICKHOUSE_HOST</listen_host>
</clickhouse>
EOF

chown clickhouse:clickhouse /etc/clickhouse-server/config.d/security.xml
chmod 640 /etc/clickhouse-server/config.d/security.xml

echo -e "${GREEN}Security settings configured${NC}"

# Create users and RBAC configuration
echo -e "${YELLOW}[7/8] Creating users and RBAC configuration...${NC}"
cat > /etc/clickhouse-server/users.d/security.xml << EOF
<clickhouse>
    <users>
        <admin_user>
            <password_sha256_hex>$PASSWORD_HASH</password_sha256_hex>
            <profile>admin_profile</profile>
            <quota>admin_quota</quota>
            <networks>
                <ip>127.0.0.1</ip>
                <ip>$CLICKHOUSE_HOST/32</ip>
            </networks>
            <grants>
                <query>GRANT ALL ON *.*</query>
            </grants>
        </admin_user>
        <readonly_user>
            <password>ReadOnly123!</password>
            <profile>readonly_profile</profile>
            <quota>readonly_quota</quota>
            <networks>
                <ip>127.0.0.1</ip>
                <ip>$CLICKHOUSE_HOST/32</ip>
            </networks>
            <grants>
                <query>GRANT SELECT ON *.*</query>
            </grants>
        </readonly_user>
    </users>
    <profiles>
        <admin_profile>
            <max_memory_usage>10000000000</max_memory_usage>
            <use_uncompressed_cache>1</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
            <max_query_size>1000000</max_query_size>
            <max_concurrent_queries_for_user>100</max_concurrent_queries_for_user>
        </admin_profile>
        <readonly_profile>
            <max_memory_usage>5000000000</max_memory_usage>
            <use_uncompressed_cache>1</use_uncompressed_cache>
            <readonly>1</readonly>
            <max_query_size>100000</max_query_size>
            <max_concurrent_queries_for_user>10</max_concurrent_queries_for_user>
        </readonly_profile>
    </profiles>
    <quotas>
        <admin_quota>
            <interval>
                <duration>3600</duration>
                <queries>10000</queries>
                <errors>100</errors>
                <result_rows>100000000</result_rows>
                <read_rows>100000000</read_rows>
                <execution_time>3600</execution_time>
            </interval>
        </admin_quota>
        <readonly_quota>
            <interval>
                <duration>3600</duration>
                <queries>1000</queries>
                <errors>10</errors>
                <result_rows>10000000</result_rows>
                <read_rows>10000000</read_rows>
                <execution_time>600</execution_time>
            </interval>
        </readonly_quota>
    </quotas>
</clickhouse>
EOF

chown clickhouse:clickhouse /etc/clickhouse-server/users.d/security.xml
chmod 640 /etc/clickhouse-server/users.d/security.xml

echo -e "${GREEN}Users and RBAC configured${NC}"

# Restart and verify ClickHouse
echo -e "${YELLOW}[8/8] Restarting and verifying ClickHouse...${NC}"
systemctl restart clickhouse-server

# Wait for service to start
sleep 5

if ! systemctl is-active --quiet clickhouse-server; then
    echo -e "${RED}ClickHouse failed to start after configuration${NC}"
    exit 1
fi

# Verify configuration
if clickhouse-client --host=$CLICKHOUSE_HOST --user=admin_user --password="$ADMIN_PASSWORD" --query="SELECT 1" &>/dev/null; then
    echo -e "${GREEN}Admin user authentication successful${NC}"
else
    echo -e "${RED}Admin user authentication failed${NC}"
    exit 1
fi

echo -e "${GREEN}ClickHouse RBAC configuration completed successfully!${NC}"
echo ""
echo "Configuration Summary:"
echo "- Admin user: admin_user"
echo "- Admin password: $ADMIN_PASSWORD"
echo "- Readonly user: readonly_user"
echo "- Readonly password: ReadOnly123!"
echo "- Listen address: $CLICKHOUSE_HOST"
echo "- Security features: Password complexity, session timeout, user quotas"
echo ""
echo "Connect using: clickhouse-client --host=$CLICKHOUSE_HOST --user=admin_user --password='$ADMIN_PASSWORD'"

Review the script before running. Execute with: bash install.sh

#clickhouse #rbac #authentication #database-security #user-management

Configure ClickHouse users and RBAC for production environments with authentication and access control

Prerequisites

What this solves

Step-by-step configuration

Update system packages and verify ClickHouse installation

Create secure directory structure for user configurations

Configure password encryption and security settings

Create admin user with full privileges

Configure RBAC roles for different access levels

Create application users with restricted access

Configure user profiles with resource limits

Implement quota system for resource management

Disable default user and enable authentication

Configure audit logging for security monitoring

Set secure file permissions on configuration files

Restart ClickHouse and verify configuration

Test authentication and user access

Test admin user authentication

Enter: AdminPass123!

Test role-based access restrictions

Enter: AnalystSecure456

Test readonly user

Should fail to create tables

Configure monitoring and alerting

Create monitoring queries for security events

Verify your setup

Verify users are configured

Check roles configuration

Test authentication enforcement

Verify audit logging is active

Check quota enforcement

Common issues

Next steps

Related tutorials

Implement network security with iptables connection tracking and stateful packet filtering

Implement Deno JWT authentication with OAuth2 integration for secure API development

Implement encryption for Consul backups using GPG with automated snapshots and secure storage

Don't want to manage this yourself?