ClickHouse Authentication Bypass Detection: From Discovery to Data Extraction

┌─────────────────────────────────────────────────────────────────┐
│ CLICKHOUSE AUTHENTICATION BYPASS ATTACK │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 1. Attacker discovers ClickHouse HTTP interface (port 8123) │
│ ↓ │
│ 2. Attacker sends unauthenticated query: SELECT 1 │
│ ↓ │
│ 3. Server receives query WITHOUT credentials │
│ ↓ │
│ 4. Server checks: Is 'default' user password set? │
│ ├── Yes: Returns 516 AUTHENTICATION_FAILED │
│ └── No: Processes query and returns results │
│ ↓ │
│ 5. Query executes successfully! ACCESS GRANTED │
│ ↓ │
│ 6. Attacker enumerates system tables: SELECT * FROM system.* │
│ ↓ │
│ 7. Attacker extracts data, modifies tables, exfiltrates DB │
│ │
└─────────────────────────────────────────────────────────────────┘

<!-- VULNERABLE: Default user without password -->
<yandex>
 <users>
 <default>
 <!-- No password element = empty password = NO AUTHENTICATION -->
 <networks>
 <ip>::/0</ip> <!-- Allows any IP -->
 </networks>
 <profile>default</profile>
 <quota>default</quota>
 </default>
 </users>
</yandex>

# What Nuclei CAN do:
id: clickhouse-detection
requests:
 - method: GET
 path:
 - "{{BaseURL}}/ping"
 matchers:
 - type: word
 words:
 - "Ok."
 - type: header
 headers:
 - "X-ClickHouse-Server"

┌──────────────────────────────────────────────────────────────────┐
│ CERT-X-GEN DETECTION FLOW │
├──────────────────────────────────────────────────────────────────┤
│ │
│ Scanner ──────► Target: GET /ping (service detection) │
│ │ │
│ ▼ │
│ Scanner: Parse response headers for ClickHouse indicators │
│ │ │
│ ▼ │
│ Scanner ──────► Target: /?query=SELECT 1 (no auth) │
│ │ │
│ ▼ │
│ Response Analysis: │
│ ├── HTTP 200 + "1" ───► Continue to system table test │
│ ├── HTTP 516 ───► Authentication enabled (not vulnerable) │
│ └── HTTP 403 ───► Access denied (not vulnerable) │
│ │ │
│ ▼ │
│ Scanner ──────► Target: /?query=SELECT count() FROM system.tables│
│ │ │
│ ▼ │
│ Response 200 + table count? ───► CRITICAL: Full DB access! │
│ Response 4xx/5xx? ──────────► MEDIUM: Limited access │
│ │
└──────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────┐
│ UNAUTHENTICATED QUERY TEST │
├─────────────────────────────────────────────────────────────────┤
│ │
│ Request: GET http://target:8123/?query=SELECT 1 │
│ │
│ ┌─ VULNERABLE RESPONSE ────────────────────────────────────┐ │
│ │ HTTP/1.1 200 OK │ │
│ │ X-ClickHouse-Server-Display-Name: clickhouse-server │ │
│ │ X-ClickHouse-Query-Id: abc-123-def-456 │ │
│ │ X-ClickHouse-Format: TabSeparated │ │
│ │ │ │
│ │ 1 ◀── Query result! │ │
│ └──────────────────────────────────────────────────────────┘ │
│ │
│ ┌─ SECURED RESPONSE ───────────────────────────────────────┐ │
│ │ HTTP/1.1 403 Forbidden │ │
│ │ X-ClickHouse-Exception-Code: 516 │ │
│ │ │ │
│ │ Code: 516. DB::Exception: default: Authentication │ │
│ │ failed: password is incorrect or there is no user │ │
│ │ with such name. (AUTHENTICATION_FAILED) │ │
│ └──────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────┘

def test_unauthenticated_query(base_url, query, timeout=5):
 """
 Test if query executes without authentication.
 Returns: (success, status_code, response_data, headers)
 """
 try:
 # URL encode the query
 encoded_query = quote(query)
 test_url = f"{base_url}/?query={encoded_query}"
 
 req = Request(test_url)
 req.add_header('User-Agent', 'Mozilla/5.0')
 req.add_header('Accept', '*/*')
 
 ctx = create_ssl_context()
 with urlopen(req, timeout=timeout, context=ctx) as response:
 status_code = response.getcode()
 headers = dict(response.info())
 response_data = response.read().decode('utf-8', errors='ignore')
 
 # Check for successful query execution
 if status_code == 200 and response_data:
 return True, status_code, response_data, headers
 
 except HTTPError as e:
 status_code = e.code
 response_data = e.read().decode('utf-8', errors='ignore')
 
 # Check for authentication errors (indicates security is enabled)
 if status_code == 401:
 return False, status_code, response_data, {}
 elif status_code == 516 or 'Authentication failed' in response_data:
 return False, status_code, response_data, {}
 
 return False, None, None, {}

def detect_clickhouse_service(base_url, timeout=5):
 """
 Detect if ClickHouse HTTP interface is present.
 Returns: (is_clickhouse, version, server_name)
 """
 try:
 # Ping endpoint
 req = Request(f"{base_url}/ping")
 req.add_header('User-Agent', 'Mozilla/5.0')
 
 ctx = create_ssl_context()
 with urlopen(req, timeout=timeout, context=ctx) as response:
 headers = dict(response.info())
 response_data = response.read().decode('utf-8', errors='ignore')
 
 # Check for ClickHouse indicators
 server_header = headers.get('Server', '')
 display_name = headers.get('X-ClickHouse-Server-Display-Name', '')
 
 if 'ClickHouse' in server_header or 'X-ClickHouse' in str(headers) \
 or response_data.strip() == 'Ok.':
 # Extract version
 version_match = re.search(r'ClickHouse/([0-9.]+)', server_header)
 version = version_match.group(1) if version_match else "unknown"
 
 return True, version, display_name or "ClickHouse"
 except:
 pass
 
 return False, None, None

def test_data_access(base_url, timeout=5):
 """
 Test if we can access system tables (conclusive proof of bypass).
 Returns: (success, table_count, error_message)
 """
 try:
 # Query system.tables to enumerate accessible tables
 query = "SELECT count() FROM system.tables"
 encoded_query = quote(query)
 test_url = f"{base_url}/?query={encoded_query}"
 
 req = Request(test_url)
 ctx = create_ssl_context()
 
 with urlopen(req, timeout=timeout, context=ctx) as response:
 if response.getcode() == 200:
 data = response.read().decode('utf-8', errors='ignore')
 table_count = int(data.strip())
 return True, table_count, None
 except HTTPError as e:
 error_msg = e.read().decode('utf-8', errors='ignore')
 return False, 0, error_msg
 
 return False, 0, None

# Scan a target for ClickHouse authentication bypass
cxg scan --scope clickhouse.example.com --ports 8123 --templates clickhouse-auth-bypass.py

# Scan with default HTTP port
cxg scan --scope clickhouse.example.com --templates clickhouse-auth-bypass.py

# Scan HTTPS ClickHouse (port 8443)
cxg scan --scope clickhouse.example.com --ports 8443 --templates clickhouse-auth-bypass.py

# JSON output
cxg scan --scope clickhouse.example.com --templates clickhouse-auth-bypass.py --output-format json

# Run the Python template directly
export CERT_X_GEN_TARGET_HOST=clickhouse.example.com
export CERT_X_GEN_TARGET_PORT=8123
python3 clickhouse-auth-bypass.py

{
 "findings": [{
 "template_id": "clickhouse-auth-bypass",
 "template_name": "ClickHouse Authentication Bypass",
 "severity": "high",
 "cvss_score": 8.6,
 "host": "118.25.14.157",
 "port": 8123,
 "protocol": "http",
 "description": "ClickHouse HTTP interface on 118.25.14.157:8123 allows unauthenticated access. Queries execute without credentials, exposing database operations. System tables accessible - 132 tables enumerated.",
 "evidence": {
 "url": "http://118.25.14.157:8123",
 "clickhouse_version": "unknown",
 "server_name": "81f5ec6295ae",
 "test_query": "SELECT 1",
 "test_response": "1\\n",
 "status_code": 200,
 "system_table_access": true,
 "accessible_tables": 132
 }
 }]
}

{
 "findings": []
}

Server: 81f5ec6295ae (Docker container ID)
Unauthenticated Query: SELECT 1 → Returned: 1
System Tables Query: SELECT count() FROM system.tables → Returned: 132
Status: VULNERABLE - Full database access without credentials

<!-- SECURE: Configure password for default user -->
<yandex>
 <users>
 <default>
 <!-- Set a strong password -->
 <password>YourStrongPasswordHere</password>
 
 <!-- Or use SHA256 hash -->
 <password_sha256_hex>hash_of_your_password</password_sha256_hex>
 
 <!-- Restrict to localhost only -->
 <networks>
 <ip>127.0.0.1/8</ip>
 <ip>::1</ip>
 </networks>
 
 <profile>default</profile>
 <quota>default</quota>
 </default>
 </users>
</yandex>

<users>
 <!-- Application user with limited permissions -->
 <app_user>
 <password_sha256_hex>...</password_sha256_hex>
 <networks>
 <ip>10.0.0.0/8</ip> <!-- Private network only -->
 </networks>
 <profile>readonly</profile>
 <quota>default</quota>
 <!-- Read-only access -->
 <access_management>0</access_management>
 </app_user>
</users>

<!-- SECURE: Listen only on specific interfaces -->
<yandex>
 <!-- Bind to localhost only -->
 <listen_host>127.0.0.1</listen_host>
 <listen_host>::1</listen_host>
 
 <!-- Or bind to specific private IP -->
 <!-- <listen_host>10.0.1.100</listen_host> -->
 
 <!-- Enable TLS for external access -->
 <https_port>8443</https_port>
</yandex>

<openSSL>
 <server>
 <certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
 <privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
 <dhParamsFile>/etc/clickhouse-server/dhparam.pem</dhParamsFile>
 <verificationMode>relaxed</verificationMode>
 <loadDefaultCAFile>true</loadDefaultCAFile>
 <cacheSessions>true</cacheSessions>
 <disableProtocols>sslv2,sslv3</disableProtocols>
 <preferServerCiphers>true</preferServerCiphers>
 </server>
</openSSL>

# Test 1: Unauthenticated query should fail
curl 'http://localhost:8123/?query=SELECT 1'
# Expected: Code: 516. DB::Exception: Authentication failed

# Test 2: With credentials should succeed
curl -H 'X-ClickHouse-User: myuser' \
 -H 'X-ClickHouse-Key: mypassword' \
 'http://localhost:8123/?query=SELECT 1'
# Expected: 1

# Test 3: External access should be blocked
curl 'http://external-ip:8123/ping'
# Expected: Connection refused or timeout

# Add custom SQL queries to test
custom_queries = [
 "SELECT version()",
 "SELECT name FROM system.databases",
 "SELECT name, engine FROM system.tables LIMIT 5",
]

for query in custom_queries:
 success, status, data, headers = test_unauthenticated_query(base_url, query)
 if success:
 findings.append({"query": query, "result": data})

def test_with_headers(base_url, user, password):
 """Test with X-ClickHouse-User and X-ClickHouse-Key headers."""
 req = Request(f"{base_url}/?query=SELECT 1")
 req.add_header('X-ClickHouse-User', user)
 req.add_header('X-ClickHouse-Key', password)
 # ...

# Test multiple common ClickHouse ports
ports = [8123, 8443, 9000, 9009, 9363]
for port in ports:
 finding = detect_auth_bypass(host, port)
 if finding:
 findings.append(finding)

# GitHub Actions example
- name: ClickHouse Security Scan
 run: |
 cxg scan \
 --scope ${{ secrets.CLICKHOUSE_HOST }} \
 --ports 8123 \
 --templates clickhouse-auth-bypass.py \
 --output-format sarif \
 --output clickhouse-results.sarif