Skip to main content
Version: 5.3

Role Model Propagation to ClickHouse

ClickHouse Configuration​

Creating a User for Role Management​

Preliminary configuration requires a ClickHouse user with permissions to create and manage roles. This can be done by executing queries under an administrator account or by creating a dedicated user.

Example​

Creating the user sm_admin with permissions to manage roles in ClickHouse.

CREATE USER sm_admin IDENTIFIED BY '<password>'
CREATE ROLE sm_role_manager;
GRANT CREATE ROLE, DROP ROLE, ROLE ADMIN ON *.* TO sm_role_manager;
GRANT SELECT, INSERT, ALTER, TRUNCATE ON *.* TO sm_role_manager WITH GRANT OPTION;
GRANT sm_role_manager TO sm_admin;

Creating the Main User​

For role model propagation, all queries must be executed under a single user. It is necessary to create a user with no default roles, for example sm_user.

CREATE USER sm_user IDENTIFIED BY '<password>';
SET DEFAULT ROLE NONE TO sm_user;

Creating a Role​

Before use, a role must be created, assigned the necessary permissions, and granted to the main user. All propagated roles must be granted to this user.

Example​

It is required to create the role analyst with the ability to query data from the default.auditd table.

CREATE ROLE analyst;
GRANT SELECT ON default.auditd TO analyst;
GRANT analyst TO sm_user;

OpenSearch Configuration​

Configuring the Connection​

To propagate the role model, it is necessary to create a Search Anywhere connection of type ClickHouse and specify the user to whom the roles are granted. In this example, this is sm_user. For more details on configuring connections, see the corresponding article.

Configuring Roles​

For correct mapping of roles between OpenSearch and ClickHouse, it is necessary to create a dummy role without permissions named clk_<role_name> and assign it to the required users. For more details on configuration, see the correspondingй article.

Example​

To grant the ClickHouse role analyst to an internal user, the role clk_analyst must be created and assigned to the user. After this, when the user utilizes the previously created connection, the role will be automatically propagated during queries.