🥇 Encryption in MySQL: Key Storage

As we approach the start of a new course enrollment "Databases" We have prepared a translation of a useful article for you.

Transparent Data Encryption (TDE) has been around in MySQL for quite some time. But have you ever wondered how it works under the hood and what impact TDE can have on your server? In this series of articles, we will explore how TDE operates internally. We will start with key storage, as it is necessary for any encryption to function. Then, we will take a detailed look at how encryption works in Percona Server for MySQL/MySQL and what additional features are available in Percona Server for MySQL. Percona Server for MySQL MySQL Keyring

Keyring is a plugin that allows the server to request, create, and delete keys in a local file (keyring_file) or on a remote server (for example, in HashiCorp Vault). Keys are always cached locally to speed up their retrieval.

Plugins can be divided into two categories:

Local storage. For example, a local file (we refer to this as a file-based keyring).

Remote storage. For example, Vault Server (we refer to this as a server-based keyring).
This distinction is important because different types of storage behave slightly differently not only in storing and retrieving keys but also during startup.

When using file-based storage, all the contents of the storage are loaded into the cache at startup: key id, key user, key type, and the key itself.

In the case of server-based storage (for example, a Vault server), only key id and key user are loaded at startup, which means retrieving all keys does not slow down the startup process. Keys are loaded lazily—meaning the key itself is retrieved from the Vault only when it is actually needed. Once loaded, the key is cached in memory so there is no need to retrieve it again via TLS connections to the Vault Server. Next, we will look at what information is present in the key storage.

Key information contains the following:

key id

— the key identifier, for example: INNODBKey-764d382a-7324-11e9-ad8f-9cb6d0d5dc99-1
key type
— the type of key based on the encryption algorithm used, possible values: 'AES', 'RSA', or 'DSA'. key length
— the length of the key in bytes; AES can be 16, 24, or 32, RSA can be 128, 256, 512, and DSA can be 128, 256, or 384. — the key owner. If the key is a system key, such as the Master Key, this field is empty. If the key is created using keyring_udf, this field indicates the key's owner.
user the key itself
the key itself

The key is uniquely identified by a pair: key_id, user.

There are also differences in the storage and deletion of keys.

File storage works faster. One might assume that the key store is just a single write of the key to a file, but that's not the case—there are more operations involved. When making any changes to the file storage, a backup of the entire content is first created. Let's say the file is named my_biggest_secrets, then the backup will be my_biggest_secrets.backup. Next, the cache is modified (keys are added or removed), and if everything is successful, the cache is flushed to the file. In rare cases, such as a server failure, you may see this backup file. The backup file is deleted during the next key loading (usually after a server restart).

When saving or deleting a key in the server storage, the storage must connect to the MySQL server with the commands "send the key" / "request key deletion."

Let's return to the server startup speed. In addition to the storage itself affecting startup speed, there is also the issue of how many keys from the storage need to be fetched at startup. This is particularly crucial for server storages. At startup, the server checks which key is needed for encrypted tables/tablespaces and requests the key from the storage. On a "clean" server with Master Key encryption, there should be one Master Key that needs to be extracted from the storage. However, more keys may be needed, for example, when a backup is restored from the primary server to a standby server. In such cases, Master Key rotation should be planned. This will be discussed in future articles, but here I want to note that a server using multiple Master Keys may start up slightly longer, especially when using a server key storage.

Now let’s talk a bit more about keyring_file. When I was developing keyring_file, I was also concerned about how to check for changes in keyring_file during server operation. In version 5.7, the check was performed based on file statistics, which was not an ideal solution, and in version 8.0, it was replaced with a SHA256 checksum.

Upon the first launch, the keyring_file computes file statistics and a checksum, which are saved by the server, and changes are only applied if they match. When the file is modified, the checksum is updated.

We have already covered many issues regarding key storage. However, there is one more important topic that is often overlooked or misunderstood — the separation of keys by servers.

What do I mean? Each server (for example, Percona Server) in a cluster must have a separate location on the Vault server where Percona Server must store its keys. Each Master Key saved in the storage contains the GUID of the Percona Server within its identifier. Why is this important? Imagine that you have only one Vault Server and all Percona Servers in the cluster use this single Vault Server. The problem seems obvious. If all Percona Servers used a Master Key without unique identifiers, for instance, id = 1, id = 2, etc., then all servers in the cluster would use the same Master Key. This is what the GUID provides — a separation between servers. So why talk about separating keys between servers if there is already a unique GUID? There is another plugin — keyring_udf. With this plugin, a user of your server can store their keys on the Vault server. The problem arises when a user creates a key, for example, on server1, and then tries to create a key with the same identifier on server2, such as:

--server1:
select keyring_key_store('ROB_1','AES',"123456789012345");
1
--1 means success
--server2:
select keyring_key_store('ROB_1','AES',"543210987654321");
1

Wait. Both servers are using the same Vault Server, shouldn't the keyring_key_store function fail on server2? Interestingly, if you try the same on one server, you will get an error:

--server1:
select keyring_key_store('ROB_1','AES',"123456789012345");
1
select keyring_key_store('ROB_1','AES',"543210987654321");
0

Correct, ROB_1 already exists.

Let's first discuss the second example. As we mentioned earlier, keyring_vault or any other storage plugin (keyring) caches all key identifiers in memory. Thus, after creating a new key, ROB_1 is added to server1, and besides sending this key to Vault, the key is also added to the cache. Now, when we try to add the same key a second time, keyring_vault checks whether this key exists in the cache and returns an error.

In the first case, the situation is different. The servers server1 and server2 have separate caches. After adding ROB_1 to the key cache on server1 and the Vault server, the key cache on server2 is not synchronized. The cache on server2 does not contain the key ROB_1. Therefore, the key ROB_1 is written to keyring_key_store and to the Vault server, which effectively overwrites the previous value (!). Now the key ROB_1 on the Vault server equals 543210987654321. Interestingly, the Vault server does not block such actions and simply overwrites the old value.

Now we see why server separation on Vault can be important — when you use keyring_udf and want to store keys in Vault. How can such separation be ensured on the Vault server?

There are two ways to achieve separation on Vault. You can create different mount points for each server or use different paths within a single mount point. This is best illustrated with examples. So, let's first look at separate mount points:

--server1:
vault_url = http://127.0.0.1:8200
secret_mount_point = server1_mount
token = (...)
vault_ca = (...)

--server2:
vault_url = http://127.0.0.1:8200
secret_mount_point = sever2_mount
token = (...)
vault_ca = (...)

Here it is clear that server1 and server2 use different mount points. When separating paths, the configuration will look as follows:

--server1:
vault_url = http://127.0.0.1:8200
secret_mount_point = mount_point/server1
token = (...)
vault_ca = (...)
--server2:
vault_url = http://127.0.0.1:8200
secret_mount_point = mount_point/sever2
token = (...)
vault_ca = (...)

In this case, both servers use the same mount point "mount_point" but different paths. When the first secret is created on server server1 at this path, the Vault server automatically creates the directory "server1". The same goes for server2. When you delete the last secret in mount_point/server1 or mount_point/server2, the Vault server also removes these directories. If you use path separation, you should create only one mount point and change the configuration files so that the servers use separate paths. The mount point can be created with an HTTP request. You can do this using CURL as follows:

curl -L -H "X-Vault-Token: TOKEN" –cacert VAULT_CA
--data '{"type":"generic"}' --request POST VAULT_URL/v1/sys/mounts/SECRET_MOUNT_POINT

All fields (TOKEN, VAULT_CA, VAULT_URL, SECRET_MOUNT_POINT) correspond to the parameters in the configuration file. Of course, you can use Vault utilities to do the same thing. But it's easier to automate the creation of the mount point this way. I hope this information proves useful to you, and we'll see you in the next articles in this series.