Full MariaDB Encryption At-Rest and In-Transit for Maximum Data Protection – Part Two

In the first part of this series, we have covered in-transit encryption configuration for MariaDB replication servers, where we configured client-server and replication encryptions. Taken from the first post, where we had partially configured our full encryption (as indicated by the green arrows on the left in the diagram) and in this blog post, we are going to complete the encryption setup with at-rest encryption to create a fully encrypted MariaDB replication setup.

The following diagram illustrates our current setup and the final setup that we are going to achieve:

At-Rest Encryption

At-rest encryption means the data-at-rest like data files and logs are encrypted on the disk, makes it almost impossible for someone to access or steal a hard disk and get access to the original data (provided that the key is secured and not stored locally). Data-at-Rest Encryption, also known as Transparent Data Encryption (TDE), is supported in MariaDB 10.1 and later. Note that using encryption has an overhead of roughly 5-10%, depending on the workload and cluster type.

For MariaDB, the following MariaDB components can be encrypted at-rest:

• InnoDB data file (shared tablespace or individual tablespace, e.g, *.ibd and ibdata1)
• Aria data and index files.
• Undo/redo logs (InnoDB log files, e.g, ib_logfile0 and ib_logfile1).
• Binary/relay logs.
• Temporary files and tables.

The following files can not be encrypted at the moment:

• Metadata file (for example .frm files).
• File-based general log/slow query log. Table-based general log/slow query log can be encrypted.
• Error log.

MariaDB’s data-at-rest encryption requires the use of a key management and encryption plugins. In this blog post, we are going to use File Key Management Encryption Plugin, which is provided by default since MariaDB 10.1.3. Note that there are a number of drawbacks using this plugin, e.g, the key can still be read by root and MySQL user, as explained in the MariaDB Data-at-Rest Encryption page.

Generating Key File

Let’s create a dedicated directory to store our at-rest encryption stuff:

$ mkdir -p /etc/mysql/rest
$ cd /etc/mysql/rest

Create a keyfile. This is the core of encryption:

$ openssl rand -hex 32 > /etc/mysql/rest/keyfile

Append a string “1;” as the key identifier into the keyfile:

$ echo '1;' 
sed -i '1s/^/1;/' /etc/mysql/rest/keyfile

Thus, when reading the keyfile, it should look something like this:

$ cat /etc/mysql/rest/keyfile
1;4eb5770dcfa691bc634cbcd3c6bed9ed4ccd0111f3d3b1dae2c51a90fbf16ed7

The above simply means for key identifier 1, the key is 4eb… The key file needs to contain two pieces of information for each encryption key. First, each encryption key needs to be identified with a 32-bit integer as the key identifier. Second, the encryption key itself needs to be provided in hex-encoded form. These two pieces of information need to be separated by a semicolon.

Create a password to encrypt the above key. Here we are going to store the password inside a file called “keyfile.passwd”:

$ echo -n 'mySuperStrongPassword' > /etc/mysql/rest/keyfile.passwd

You could skip the above step if you would like to specify the password directly in the configuration file using file_key_management_filekey option. For example: file_key_management_filekey=mySuperStrongPassword

But in this example, we are going to read the password that is stored in a file, thus we have to define the following line in the configuration file later on: 

file_key_management_filekey=FILE:/etc/mysql/encryption/keyfile.passwd

We are going to encrypt the clear text keyfile into another file called keyfile.enc, using password inside the password file:

$  openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/rest/keyfile.passwd -in /etc/mysql/rest/keyfile -out /etc/mysql/rest/keyfile.enc

When listing out the directory, we should see these 3 files:

$ ls -1 /etc/mysql/rest/
keyfile
keyfile.enc
keyfile.passwd

The content of the keyfile.enc is simply an encrypted version of keyfile:

To test out, we can decrypt the encrypted file using OpenSSL by providing the password file (keyfile.passwd):

$ openssl aes-256-cbc -d -md sha1 -pass file:/etc/mysql/rest/keyfile.passwd -in /etc/mysql/rest/keyfile.enc
1;4eb5770dcfa691bc634cbcd3c6bed9ed4ccd0111f3d3b1dae2c51a90fbf16ed7

We can then remove the plain key because we are going to use the encrypted one (.enc) together with the password file:

$ rm -f /etc/mysql/encryption/keyfile

We can now proceed to configure MariaDB at-rest encryption.

Configuring At-Rest Encryption

We have to move the encrypted key file and password to the slaves to be used by MariaDB to encrypt/decrypt the data. Otherwise, an encrypted table being backed up from the master using physical backup like MariaDB Backup would be having a problem to read by the slaves (due to different key/password combination). Logical backup like mysqldump should work with different keys and passwords.

On the slaves, create a directory to store at-rest encryption stuff:

(slave1)$ mkdir -p /etc/mysql/rest
(slave2)$ mkdir -p /etc/mysql/rest

On the master, copy the encrypted keyfile and password file to the other slaves:

(master)$ cd /etc/mysql/rest
(master)$ scp keyfile.enc keyfile.passwd root@slave1:/etc/mysql/rest/
(master)$ scp keyfile.enc keyfile.passwd root@slave2:/etc/mysql/rest/

Protect the files from global access and assign “mysql” user as the ownership:

$ chown mysql:mysql /etc/mysql/rest/*
$ chmod 600 /etc/mysql/rest/*

Add the following into MariaDB configuration file under [mysqld] or [mariadb] section:

# at-rest encryption
plugin_load_add              = file_key_management
file_key_management_filename = /etc/mysql/rest/keyfile.enc
file_key_management_filekey  = FILE:/etc/mysql/rest/keyfile.passwd
file_key_management_encryption_algorithm = AES_CBC

innodb_encrypt_tables            = ON
innodb_encrypt_temporary_tables  = ON
innodb_encrypt_log               = ON
innodb_encryption_threads        = 4
innodb_encryption_rotate_key_age = 1
encrypt-tmp-disk-tables          = 1
encrypt-tmp-files                = 1
encrypt-binlog                   = 1
aria_encrypt_tables              = ON

Take note on the file_key_management_filekey variable, if the password is in a file, you have to prefix the path with “FILE:”. Alternatively, you could also specify the password string directly (not recommended due to its verbosity): 

file_key_management_filekey=mySuperStrongPassword

Restart MariaDB server one node at a time, starting with the slaves:

(slave1)$ systemctl restart mariadb
(slave2)$ systemctl restart mariadb
(master)$ systemctl restart mariadb

Observe the error log and make sure MariaDB encryption is activated during start up:

$ tail -f /var/log/mysql/mysqld.log
...
2019-12-17  6:44:47 0 [Note] InnoDB: Encrypting redo log: 2*67108864 bytes; LSN=143311
2019-12-17  6:44:48 0 [Note] InnoDB: Starting to delete and rewrite log files.
2019-12-17  6:44:48 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 67108864 bytes
2019-12-17  6:44:48 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 67108864 bytes
2019-12-17  6:44:48 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2019-12-17  6:44:48 0 [Note] InnoDB: New log files created, LSN=143311
2019-12-17  6:44:48 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-12-17  6:44:48 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-12-17  6:44:48 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-12-17  6:44:48 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-12-17  6:44:48 0 [Note] InnoDB: Waiting for purge to start
2019-12-17  6:44:48 0 [Note] InnoDB: 10.4.11 started; log sequence number 143311; transaction id 222
2019-12-17  6:44:48 0 [Note] InnoDB: Creating #1 encryption thread id 139790011840256 total threads 4.
2019-12-17  6:44:48 0 [Note] InnoDB: Creating #2 encryption thread id 139790003447552 total threads 4.
2019-12-17  6:44:48 0 [Note] InnoDB: Creating #3 encryption thread id 139789995054848 total threads 4.
2019-12-17  6:44:48 0 [Note] InnoDB: Creating #4 encryption thread id 139789709866752 total threads 4.
2019-12-17  6:44:48 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-12-17  6:44:48 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-12-17  6:44:48 0 [Note] Using encryption key id 1 for temporary files
...

You should see lines indicating encryption initialization in the error log. At this point, the majority of the encryption configuration is now complete.

Testing Your Encryption

Create a test database to test on the master:

(master)MariaDB> CREATE SCHEMA sbtest;
(master)MariaDB> USE sbtest;

Create a standard table without encryption and insert a row:

MariaDB> CREATE TABLE tbl_plain (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255));
MariaDB> INSERT INTO tbl_plain SET data = 'test data';

We can see the stored data in clear text when browsing the InnoDB data file using a hexdump tool:

$ xxd /var/lib/mysql/sbtest/tbl_plain.ibd | less
000c060: 0200 1c69 6e66 696d 756d 0002 000b 0000  ...infimum......
000c070: 7375 7072 656d 756d 0900 0000 10ff f180  supremum........
000c080: 0000 0100 0000 0000 0080 0000 0000 0000  ................
000c090: 7465 7374 2064 6174 6100 0000 0000 0000  test data.......
000c0a0: 0000 0000 0000 0000 0000 0000 0000 0000  ................

Create an encrypted table and insert a row:

MariaDB> CREATE TABLE tbl_enc (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255)) ENCRYPTED=YES;
MariaDB> INSERT INTO tbl_enc SET data = 'test data';

We can’t tell what is stored in InnoDB data file for encrypted tables:

$ xxd /var/lib/mysql/sbtest/tbl_enc.ibd | less
000c060: 0c2c 93e4 652e 9736 e68a 8b69 39cb 6157  .,..e..6...i9.aW
000c070: 3cd1 581c 7eb9 84ca d792 7338 521f 0639  <.X.~.....s8R..9
000c080: d279 9eb3 d3f5 f9b0 eccb ed05 de16 f3ac  .y..............
000c090: 6d58 5519 f776 8577 03a4 fa88 c507 1b31  mXU..v.w.......1
000c0a0: a06f 086f 28d9 ac17 8923 9412 d8a5 1215  .o.o(....#......

Note that the metadata file tbl_enc.frm is not encrypted at-rest. Only the InnoDB data file (.ibd) is encrypted.

When comparing the "plain" binary or relay logs, we can clearly see the content of it using hexdump tool:

$ xxd binlog.000002 | less
0000560: 0800 0800 0800 0b04 726f 6f74 096c 6f63  ........root.loc
0000570: 616c 686f 7374 0047 5241 4e54 2052 454c  alhost.GRANT REL
0000580: 4f41 442c 4c4f 434b 2054 4142 4c45 532c  OAD,LOCK TABLES,
0000590: 5245 504c 4943 4154 494f 4e20 434c 4945  REPLICATION CLIE
00005a0: 4e54 2c45 5645 4e54 2c43 5245 4154 4520  NT,EVENT,CREATE
00005b0: 5441 424c 4553 5041 4345 2c50 524f 4345  TABLESPACE,PROCE
00005c0: 5353 2c43 5245 4154 452c 494e 5345 5254  SS,CREATE,INSERT
00005d0: 2c53 454c 4543 542c 5355 5045 522c 5348  ,SELECT,SUPER,SH
00005e0: 4f57 2056 4945 5720 4f4e 202a 2e2a 2054  OW VIEW ON *.* T

While for an encrypted binary log, the content looks gibberish:

$ xxd binlog.000004 | less
0000280: 4a1d 1ced 2f1b db50 016a e1e9 1351 84ba  J.../..P.j...Q..
0000290: 38b6 72e7 8743 7713 afc3 eecb c36c 1b19  8.r..Cw......l..
00002a0: 7b3f 6176 208f 0000 00dc 85bf 6768 e7c6  {?av .......gh..
00002b0: 6107 5bea 241c db12 d50c 3573 48e5 3c3d  a.[.$.....5sH.<=
00002c0: 3179 1653 2449 d408 1113 3e25 d165 c95b  1y.S$I....>%.e.[
00002d0: afb0 6778 4b26 f672 1bc7 567e da96 13f5  ..gxK&.r..V~....
00002e0: 2ac5 b026 3fb9 4b7a 3ef4 ab47 6c9f a686  *..&?.Kz>..Gl...

Encrypting Aria Tables

For Aria storage engine, it does not support the ENCRYPTED option in CREATE/ALTER statement since it follows the aria_encrypt_tables global option. Therefore, when creating an Aria table, simply create the table with ENGINE=Aria option:

MariaDB> CREATE TABLE tbl_aria_enc (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255)) ENGINE=Aria;
MariaDB> INSERT INTO tbl_aria_enc(data) VALUES ('test data');
MariaDB> FLUSH TABLE tbl_aria_enc;

We can then verify the content of the table's data file (tbl_aria_enc.MAD) or index file (tbl_aria_enc.MAI) with hexdump tool. To encrypt an existing Aria table, the table needs to be re-built:

MariaDB> ALTER TABLE db.aria_table ENGINE=Aria ROW_FORMAT=PAGE;

This statement causes Aria to rebuild the table using the ROW_FORMAT table option. In the process, with the new default setting, it encrypts the table when it writes to disk.

Encrypting General Log/Slow Query Log

To encrypt general and slow query logs, we can set MariaDB log_output option to 'TABLE' instead of the default 'FILE':

MariaDB> SET GLOBAL log_ouput = 'TABLE';

However, MariaDB will by default create the necessary tables using CSV storage engine, which is not encrypted by MariaDB. No engines other than CSV, MyISAM or Aria are legal for the log tables. The trick is to rebuild the default CSV table with Aria storage engine, provided that aria_encrypt_tables option is set to ON. However, the respective log option must be turned off for the table alteration to succeed.

Thus, the steps to encrypt general log table is:

MariaDB> SET GLOBAL general_log = OFF;
MariaDB> ALTER TABLE mysql.general_log ENGINE=Aria;
MariaDB> SET GLOBAL general_log = ON;

Similarly, for slow query log:

MariaDB> SET GLOBAL slow_query_log = OFF;
MariaDB> ALTER TABLE mysql.slow_log ENGINE=Aria;
MariaDB> SET GLOBAL slow_query_log = ON;

Verify the output of general logs within the server:

MariaDB> SELECT * FROM mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                     |
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------+
| 2019-12-17 07:45:53.109558 | root[root] @ localhost [] |        19 |     28001 |        Query | select * from sbtest.tbl_enc |
| 2019-12-17 07:45:55.504710 | root[root] @ localhost [] |        20 |     28001 |        Query | select * from general_log    |
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------+

As well as the encrypted content of the Aria data file inside data directory using hexdump tool:

$ xxd /var/lib/mysql/mysql/general_log.MAD | less
0002040: 1d45 820d 7c53 216c 3fc6 98a6 356e 1b9e  .E..|S!l?...5n..
0002050: 6bfc e193 7509 1fa7 31e2 e22a 8f06 3c6f  k...u...1..*..

MariaDB at-rest encryption is now complete. Combine this with in-transit encryption we have done in the first post, our final architecture is now looking like this:

Conclusion

It's now possible to totally secure your MariaDB databases via encryption for protection against physical and virtual breach or theft. ClusterControl can help you maintain this type of security as well and you can download it for free here.