Encryption at Rest in MariaDB

Encryption-at-rest prevents an attacker from accessing encrypted data stored on the disk even if he has access to the system. The open-source databases MySQL and MariaDB now support encryption-at-rest feature that meets the demands of new EU data protection legislation. MySQL encryption at rest is slightly different from MariaDB as MySQL only provides encryption for InnoDB tables. Whereas MariaDB also provides an option to encrypt files such as redo logs, slow logs, audit logs, error logs, etc. However, both can’t encrypt data on a RAM and protect it from a malicious root.

In this article, we will learn to configure database-level encryption for MariaDB.

Getting Started

The data at rest encryption requires an encryption plugin along with the key management. The encryption plugin is responsible for managing the encryption key as well as encrypting/decrypting the data.

MariaDB provides three encryption key management solutions, so how you databases manage encryption key depends on the solution you are using. This tutorial will demonstrate database-level encryption using the MariaDB File Key Management solution. However, this plugin does not provide a key rotation feature.

If you are using a LAMP server, the files to add this plugin are located in the “/opt/lamp” directory. If not, then the changes are made in the “/etc/mysql/conf.d” folder.

Creating Encryption Keys

Before encrypting the database using the File key management plugin, we need to create the files containing encryption keys. We will create a file with two pieces of information. That’s an encryption key in a hex-encoded format along with a 32-bit key identifier.

We will create a new folder “keys” in the “/etc/mysql/” directory and use the OpenSSL utility to randomly generate 3 Hex strings and redirect the output to a new file in the keys folder. Type in the following commands:

[email protected]:~$ sudo mkdir /etc/mysql/keys
[email protected]:~$ echo -n "1;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"
[email protected]:~$ echo -n "2;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"
[email protected]:~$ echo -n "3;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

Where 1,2,3 are the key identifiers; we include them to create a reference to the encryption keys using variable innodb_default_encryption_key_id in MariaDB. The output file will look like this:

1;01495ba35e1c9602e14e40bd6de41bb8
2;3cffa4a5d288e90108394dbf639664f8
3;9953297ed1a58ae837486318840f5f1d

Key File Encryption

We can easily set the system variable file_key_management_filename with the appropriate path inside the File Key Management plugin. But it’s not secure to leave the keys in plain text. We can reduce the risk to some extent by assigning file permissions but, that isn’t sufficient.

Now we will encrypt previously created keys using a randomly generated password. In contrast, the key-size can vary from 128/192/256-bits.

[email protected]:~$ openssl rand -hex 192> /etc/mysql/keys/enc_paswd.key

Hence we will use the openssl enc command in the terminal to encrypt the enc_key.txt file to enc_key.enc, using the encryption key created above. Besides, MariaDB only supports the CBC mode of AES to encrypt its encryption keys.

[email protected]:~$ openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/keys/enc_paswd.key -in /etc/mysql/keys/enc_key.txt -out /etc/mysql/keys/enc_key.enc && sudo rm /etc/mysql/keys/enc_key.txt

We also delete our enc_keys.txt file as it is no longer required. Besides, we can always decrypt our data in MariaDB as long as our password file is secure.

Configuring File Key Management Plugin

We will now configure MariaDB with the File Key Management plugin by adding the following variables in the configuration file. The configuration files are usually located in ‘/etc/mysql’ and read all the .cnf files by default. Or you can create a new configuration file “mariadb_enc.cnf” under ‘/etc/mysql/conf.d/ directory.

Now your configuration file can look entirely different from this. However, add these encryption variables under [sqld]. If the key is encrypted, the plugin requires two system variables to configure, i.e., file_key_management_filename and file_key_management_filekey.

[sqld]

#File Key Management Plugin
plugin_load_add=file_key_management
file_key_management = ON file_key_management_encryption_algorithm=aes_cbc file_key_management_filename = /etc/mysql/keys/enc_keys.enc
file_key_management_filekey = /etc/mysql/keys/enc_paswd.key

# InnoDB/XtraDB Encryption Setup
innodb_default_encryption_key_id = 1
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4

# Aria Encryption Setup
aria_encrypt_tables = ON

# Temp & Log Encryption
encrypt-tmp-disk-tables = 1
encrypt-tmp-files = 1
encrypt_binlog = ON

You can find details for each system variable from the official MariaDB website.

Securing The Password File

We will change our MySQL directory permissions to secure the password and other sensitive files. The ownership of the MariaDB will be changed to the current user, which on Ubuntu is mysql.

sudo chown -R mysql:root /etc/mysql/keys
sudo chmod 500 /etc/mysql/keys/

Now we will change the password and encrypted file permissions to

sudo chown mysql:root /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc

sudo chmod 600 /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc

Now restart the database service.

sudo service mysql restart

Conclusion

This article has learned how database-level encryption is the need of the hour and how we can configure encryption-at-rest in MariaDB. The only drawback of the File Key Management plugin is that it does not support key rotation. However, apart from this plugin, many other key management encryption solutions, i.e., AWS Key Management Plugin and Eperi Key Management Plugin. You can find more details on these plugins from MariaDB’s official website.