Mapping Roles to ClickHouse

ClickHouse Configuration

Creating a User for Role Management

Setup requires a ClickHouse user with permissions to create and manage roles. Run the queries as an administrator or create a separate user with the required permissions.

Example

Create the sm_admin user with permissions to manage ClickHouse roles:

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 Primary User

For role model translation, all queries must run as a single user. Create a user without default roles, for example, sm_user.

Do not grant this user direct table permissions: data access must be provided by the assigned roles.

Role Assignment Failure

If the Search Anywhere Framework user has no roles with the clk_ prefix, the SET ROLE command is not sent. A SET ROLE error is logged on the node and does not by itself stop the main query. The query continues with the effective permissions of the ClickHouse service account. Direct grants and default roles on this account may expose data regardless of the expected role mapping.

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

Creating a Role

Before use, create a role, grant required permissions, and assign it to the primary user. All translated roles must be assigned to this user.

Example

Create the analyst role with permission 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

Connection Configuration

To translate the role model, create a Search Anywhere connection of type ClickHouse and specify the user to which roles are assigned. In this example, it is sm_user. See connection setup in the related article.

Role Configuration

To map roles between OpenSearch and ClickHouse, create a placeholder role without permissions named clk_<role_name> and assign it to the required users. For details, see the role configuration article.

Example

To assign the ClickHouse analyst role to an internal user, create the clk_analyst role and assign it to that user. The role is then passed automatically in queries made through the previously created connection.