Connect to ClickHouse with OAuth (Beta)

If you want to authenticate to ClickHouse from Sigma using connection-level OAuth, follow the steps in this document after you have started configuring a ClickHouse connection, as well as created a Sigma OAuth application.

For more details about using OAuth with Sigma, see About using OAuth with Sigma.

How ClickHouse OAuth works

Sigma uses passthrough OAuth for ClickHouse. Each user signs in through your identity provider (IdP), and Sigma passes that user’s IdP-issued JSON Web Token (JWT) directly to ClickHouse as a bearer token to run queries. Sigma does not mint or exchange tokens, and it does not store user database passwords for the connection.

Because the user’s own token is sent to ClickHouse, ClickHouse rather than Sigma enforces access based on the identity and roles or grants associated with that token. The connection uses Transport Layer Security (TLS), which is required and cannot be disabled for a ClickHouse OAuth connection.

Prerequisites

Before beginning to configure OAuth for your ClickHouse connection, do the following in Sigma:

• If you are creating a new connection, follow steps 1-3 in Create a ClickHouse connection to open the Add new connection page and specify the connection name and type. When you reach Authentication in the Connection credentials section, select OAuth, then return to this document to complete the OAuth configuration.
• Complete the procedure in Configure a Sigma OAuth application. Information from this application (client ID, client secret, metadata URI, and configured scopes) is required throughout the OAuth setup process.

Additionally, work with your ClickHouse administrator to ensure the following are configured in ClickHouse and your IdP:

• The IdP is trusted by ClickHouse. ClickHouse must validate tokens from your IdP (issuer, audience, and signing keys). For ClickHouse Cloud, this is set up with ClickHouse for your service. See the ClickHouse documentation on JWT authentication and external authenticators.
• The token carries a roles or grants claim. Without a claim conveying the user’s roles or grants, an authenticated user has no privileges. By default, ClickHouse reads roles from clickhouse:roles and grants from clickhouse:grants, so configure your IdP to emit the claim (for example, mapping group membership to a clickhouse_roles array). See the ClickHouse documentation on Access control and account management.
• ClickHouse and your IdP must use the same claim name. Some IdPs can’t emit a name containing a colon (for example, Okta), so they can’t produce the default names. Configure ClickHouse to read roles or grants from a claim name your IdP can emit, such as clickhouse_roles. See the ClickHouse documentation on JWT authentication and external authenticators.
• The referenced roles exist with the right grants. A ClickHouse role with the appropriate SHOW, SELECT, and any write privileges on the target databases must exist, be referenced by the token’s roles claim, and be within the service’s permission limit. See the ClickHouse documentation on Access control and account management and Manage database users for ClickHouse Cloud.

Limitations

For a ClickHouse connection configured to use OAuth, the following limitations apply:

• You cannot reuse an organization-level OAuth configuration for a ClickHouse connection. You must configure OAuth separately for the connection using an external IdP. See Configure connection-level OAuth.
• Service accounts are not supported for ClickHouse OAuth connections in this release.
• Scheduled operations, such as scheduled exports and materializations, run using the schedule owner’s OAuth credentials and require a valid refresh token. See the note about the offline_access scope in Connect to ClickHouse with connection-level OAuth.

Requirements

• You must be assigned the Admin account type or an account type with the Manage connections feature permission enabled.

Connect to ClickHouse with connection-level OAuth

After completing all prerequisites and selecting OAuth for Authentication while configuring your ClickHouse connection, do the following:

1. In the Connection credentials section, confirm the Enable TLS toggle is on. TLS is required for ClickHouse OAuth and cannot be turned off while OAuth is selected.

2. In the OAuth features section, for OAuth provider, select your external IdP, such as Okta, Microsoft Entra ID, Auth0, PingIdentity, or ADFS.

3. For Scopes, enter any additional scopes that define what the OAuth token can access.

   • The default scopes openid, profile, and email are required.
   • The default scope offline_access is strongly recommended but not required. Without this scope, users must sign in again every time their access token expires. Scheduled operations also fail if they run longer than the access token lifetime configured in your IdP.

4. In the Metadata URL field, enter the OAuth metadata URI from your IdP, obtained when you configured your Sigma OAuth application.

5. In the Redirect URI field, select Copy to clipboard () and store the value somewhere. You need this value to complete the OAuth configuration in your IdP.

6. In the Client ID field, enter the client ID from your OAuth application.

7. Provide the relevant authentication details to match what you configured in your OAuth application:

   • If you configured a client secret in your OAuth application, for Client secret, enter the client secret. After you enter and save this value, Sigma does not display it.
   • If you also configured Proof Key for Code Exchange (PKCE) in your OAuth application, select the checkbox for Require PKCE.
   • If you configured your OAuth application to authenticate with a public key and private key pair, or a JWT bearer token, select the checkbox for Use JWT bearer tokens. You do not need to provide a client secret.

8. Select Create to finish creating the connection.

Related resources

• Connect to ClickHouse
• About using OAuth with Sigma
• Configure a Sigma OAuth application