Posted on by mike

Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL

Encryption at rest MariaDB MySQL Percona Server

Encryption at rest MariaDB MySQL Percona ServerProtecting the data stored in your database may have been at the top of your priorities recently, especially with the changes that were introduced earlier this year with GDPR.

There are a number of ways to protect this data, which until not so long ago would have meant either using an encrypted filesystem (e.g. LUKS), or encrypting the data before it is stored in the database (e.g. AES_ENCRYPT or other abstraction within the application). A few years ago, the options started to change, as Alexander Rubin discussed in MySQL Data at Rest Encryption, and now MariaDB®, MySQL®, and Percona Server for MySQL all support encryption at-rest. However, the options that you have—and, indeed, the variable names—vary depending upon which database version you are using.

In this blog post we will take a look at what constitutes the maximum level of at-rest encryption that can be achieved with each of the latest major GA releases from each provider. To allow a fair comparison across the three, we will focus on the file-based key management; keyring_file plugin for MySQL and Percona Server for MySQL along with file_key_management plugin for MariaDB.

MariaDB 10.3

The MariaDB team take the credit for leading the way with at-rest encryption, as most of their features have been present since the 10.1 release (most notably the beta release of 10.1.3 in March 2015). Google donated the tablespace encryption, and eperi donated per-table encryption and key identifier support.

The current feature set for MariaDB 10.3 comprises of the following variables:

Maximising at-rest encryption with MariaDB 10.3

Using the following configuration would give you maximum at-rest encryption with MariaDB 10.3:

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
innodb_encrypt_log = ON
innodb_encrypt_tables = FORCE
Innodb_encrypt_threads = 4
encrypt_binlog = ON
encrypt_tmp_disk_tables = ON
encrypt_tmp_files = ON
aria_encrypt_tables = ON

This configuration would provide the following at-rest protection:

  • automatic and enforced InnoDB tablespace encryption
  • automatic encryption of existing tables that have not been marked with 
    ENCRYPTED=NO
  • 4 parallel encryption threads
  • encryption of temporary files and tables
  • encryption of Aria tables
  • binary log encryption
  • an encrypted file that contains the main encryption key

You can read more about preparing the keys, as well as the other key management plugins in the Encryption Key Management docs.

There is an existing bug related to encrypt_tmp_files (MDEV-14884), which causes the use of 

mysqld --help --verbose

 to fail, which if you are using the official MariaDB Docker container for 10.3 will cause you to be unable to keep mysqld up and running. Messages similar to these would be visible in the Docker logs:

ERROR: mysqld failed while attempting to check config
command was: "mysqld --verbose --help --log-bin-index=/tmp/tmp.HDiERM4SPx"
2018-08-15 13:38:15 0 [Note] Plugin 'FEEDBACK' is disabled.
2018-08-15 13:38:15 0 [ERROR] Failed to enable encryption of temporary files
2018-08-15 13:38:15 0 [ERROR] Aborting

N.B. you should be aware of the limitations for the implementation, most notably log tables and files are not encrypted and may contain data along with any query text.

One of the key features supported by MariaDB that is not yet supported by the other providers is the automatic, parallel encryption of tables that will occur when simply enabling 

innodb_encrypt_tables

 . This avoids the need to mark the existing tables for encryption using 

ENCRYPTED=YES

 , although at the same time it also does not automatically add the comment and so you would not see this information. Instead, to check for encrypted InnoDB tables in MariaDB you should check 

information_schema.INNODB_TABLESPACES_ENCRYPTION

 , an example query being:

mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
   ->   SUBSTRING_INDEX(name, '/', -1) AS db_table,
   ->   COALESCE(ENCRYPTION_SCHEME, 0) AS encrypted
   -> FROM information_schema.INNODB_SYS_TABLESPACES
   -> LEFT JOIN INNODB_TABLESPACES_ENCRYPTION USING(name);
+---------+----------------------+-----------+
| db_name | db_table             | encrypted |
+---------+----------------------+-----------+
| mysql   | innodb_table_stats   |      1    |
| mysql   | innodb_index_stats   |      0    |
| mysql   | transaction_registry |      0    |
| mysql   | gtid_slave_pos       |      0    |
+---------+----------------------+-----------+

As can be inferred from this query, the system tables in MariaDB 10.3 are still predominantly MyISAM and as such cannot be encrypted.

MySQL

Whilst the enterprise version of MySQL has support for a number of data at-rest encryption features as of 5.7, most of them are not available to the community edition. The latest major release of the community version sees the main feature set comprise of:

The enterprise edition adds the following extra support:

Maximising at-rest encryption with MySQL 8.0

Using the following configuration would give you maximum at-rest encryption with MySQL 8.0:

early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring
innodb_redo_log_encrypt=ON
innodb_undo_log_encrypt=ON

This configuration would provide the following at-rest protection:

  • optional InnoDB tablespace encryption
  • redo and undo log encryption

You would need to create new, or alter existing tables with the 

ENCRYPTION=Y

 option, which would then be visible by examining 

information_schema.INNODB_TABLESPACES

 , an example query being:

mysql> SELECT TABLE_SCHEMA AS db_name,
   ->    TABLE_NAME AS db_table,
   ->    CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%' AS encrypted
   -> FROM information_schema.INNODB_TABLESPACES ts
   -> INNER JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = SUBSTRING_INDEX(ts.name, '/', 1)
   ->                                        AND t.TABLE_NAME = SUBSTRING_INDEX(ts.name, '/', -1);
