Secure ClickHouse Access with SSO, TLS, and Auditing

Secure ClickHouse access with SSO while keeping databases off the public internet, authenticating with short-lived client certificates, and recording queries over the HTTP interface in the audit log.

Setup takes four steps: create a join token, configure the ClickHouse server, grant access through AlmaForge RBAC, and connect with native clickhouse-client or the HTTP API.

Prerequisites

ClickHouse CLI

On your computer, check whether the ClickHouse client is installed:

Terminal

clickhouse-client --versionClickHouse client version 26.9.8.3 (official build).

If it is missing, check whether the single ClickHouse binary is installed:

Terminal

clickhouse client --versionClickHouse client version 26.9.8.3 (official build).

If both commands are missing, install the Homebrew cask:

Terminal

brew install --cask clickhouse...clickhouse was successfully installed!

alma db connect uses clickhouse-client when available and otherwise runs clickhouse client. Check either command's version before continuing. For Linux, use the ClickHouse package instructions.

ClickHouse server

On your computer, open a second terminal and SSH into the database server. Replace <ssh-user> and <database-host> with your SSH account and server address:

Terminal

ssh <ssh-user>@<database-host>...[<ssh-user>@<database-host> ~]$

On the database server, check that you can run administrator commands, then check the operating system and installed database package:

Terminal

sudo -v# No output on success. A password prompt may appear first.grep -E '^(ID_LIKE|VERSION_ID)=' /etc/os-releaseID_LIKE="rhel centos fedora"VERSION_ID="9.8"rpm -q clickhouse-serverclickhouse-server-26.9.8.3-1.x86_64

sudo -v must succeed. If ClickHouse is not installed, install clickhouse-server and clickhouse-client from ClickHouse's package repository before continuing. Otherwise, the command prints its version.

Use an RPM-based Linux host with dnf for this example.

AlmaForge CLI

On your computer, check whether alma is installed:

Terminal

alma version --clientClient Version: v2026.1.0+<build>

If the command is not found, install it and repeat the check:

Terminal

curl -fL https://get.almaforge.com/install.sh | sh...==> AlmaForge installed successfullyalma version --clientClient Version: v2026.1.0+<build>

The installer supports Linux and macOS on both amd64 and arm64. See CLI installation for package-specific options.

AlmaForge Cluster

On your computer, sign in to your cluster and check your permissions.

Get your cluster's hostname from its administrator. If you do not have an AlmaForge cluster yet, complete the standalone deployment before continuing. Replace almaforge.example.com below with that hostname. Run the login command and complete the sign-in in your browser:

Terminal

alma login --proxy=almaforge.example.com:443

text

...
> Profile URL:        https://almaforge.example.com:443
  Logged in as:       [email protected]
  Cluster:            almaforge.example.com
  Roles:              admin
  ...

After signing in, check your account and roles:

Terminal

alma status

text

> Profile URL:        https://almaforge.example.com:443
  Logged in as:       [email protected]
  Cluster:            almaforge.example.com
  Roles:              admin
  ...

In the status output, check that:

• Cluster names the cluster you intend to configure.
• Logged in as shows your account.
• Roles includes admin.

If admin is missing, ask a cluster administrator to grant that role, then run alma logout and repeat the login and status commands. Continue once Roles includes admin.

Step 1. Create AlmaForge join token

On your computer, create an AlmaForge join token. The database agent uses it to join your AlmaForge cluster:

Terminal

alma create token --services=databaseToken created.  client_id:     <client-id>  client_secret: <client-secret>

You will copy client_id and client_secret into the agent configuration in Step 2. They are valid for 30 minutes. If they expire before the agent joins, create a new token and use its values.

Step 2. Configure the ClickHouse server

Install and configure the agent

The agent is almad with its Database Service enabled. It connects outward to your cluster on port 443 and carries connections between users and ClickHouse.

On the ClickHouse server, check whether almad is installed:

Terminal

almad versionAlmaForge v2026.1.0+<build>

If the command is not found, install the AlmaForge package:

Terminal

curl -fL https://get.almaforge.com/install.sh | sh...==> AlmaForge installed successfullyalmad versionAlmaForge v2026.1.0+<build>

On an RPM-based system, the installer uses the RPM package and installs the almad systemd service. It supports both amd64 and arm64. The version command must succeed before you configure the agent. For other Linux distributions, see Linux package installation.

Already running an agent on this host?

Keep its existing settings and enable ALMA_DATABASE_SERVICE=true. Preserve the other services and label selectors. The join credentials must permit the database service. The local target created below does not need a label selector.

On the ClickHouse server, open the agent configuration:

Terminal

