Encryption codecs

Elena Dvoryadkina

Contents

Configure encryption keys
Apply data encryption algorithms
- Encrypt passwords
- Encrypt data of table columns
Example

ADQM allows you to encrypt sensitive data (passwords stored in ClickHouse configuration files) and table column data on disk using the AES-128 or AES-256 algorithm in GCM-SIV mode.

Configure encryption keys

Configuration parameters for the AES-128 and AES-256 encryption algorithms are located in the Encryption section on the configuration page of the ADQMDB service. This section becomes visible when the Show advanced option is enabled.

Configuration of encryption algorithms

Use parameters in this section to set a key and nonce for the required encryption algorithm or to specify where to load them from (it is recommended to store encryption keys in environment variables or ZooKeeper nodes instead of configuration files). For parameter descriptions, see the corresponding section of the Configuration parameters article.

AES-128

Load keys from environment variables:
1. In the aes_128_key_env and aes_128_nonce_env parameters, specify environment variables that contain a key and nonce for the encryption algorithm, respectively.
2. Set the aes_128_source parameter to from_env.
Load keys from ZooKeeper nodes:
- In the aes_128_key_zk_path and aes_128_nonce_zk_path parameters, specify ZooKeeper nodes that contain a key and nonce for the encryption algorithm, respectively.
- Set the aes_128_source parameter to from_zk.
Store keys in a configuration file (not recommended):
- In the AES-128 GCM-SIV key and AES-128 GCM-SIV nonce parameters, specify a key and nonce for the encryption algorithm, respectively.
- Set the aes_128_source parameter to none.

AES-256

Load keys from environment variables:
- In the aes_256_key_env and aes_256_nonce_env parameters, specify environment variables that contain a key and nonce for the encryption algorithm, respectively.
- Set the aes_256_source parameter to from_env.
Load keys from ZooKeeper nodes:
- In the aes_256_key_zk_path and aes_256_nonce_zk_path parameters, specify ZooKeeper nodes that contain a key and nonce for the encryption algorithm, respectively.
- Set the aes_256_source parameter to from_zk.
Store keys in a configuration file (not recommended):
- In the AES-256 GCM-SIV key and AES-256 GCM-SIV nonce parameters, specify a key and nonce for the encryption algorithm, respectively.
- Set the aes_256_source parameter to none.

After you have defined the parameters, click Save and run the Reconfig and restart action for the ADQMDB service.

Encryption algorithm settings are saved to the encryption_codecs section of the /etc/clickhouse-server/config.d/encryption.xml configuration file.

Apply data encryption algorithms

Once the keys for the AES-128/AES-256 algorithms have been specified, you can enable encryption for:

passwords stored in a configuration file;
table column data on disk.

Encrypt passwords

In the Encryption section of the ADQMDB service configuration, set the Credentials encrypt parameter to AES-128 or AES-128. Save the configuration and execute the Reconfig and restart service action.

After this, values of the following parameters (if set up) will be hidden in the /etc/clickhouse-server/credentials.xml configuration file:

interserver_http_password — password for authentication between replicas;
shard_secret — cluster secret to validate distributed queries on shards;
default_user_password — password of the default user.

Example of credentials.xml

<clickhouse>
    <interserver_http_password>
        <password hide_in_preprocessed="true" encrypted_by="AES_256_GCM_SIV">...encrypted value...</password>
    </interserver_http_password>
    <shard_secret>
        <secret hide_in_preprocessed="true" encrypted_by="AES_256_GCM_SIV">...encrypted value...</secret>
    </shard_secret>
    <default_user_password>
        <password hide_in_preprocessed="true" encrypted_by="AES_256_GCM_SIV">...encrypted value...</password>
    </default_user_password>
</clickhouse>

CAUTION

Loading keys from ZooKeeper nodes is currently not supported for password encryption if SSL is enabled for connections between ClickHouse and ZooKeeper.

Encrypt data of table columns

In ClickHouse, a data encryption algorithm applied to a table column is defined by an encryption codec. To assign an encryption codec to a column, use the CODEC clause in the CREATE TABLE query:

CREATE TABLE <table_name>
(   <column_name1> <data_type1> CODEC(AES_128_GCM_SIV | AES_256_GCM_SIV) [...],
    <column_name2> <data_type2> CODEC(AES_128_GCM_SIV | AES_256_GCM_SIV) [...],
    ...)
ENGINE = MergeTree()
...;

Encryption codecs actually only encrypt column data on disk but do not compress it. If compression is required, specify it explicitly for a column — to do this, list the desired data compression codecs separated by commas (specialized codecs first, and then general-purpose codecs) before an encryption codec in the CODEC clause. For example:

CREATE TABLE <table_name>
(   <column_name> <data_type> CODEC(Delta, LZ4, AES_256_GCM_SIV) [...],
    ...)
ENGINE = MergeTree()
...;

CAUTION

Most table engines, including MergeTree family engines, create index files on disk without applying encryption codecs. This means that plaintext may appear on disk if an encrypted column is indexed.
If a SELECT query mentions a specific value from an encrypted column (for example, within the WHERE expression), this value may appear in the system.query_log log table. In this case, you may want to disable logging.

Example

The following example shows how to set up a key stored in ZooKeeper for the AES-256 encryption algorithm in ADQM, and how to apply this algorithm to encrypt the default user password (which should be set — see Configure authorization for a new cluster) and data of a test table column.

Create two ZooKeeper nodes to store a key and nonce for the AES-256 encryption algorithm (for this, you can use the ZooKeeper CLI available on each cluster host where ZooKeeper is installed):

$ sudo /usr/lib/zookeeper/bin/zkCli.sh

create /clickhouse/clickhouse/aes256_key_hex '00112233445566778899AABBCCDDEEFF00112233445566778899AABBCCDDEEFF'
create /clickhouse/clickhouse/aes256_nonce_hex 'ABCDEFABCDEFABCDEFABCDEF'

In the Encryption section of the ADQMDB service configuration, set the following parameter values:
- aes_256_key_zk_path: /clickhouse/aes256_key_hex;
- es_256_nonce_zk_path: /clickhouse/aes256_nonce_hex;
- aes_256_source: from_zk;
- Credentials encrypt: AES-256.
Save the configuration and run the Reconfig and restart action for the ADQMDB service.

Make sure that the encryption codec settings are saved in the /etc/clickhouse-server/config.d/encryption.xml file:
<clickhouse> <encryption_codecs> <aes_256_gcm_siv> <key_hex hide_in_preprocessed="true" from_zk="/clickhouse/aes256_key_hex"/> <nonce_hex hide_in_preprocessed="true" from_zk="/clickhouse/aes256_nonce_hex"/> </aes_256_gcm_siv> </encryption_codecs> </clickhouse>

In the /etc/clickhouse-server/credentials.xml configuration file, check that the password of the default user is now encrypted:

<clickhouse>
    <default_user_password>
        <password hide_in_preprocessed="true" encrypted_by="AES_256_GCM_SIV">...encrypted password...</password>
    </default_user_password>
</clickhouse>

Create an ADQM table and insert test data into it:
CREATE TABLE encryption_test (x String CODEC(AES_256_GCM_SIV), y String) ENGINE = MergeTree ORDER BY x; INSERT INTO encryption_test VALUES ('1', '10'), ('2', '20'), ('3', '30');
Check the contents of /var/lib/clickhouse/data/default/encryption_test/all_1_1_0/data.bin to ensure that the first column data is stored encrypted on disk.

Found a mistake? Seleсt text and press Ctrl+Enter to report it