MariaDB cluster data encryption with AWS KMS

MariaDB InnoDB engine now has support for data at rest encryption. One can select to encrypt everything, individual tables, or everything excluding certain tables.

My environment is a MariaDB cluster consisting of EC2-based and on-premises instances. MariaDB default way of managing the encryption key is to place it in a local file. Since I have access to AWS services, I decided to use the Key Management Server (KMS) instead. This is a more (not most though) secure configuration. This post is only about my experience setting up data-at-rest encryption in a MariaDB cluster (specifically using AWS KMS for encryption key management). It does not solve or address all security issues.

A word regarding encryption/decryption overhead. Apparently, some tests were conducted to characterize performance impact when using encryption. The claim is that there is only 3–5% penalty. Even so, it is probably best to be selective about choosing tables that should be encrypted.

Installation and configuration steps are as follows:

• Install MariaDB 10.2
• Install AWS KMS plugin
• Create IAM role, user, and encryption key
• Configure MariaDB on EC2 and on-premises servers
• Restart MariaDB and check status

Most of the information for this setup came from MariaDB documentation and tutorials although I did not find it straightforward. Information seems to be scattered around in various places, often describing different configuration alternatives. It took some trial and error to get it working, particularly when using KMS on-premises.

AWS KMS plugin uses port 443 to communicate. This is not an issue for the EC2 based database. It is necessary to make sure though that the on-premises server is able to access KMS via this port.

Install MariaDB 10.2

Version 10.2 is needed in order to use the AWS KMS Encryption Plugin. The latest version did not seem to have as I was making this setup.

ubuntu@ip-10–0–0–10:~$ sudo apt-get install software-properties-common
ubuntu@ip-10–0–0–10:~$ sudo apt-key adv — recv-keys — keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
ubuntu@ip-10–0–0–10:~$ sudo add-apt-repository ‘deb [arch=amd64,arm64,ppc64el] http://mirror.jaleco.com/mariadb/repo/10.2/ubuntu bionic main’
ubuntu@ip-10–0–0–10:~$ sudo apt update
ubuntu@ip-10–0–0–10:~$ sudo apt install mariadb-server

Install KMS Plugin

ubuntu@ip-10–0–0–10:~$ apt install mariadb-plugin-aws-key-management-10.2

Most of the following IAM-related steps are decently described in the Amazon Web Services (AWS) Key Management Service (KMS) Encryption Plugin Setup Guide. The main point is that the IAM user is not optional for this setup because it is used for the on-premises server configuration. It is absolutely needed. Here I’ll just mention the key points.

Create IAM Role

The IAM role is used by the EC2 server to access the encryption key. The IAM role will be subsequently associated with the encryption key and the EC2 instance.

While creating the role:

• For type of trusted entity select “AWS service”
• For service that will use the role select “Allows EC2 instances to call AWS services on your behalf”
• Do not select any policies

Create IAM User

The IAM user is needed by the on-premises server. While creating the user, download and save the Access Key ID and the Secret Access Key. It will be needed later when configuring access from the on-premises server. The IAM user will be associated with the encryption key.

Create Master Encryption Key

• Select the AWS region. For the subsequent configuration steps, us-east-1 is the default used by the KMS plugin. If you select a different region here, you will have to tell KMS plugin what it is.
• Enter and make a note of the key alias for use at the later step.
• Do not make IAM Role or IAM User the Key Administrator. This is a security precaution.
• Grant access to the Master Encryption Key by IAM user and role.

Now the “MariaDBenc” Master Encryption Key is setup in IAM and “MariaDBenc” IAM User and the “MariaDBenc” IAM Role both have access to this key.

Attach IAM Role to EC2 Instance

Since “MariaDBenc” IAM Role has access to the “MariaDBenc” Master Encryption Key, the EC2 instance will be able to access it when starting the database server.

Configure EC2 MariaDB instance

Add configuration with settings for encryption to /etc/mysql/my.cnf.

The file should contain the following:

[mariadb]
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
plugin-load = aws_key_management
aws-key-management
aws-key-management-master-key-id = alias/MariaDBenc
aws-key-management-region = us-east-1
!include /etc/mysql/conf.d/enable_encryption.preset

Here “MariaDBenc” is the alias used in the AWS Master Encryption Key setup. The innodb_encrypt_tables directive may be set to NO, ON, and FORCE. The difference between ON and FORCE is that with ON unencrypted tables are allowed. FORCE does not allow unencrypted tables.

Restart MariaDB

ubuntu@ip-10–0–0–10:~$ sudo systemctl restart mariadb

Check status

ubuntu@ip-10–0–0–10:~$ sudo journalctl –xe

There should be a line like this:

Jan 15 22:16:23 ip-10–0–0–10 mysqld[11962]: 2019–01–15 22:16:23 140095074936768 [Note] /usr/sbin/mysqld: ready for connections.

Check tables encryption status:

ubuntu@ip-10–0–0–10:~$ sudo mysql –u root –p password
MariaDB [enc_test]> SELECT * FROM information_schema.innodb_tablespaces_encryption \G

The output should be something like this:

************************** 1. row ***************************
SPACE: 0
NAME: innodb_system
ENCRYPTION_SCHEME: 1
KEYSERVER_REQUESTS: 1
MIN_KEY_VERSION: 1
CURRENT_KEY_VERSION: 1
KEY_ROTATION_PAGE_NUMBER: NULL
KEY_ROTATION_MAX_PAGE_NUMBER: NULL
CURRENT_KEY_ID: 1
ROTATING_OR_FLUSHING: 0
...

This is just an example. The output will list all table spaces currently encrypted.

Configure on-premises MariaDB instance

On-premises server configuration is very similar to the EC2. The main difference is that unlike the EC2, it does not have the IAM role association, so we have to configure the IAM user access key to enable the KMS plugin to access the encryption key.

Add configuration with settings for encryption to /etc/mysql/my.cnf.

[mariadb]
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
plugin-load = aws_key_management
aws-key-management
aws-key-management-master-key-id = alias/MariaDBenc
aws-key-management-region = us-east-1
!include /etc/mysql/conf.d/enable_encryption.preset

Create a “drop-in” systemd file with AWS user (this example uses the access key of AWS MariaDBenc user created above) credentials inside. MariaDB documentation mentions several different ways of creating this configuration. After some trial and error, this is the only way that worked for me.

Create a file /etc/systemd/system/mariadb.service.d/aws-kms.conf with the following contents:

[Service]
Environment=AWS_ACCESS_KEY_ID= IHxfV9vgGlQ46IvdVtmi
Environment=AWS_SECRET_ACCESS_KEY=G6P0MX6yOHVlWZoMIzKMQeW1lZOJrBi9hdjxrSr0

Don’t worry, this is not a real key!

Restart systemd:

ubuntu@172.0.0.10:~$ sudo systemctl daemon-reload

Restart mariadb:

ubuntu@172.0.0.10:~$ sudo systemctl restart mariadb

Verify that the KMS plugin got the encryption key:

ubuntu@172.0.0.10:~$ journalctl -xe

Look for something like this:

Jan 14 18:41:28 172.0.0.10 mysqld[21267]: 2019–01–14 18:41:28 140095720077248 [Note] mysqld: AWS KMS plugin: loaded key 1, version 1, key length 128 bit

KMS plugin loaded the key successfully.

For testing, encrypted tables may be created like this:

CREATE TABLE t (i int) ENGINE=InnoDB ENCRYPTED=YES

I think that using KMS plugin is a big improvement compared to storing encryption keys on local disk. However, there is still the issue of the IAM user access key. Storing it in a file still seems like a compromise. Perhaps adding an AWS System Management (SSM) plugin might be a worthwhile improvement to the configuration. This way it should not be necessary to store IAM user access key locally.

Even though I used the same encryption key on two different nodes in the cluster, this is not a requirement. Data-at-rest encryption is a local matter and the cluster does not know anything about it. Securing communication within the cluster is a separate topic.

References

Amazon Web Services (AWS) Key Management Service (KMS) Encryption Plugin Setup Guide

AWS Identity and Access Management

Data at Rest Encryption