sudoedit /etc/almaforge/almad.env# Your text editor opens.

For a new agent, enter the settings below. Replace ALMA_PROXY with your cluster's hostname and port 443. Set ALMA_CLIENT_ID and ALMA_CLIENT_SECRET to the values from Step 1:

almad.env:

/etc/almaforge/almad.env

# Database Service agent on the ClickHouse host.
# Install at /etc/almaforge/almad.env as root:root with mode 0600.
ALMA_PROXY=almaforge.example.com:443

# Printed by `alma create token --services=database`.
ALMA_CLIENT_ID=paste-client-id-here
ALMA_CLIENT_SECRET=paste-client-secret-here

ALMA_DATABASE_SERVICE=true
ALMA_SSH_SERVICE=false

# Agent labels, separate from the database's labels.
ALMA_LABELS=env=demo

Save the file and close the editor. Restrict it to root because it contains the join credentials:

Terminal

sudo chown root:root /etc/almaforge/almad.env# No output on success.sudo chmod 0600 /etc/almaforge/almad.env# No output on success.

Create the TLS certificates

The agent and ClickHouse run on the same server. Their connection uses the loopback interface (localhost, or 127.0.0.1), so this traffic stays on that server. A self-signed certificate for localhost is sufficient. You do not need a public hostname or a certificate from a public CA.

ClickHouse uses the AlmaForge database CA to verify client certificates presented by the agent. The files below establish mutual TLS between the agent and ClickHouse.

Keep running these commands on the ClickHouse server.

Download your cluster's public database CA. Replace almaforge.example.com with your cluster's hostname:

Terminal

curl -fsS -o almaforge-db-ca.pem 'https://almaforge.example.com:443/api/v1/auth/export?type=db'# No output on success.openssl x509 -in almaforge-db-ca.pem -noout# No output means the file contains a valid certificate.sudo install -d -o clickhouse -g clickhouse -m 0700 /etc/clickhouse-server/certssudo install -o clickhouse -g clickhouse -m 0644 almaforge-db-ca.pem /etc/clickhouse-server/certs/server.cas# No output on success.

Create a self-signed server certificate and private key for localhost:

Terminal

sudo openssl req -x509 -newkey rsa:2048 -nodes -days 3650 \    -subj '/CN=localhost' \    -addext 'subjectAltName=DNS:localhost,IP:127.0.0.1' \    -keyout /etc/clickhouse-server/certs/server.key -out /etc/clickhouse-server/certs/server.crt...-----sudo chown clickhouse:clickhouse /etc/clickhouse-server/certs/server.key /etc/clickhouse-server/certs/server.crt# No output on success.sudo chmod 0600 /etc/clickhouse-server/certs/server.key# No output on success.

Enable TLS in ClickHouse

Open /etc/clickhouse-server/config.d/almaforge.yaml on the server:

Terminal

sudoedit /etc/clickhouse-server/config.d/almaforge.yaml# Your text editor opens.

Set the following configuration to enable the native secure TCP port (9440) and configure certificate verification:

almaforge.yaml:

/etc/clickhouse-server/config.d/almaforge.yaml

# Serve ClickHouse over TLS and require AlmaForge client certificates.
# Place this configuration in /etc/clickhouse-server/config.d/almaforge.yaml.
tcp_port_secure: 9440 # Native protocol. Register with protocol: clickhouse.
https_port: 8443 # HTTP interface. Register with protocol: clickhouse-http.
openSSL:
  server:
    certificateFile: /etc/clickhouse-server/certs/server.crt
    privateKeyFile: /etc/clickhouse-server/certs/server.key
    caConfig: /etc/clickhouse-server/certs/server.cas # AlmaForge CA. Validates the agent's certificate.
    verificationMode: relaxed

Save the file and close the editor. Then configure the certificate user in /etc/clickhouse-server/users.d/almaforge.xml:

Terminal

sudoedit /etc/clickhouse-server/users.d/almaforge.xml# Your text editor opens.

Protect the default administrator account with a password and add the demo user mapped to the certificate common name demo. Choose a strong administrator password and store it in your password manager. Generate its SHA-256 hash without putting the password in shell history:

Terminal

bash -c 'read -rs -p "ClickHouse administrator password: " password; printf "\n" >&2; printf "%s" "$password" | sha256sum'

Copy the 64-character hash into REPLACE_WITH_ADMIN_PASSWORD_SHA256 below. The default entry replaces the packaged passwordless account. If this server already uses that account, keep its current password and use its hash so existing clients can still authenticate.

users.xml:

/etc/clickhouse-server/users.d/almaforge.xml

