Database Configuration & Permissions

This guide covers the database-side configuration required for UptimeDock to monitor your ClickHouse instance effectively. You'll learn about required permissions, system tables we access, and how to ensure query logging is enabled.

Overview

UptimeDock collects metrics by periodically executing SELECT queries against ClickHouse system tables. To function properly, the database user you provide must have read access to specific system tables, and query logging should be enabled for performance monitoring.

Required Permissions

The monitoring user needs SELECT permission on the following system tables. These tables provide essential metrics about your database health, performance, and resource usage.

System Tables

Table	Purpose
`system.disks`	Disk space monitoring - tracks storage usage and available space
`system.asynchronous_metrics`	Background metrics - memory usage, cache statistics, uptime
`system.metrics`	Current server metrics - active connections, running queries
`system.settings`	Server configuration - validates settings and limits
`system.query_log`	Query performance - execution times, errors, resource consumption
`system.parts`	Table parts - data distribution and merge status
`system.mutations`	Mutation tracking - ALTER/UPDATE/DELETE operation status
`system.detached_parts`	Detached parts - identifies orphaned or problematic data parts

Read-Only Access

UptimeDock only requires SELECT permissions. We never modify your data or execute any write operations. Using a read-only user is a security best practice.

Creating a Monitoring User

We recommend creating a dedicated user for monitoring. Here's how to create a read-only user with access to the required system tables:

-- Create a dedicated monitoring user
CREATE USER uptimedock_monitor IDENTIFIED BY 'your_secure_password';

-- Grant SELECT access to required system tables
GRANT SELECT ON system.disks TO uptimedock_monitor;
GRANT SELECT ON system.asynchronous_metrics TO uptimedock_monitor;
GRANT SELECT ON system.metrics TO uptimedock_monitor;
GRANT SELECT ON system.settings TO uptimedock_monitor;
GRANT SELECT ON system.query_log TO uptimedock_monitor;
GRANT SELECT ON system.parts TO uptimedock_monitor;
GRANT SELECT ON system.mutations TO uptimedock_monitor;
GRANT SELECT ON system.detached_parts TO uptimedock_monitor;

Alternative: Grant access to all system tables

If you prefer a simpler setup and don't mind granting access to all system tables:

-- Grant SELECT access to all system tables
GRANT SELECT ON system.* TO uptimedock_monitor;

Use Strong Passwords

Always use a strong, unique password for your monitoring user. Avoid using the same credentials as your application or admin users.

Query Logging Configuration

Query logging is essential for performance monitoring. UptimeDock uses the system.query_log table to analyze query performance, identify slow queries, and track errors.

By default, ClickHouse enables query logging. However, if logging has been disabled in your configuration, you'll need to re-enable it for full monitoring capabilities.

Check Logging Status

Run the following query to check if query logging is enabled:

SELECT name, value 
FROM system.settings 
WHERE name = 'log_queries';

If log_queries is 1, logging is enabled. If it's 0, you need to enable it.

You can also check if the query_log table has recent data:

SELECT count(*) 
FROM system.query_log 
WHERE event_time > now() - INTERVAL 1 HOUR;

Enable Query Logging

If query logging is disabled, you can enable it by modifying your ClickHouse configuration.

Option 1: Edit config.xml

Add or modify the following in your config.xml or config.d/ directory:

<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>
</clickhouse>

Option 2: Edit users.xml

Ensure log_queries is enabled for your users in users.xml:

<clickhouse>
    <profiles>
        <default>
            <log_queries>1</log_queries>
        </default>
    </profiles>
</clickhouse>

After making changes:

Save your configuration file
Restart ClickHouse server: sudo systemctl restart clickhouse-server
Verify logging is working by running a test query and checking system.query_log

Configuration File Locations

Common configuration file locations:

/etc/clickhouse-server/config.xml
/etc/clickhouse-server/config.d/*.xml
/etc/clickhouse-server/users.xml
/etc/clickhouse-server/users.d/*.xml

ClickHouse Cloud

If you're using ClickHouse Cloud, query logging is enabled by default and cannot be disabled. No additional configuration is required.

ClickHouse Cloud

ClickHouse Cloud users don't need to configure query logging. It's automatically enabled and managed by the platform.

Verify Configuration

After setting up permissions and ensuring logging is enabled, verify everything is configured correctly:

-- Test 1: Check user permissions (run as your monitoring user)
SELECT 'disks' AS table_name, count(*) FROM system.disks
UNION ALL SELECT 'asynchronous_metrics', count(*) FROM system.asynchronous_metrics
UNION ALL SELECT 'metrics', count(*) FROM system.metrics
UNION ALL SELECT 'settings', count(*) FROM system.settings
UNION ALL SELECT 'query_log', count(*) FROM system.query_log
UNION ALL SELECT 'parts', count(*) FROM system.parts
UNION ALL SELECT 'mutations', count(*) FROM system.mutations
UNION ALL SELECT 'detached_parts', count(*) FROM system.detached_parts;

If all queries return results without permission errors, your monitoring user is properly configured.

Once verified, you can proceed with the Quick Setup Guide to create your ClickHouse monitoring check in UptimeDock.