ClickHouse Audit Log Configuration and Usage (Compliance Checks)

Background

ClickHouse is an open-source columnar database management system designed for processing large datasets. When using ClickHouse, it is essential to ensure that Auditor log settings and usage comply with specifications.

Purpose of Audit Logs

Auditor logs are primarily used to record ClickHouse operational activities, including detailed records of CRUD (create, read, update, delete) operations and system change logs. This information is highly useful for various analyses, including system maintenance and bug reports.

Audit Log Configuration

1. Log Level Settings

The log level can be controlled by configuring log_level. Generally, all operational activities need to be recorded, including each CRUD operation and system changes.

SET GLOBAL log_level = 'DEBUG';

2. Log Storage Location

Log information will be written to ClickHouse's internal log cache and then periodically transferred from the cache to the file system. The location for log storage can be specified by configuring log_dir.

SET GLOBAL log_dir = '/path/to/log';

3. Log Format Settings

The log output format can be controlled by configuring log_format. For example, you can set the length of each log line or define a custom log format.

SET GLOBAL log_format = '%t %m %u %p %q';

Usage of Audit Logs

1. Log Level Analysis

You can check the log_level to understand the system's current log level. Since all operational activities typically need to be recorded, setting it to the highest level is recommended.

SELECT * FROM system.global_variables WHERE variable='log_level';

2. Log Search and Replication

ClickHouse provides log search functionality, which can be implemented using the clickhouse-client command.

clickhouse-client -h localhost --user root --password pass
SELECT * FROM auditor;

3. Log Analysis Tools

Third-party log analysis tools can be used, such as the dbclick software provided internally by ClickHouse.

Compliance Checks

1. Log Level Compliance Check

It is necessary to verify whether the current log level complies with specifications.

SELECT * FROM system.global_variables WHERE variable='log_level';

2. Log Storage Location Compliance Check

It is necessary to verify whether the log storage location complies with specifications.

SELECT * FROM system.global_variables WHERE variable='log_dir';

3. Log Format Compliance Check

It is necessary to verify whether the log format complies with specifications.

SELECT * FROM system.global_variables WHERE variable='log_format';

Conclusion

In ClickHouse applications, setting up and using Auditor logs is crucial for monitoring system behavior and maintaining database health. By following the steps outlined above, you can ensure that Auditor log configuration and usage comply with specifications.