Encrypting Data for Aria

MariaDB can encrypt data in tables that use the Aria storage engine. This include both user-created tables and internal on-disk temporary tables that use the Aria storage engine. This ensures that your Aria data is only accessible through MariaDB.

For encryption with the InnoDB and XtraDB storage engines, see Encrypting Data for InnoDB/XtraDB.

Basic Configuration

In order to enable encryption for tables using the Aria storage engine, there are a couple server system variables that you need to set and configure. Most users will want to set aria_encrypt_tables and encrypt_tmp_disk_tables.

Users of data-at-rest encryption will also need to have an key management and encryption plugin configured. Some examples are File Key Management Plugin and AWS Key Management Plugin.

[mariadb]
...

# File Key Management
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/keys.enc
file_key_management_filekey = FILE:/etc/mysql/.key
file_key_management_encryption_algorithm = aes_cbc

# Aria Encryption
aria_encrypt_tables=ON
encrypt_tmp_disk_tables=ON

Encryption Keys

Key management and encryption plugins support using multiple encryption keys. Each encryption key can be defined with a different 32-bit integer as a key identifier.

Aria uses the encryption key with ID 1 to encrypt user-created tables.

Aria uses the encryption key with ID 2 to encrypt internal temporary tables if it exists. Otherwise, it uses the encryption key with ID 1.

Key Rotation

When your key management and encryption plugin provides the relevant support, you can automatically rotate and version your encryption keys. For example, the AWS key management plugin supports key rotation, but the file key management plugin does not.

The InnoDB storage engine has background encryption threads that can automatically re-encrypt pages when key rotations occur. The Aria storage engine does not currently have a similar mechanism to re-encrypt pages in the background when key rotations occur.

Enabling Encryption

Enabling Encryption for Automatically Encrypted Tables

Enabling Encryption for User-created Tables

For tables that use the Aria storage engine, you can only enable data-at-rest encryption for those tables that have the ROW_FORMAT table option set to PAGE, (which is the default). Encryption is not available for Aria tables where ROW_FORMAT is set to DYNAMIC or FIXED.

Enabling encryption of Aria tables is done by setting aria_encrypt_tables=ON. When this is set, all Aria tables that have ROW_FORMAT=PAGE that are created from that point forward will be automatically encrypted.

Encrypting Pre-existing Aria Tables

The InnoDB storage engine has background encryption threads that allow the storage engine to automatically perform encryption changes in the background as the configuration changes. Aria does not currently have anything like that.

If you want to encrypt pre-existing Aria tables after a configuration change, then it will take a bit more work.

First, set aria_encrypt_tables=ON:

SET GLOBAL aria_encrypt_tables=ON;

Then, find any Aria tables that use the PAGE ROW_FORMAT:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM information_schema.TABLES 
WHERE ENGINE='Aria' 
AND ROW_FORMAT='PAGE'
AND TABLE_SCHEMA != 'information_schema';

Then, for each table in the results, rebuild the table:

ALTER TABLE aria_tab ENGINE=Aria ROW_FORMAT=PAGE;

When the table is rebuilt, it will be encrypted.

Enabling Encryption for Internal On-disk Temporary Tables

MariaDB regularly creates internal temporary tables during the execution of queries. These internal temporary tables tables will initially use the MEMORY storage engine, which stores all table data in memory. When the table size exceeds max_heap_table_size, MariaDB writes the data to disk using another storage engine. If aria_used_for_temp_tables=ON is set, then MariaDB will use the Aria storage engine for this.

Encryption for these temporary tables is handled separately from encryption for user-created tables. It can be enabled by setting encrypt_tmp_disk_tables=ON. If this is enabled, then internal on-disk temporary tables that use Aria will be automatically encrypted.

Enabling Encryption for Manually Encrypted Tables

Aria does not currently support the ENCRYPTED and ENCRYPTION_KEY_ID table options, so manually choosing which Aria tables to encrypt is not currently supported. See MDEV-18049 about that.

The InnoDB storage engine does support these options.

Disabling Encryption

Disabling Encryption for Automatically Encrypted Tables

Disabling Encryption for User-created Tables

Disabling encryption of Aria tables is done by setting aria_encrypt_tables=OFF. When this is set, all Aria tables that are created from that point forward will be unencrypted.

Decrypting Pre-existing Aria Tables

The InnoDB storage engine has background encryption threads that allow the storage engine to perform encryption changes as the configuration changes. Aria does not currently have anything like that.

If you want to decrypt pre-existing Aria tables after a configuration change, then it will take a bit more work.

First, set aria_encrypt_tables=OFF:

SET GLOBAL aria_encrypt_tables=OFF;

Then, find any Aria tables that use the PAGE ROW_FORMAT:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM information_schema.TABLES 
WHERE ENGINE='Aria' 
AND ROW_FORMAT='PAGE'
AND TABLE_SCHEMA != 'information_schema';

Then, for each table in the results, rebuild the table:

ALTER TABLE aria_tab ENGINE=Aria ROW_FORMAT=PAGE;

When the table is rebuilt, it will be unencrypted.

Disabling Encryption for Internal On-disk Temporary Tables

Disabling encryption of internal temporary tables that use Aria is done by setting encrypt_tmp_disk_tables=OFF. When this is set, all internal temporary tables that are created from that point forward will be unencrypted.

Determining Whether a Table is Encrypted

The InnoDB storage engine has the information_schema.INNODB_TABLESPACES_ENCRYPTION table that can be used to get information about which tables are encrypted. Aria does not currently have anything like that (see MDEV-17324 about that).

To determine whether an Aria table is encrypted, you currently have to search the data file for some plain text that you know is in the data.

For example, let's say that we have the following table:

SELECT * FROM db1.aria_tab LIMIT 1;
+----+------+
| id | str  |
+----+------+
|  1 | str1 |
+----+------+
1 row in set (0.00 sec

Then, we could search the data file that belongs to db1.aria_tab for str1 using a command-line tool, such as strings:

$ sudo strings /var/lib/mysql/db1/aria_tab.MAD | grep "str1"
str1

If you can find the plain text of the string, then you know that the table is not encrypted.

Encryption and the Aria Log

Only Aria tables are currently encrypted. The Aria log is not yet encrypted. See MDEV-8587 about that.

Comments

Comments loading...