Database Access with ClickHouse

The Teleport Clickhouse integration allows you to enroll ClickHouse databases with Teleport.

This guide will help you to:

• Install and configure a Teleport database agent.
• Set up Teleport to access your self-hosted ClickHouse database.
• Connect to your database through Teleport.

How it works

The Teleport Database Service authenticates to ClickHouse using x509 certificates, which are available for the ClickHouse HTTP and Native (TCP) interfaces. The Teleport Database Service can communicate in both the ClickHouse Native (TCP) and HTTP protocols, and you can select which protocol to use when configuring the Teleport Database Service.

Teleport audit logs for query activity are only supported for the ClickHouse HTTP interface. Teleport support for ClickHouse's native interfaces does not include audit logs for database query activity.

• Self-Hosted
• Teleport Cloud

Prerequisites

• A running Teleport cluster version 15.2.4 or above. If you want to get started with Teleport, sign up for a free trial or set up a demo environment.

• The tctl admin tool and tsh client tool.

  On Teleport Enterprise, you must use the Enterprise version of tctl, which you can download from your Teleport account workspace. Otherwise, visit Installation for instructions on downloading tctl and tsh for Teleport Community Edition.

• Either a Linux host or Kubernetes cluster where you will run the Teleport Database Service.

You will also need the following, depending on the ClickHouse protocol you choose:

Step 1/5. Create a Teleport token and user

The Database Service requires a valid auth token to connect to the cluster. Generate one by running the following command against your Teleport Auth Service and save it in /tmp/token on the node that will run the Database Service:

tctl tokens add --type=db

tctl users add \  --roles=access \  --db-users=\* \  --db-names=\* \  alice

tctl users add \  --roles=access,requester \  --db-users=\* \  --db-names=\* \  alice

For more detailed information about database access controls and how to restrict access see RBAC documentation.

Step 2/5. Create a certificate/key pair

Teleport uses mutual TLS authentication with self-hosted databases. These databases must be configured with Teleport's certificate authority to be able to verify client certificates. They also need a certificate/key pair that Teleport can verify.

If you are using Teleport Cloud, your Teleport user must be allowed to impersonate the system role Db in order to be able to generate the database certificate.

Include the following allow rule in in your Teleport Cloud user's role:

allow:
  impersonate:
    users: ["Db"]
    roles: ["Db"]

Export Teleport's certificate authority and a generated certificate/key pair for host db.example.com with a 1-year validity period:

tctl auth sign --format=db --host=clickhouse.example.com --out=server --ttl=8766h

This command will create three files:

• server.cas: Teleport's certificate authority
• server.key: a generated private key
• server.crt: a generated host certificate

Step 3/5. Configure ClickHouse

Use the generated secrets to enable mutual TLS in your clickhouse-server/config.xml configuration file:

<openSSL>
    <server>
       <privateKeyFile>/path/to/server.key</privateKeyFile>
       <caConfig>/path/to/server.cas</caConfig>
       <certificateFile>/path/to/server.crt</certificateFile>
       <verificationMode>strict</verificationMode>
    </server>
</openSSL>

Additionally, your ClickHouse database user accounts must be configured to require a valid client certificate:

CREATE USER alice IDENTIFIED WITH ssl_certificate CN 'alice';

By default, the created user may not have access to anything and won't be able to connect, so let's grant it some permissions:

GRANT ALL ON *.* TO alice;

Step 4/5. Configure and start the Database Service

Install and configure Teleport on the host or Kubernetes cluster where you will run the Teleport Database Service:

teleport db configure create \   -o file \   --token=/tmp/token \   --proxy=teleport.example.com:443 \   --name=example-clickhouse \   --protocol=clickhouse-http \   --uri=clickhouse.example.com:8443 \   --labels=env=dev

teleport db configure create \   -o file \   --token=/tmp/token \   --proxy=teleport.example.com:443 \   --name=example-clickhouse \   --protocol=clickhouse \   --uri=clickhouse.example.com:9440 \   --labels=env=dev

sudo systemctl enable teleport
sudo systemctl start teleport

sudo teleport install systemd -o /etc/systemd/system/teleport.service
sudo systemctl enable teleport
sudo systemctl start teleport

helm repo add teleport https://charts.releases.teleport.dev

helm repo update

JOIN_TOKEN=$(cat /tmp/token)
helm install teleport-kube-agent teleport/teleport-kube-agent \  --create-namespace \  --namespace teleport-agent \  --set roles=db \  --set proxyAddr=teleport.example.com:443 \  --set authToken=${JOIN_TOKEN?} \  --set "databases[0].name=example-clickhouse" \  --set "databases[0].uri=clickhouse.example.com:8443" \  --set "databases[0].protocol=clickhouse-http" \  --set "labels.env=dev" \  --version 15.2.4

JOIN_TOKEN=$(cat /tmp/token)
helm install teleport-kube-agent teleport/teleport-kube-agent \  --create-namespace \  --namespace teleport-agent \  --set roles=db \  --set proxyAddr=teleport.example.com:443 \  --set authToken=${JOIN_TOKEN?} \  --set "databases[0].name=example-clickhouse" \  --set "databases[0].uri=clickhouse.example.com:9440" \  --set "databases[0].protocol=clickhouse" \  --set "labels.env=dev" \  --version 15.2.4

Step 5/5. Connect

Once the Database Service has joined the cluster, log in to see the available databases:

tsh login --proxy=teleport.example.com --user=alice
tsh db ls
Name                      Description Allowed Users Labels  Connect
------------------------- ----------- ------------- ------- -------
example-clickhouse-http               [*]           env=dev

tsh proxy db --db-user=alice --tunnel example-clickhouse-http
Started authenticated tunnel for the Clickhouse (HTTP) database "clickhouse-http" in cluster "teleport.example.com" on 127.0.0.1:59215.
To avoid port randomization, you can choose the listening port using the --port flag.
Use the following command to connect to the database or to the address above using other database GUI/CLI clients:
$ curl http://localhost:59215/

echo 'select currentUser();' | curl http://localhost:59215/  --data-binary @-
alice

Remove credentials for a particular database instance.
tsh db logout example-clickhouse-http
Remove credentials for all database instances.
tsh db logout

tsh login --proxy=teleport.example.com --user=alice
tsh db ls
Name                    Description Allowed Users Labels  Connect
----------------------- ----------- ------------- ------- -------
example-clickhouse                  [*]           env=dev

tsh db connect --db-user=alice example-clickhouse
ClickHouse client version 22.7.2.1.
Connecting to localhost:59502 as user default.
Connected to ClickHouse server version 23.4.2 revision 54462.
350ddafd1941 :) select 1;
SELECT 1
Query id: 327cfd34-2fec-4e04-a185-79fc840aa5cf
┌─1─┐
│ 1 │
└───┘
↓ Progress: 1.00 rows, 1.00 B (208.59 rows/s., 208.59 B/s.)                                                                                                                                                            (0.0 CPU, 9.19 KB RAM)
1 row in set. Elapsed: 0.005 sec.
350ddafd1941 :)

Remove credentials for a particular database instance.
tsh db logout example-clickhouse
Remove credentials for all database instances.
tsh db logout

Next steps

• Learn how to restrict access to certain users and databases.

• View the High Availability (HA) guide.

• Take a look at the YAML configuration reference.

• See the full CLI reference.