<!-- ClickHouse user configuration for AlmaForge certificate authentication -->
<clickhouse>
  <users>
    <default replace="replace">
      <password_sha256_hex>REPLACE_WITH_ADMIN_PASSWORD_SHA256</password_sha256_hex>
      <networks>
        <ip>127.0.0.1</ip>
        <ip>::1</ip>
      </networks>
      <profile>default</profile>
      <quota>default</quota>
      <access_management>1</access_management>
    </default>
    <demo>
      <ssl_certificates>
        <common_name>demo</common_name>
      </ssl_certificates>
      <networks>
        <ip>127.0.0.1</ip>
        <ip>::1</ip>
      </networks>
      <profile>default</profile>
      <quota>default</quota>
    </demo>
  </users>
</clickhouse>

Save the file, close the editor, and restart ClickHouse:

Terminal

sudo systemctl restart clickhouse-server# No output on success.systemctl is-active clickhouse-serveractive

Create the database and sample data

Connect locally as the password-protected default administrator to create the demo database and sample table. Each command prompts for the password:

Terminal

clickhouse-client --user default --password --query 'CREATE DATABASE IF NOT EXISTS demo;'clickhouse-client --user default --password --query 'CREATE TABLE IF NOT EXISTS demo.demo (id UInt32, name String) ENGINE = MergeTree ORDER BY id;'clickhouse-client --user default --password --query "INSERT INTO demo.demo VALUES (1, 'alice'), (2, 'bob'), (3, 'carol');"

Register ClickHouse with the agent

Still on the ClickHouse server, display the public server certificate:

Terminal

sudo cat /etc/clickhouse-server/certs/server.crt-----BEGIN CERTIFICATE-----...-----END CERTIFICATE-----

Copy the entire certificate, including the BEGIN and END lines. The agent uses it to verify ClickHouse. Then open the target configuration:

Terminal

sudo install -d -o root -g root -m 0700 /etc/almaforge/almad.d# No output on success.sudoedit /etc/almaforge/almad.d/clickhouse.yaml# Your text editor opens.

For a new file, copy the following YAML. If the file already exists, update the existing resource and preserve its other settings. Replace <paste the complete server certificate here> with the certificate you copied. Indent every certificate line by six spaces beneath caCert: |:

target.yaml.in:

/etc/almaforge/almad.d/clickhouse.yaml

# Connect the local ClickHouse TLS listener through AlmaForge.
# Replace the placeholder with the contents of /etc/clickhouse-server/certs/server.crt.
apiVersion: almaforge.com/v1
kind: DatabaseTarget
metadata:
  name: clickhouse
  labels:
    db: clickhouse
    env: prod
spec:
  protocol: clickhouse
  uri: clickhouse://localhost:9440
  tls:
    caCert: |
      <paste the complete server certificate here>

Check that the certificate includes both its BEGIN and END lines, then save the file and close the editor. Restrict the file to root:

Terminal

sudo chown root:root /etc/almaforge/almad.d/clickhouse.yaml# No output on success.sudo chmod 0600 /etc/almaforge/almad.d/clickhouse.yaml# No output on success.

Start and check the agent

Enable the services at boot and start the agent:

Terminal

sudo systemctl enable clickhouse-server almad...sudo systemctl restart almad# No output on success.systemctl is-active clickhouse-server almadactiveactive

Both services must report active. If either fails, inspect its log:

Terminal

sudo journalctl -u clickhouse-server -u almad -n 50 --no-pager...<date> <time> <database-host> almad[<pid>]: <log-message>...

Resolve any reported errors before continuing. The agent reads the target configuration at startup, so restart almad after changing /etc/almaforge/almad.d/clickhouse.yaml.

Step 3. Grant access

On your computer, confirm that the agent has registered the database:

Terminal

alma get dbHost             Name            Protocol       URI                 Labels             Version---------------- --------------- -------------- ------------------- ------------------ --------<database-host>  clickhouse      clickhouse     <database-uri>      db=clickhouse ... 2026.1.0

Check that clickhouse appears with protocol clickhouse and your database server in the Host column. If either is missing, see Database is missing or has no host.

Download clickhouse-access.yaml and save it locally. This role allows a client certificate for demo on targets labeled db: clickhouse. ClickHouse authenticates that certificate as the demo account, which can read the sample table. Everyone assigned this role uses the same database account, while AlmaForge records the signed-in person on the session.

examples/databases/clickhouse/roles/clickhouse-access.yaml

# Allow access to the dedicated ClickHouse target.
# ClickHouse grants control database and table permissions.
apiVersion: almaforge.com/v1
kind: Role
metadata:
  name: clickhouse-access
