Transparent Data Encryption (TDE) Using MariaDB’s File Key Management Encryption Plugin

Transparent Data Encryption (TDE) is one of the most common customer requirements. MariaDB supports TDE and provides a variety of options for implementing it. This blog discusses implementing TDE at the database layer using MariaDB’s File Key Management Encryption Plugin. This blog is the first in a series regarding data-at-rest encryption.

In this blog, I answer the following questions:

  • What is TDE and how does it protect data?
  • How does MariaDB support TDE?
  • How can you implement TDE with MariaDB’s File Key Management Encryption Plugin?

TDE is only one part of the security landscape and it should be implemented within the greater security context. To deploy encryption for production workloads, particularly those with regulatory requirements, additional planning and implementation is needed. Data-at-rest encryption and data-in-transit encryption are both supported by MariaDB Enterprise Server. This blog describes one way data-at-rest encryption can be deployed with MariaDB Enterprise Server, but this is not the only possible way. It’s also worth noting that data-at-rest encryption is automatic when you use MariaDB’s cloud database, MariaDB SkySQL.

Transparency – No User Keys Required

Before we discuss implementing Transparent Database Encryption, I’d like to clarify the meaning of “transparent”. There is a common misconception about database encryption that anyone who does not have access to the security key will get junk or encrypted data and won’t be able to read it. This is not true.

With TDE, the encryption is transparent to the clients and the users. The database is encrypted at the filesystem level, which means the data files containing the actual data are encrypted. Encryption and decryption of data are performed by MariaDB Server with the help of a secret key. As long as MariaDB can access the secret key, any user who has SELECT privilege to the tables can read the data, and the same thing applies for other DML privileges, INSERT, UPDATE and DELETE. None of these operations are impacted by encryption of data-at-rest or TDE, as I’ll demonstrate later in this blog.

TDE and Physical Security

With appropriate controls, TDE can protect the database during the physical theft of servers or drives. Suppose anyone physically gets hold of the HDD storage drive containing the actual data directory. They will not be able to start the server even if they plug in the HDD on their computer provided the drive does not also contain the security key required to decrypt the database.

One way to implement this is an external mount point that contains the keys, with the correct owner and permissions to restrict access to read-only for the owner. Typically this is mysql:mysql owner and group, and 0400 permissions, but should be adapted accordingly if customized in your deployment. This way, in the case of the physical theft of the actual server or the hard drive, the data will still be protected as the keys will be missing.

MariaDB Encryption Key Plugins

In this blog, we describe the File Key Management Encryption Plugin. MariaDB Enterprise Server supports several flavors of key management with TDE:

  • File Key Management Encryption Plugin
    • The key file is stored within the MariaDB Server itself.
    • The best practice is to keep the key file in a separate mount that is not a part of the server.
  • AWS Key Management Encryption Plugin
    • The key file is stored on AWS and managed by AWS.
    • This is very secure but some clients may not have access to the internet from their production servers. This is a good choice if the database is running on AWS.
  • HashiCorp Vault Encryption Plugin
    • HashiCorp Vault and MariaDB
    • HashiCorp (opensource) Vault can be set up within the customer environment and the MariaDB HashiCorp Vault Encryption plugin can access it for getting the secure keys.
    • This is the most secure way to set up encryption keys if internet access is not available.

Process for Enabling TDE

The following are the high-level steps that are required to implement database encryption at rest:

  • Generate one or more encryption keys.
  • Encrypt the key file using AES 256, 196, or 128 bits.
  • Protect the key file so that no one other than the MariaDB process owner (mysql) user can access it.
  • Configure the MariaDB config file to implement encryption parameters.
    • This will start to execute background encryption threads of the entire database and other objects that we configure to be encrypted.
  • Verify the progress of the encryption by querying specific Information Schema tables.
  • Verify the physical data files are actually encrypted and unreadable.

Remember that to deploy encryption for production workloads, additional planning and implementation may be needed.

Generating Keys

Some environments require keys to be managed from a secure host. In this blog, we keep things simple so you can see how the feature works. Though not described here, it is highly recommended that the key be mounted to external storage so that even if someone were to steal the server itself, the server wouldn’t be able to start because of the missing key.

For the sake of this blog, we will be creating a secure folder within our MariaDB VM and will protect it the best we can (not recommended!).

We are going to create a new folder under /etc/mysql/encryption to store our keys:

$ mkdir -p /etc/mysql/encryption
$ echo "1;"$(openssl rand -hex 32) > /etc/mysql/encryption/keyfile

The above generates a random 32 byte key to the /etc/mysql/encryption/keyfile file prefixed with  “1;”. A 32 byte key is good for AES 256 bit encryption.

Let’s verify the keys:

$ cat /etc/mysql/encryption/keyfile
1;41c32aba5037876ae014f9a17c4bbf1f1de1266026ad77adfee502eb344dc59c

The key which will be used for encrypting is now ready. But before we can use this, to make it secure, we need to encrypt the key itself!

To generate another key, we can either generate the key randomly or key in a password. It’s up to us.

Let’s generate a random key and then encrypt our key file /etc/mysql/encryption/keyfile using this new key:

$ openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key
$ openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/encryption/keyfile.key -in /etc/mysql/encryption/keyfile -out /etc/mysql/encryption/keyfile.enc

The above command has encrypted our key file with the new encryption key, so all key IDs and all encryption keys in the key file are now encrypted.

We can now remove the original unencrypted key file:

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

$ ls -lrt /etc/mysql/encryption/
total 8
-rw-r--r--. 1 root root 257 Mar 22 17:43 keyfile.key
-rw-r--r--. 1 root root  96 Mar 22 17:43 keyfile.enc

Finally, we need to change the ownership of the /etc/mysql folder and all the files within it to be owned by mysql:mysql user and group and set permission to read-only and no permission for group / global. (Adapt for the correct user/group if customized in your install, such as when deploying from binary tarball.)

$ chown -R mysql:mysql /etc/mysql
$ chmod -R 500 /etc/mysql
$ ls -lRt /etc/mysql
/etc/mysql:
total 0
dr-x------. 2 mysql mysql 44 Mar 22 17:45 encryption

/etc/mysql/encryption:
total 8
-r-x------. 1 mysql mysql  96 Mar 22 17:43 keyfile.enc
-r-x------. 1 mysql mysql 257 Mar 22 17:43 keyfile.key

The key files are now secure with proper ownership and permissions. We are ready for the encryption of the database at rest.

Enable Encryption Within MariaDB

Before we encrypt the database, let’s do a few quick tests. The simple SELECT statement retrieves the data as per normal:

MariaDB [testdb]> select * from employee;
+----+--------------+
| id | c1           |
+----+--------------+
|  1 | Roger Rabbit |
|  2 | Peter Pan    |
|  3 | Bugs Bunny   |
+----+--------------+
3 rows in set (0.002 sec)

Let’s view the data stored within the raw file /var/lib/mysql/testdb/employee.ibd.

$ strings /var/lib/mysql/testdb/employee.ibd | head -20
infimum
supremum
Roger Rabbit
Peter Pan
Bugs Bunny

The data is clearly visible in the open text as expected.

Now we can implement the encryption configuration and restart MariaDB Server.

Edit the /etc/my.cnf.d/server.cnf file and add the following in the [mariadb] section as follows:

plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
file_key_management_encryption_algorithm = AES_CTR

innodb_encrypt_tables = FORCE
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables = ON

encrypt_tmp_disk_tables = ON
encrypt_tmp_files = ON
encrypt_binlog = ON
aria_encrypt_tables = ON

innodb_encryption_threads = 4
innodb_encryption_rotation_iops = 2000

The first section of the config:

  • loads the file_key_management plugin
  • defines the path to the key file_key_management_filename = /etc/mysql/encryption/keyfile.enc
  • defines the path to the encrypted key file file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
  • specifies the encryption algorithm to be used file_key_management_encryption_algorithm = AES_CTR (Note: AES_CTR is only available if the mariadbd binary uses OpenSSL. If the mariadbd binary uses yaSSL or wolfSSL, then only AES_CBC is available.)

The second section:

  • enables forced encryption of all InnoDB tables with innodb_encrypt_tables = FORCE
  • encrypts the InnoDB redo logs with the help of innodb_encrypt_log = ON
  • enables encryption of user-created InnoDB temporary tables innodb_encrypt_temporary_tables = ON

The third section:

  • encrypts the temporary tables on disk which are “Aria” storage engine based encrypt_tmp_disk_tables = ON
  • encrypts temporary files encrypt_tmp_files = ON,
  • encrypts the binary logs encrypt_binlog = ON
  • encrypts the ARIA tables aria_encrypt_tables = ON

The fourth section:

  • sets the number of background threads that will encrypt/decrypt the data innodb_encryption_threads = 4
  • sets the IOPS setup to speed up the process with the help of innodb_encryption_rotation_iops = 2000

We’ve enabled encryption but must restart the server to start the tables encrypting in the background.

Start and Monitor Encryption

Let’s restart the MariaDB Server using systemctl restart mariadb  to see the tables encrypting. This is a background process and does not impact the normal usage of the database.

We can monitor the progress of the background encryption by executing the following:

MariaDB [none]> SELECT CASE WHEN INSTR(NAME, '/') = 0 
                   THEN '01-SYSTEM TABLESPACES'
                   ELSE CONCAT('02-', SUBSTR(NAME, 1, INSTR(NAME, '/')-1)) END 
                     AS "Schema Name",
         SUM(CASE WHEN ENCRYPTION_SCHEME > 0 THEN 1 ELSE 0 END) "Tables Encrypted",
         SUM(CASE WHEN ENCRYPTION_SCHEME = 0 THEN 1 ELSE 0 END) "Tables Not Encrypted"
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
GROUP BY CASE WHEN INSTR(NAME, '/') = 0 
                   THEN '01-SYSTEM TABLESPACES'
                   ELSE CONCAT('02-', SUBSTR(NAME, 1, INSTR(NAME, '/')-1)) END
ORDER BY 1;
;

+-----------------------+------------------+----------------------+
| Schema Name           | Tables Encrypted | Tables Not Encrypted |
+-----------------------+------------------+----------------------+
| 01-SYSTEM TABLESPACES |                0 |                    1 |
| 02-mysql              |                1 |                    3 |
| 02-testdb             |                1 |                    1 |
+-----------------------+------------------+----------------------+
3 rows in set (0.001 sec)

The above output shows that the tables/tablespaces are being encrypted. Wait until the output shows zero under the “Tables Not Encrypted” column, then we can be sure that all the objects have been encrypted successfully.

Once the encryption has completed, the above SQL will return the following output.

MariaDB [none]> SELECT CASE WHEN INSTR(NAME, '/') = 0 
                   THEN '01-SYSTEM TABLESPACES'
                   ELSE CONCAT('02-', SUBSTR(NAME, 1, INSTR(NAME, '/')-1)) END 
                     AS "Schema Name",
         SUM(CASE WHEN ENCRYPTION_SCHEME > 0 THEN 1 ELSE 0 END) "Tables Encrypted",
         SUM(CASE WHEN ENCRYPTION_SCHEME = 0 THEN 1 ELSE 0 END) "Tables Not Encrypted"
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
GROUP BY CASE WHEN INSTR(NAME, '/') = 0 
                   THEN '01-SYSTEM TABLESPACES'
                   ELSE CONCAT('02-', SUBSTR(NAME, 1, INSTR(NAME, '/')-1)) END
ORDER BY 1;

+-----------------------+------------------+----------------------+
| Schema Name           | Tables Encrypted | Tables Not Encrypted |
+-----------------------+------------------+----------------------+
| 01-SYSTEM TABLESPACES |                1 |                    0 |
| 02-mysql              |                4 |                    0 |
| 02-testdb             |                2 |                    0 |
+-----------------------+------------------+----------------------+
3 rows in set (0.002 sec)

All the above tables under mysql.* containing data have been successfully encrypted.

Let’s repeat our earlier test:

MariaDB [testdb]> SELECT * FROM employee;
+----+--------------+
| id | c1           |
+----+--------------+
|  1 | Roger Rabbit |
|  2 | Peter Pan    |
|  3 | Bugs Bunny   |
+----+--------------+
3 rows in set (0.002 sec)

The data is still accessible without any special technique. As long as the user has access grants to the table, the data is accessible. As stated previously, the traffic over the network (data-in-transit) is not encrypted unless SSL/TLS is configured for the user accounts as explained here.

Let’s view the data stored inside the raw InnoDB IBD file /var/lib/mysql/testdb/employee.ibd.

[root@mariadb-201 testdb]#  strings employee.ibd | head -20
.*0/
X<!J
Go7V
yKYxz
`5al
)(x5*
"%Lwb
-^#c
Z4}8vh
rO>$p
xw&}Q
~^>$$
gMxa
>zy_\
NY]d
`WTF
VO}Iw)
 !Y9j
o<$b
\nsP

We can see that even though the user can query the table using SELECT, the data in the data files is indeed encrypted and unreadable. If anyone tries to use these data files within their own server, they will not be able to because these files are encrypted using a secret key.

Removing TDE

It’s worth noting that binlogs that have been previously encrypted, will remain encrypted. Keep the encryption key safe in case the binlogs need to be decrypted for point-in-time recovery or any other purpose.

That being said, the easiest way to remove encryption for InnoDB tables is by simply executing the following SET GLOBAL statement using the MariaDB client.

SET GLOBAL innodb_encrypt_tables = OFF;

Different steps are used for Aria tables.

Once done, verify the query output to ensure all the tables have been unencrypted. Then set the following in the server.cnf file and restart MariaDB.

innodb_encrypt_tables = OFF
innodb_encrypt_log = OFF
aria_encrypt_tables = OFF
encrypt_tmp_disk_tables = OFF
innodb_encrypt_temporary_tables = OFF
encrypt_tmp_files = OFF
encrypt_binlog = OFF

innodb_encryption_rotation_iops = 2000
innodb_encryption_threads = 4

Once the server has restarted successfully, monitor the progress using the same SQL as previously:

MariaDB [none]> SELECT CASE WHEN INSTR(NAME, '/') = 0 
                   THEN '01-SYSTEM TABLESPACES'
                   ELSE CONCAT('02-', SUBSTR(NAME, 1, INSTR(NAME, '/')-1)) END 
                     AS "Schema Name",
         SUM(CASE WHEN ENCRYPTION_SCHEME > 0 THEN 1 ELSE 0 END) "Tables Encrypted",
         SUM(CASE WHEN ENCRYPTION_SCHEME = 0 THEN 1 ELSE 0 END) "Tables Not Encrypted"
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
GROUP BY CASE WHEN INSTR(NAME, '/') = 0 
                   THEN '01-SYSTEM TABLESPACES'
                   ELSE CONCAT('02-', SUBSTR(NAME, 1, INSTR(NAME, '/')-1)) END
ORDER BY 1;

Keep the encryption key safe in case the binlogs need to be decrypted for point-in-time recovery or any other purpose.

Once encryption has been removed from all the tables,  remove all the encryption related configuration from the server.cnf file, restart the MariaDB service using systemctl restart mariadb,.

For More Information

TDE provides data-at-rest encryption that is transparent to clients and applications and provides protection against physical theft of data. MariaDB’s File Key Management Encryption Plugin is only one method for implementing TDE on MariaDB databases. In my next blog I will talk about implementing TDE using Hashicorp Vault for security and protection of the encryption keys.

MariaDB Enterprise Server includes many other features in addition to TDE that can be used to enhance security including MaxScale filtering capabilities, Enterprise Audit, and access controls such as simple password check plugin, grants and privileges.

Related links: