ClickHouse’s RBAC isn’t about traditional role hierarchies; it’s a flat, permission-based system where users are directly granted privileges on specific objects.
Let’s watch a real-time scenario. Imagine a user, tenant_a_analyst, who needs to query data in tenant_a_db.
-- First, create the database and a table for tenant_a
CREATE DATABASE tenant_a_db;
CREATE TABLE tenant_a_db.sales (
event_date Date,
amount Decimal(10, 2),
region String
) ENGINE = MergeTree()
ORDER BY event_date;
-- Populate with some data
INSERT INTO tenant_a_db.sales VALUES ('2023-10-26', 150.75, 'North');
INSERT INTO tenant_a_db.sales VALUES ('2023-10-26', 200.00, 'South');
-- Now, create a user for tenant_a
CREATE USER tenant_a_analyst IDENTIFIED WITH sha256_password BY 'secure_password_a';
-- Grant the user SELECT privilege on the specific table
GRANT SELECT ON tenant_a_db.sales TO tenant_a_analyst;
-- Connect as the tenant_a_analyst and query
-- (In a real client, you'd use: clickhouse-client --user tenant_a_analyst --password secure_password_a)
-- clickhouse-client --user tenant_a_analyst --password secure_password_a -q "SELECT * FROM tenant_a_db.sales WHERE region = 'North';"
If tenant_a_analyst tries to query tenant_b_db.orders (which doesn’t exist yet, but you get the idea), they’ll get a permission denied error. If they try to INSERT into tenant_a_db.sales, they’ll also get denied. This granular control is the core of RBAC in ClickHouse.
The problem this solves is isolating data and operations for different tenants or user groups within a single ClickHouse cluster. Without it, every user would see and potentially modify every piece of data, which is a non-starter for any multi-tenant application. ClickHouse’s RBAC allows you to define exactly who can do what, on which specific database and table.
Internally, ClickHouse manages these grants in system tables, primarily system.grants. When a user attempts an operation, the server checks system.grants to see if the user has the necessary privilege for the requested object. This check happens for every query.
The exact levers you control are:
- Users:
CREATE USER,ALTER USER,DROP USER. - Privileges:
SELECT,INSERT,ALTER,CREATE,DROP,TRUNCATE,OPTIMIZE,KILL QUERY,ACCESS MANAGEMENT,SYSTEM,RELOAD,CREATE TABLE,CREATE DATABASE,CREATE DICTIONARY,CREATE VIEW,CREATE FUNCTION,CREATE USER,CREATE ROLE,CREATE QUOTA,CREATE ROW POLICY,CREATE DIALECT. - Objects: Databases (
ON database_name.*), Tables (ON database_name.table_name), Views, Dictionaries, Functions. - Grants:
GRANT privilege ON object TO user_or_role. - Revokes:
REVOKE privilege ON object FROM user_or_role. - Roles:
CREATE ROLE,GRANT role TO user. Roles act as containers for privileges, simplifying management. You can grant a role to multiple users, and then grant privileges to the role.
A common pattern is to create roles for different tenant types (e.g., tenant_admin, tenant_analyst) and then assign users to these roles. For example:
CREATE ROLE tenant_a_admin;
GRANT CREATE, ALTER, DROP ON tenant_a_db.* TO tenant_a_admin;
GRANT tenant_a_admin TO tenant_a_user;
This way, if you need to change permissions for all tenant A admins, you only modify the role.
The most surprising aspect is how system-level privileges interact with object-level grants. A user with the SYSTEM privilege can bypass many object-level restrictions, allowing them to, for instance, KILL QUERY on any database or RELOAD configuration. This makes the SYSTEM privilege extremely powerful and something to guard closely, often reserved for cluster administrators rather than application users.
The next hurdle is often implementing row-level security using row policies, which allows you to filter data based on the user executing the query.