Data-at-Rest in MariaDB

Recently I had to set up data encryption at rest for a client I work for on a regular basis.  It certainly seemed to be fairly straight forward at first, but there are some tricks that will help you get it done without all the trial and error I went through.

Of course you can learn more about how it all works on the Maria website.

The server I set this up on is a Ubuntu 18.04.5 LTS managed through Laravel Forge so when you see references to the forge user, you'll need to adjust that to your own setup.

So let's just jump right in shall we?

First off I create two 20 string random passwords using Strong Password Generator. One of these we'll use as the name of our encrypted key file, (the one that holds all of our keys), and the other we'll use as our password for doing the encryption. So you'll see them below in code as <filename> and <password>.

SSH into your server, and first off let's create several encryption keys and save them to a file to encrypt.

Unfortunately I needed to CHMOD the file in order to write to it, YMMV.

Next let's create 9 keys and them into this file.

Run that command 9 times or whatever you find sufficient. 5-10 should be plenty.

Next open the file and increment each key with a number and semi-colon in front of the key for each one you made. So the final key on each line should resemble this:

1;hyuqogyueorgo678ogfy
2;uifqghuieo7eigheug77
...

Obviously those aren't real keys, but you understand the pattern here I hope.

Now let's save our password that we made for encrypting the keyfile into it's own file so Maria can understand how to process the encryption.

... then paste in your <password> from step one. NOT the one you used to name your file, the other one.

Ok so now that we have that done, we're ready to actually encrypt this file for use. I find it easier to run this long command from the /etc/mysql directory.

You can safely delete <filename>.txt now, but I would wait until you have a complete working and re-bootable system before you do, just in case you need to redo the .enc file.

Next we need to create a configuration file for Maria to implement the encryption.

And paste in the following:

[mariadb]
plugin_load_add=file_key_management
file_key_management = ON
file_key_management_filename = /etc/mysql/<filename>.enc
file_key_management_filekey = FILE:/etc/mysql/.key
file_key_management_encryption_algorithm=aes_cbc
encrypt_binlog = 1
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
aria_encrypt_tables = ON
encrypt_tmp_disk_tables = ON
innodb_encryption_threads = 8
#innodb_encryption_rotate_key_age = 0 # Do not rotate key

NOTE: The last line, innodb_encryption_rotate_key_age = 0 is commented out. Maria will not run the encryption initially if this is set to zero. We'll uncomment it after the initial run.

We should be ready at this point to run the encryption.

Now you can check if your db files are now encrypted by running strings on one of the files. I have a small table called roles that has text columns so I looked at that one.

You should see that all that data is now encrypted and unreadable.

HOORAY!

Now for the tricky part.

It's important that you store your key remotely, download it prior to when Maria starts, then delete it once the server is running. It's only needed during the start process so it's safe to remove it, and it ensures that those asshat hackers can't decrypt your files.

There are other ways to do it, like store the key on an unmounted drive, mount the drive before the server starts, then unmount it again, but our way is automated and requires no human interaction ;)

You'll need a remote storage space so that you can remotely download the key. We happen to be using Digital Ocean Spaces in our example below.

First copy the .enc keyfile to a place your can download if, I used SFTP.

Then download your key file and upload it to your storage space and copy the link to the file so we can add it to our service script.

So we're gonna piggy back our service off of the MariaDB service which makes things much easier.

So let's make a new systemd directory for our service.

Next paste in the following, paying special attention to the notes after this code block.

[Service]
PermissionsStartOnly=true
ExecStartPre=+/usr/bin/wget -P /etc/mysql https://hpdc.nyc3.digitaloceanspaces.com/<some folder>/<filename>.enc
ExecStartPre=+/usr/bin/chmod -R 644 /etc/mysql/<filename>.enc
ExecStartPost=+/usr/bin/sleep 10
ExecStartPost=+rm /etc/mysql/<filename>.enc

This was the most confusing piece for me. WGET saves the file with the wrong permissions be default, and I couldn't get the CHMOD command to run properly. The PermissionsStartOnly command fixes this and makes everything run correctly.

So running through the steps here:

1. Extend extra permissions in the script.
2. Download our remote file and save it to /etc/mysql
3. CHMOD the file to 644 so it can be read
4. Sleep for 10 seconds to allow Maria to start up
5. Delete the .enc keyfile

Once you've saved the file you need to reload the daemon process.

And we should now be ready to test the automation.

And if it restarted with no errors you're done!

If not, you probably missed a step, or you have a typo or something simple.

Play with it and get those databases encrypted.

Peace.