+---------+-----------------+-----------+
| db_name | db_table        | encrypted |
+---------+-----------------+-----------+
| sys     | sys_config      |     1     |
+---------+-----------------+-----------+

N.B. You are able to encrypt the tablespaces in 5.7, in which case you should use 

information_schema.INNODB_SYS_TABLESPACES

 as the internal system views on the data dictionary were renamed (InnoDB Changes).

Unfortunately, whilst all of the tables in the mysql schema use the InnoDB engine (except for the log tables), you cannot encrypt them and instead get the following error:

ERROR 3183 (HY000): This tablespace can't be encrypted.

Interestingly, you are led to believe that you can indeed encrypt the 

general_log

 and 

slow_log

 tables, but this is in fact a bug (#91791).

Percona Server for MySQL

Last, but not least we have Percona Server for MySQL, which, whilst not completely matching MariaDB for features, is getting very close. As we shall see shortly, it does in fact have some interesting differences to both MySQL and MariaDB.

The current feature set for 5.7, which does indeed exceed the features provided by MySQL 5.7 and for the most part 8.0, is as follows:

Maximising at-rest encryption with Percona Server for MySQL 5.7

Using the following configuration would give you maximum at-rest encryption with Percona Server 5.7:

early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring
innodb_temp_tablespace_encrypt=ON
innodb_encrypt_online_alter_logs=ON
innodb_encrypt_tables=FORCE
encrypt_binlog=ON
encrypt_tmp_files=

This configuration would provide the following at-rest protection:

  • automatic and enforced InnoDB tablespace encryption
  • encryption of temporary files and tables
  • binary log encryption
  • encryption when performing online DDL

There are some additional features that are due for release in the near future:

  • Encryption of the doublewrite buffer
  • Automatic key rotation
  • Undo log and redo log encryption
  • InnoDB system tablespace encryption
  • InnoDB tablespace and redo log scrubbing
  • Amazon KMS keyring plugin

Just like MySQL, encryption of any existing tables needs to be specified via 

ENCRYPTION=Y

 via an 

ALTER

, however new tables are automatically encrypted. Another difference is that in order to check which tables are encrypted you can should the flag set against the tablespace in 

information_schema.INNODB_SYS_TABLESPACES

, an example query being:

mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
   ->    SUBSTRING_INDEX(name, '/', -1) AS db_table,
   ->    (flag & 8192) != 0 AS encrypted
   -> FROM information_schema.INNODB_SYS_TABLESPACES;
+---------+---------------------------+-----------+
| db_name | db_table                  | encrypted |
+---------+---------------------------+-----------+
| sys     | sys_config                |      1    |
| mysql   | engine_cost               |      1    |
| mysql   | help_category             |      1    |
| mysql   | help_keyword              |      1    |
| mysql   | help_relation             |      1    |
| mysql   | help_topic                |      1    |
| mysql   | innodb_index_stats        |      1    |
| mysql   | innodb_table_stats        |      1    |
| mysql   | plugin                    |      1    |
| mysql   | servers                   |      1    |
| mysql   | server_cost               |      1    |
| mysql   | slave_master_info         |      1    |
| mysql   | slave_relay_log_info      |      1    |
| mysql   | slave_worker_info         |      1    |
| mysql   | time_zone                 |      1    |
| mysql   | time_zone_leap_second     |      1    |
| mysql   | time_zone_name            |      1    |
| mysql   | time_zone_transition      |      1    |
| mysql   | time_zone_transition_type |      1    |
| mysql   | gtid_executed             |      0    |
+---------+---------------------------+-----------+

Here you will see something interesting! We are able to encrypt most of the system tables, including two that are of significance, as they can contain plain text passwords:

+---------+-------------------+-----------+
| db_name | db_table          | encrypted |
+---------+-------------------+-----------+
| mysql   | servers           |      1    |
| mysql   | slave_master_info |      1    |
+---------+-------------------+-----------+

In addition to the above, Percona Server for MySQL also supports using the opensource HashiCorp Vault to host the keyring decryption information using the keyring_vault plugin; utilizing this setup (provided Vault is not on the same device as your mysql service, and is configured correctly) gains you an additional layer of security.

You may also be interested in my earlier blog post on using Vault with MySQL, showing you how to store your credentials in a central location and use them to access your database, including the setup and configuration of Vault with Let’s Encrypt certificates.

Summary

There are significant differences both in terms of features and indeed variable names, but all of them are able to provide encryption of the InnoDB tablespaces that will be containing your persistent, sensitive data. The temporary tablespaces, InnoDB logs and temporary files contain transient data, so whilst they should ideally be encrypted, only a small section of data would exist in them for a finite amount of time which is less of a risk, albeit a risk nonetheless.

Here are the highlights:

MariaDB 10.3 MySQL 8.0 Percona Server 5.7
encrypted InnoDB data Y Y Y
encrypted non-InnoDB data Aria-only N N
encrypted InnoDB logs Y Y TBA
automatic encryption Y N Y
enforced encryption Y N Y
automatic key rotation Y N TBA
encrypted binary logs Y N Y
encrypted online DDL ? N Y
encrypted keyring Y Enterprise-only N
mysql.slave_master_info N N Y
mysql.servers N N Y
Hashicorp Vault N N Y
AWS KMS Y Enterprise-only TBA

 

Extra reading:

 

The post Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL appeared first on Percona Database Performance Blog.

via MySQL Performance Blog
Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL