Encrypting MariaDB on Ubuntu 16.04

Photo by Markus Spiske on Unsplash

MariaDB introduced encryption with version 10.1. This article details setting it up for on a existing MariaDB installation running on Ubuntu 16.04.

With the introduction of GDPR in May 2018, the need for secure methods of personal data storage has come sharply into focus. For those employing a MySQL database, a simple way to do this is to use the encryption built in to MariaDB since version 10.1.

I had cause to encrypt an existing MariaDB 10.2.6 database running on an Ubuntu 16.04 instance hosted with DigitalOcean. This version of MariaDB introduced the mariadb-plugin-aws-key-management-10.2 plugin, which we make use of here.

Our Setup

We will be taking advantage of features only available on recent versions of MariaDB. For completeness, here is the tested setup:

• Server running Ubuntu 16.04
• MariaDB 10.2.6 installed and running

Security

Encryption is only of value if the relevant keys are equally as hard to get at. Storing the encryption key entirely separately from the hardware holding the database provides the greatest degree of separation here, but this may not always be practical. Using AWS for encryption key management provides a good balance here.

Setting Up AWS

Most of the following information has been adapted from the MariaDB documentation on setting up AWS, with some slight modifications to bring it up to date or where my use case differed.

First step is to ensure you have an Amazon Web Services. It is free to sign up for an account.

Creating a User

We will be setting up a new user using the AWS Identity and Access Management service. Navigate to your AWS console and search for IAM.

Creating a user in the AWS console

To set up a new user, click “Users” on the left hand menu, then on the blue Add User button. Give your new user a name and then select the Programmatic access type. This will generate a new user with access keys we can use to request the encryption keys. Click the blue Next: Permissions button.

On the next page, this is normally where you would set the appropriate permissions for the user. We can skip this step and permit access to the encryption key later on when we create it, so for now just click the blue Next: Review button.

Again, don’t worry that on this page we are informed that “This user has no permissions”. We will handle this later. Click Create User.

The added user and credentials

AWS will now create the user along with a set of access keys. This is the only time you will get to see the secret access key, so ensure to copy it down now, or use the “Download .csv” button. If you forget this step, don’t worry, you will just need to generate a new key later on from the users panel.

Generating a Master Encryption Key

This is the key, stored only on AWS which will be used by our MariaDB instance to encrypt and decrypt its own encryption keys, which in turn are used to encrypt and decrypt your data. This is the most critical layer of our encryption key inception. If this key is deleted or you lose access to it, you will not be able to decrypt your database.

Select a region and then create your key

Now we create the encryption key itself. Back at the IAM console, click on Encryption Keys on the left hand menu, then select the region you would like to make you key in. Click the Create Key button.

Give your key an Alias and an optional description then click Next Step. In the next page you can add tags (if you use them) then click Next Step. The following page is for setting who can administer this key. Unless you know you need specific users to be able to manage this key, you should skip this stage.

Give the new user permission to access the key

Next we need to give our newly created user permission to use the key. Simply check the box next to the appropriate user and click Next Step. Our key and policy are now ready to go, just click Finish.

Summary

So, so far we have generated a user and noted its access keys. We have also set up a new encryption key and given it an alias (“test-key” in this example), and given our new user permission to use it. As mentioned above, we have not given the user permission to manage the new key, only to use it. We also need to make a note of which AWS region (“eu-west-1” in this example) the key is located in.

Configuring our Server

The focus now switches away from AWS and over to our database.

Let’s start by taking a look at our unencrypted database, and how readily available our data is. On your server, run:

$ strings /var/lib/mysql/database_name/table_name.ibd | grep "known-data"

replacing database_name and table_name with something appropriate from your database. “known-data” should be replaced with an example of a string which you know to be present in your database.

For example, I have a database called “test” with a table named “users” and one entry with my name in it. Let’s run the command and see what we get.

$ sudo strings /var/lib/mysql/test/users.ibd | grep "Alex"
HAlexCurrie-ClarkMedium blogger and programmer
%AlexCurrie-Clark

As we can see, the data is stored in an easily extractable format, which we can even search through. Let’s get encrypting.

Install the Plugin

First off, lets install the AWS key management plugin we mentioned earlier:

$ sudo apt install mariadb-plugin-aws-key-management-10.2

This is the plugin also adds the config files we need to enable encryption.

Storing our Credentials

We need to store our AWS user credentials on our server so that it can access the encryption keys. There are some notes on the MariaDB docs about further security steps which can be made when storing your credentials, but for our purposes we will place them in in a file where MariaDB can find them and endure they are only readable by the mysql user.

By default, the AWS API client looks for a credentials file in an.aws folder stored in the mysql home directory. For Ubuntu 16.04, this is /var/lib/mysql . You can confirm this in your terminal:

// should print out /var/lib/mysql
$ echo ~mysql

If you get something like /nonexistent then you will first need to set a home directory for mysql.

// first make sure that mariadb is stopped
$ sudo systemctl stop mariadb
// then set the home directory for the mysql user
$ sudo usermod -d /var/lib/mysql/ mysql

This is important for later on. If you don’t ensure the mysql user has the correct home directory then the AWS plugin will not be able to find the required credentials. Mariadb will not launch and the logs will show an error similar to:

AWS KMS plugin : GenerateDataKeyWithoutPlaintext failed : MissingAuthenticationTokenException - Missing Authentication Token

Then, make sure you have your .aws directory set up an create the credentials file. Use your favourite editor:

# contents of /var/lib/mysql/.aws/credentials
[default]
aws_access_key_id = REPLACE_WITH_USER_ACCESS_KEY_ID
aws_secret_access_key = REPLACE_WITH_SECRET_ACCESS_KEY
region = AWS_REGION_CODE

Replace the 3 bold elements with the information from earlier when we set up AWS. The AWS REGION CODE should match the region the encryption key is set up in. For example, Ireland is “eu-west-1”.

Now we ensure the permissions are locked down on the credentials file, so that only the mysql user can read it.

$ sudo chown mysql /var/lib/mysql/.aws/credentials
$ sudo chmod 600 /var/lib/mysql/.aws/credentials

Configuring MariaDB

Next we need to tell MariaDB that we are using AWS to manage our encryption. Ubuntu 16.04 MariaDB config files are typically stored in /etc/mysql/conf.d . Let’s create a new file there named encryption.cnf .

# contents of /etc/mysql/conf.d/encryption.cnf
[mariadb]
plugin-load = aws_key_management
aws-key-management
aws-key-management-master-key-id = alias/AWS_KEY_ALIAS
aws-key-management-region = AWS_REGION_CODE
!include /etc/mysql/conf.d/enable_encryption.preset
innodb-tablespaces-encryption

Let’s talk through what this does. First we load the aws-key-management plugin. Then we let it know which key we are using. In the example started above, this would be “test-key”. More accurately alias/test-key; the alias/ is important. Again, enter the correct AWS region code for your key.

Finally, this loads the encryption configuration in the enable_encryption.preset file. This contains everything you need to ensure your system allows encryption. This includes turning on table encryption, as well as encryption of temporary tables and logs. For more options you can add to this configuration, see the MariaDB encryption docs.

With configuration all set, restart your Mysql service.

$ sudo systemctl restart mysql

If all goes well, you should get no errors. If you do, try running:

$ sudo journalctl --no-pager -o cat -u mariadb.service

to diagnose any problems.

Encrypting Tables

We are now in a position to start encrypting our data. We can encrypt new and existing tables. From the MySQL prompt, we can alter the encryption type for the users table in the test database mentioned above:

ALTER TABLE users ENCRYPTED=YES;

Now, if all is well, we can run the same command from above and see that the output is null, as the table data is now encrypted:

$ sudo strings /var/lib/mysql/test/users.ibd | grep "Alex"

Success! We have a fully encrypted table. Of course, you can also encrypt a table at create time too. For example:

CREATE TABLE table1 (col1 INT NOT NULL PRIMARY KEY, secret CHAR(200)) ENGINE=InnoDB ENCRYPTED=YES;

References

Table and tablespace encryption on MariaDB 10.1

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

Encrypting InnoDB - DZone Database

Data at Rest Encryption

Encrypting Data for InnoDB / XtraDB