ClickHouse Security Basics

Overview

Security best practices for ClickHouse API keys, tokens, and access control.

Prerequisites

• ClickHouse SDK installed
• Understanding of environment variables
• Access to ClickHouse dashboard

Instructions

Step 1: Configure Environment Variables

# .env (NEVER commit to git)
CLICKHOUSE_API_KEY=sk_live_***
CLICKHOUSE_SECRET=***

# .gitignore
.env
.env.local
.env.*.local

Step 2: Implement Secret Rotation

# 1. Generate new key in ClickHouse dashboard
# 2. Update environment variable
export CLICKHOUSE_API_KEY="new_key_here"

# 3. Verify new key works
curl -H "Authorization: Bearer ${CLICKHOUSE_API_KEY}" \
  https://api.clickhouse.com/health

# 4. Revoke old key in dashboard

Step 3: Apply Least Privilege

Output

• Secure API key storage
• Environment-specific access controls
• Audit logging enabled

Error Handling

Examples

Service Account Pattern

const clients = {
  reader: new ClickHouseClient({
    apiKey: process.env.CLICKHOUSE_READ_KEY,
  }),
  writer: new ClickHouseClient({
    apiKey: process.env.CLICKHOUSE_WRITE_KEY,
  }),
};

Webhook Signature Verification

import crypto from 'crypto';

function verifyWebhookSignature(
  payload: string, signature: string, secret: string
): boolean {
  const expected = crypto.createHmac('sha256', secret).update(payload).digest('hex');
  return crypto.timingSafeEqual(Buffer.from(signature), Buffer.from(expected));
}

Security Checklist

• [ ] API keys in environment variables
• [ ] .env files in .gitignore
• [ ] Different keys for dev/staging/prod
• [ ] Minimal scopes per environment
• [ ] Webhook signatures validated
• [ ] Audit logging enabled

Audit Logging

interface AuditEntry {
  timestamp: Date;
  action: string;
  userId: string;
  resource: string;
  result: 'success' | 'failure';
  metadata?: Record<string, any>;
}

async function auditLog(entry: Omit<AuditEntry, 'timestamp'>): Promise<void> {
  const log: AuditEntry = { ...entry, timestamp: new Date() };

  // Log to ClickHouse analytics
  await clickhouseClient.track('audit', log);

  // Also log locally for compliance
  console.log('[AUDIT]', JSON.stringify(log));
}

// Usage
await auditLog({
  action: 'clickhouse.api.call',
  userId: currentUser.id,
  resource: '/v1/resource',
  result: 'success',
});

Resources

• ClickHouse Security Guide
• ClickHouse API Scopes

Next Steps

For production deployment, see clickhouse-prod-checklist.