> ## Documentation Index
> Fetch the complete documentation index at: https://docs.opal.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Clickhouse

> Learn how to connect Opal to Clickhouse to manage access.

> [!NOTE]
>
> **User matching**: Opal matches ClickHouse users to Opal accounts by email. If
> the user's email in Clickhouse does not match the user's email in Opal, the
> account will appear as unlinked and can be manually associated through
> Clickhouse.

With Opal's ClickHouse integration you can manage **two kinds of access** from one app:

* **SQL access** — ClickHouse databases, tables, and SQL roles (`GRANT` / `REVOKE` on the service)
* **Cloud console access** (optional) — ClickHouse Cloud organization members, invitations, and console roles (Admin, Developer, and custom roles)

You can keep using Opal for SQL-only management. Add a Cloud API key if you also want joiner/mover/leaver for the Cloud console.

With the integration:

* **Users** can request access to ClickHouse roles, databases, tables, and (when Cloud is configured) console roles, with time-limited grants that expire automatically
* **Admins** can import ClickHouse resources into Opal's catalog, configure reviewers, and see a full audit trail of who has access to what
* **Admins** can revoke access manually at any time — SQL changes apply in ClickHouse, and console changes apply in ClickHouse Cloud

## Supported resources

| **Resource** | **Read** | **Grant and revoke access** | Notes |
| - | - | - | - |
| ClickHouse Database | ✔️ | ✔️ | SQL privileges such as `SELECT` and `INSERT` |
| ClickHouse Table | ✔️ | ✔️ | Table-level SQL privileges. Users who have access on the parent database also appear on the table |
| ClickHouse Role | ✔️ | ✔️ | SQL roles from `system.roles` |
| ClickHouse Console Role | ✔️ | ✔️ | Cloud console roles. Only synced when Cloud API credentials are set |
| ClickHouse User | ✔️ | | SQL users are discovered. Opal does not create SQL logins. With Cloud credentials, Opal can invite and remove Cloud members |

> [!NOTE]
>
> Console roles and SQL roles are different objects, even if they share a name.
> Granting a console role does not run `GRANT` in ClickHouse SQL, and granting a
> SQL role does not change Cloud console permissions.

## Requirements

To connect ClickHouse to Opal, you must first:

* Be an **Opal Admin**
* Have access to a running ClickHouse instance in ClickHouse Cloud
* Have credentials for a [ClickHouse admin](https://clickhouse.com/docs/cloud/security/common-access-management-queries#admin-user) user able to create a dedicated SQL service user and grant permissions
* To manage Cloud console members and roles: a ClickHouse Cloud **Admin** [API key](https://clickhouse.com/docs/cloud/manage/openapi)

## 1. Create a service account in ClickHouse

Opal requires a dedicated SQL service account with read access to system tables and permission to manage grants. Do **not** use the Cloud `default` user — ClickHouse Cloud does not allow grants to that user.

In your ClickHouse SQL Console (or via `curl`), run:

```sql theme={null}
  -- Create the service user
  CREATE USER IF NOT EXISTS opal_service
      IDENTIFIED WITH sha256_password BY '<strong-password>';

  -- Grant read access to system tables
  GRANT SELECT ON system.users TO opal_service;
  GRANT SELECT ON system.roles TO opal_service;
  GRANT SELECT ON system.role_grants TO opal_service;
  GRANT SELECT ON system.grants TO opal_service;
  GRANT SELECT ON system.databases TO opal_service;
  GRANT SELECT ON system.tables TO opal_service;

  -- Grant access management permissions
  GRANT CURRENT GRANTS(ACCESS MANAGEMENT ON *.*) TO opal_service;
```

> [!NOTE]
>
> You must use `GRANT CURRENT GRANTS(ACCESS MANAGEMENT ON *.*)` instead of
> `GRANT ACCESS MANAGEMENT`. This is because the Cloud `default` user doesn't
> hold the full `ACCESS MANAGEMENT` bundle, so `CURRENT GRANTS` passes only the
> subset needed by Opal.

## 2. Add Clickhouse to Opal

In Opal, go to **Inventory > + App**, then select **ClickHouse**.

Fill in the connection form using the following details.

| Field | Required | Where to find it | Example |
| - | - | - | - |
| **Hostname** | Yes | ClickHouse Cloud → your service → Connect tab | `abc123.us-east-1.aws.clickhouse.cloud` |
| **Port** | Yes | Always `8443` on ClickHouse Cloud (HTTPS) | `8443` |
| **Use TLS** | Yes | Always enabled on ClickHouse Cloud | - |
| **Username** | Yes | The service account created in step 1. Not `default` | `opal_service` |
| **Password** | Yes | The password set for the service account in step 1 | |
| **Cloud organization ID** | For console management | ClickHouse Cloud → **Settings** → **Organization settings** | UUID |
| **Cloud API key ID** | For console management | ClickHouse Cloud → **Settings** → **API keys** | |
| **Cloud API key secret** | For console management | Shown once when you create the API key | |

Leave the three Cloud fields empty to manage SQL access only. If you fill in any of them, all three are required.

Upon clicking **Create**, Opal validates the SQL connection. If Cloud fields are set, Opal also lists Cloud members and console roles. If validation fails, verify hostname, port, and credentials, that your IP is allowed in ClickHouse Cloud network settings, and that the API key can administer the organization.

## 3. Optional: enable Cloud console management

To invite people into the ClickHouse Cloud organization, remove members, and grant console roles through Opal:

1. In ClickHouse Cloud, open **Settings** (organization) and select **API keys**. Create a key with **Admin** access. Your **organization ID** is under **Organization settings** in the same sidebar.

   ![ClickHouse Cloud Settings sidebar with API keys selected](https://mintcdn.com/opalsecurity/3B5TAsomturNSivb/images/docs/clickhouse-cloud-api-keys.png?fit=max\&auto=format\&n=3B5TAsomturNSivb\&q=85\&s=e88d15b9d27dd554b3b28740b3b8b7ef)

2. Copy the **key ID**, **key secret**, and your **organization ID**.

3. Enter all three on the ClickHouse app in Opal (on create, or later in the app's credentials).

After the next sync, Opal imports **console roles** as resources. Granting a console role in Opal assigns that Cloud role to the person. Adding a user to the ClickHouse app invites them to the Cloud organization; removing them cancels a pending invite or removes the Cloud member.

> [!WARNING]
>
> If you already provision ClickHouse Cloud members with SCIM (Okta or Microsoft Entra), do not also push the same people from Opal. Opal and SCIM can overwrite each other. Pick one writer for Cloud membership.

Opal does **not** create ClickHouse SQL users. A Cloud invite lets someone sign in to the Cloud console; it does not create a `CREATE USER` login for `clickhouse-client`.

## 4. Import resources

After the connection is created, Opal will run an initial sync to discover your ClickHouse databases, tables, SQL roles, and users. If Cloud credentials are set, it also discovers console roles and Cloud members (including pending invitations). Go to **Assets** to see everything that was discovered.

Database resources in Opal list their tables on the **Assets** tab. On a table, **User Access** includes people granted the table directly and people who inherited access from the parent database (`GRANT … ON database.*`).

To make a resource or role requestable, import them and configure reviewers and access policies as needed.

> [!NOTE]
>
> Only imported resources with “Allow Requests” toggled ON will appear in Opal's **Request Access** catalog.

## User provisioning

> [!NOTE]
>
> User provisioning is only available when Cloud console management is
> enabled. Follow [step 3](#3-optional-enable-cloud-console-management) to add
> a Cloud API key. Opal provisions ClickHouse Cloud organization members, not
> SQL users.

Opal can automatically provision and deprovision users in ClickHouse Cloud.

When provisioning is enabled, users who aren't already members of your ClickHouse Cloud organization are invited when they are granted access to a ClickHouse resource. Invitations don't include console roles, which are granted separately. Deprovisioning removes users from the ClickHouse Cloud organization, or cancels their pending invitation, when you deprovision them manually, revoke their account in an access review, or when they're deprovisioned in your IdP or HRIS.

This is configurable in **Setup > Edit > Provision ClickHouse users** and **Deprovision ClickHouse users**. For more details, see [User provisioning](https://docs.opal.dev/docs/user-provisioning.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.