spec:
  allow:
    databaseLabels:
      db: clickhouse
    databaseUsers:
      - demo

Apply the role:

Terminal

alma apply -f clickhouse-access.yamlrole 'clickhouse-access' has been created

Creating the role does not assign it to anyone. To grant it to an SSO group, list your connectors:

Terminal

alma get oidcKind          Name------------- -----------OIDCConnector company-sso

Find the connector your team uses to sign in. Replace <connector-name> with its name from the output, then open it in your text editor:

Terminal

alma edit oidc/<connector-name># Your text editor opens. After saving your changes:oidc connector "<connector-name>" has been updated

Under spec.claimsToRoles, find the entry for the group that should have ClickHouse access. Add clickhouse-access to that entry's roles list. Preserve the existing roles and mappings, including those that grant admin, then save and close the editor. The command applies your changes. If the group has no mapping yet, follow OIDC role mapping to add one.

Step 4. Connect

On your computer, sign out of saved sessions, then sign back in with an account in the group granted access in Step 3 to get a session with the new role. logout clears saved sessions for all clusters. Use your cluster's hostname in the login command:

Terminal

alma logoutLogged out all users from all proxies.alma login --proxy=almaforge.example.com:443...> Profile URL:        https://almaforge.example.com:443  Logged in as:       [email protected]  Cluster:            almaforge.example.com  Roles:              clickhouse-access  ...alma status> Profile URL:        https://almaforge.example.com:443  Logged in as:       [email protected]  Cluster:            almaforge.example.com  Roles:              clickhouse-access  ...

Check that Roles includes clickhouse-access. If it is missing, check your SSO group membership and the mapping from Step 3 before continuing.

Connect to the sample data:

Terminal

alma db connect --db-user=demo clickhouse...:) SELECT currentUser();
   ┌─currentUser()─┐1. │ demo          │   └───────────────┘
:) SELECT count() FROM demo.demo;
   ┌─count()─┐1. │       3 │   └─────────┘

The results must show user demo and three sample rows. Use the full table name demo.demo so the query works regardless of the client's current database. Press Ctrl+D to leave the client.

Advanced configuration

GUI clients and scripts

Leave this command running while your client uses the connection:

Terminal

alma proxy db --tunnel --db-user=demo clickhouseStarted authenticated tunnel for the ClickHouse database "clickhouse" in cluster "almaforge.example.com" on 127.0.0.1:54321....

Use a client that supports the ClickHouse native protocol. Set its host and port to the printed address, use demo as the username, leave the password empty, and disable TLS for this local connection. The tunnel authenticates you to AlmaForge, and the agent presents the client certificate to ClickHouse. HTTP clients require a separate clickhouse-http target and an HTTPS listener on ClickHouse.

Architecture

The database agent runs beside ClickHouse and opens an outbound tunnel to the AlmaForge Proxy. ClickHouse listens on localhost and trusts the agent's client certificates.

Troubleshooting

Database is missing or has no host

On the database server, check that /etc/almaforge/almad.d/clickhouse.yaml exists and ALMA_DATABASE_SERVICE=true is set in /etc/almaforge/almad.env. The agent must reach your cluster on port 443. Inspect its log:

Terminal

sudo journalctl -u almad -n 50 --no-pager...<date> <time> <database-host> almad[<pid>]: <log-message>...

Restart almad after correcting its configuration, then repeat alma get db on your computer.

Access is denied

Run alma status and check for clickhouse-access. If it is missing, verify the SSO group mapping from Step 3, then sign out and back in. The role's database labels must match the target file.

Client executable is missing

Check that clickhouse-client or clickhouse is on your PATH. Install the client from Prerequisites, then repeat the version check.

Unknown table

Use the full table name demo.demo. The native client can open with default as its current database.

Certificate authentication fails

The demo account in users.d/almaforge.xml must accept a certificate with common name demo. The server's caConfig must point to the public AlmaForge database CA installed in /etc/clickhouse-server/certs/server.cas.

Limitations

• In this release, AlmaForge does not provision ClickHouse users. Create the account and configure certificate authentication before connecting. Step 2 shows the demo user configuration.
• The role's databaseNames does not restrict databases or tables. Use ClickHouse grants for those permissions.
• Native connections pass the client's username to ClickHouse. --db-user selects the certificate identity. Keep other accounts, including default, password-protected. A passwordless account remains usable through an authorized tunnel.
• Native connections record session start and end events, but do not record individual queries. The clickhouse-http protocol supports HTTP query auditing.

References

• Database access overview: architecture and supported databases.
• RBAC reference: database access fields on a role.
• Declarative configuration: the DatabaseTarget schema.