How to Implement Encryption at Rest Using Hashicorp Vault and MariaDB
With the MariaDB Hashicorp Vault KMS plugin, MariaDB customers can use the Hashicorp Vault KMS to hold encryption keys in a sealed “secrets” Vault and implement key rotation. Data at rest is encrypted and can only be accessed by connecting to the Vault. The Vault resides on an external server or cluster of servers and must be “unsealed” by an authorized user using “unseal keys” before the encryption keys can be retrieved from the Vault. Even if the database server is physically stolen, the encryption keys will not be included.
In this blog, I will demonstrate how to:
- set up, configure, and test TDE using the MariaDB Hashicorp Vault KMS plugin
- use key rotation with the MariaDB Hashicorp Vault KMS plugin
The instructions on this blog post were tested with the open-source version of Hashicorp Vault. The enterprise version of Hashicorp Vault is also supported.
The scope of this blog is to cover how to use the MariaDB Vault plugin, not how the Vault internals work.
TDE with key management 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. For a detailed discussion of what TDE is and how it protects data, see my previous blog.
This blog describes one way to deploy data-at-rest encryption with MariaDB Enterprise Server using the Hashicorp Vault plugin. There are other methods but they are beyond the scope of this blog.
Protection and Transparency
TDE (Transparent Data Encryption) is for data-at-rest and is performed and managed by the MariaDB server. TDE requires a security key that the server uses to encrypt and decrypt the data files. The clients and apps don’t have to worry about the key.
With the Hashicorp Vault KMS plugin, encryption keys can be held in an external server or a cluster of servers (used for HA) rather than on the database server or an external mount. HashiCorp Vault, either Vault Open Source or Vault Enterprise, can be set up within the customer environment, and the MariaDB hashicorp_key_management plugin can access it to get the secure keys. The key is provided to the MariaDB plugin on demand.
With the Hashicorp Vault KMS plugin, we are sure the keys are secured in a safe place and even if someone were to steal the entire MariaDBrack/server, the thief would not be able to start the MariaDB service – let alone read any data.
Assumptions
For the purpose of this blog, we are going to set up two CentOS 7 VMs. One will be used to run Hashicorp Vault while the other will be running MariaDB 10.6 Enterprise Server.
Base assumptions:
- MariaDB 10.6 Enterprise Server is used.
- Hashicorp Vault is used as the KMS for MariaDB encryption.
rootuser access for both MariaDB and Vault servers is available.- Refer to Vault’s hardening documentation
Note: Vault runs on port 8200, which must be opened for network traffic between MariaDB and Vault nodes.
Implementing TDE Overview
The following is an overview of the high-level steps for implementing database encryption-at-rest using the Hashicorp Vault.
- Install and Configure the Hashicorp Vault
- Configure the
vault.servicefile forsystemdhandling - Startup a Vault server
- Initialize the Vault keys and Token
- Generate multiple Security Keys
- Upload the keys to the Vault
- Configure MariaDB Hashicorp Vault plugin within
/etc/my.cnf.d/server.cnffile - Verify the progress of the encryption within the MariaDB server using MariaDB CLI
- Verify the physical data files are actually encrypted and unreadable.
The Vault architecture we are trying to achieve will look like this:
Install Hashicorp Vault
Directory Setup
Create the following folders on the Vault server in advance
$ mkdir -p /hashicorp/vault/bin $ mkdir -p /hashicorp/data $ mkdir -p /hashicorp/config
/hashicorp/vault/binwill contain thevaultexecutable, which is only one file by the namevault/hashicorp/datawill be used as the backend storage to store “secrets” that are uploaded by the operator or users. The secrets will include the MariaDB encryption keys./hashicorp/configwill store the configuration file in JSON format.- Vault supports both JSON and HCL “https://github.com/hashicorp/hcl” format. We will use
hclfor this blog.
Download Vault
Download the Vault zip file for the targeted OS from https://www.vaultproject.io/downloads, we will be downloading it for Linux.
The downloaded file is vault_1.8.5_linux_amd64.zip. Move it to a folder of your choice. We will use /hashicorp/vault/bin which was created earlier and will unzip the Vault binaries to extract the executable vault binary file. You may need to install unzip using yum install unzip
The contents of the folder will look like this:
$ mv vault_1.8.5_linux_amd64.zip /hashicorp/vault/bin $ cd /hashicorp/vault/bin $ unzip vault_1.8.5_linux_amd64.zip Archive: vault_1.8.5_linux_amd64.zip inflating: vault
Confirm that the vault has read/execute permissions:
$ ls -lrt total 239620 -rwxr-xr-x 1 root root 178690334 Nov 3 17:42 vault -rw-r--r-- 1 root root 66677285 Nov 4 10:10 vault_1.8.5_linux_amd64.zip
Set the path /hashicorp/vault/bin under the script /etc/profile and import the updated profile into the current session:
$ echo "export PATH=$PATH:/hashicorp/vault/bin" >> /etc/profile $ . /etc/profile
Note: Take note of the space between the dot and /etc/profile .
Once the above is done, the vault script can be executed from any location. Let’s test if vault is accessible:
$ vault --version Vault v1.8.5 (647eccfe0bd5817bdd8628f3c3171402dfc8a8fc)
vault-config.hcl file
The config file needs to be created as /hashicorp/config/vault-config.hcl file with the following content:
storage "file" {
path = "/hashicorp/data"
}
listener "tcp" {
address = "0.0.0.0:8200"
tls_disable = 1
}
api_addr = "http://127.0.0.1:8200"
max_lease_ttl = "730h"
default_lease_ttl = "730h"
max_versions=2
ui = true
log_level = "Trace"
storageis defined as"file"with a path. This is where the backend data for the keys will be stored after encryption. Refer to the vaultproject documentation for a complete list of options available.
Example for an S3 bucket as the backend storage:
storage "s3" {
access_key = "abcd1234"
secret_key = "defg5678"
bucket = "my-bucket"
}
ui = true
listeneris defined astcpand is listening to0.0.0.0:8200which means any source IP but on port8200.api_addris pointing to the Vault server’s public IP address and8200port and is used for external clients trying to connect to the Vault via REST API.tls_disable = 1is used to disable the TLS communication between clients and the Vault, for production environments it’s highly recommended to enable tls_support usingtls_disable = 0and configure the required server/client certificates. To keep this blog focused on Vault instructions, we are going to keep the TLS disabled.max_lease_ttland thedefault_lease_ttldefine the Time To Live (ttl) for the tokens. Once MariaDB or a client has fetched the token, the lease starts. We have configured this to be 730 hours which means 1 month. This can be increased or decreased based on the security policies of the organization.
Once the lease expires, the tokens will no longer be available and the operator/automated batch job must renew the lease for another month or another period using Vault commands. Refer to the vaultproject Lease page for details.max_versions=2ensures that at least two versions of the keys are kept. This is used when keys are rotated and the old version is used to decrypt while the new version is used to re-encrypt the data.ui = trueis to enable the GUI management console. Which can be accessed from the browser and going to thehttp://192.168.56.101:8200URL from any remote browser. Here, 192.168.56.100 is the IP of the Vault server.
log_levelsets the logging level astraceto log more details for troubleshooting in case needed. Available options are, in the order of increasing detail,trace,debug,info,warn, anderr. The default log level isinfo. Refer to the Hashicorp Server Log troubleshooting page for details.
Confirm Setup
We have everything in place and can confirm it by starting the Vault service by executing vault server -config /hashicorp/config/vault-config.hcl :
$ vault server -config /hashicorp/config/vault-config.hcl ==> Vault server configuration: Api Address: http://192.168.56.101:8200 Cgo: disabled Cluster Address: https://192.168.56.101:8201 Go Version: go1.15.10 Listener 1: tcp (addr: "0.0.0.0:8200", cluster address: "0.0.0.0:8201", max_request_duration: "1m30s", max_request_size: "33554432", tls: "disabled") Log Level: info Mlock: supported: true, enabled: true Recovery Mode: false Storage: file Version: Vault v1.8.5 Version Sha: 4e222b85c40a810b74400ee3c54449479e32bb9f ==> Vault server started! Log data will stream in below: 2021-03-31T18:32:45.360+0800 [INFO] proxy environment: http_proxy= https_proxy= no_proxy=
With the confirmation complete, we want to stop the service, so that we can configure and manage vault using systemd such as systemctl start vault, systemctl stop vault, etc.
Use ctrl-c from the above process to stop the running process.
Configure Vault Service
The next step is to define a Vault service file on the CentOS/RHEL OS. Create the service file using:
vi /etc/systemd/system/vault.service
Add the following block to the file:
[Unit] Description=Hashicorp Vault Service Requires=network-online.target After=network-online.target ConditionFileNotEmpty=/hashicorp/config/vault-config.hcl [Service] KillSignal=SIGTERM EnvironmentFile=-/etc/sysconfig/vault Environment=GOMAXPROCS=2 Restart=on-failure # Vault startup command line, all the logs will be redirected to the syslog ExecStart=/bin/sh -c '/hashicorp/vault/bin/vault server -config=/hashicorp/config/vault-config.hcl' LimitMEMLOCK=infinity ExecReload=/bin/kill -HUP $MAINPID
Once the file is saved, we need to reload the Linux’s systemd daemon so the new service file is loaded and ready. This is done by executing:
systemctl daemon-reload
We’ll use systemctl start vault to start the service properly this time:
$ systemctl start vault $ ps -aux | grep vault root 2184 0.0 0.0 115404 1424 ? Ss 20:28 0:00 /bin/sh -c /hashicorp/vault/bin/vault server -config=/hashicorp/config/vault-config.hcl root 2185 3.0 12.9 842300 243116 ? SLl 20:28 0:00 /hashicorp/vault/bin/vault server -config=/hashicorp/config/vault-config.hcl
Review Logs
We can see the service started without problems. Let’s review the Vault logs using the journalctl -b --no-pager -u vault command:
$ journalctl -b --no-pager -u vault
-- Logs begin at Thu 2021-04-01 12:01:36 +08, end at Thu 2021-04-01 12:02:48 +08. --
Apr 01 12:01:58 localhost.localdomain systemd[1]: Started Hashicorp Vault Service.
Apr 01 12:01:58 localhost.localdomain sh[1483]: 2021-04-01T12:01:58.376+0800 [INFO] proxy environment: http_proxy= https_proxy= no_proxy=
Apr 01 12:01:58 localhost.localdomain sh[1483]: 2021-04-01T12:01:58.376+0800 [DEBUG] core: set config: sanitized config={"api_addr":"http://192.168.56.101:8200","cache_size":0,"cluster_addr":"","cluster_cipher_suites":"","cluster_name":"","default_lease_ttl":2628000000000000,"default_max_request_duration":0,"disable_cache":false,"disable_clustering":false,"disable_indexing":false,"disable_mlock":false,"disable_performance_standby":false,"disable_printable_check":false,"disable_sealwrap":false,"disable_sentinel_trace":false,"enable_ui":true,"listeners":[{"config":{"address":"0.0.0.0:8200","tls_disable":1},"type":"tcp"}],"log_format":"unspecified","log_level":"Trace","max_lease_ttl":2628000000000000,"pid_file":"","plugin_directory":"","raw_storage_endpoint":false,"seals":[{"disabled":false,"type":"shamir"}],"storage":{"cluster_addr":"","disable_clustering":false,"redirect_addr":"http://192.168.56.101:8200","type":"file"}}
Apr 01 12:01:58 localhost.localdomain sh[1483]: 2021-04-01T12:01:58.376+0800 [DEBUG] storage.cache: creating LRU cache: size=0
Apr 01 12:01:58 localhost.localdomain sh[1483]: 2021-04-01T12:01:58.413+0800 [DEBUG] cluster listener addresses synthesized: cluster_addresses=[0.0.0.0:8201]
...
...
...
Since we defined a Trace log level, a lot of data will be logged. The log level can be adjusted later to info or warn based on logging requirements.
Check Vault Status and Define API Path
Let’s check the Vault status:
$ vault status Error checking seal status: Get "https://127.0.0.1:8200/v1/sys/seal-status": http: server gave HTTP response to HTTPS client
We need to define the API path and add it to the /etc/profile so that it is always available. The IP is the localhost IP and we can set it up as follows:
$ export VAULT_ADDR=http://127.0.0.1:8200 $ echo "export VAULT_ADDR=http://127.0.0.1:8200" >> /etc/profile
Initialize the Vault Keys and Token
We need to initialize the Vault for the first time so that the tokens and security keys are generated.
Initializing the Vault generates its access token and seal/unseal keys and generates the init.file file in the /hashicorp/vault directory. This file will contain the seal/unseal keys and an access token. Initialize the Vault with:
$ vault operator init > /hashicorp/vault/init.file
We can now check the Vault status:
$ vault status Key Value --- ----- Seal Type shamir Initialized true Sealed true Total Shares 5 Threshold 3 Unseal Progress 0/3 Unseal Nonce n/a Version 1.8.5 Storage Type file HA Enabled false
Copy the init.file file to a secure location as a backup. Make sure the backup file has been secured and no one has access to it other than the responsible personnel. It is important to strongly secure the original /hashicorp/vault/init.file with strong access control like performing chmod 400 /hashicorp/vault/init.file on the Vault server since it contains the keys that can unseal the entire Vault.
Review the init.file :
$ cat /hashicorp/vault/init.file Unseal Key 1: pjGeGw17vdPsk7GQ8Nnv4dYS2MSJIyjpQQTcLJc3RlwY Unseal Key 2: Ro1phaFG0F/jooJbswOi1e9g/s9bSmxK6CTIHwCtYCXm Unseal Key 3: +2ZQk+wPPsJwYa0GFl8d8S13E9L2QJTZsrwLk6nVAGdG Unseal Key 4: tfjbQj1cK3m+aJBOoXD/Aga+IbQRjCsGc8YAUlNlfdjJ Unseal Key 5: oopA7zV7Bn0FQGPnl4znJ1OYKlGRA/xGdxXhu95Q1sQm Initial Root Token: s.jIUH6bxqriDg0b3lCIA8lJw0 Vault initialized with 5 key shares and a key threshold of 3. Please securely distribute the key shares printed above. When the Vault is re-sealed, restarted, or stopped, you must supply at least 3 of these keys to unseal it before it can start servicing requests. Vault does not store the generated master key. Without at least 3 keys to reconstruct the master key, Vault will remain permanently sealed! It is possible to generate new unseal keys, provided you have a quorum of existing unseal keys shares. See "vault operator rekey" for more information.
We can see there are five (5) Unseal Keys and one Initial Root Token. This token is going to be used in the MariaDB server config file server.cnf to connect and talk to the Vault. The Unseal Keys are used to unseal the Vault. When the Vault service is restarted or stopped, it automatically gets sealed and no one can access it unless the operator unseals it. The Vault can also be manually re-sealed.
Test the Seal
To unseal the Vault, the operator must provide at least 3 of the 5 keys. Let’s do a quick test to demonstrate this.
The current status of the Vault, (vault status), shows the following two lines:
Sealed true Unseal Progress 0/3
This shows that the Vault is currently in a sealed position. If we try to pull any secrets from it, it will not allow any access.
We will try to create a new secrets Vault with the name mariadb, it’s going to be a KV (key-value) pair:
$ vault secrets enable -path /mariadb -version=2 kv Error enabling: Error making API request. URL: POST http://192.168.56.101:8200/v1/sys/mounts/mariadb Code: 503. Errors: * error performing token check: Vault is sealed
Unseal the Vault
We can see clearly that the Vault is sealed. Let’s unseal it by providing any of the three unseal keys from the init.file:
$ vault operator unseal pjGeGw17vdPsk7GQ8Nnv4dYS2MSJIyjpQQTcLJc3RlwY Key Value --- ----- Seal Type shamir Initialized true Sealed true Total Shares 5 Threshold 3 Unseal Progress 1/3 Unseal Nonce 23755f43-2bfa-0b18-d708-3daff03ff95e Version 1.8.5 Storage Type file HA Enabled false $ vault operator unseal Ro1phaFG0F/jooJbswOi1e9g/s9bSmxK6CTIHwCtYCXm Key Value --- ----- Seal Type shamir Initialized true Sealed true Total Shares 5 Threshold 3 Unseal Progress 2/3 Unseal Nonce 23755f43-2bfa-0b18-d708-3daff03ff95e Version 1.8.5 Storage Type file HA Enabled false $ vault operator unseal +2ZQk+wPPsJwYa0GFl8d8S13E9L2QJTZsrwLk6nVAGdG Key Value --- ----- Seal Type shamir Initialized true Sealed false Total Shares 5 Threshold 3 Version 1.8.5 Storage Type file Cluster Name vault-cluster-6f2f06ac Cluster ID d1b9aedd-e2b8-9434-a53e-6fb0786f3943 HA Enabled false
We can see from the output, every time we unseal with one of the keys, we see the Unseal Progress progresses 1/3, 2/3, and finally on the third attempt, it shows Sealed false. So every time the Vault service is restarted or an API seals the Vault, someone must unseal it before the clients, MariaDB in our case, can access the encryption keys.
Enable a Secrets Vault to Keep Keys
Now that the Vault has been unsealed, let’s enable the secrets Vault so we can upload MariaDB encryption keys to it.
Restricted Access
$ vault secrets enable -path /mariadb -version=2 kv Error enabling: Error making API request. URL: POST http://192.168.56.101:8200/v1/sys/mounts/mariadb Code: 403. Errors: * permission denied
This time we get a permission denied error because it’s a secured environment. We need to log in before we can start using its services.
Log In
To log in, we need to pass the Root Token from the init.file . The value in our /hashicorp/vault/init.file is shown as Initial Root Token: s.jIUH6bxqriDg0b3lCIA8lJw0 . We’ll use that to log in:
$ vault login s.jIUH6bxqriDg0b3lCIA8lJw0 Success! You are now authenticated. The token information displayed below is already stored in the token helper. You do NOT need to run "vault login" again. Future Vault requests will automatically use this token. Key Value --- ----- token s.jIUH6bxqriDg0b3lCIA8lJw0 token_accessor Fc9TUtgg5EWiUPIC0grmfEeu token_duration ∞ token_renewable false token_policies ["root"] identity_policies [] policies ["root"]
Create Vault
Now, finally, let’s create a version 2 Vault as a Key-Value pair, version 2 enables the Vault to store multiple keys history:
$ vault secrets enable -path /mariadb -version=2 kv Success! Enabled the kv secrets engine at: /mariadb/
Let’s see the list of secrets currently available, we can find mariadb secrets Vault mounted:
$ vault secrets list -detailed Path Plugin Accessor Default TTL Max TTL Force No Cache Replication Seal Wrap External Entropy Access Options Description UUID ---- ------ -------- ----------- ------- -------------- ----------- --------- ----------------------- ------- ----------- ---- cubbyhole/ cubbyhole cubbyhole_7935827d n/a n/a false local false false map[] per-token private secret storage bfb0b301-4b81-7414-d169-bcab81afefb3 identity/ identity identity_c167cd3c system system false replicated false false map[] identity store 24df9e7e-e5b3-a98d-8101-56d80fcffb96 mariadb/ kv kv_8a5e5da0 system system false replicated false false map[version:2] n/a e029724f-927b-918f-0d85-7ecf7bccf472 sys/ system system_13a2b436 n/a n/a false replicated false false map[] system endpoints used for control, policy and debugging bd939820-ea73-40fb-d9bf-d102b8b0bcab
The Vault has been created. This is like creating a backend database within the Vault to store our key/value data. For us, it is going to be MariaDB encryption keys.
Add Keys to Vault
We can now generate a random 32-bit encryption key and upload it to the Vault directly using the following method:
$ vault kv put /mariadb/1 data=$(openssl rand -hex 32) Key Value --- ----- created_time 2021-11-13T18:27:24.923683572Z deletion_time n/a destroyed false version 1
The key has been uploaded to the mariadb KV Vault as v1, as we can see the version 1 in the previous output.
Let’s retrieve the key we just created from the Vault:
$ vault kv get mariadb/1
====== Metadata ======
Key Value
--- -----
created_time 2021-11-13T18:27:24.923683572Z
deletion_time n/a
destroyed false
version 1
=== Data ===
Key Value
--- -----
data 439eba6b447f84db460e0792fb9deb178181252337726418a1fc5a7535cb4255
The key is created with version 1 and we can see the key and its value as its stored in the Vault. Remember that the Vault is unlocked. That’s why we can see this information. Only people who have the unlock keys or the authorized clients can see this data.
Rotate Keys
We can now rotate the keys by simply executing the same command that we used to upload the initial key, this time using a different value for the key. Let’s test this out.
$ vault kv put /mariadb/1 data=0123456789ABCDEF0123456789ABCDEF
Key Value
--- -----
created_time 2021-11-13T18:34:28.813686083Z
deletion_time n/a
destroyed false
version 2
We can see the version two has been created for the key ID “/mariadb/1”
We can also fetch all the keys from the Vault, and the keys are decrypted automatically and shown to us because we have the required login and an unsealed Vault. Without proper login and the unseal keys to the Vault, no one can see this data.
To get the latest version of the key in the Vault, use the following command, we can see it fetches the version 2:
$ vault kv get mariadb/1 vault kv get mariadb/1 ====== Metadata ====== Key Value --- ----- created_time 2021-11-13T18:34:28.813686083Z deletion_time n/a destroyed false version 2 === Data === Key Value --- ----- data 0123456789ABCDEF0123456789ABCDEF
To get a list of all the versions of a particular key, use the following command:
$ vault kv metadata get mariadb/1 ========== Metadata ========== Key Value --- ----- cas_required false created_time 2021-11-13T18:27:24.923683572Z current_version 2 delete_version_after 0s max_versions 0 oldest_version 0 updated_time 2021-11-13T18:34:28.813686083Z ====== Version 1 ====== Key Value --- ----- created_time 2021-11-13T18:27:24.923683572Z deletion_time n/a destroyed false ====== Version 2 ====== Key Value --- ----- created_time 2021-11-13T18:34:28.813686083Z deletion_time n/a destroyed false
Enable TLS on the Vault
We are using Hashicorp Vault without TLS, it’s highly recommended to enable TLS.
Follow the Vault documentation at https://learn.hashicorp.com/tutorials/vault/pki-engine for generating the certificates to enable TLS
Enable Encryption within MariaDB
Let’s demonstrate encryption with a very simple database and table testdb.employee.
Before we encrypt the database, we’ll do a few quick tests. The simple SELECT statement retrieves the data as per normal:
MariaDB [testdb]> select * from employee; +----+--------------+ | id | name | +----+--------------+ | 1 | Roger Rabbit | | 2 | Peter Pan | | 3 | Bugs Bunny | +----+--------------+ 3 rows in set (0.001 sec)
Let’s view the data stored within the raw file /var/lib/mysql/testdb/employee.ibd:
$ cat /var/lib/mysql/testdb/employee.ibd | strings | head -20 infimum supremum Roger Rabbit Peter Pan Bugs Bunny
The data in the actual data file is clearly visible in the open text as expected. Now we can proceed to implement the encryption configuration and restart the MariaDB server.
Install the MariaDB Hashicorp Plugin
The plugin must be installed separately just like other tools such as MariaDB-backup, etc. We’re installing on CentOS/RHEL, so we’ll use the YUM package manager.
$ yum -y install MariaDB-hashicorp-key-management Dependencies Resolved ================================================================================================================ Package Arch Version Repository Size ================================================================================================================ Installing: MariaDB-hashicorp-key-management x86_64 10.5.9_6-1.el7_9 mariadb-es-main 31 k Transaction Summary ================================================================================================================ Install 1 Package Total download size: 31 k Installed size: 65 k Downloading packages: MariaDB-hashicorp-key-management-10.5.9_6-1.el7_9.x86_64.rpm | 31 kB 00:00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : MariaDB-hashicorp-key-management-10.5.9_6-1.el7_9.x86_64 1/1 Verifying : MariaDB-hashicorp-key-management-10.5.9_6-1.el7_9.x86_64 1/1 Installed: MariaDB-hashicorp-key-management.x86_64 0:10.5.9_6-1.el7_9 Complete!
Edit the /etc/my.cnf.d/server.cnf file and add the following in the [mariadb] section as follows:
[mariadb] plugin_load_add = hashicorp_key_management hashicorp-key-management-vault-url=http://192.168.56.101:8200/v1/mariadb hashicorp-key-management-token=s.jIUH6bxqriDg0b3lCIA8lJw0 hashicorp-key-management-caching-enabled=ON 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 log_error=server.log
- The
vault-urlabove is pointing to themariadbKV (Key-Value) that we created earlier and the IP points to theVaultserver. The URL also supports TLS using HTTPS. tokenpoints to theRoot Tokenfrom theinit.file, the value for us is shown asInitial Root Token: s.jIUH6bxqriDg0b3lCIA8lJw0tokenis used in the header of all the requests which are sent to the Vault for authentication.
The rest of the parameters are the same as we used in my earlier blog about TDE when using the “File-based key management plugin” as follows.
- 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 - 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 - 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
Note: Remember that the Vault plugin does not unseal the Vault if it’s been sealed by someone. This is to prevent unintentional/unauthorized access to the Vault. The Vault operator must unseal the Vault using the process explained earlier before the MariaDB server can access the Vault and the keys.
Test the Hashicorp Plugin
If we restart the Vault and the MariaDB server now, we will notice that the MariaDB server is unable to start. This is because MariaDB does not have the unseal keys to unseal the Vault for usage, MariaDB does have the root token, but that will only work if the Vault is in an unsealed state.
The MariaDB server log will contain the following error messages which clearly show the Vault is sealed.
2021-11-16 15:19:27 0 [Warning] mariadbd: hashicorp: Hashicorp server error: 503, response: {"errors":["Vault is sealed"]}
2021-11-16 15:19:27 0 [ERROR] mariadbd: hashicorp: Unable to get key data
2021-11-16 15:19:27 0 [Warning] mariadbd: hashicorp: Hashicorp server error: 503, response: {"errors":["Vault is sealed"]}
..
..
2021-11-16 15:19:27 0 [ERROR] Aborting
2021-11-16 15:19:27 server_audit: STOPPED
The Vault must be unsealed by someone with the authorization and the unseal keys contained in the init.file file. This also adds up as an additional security layer that if someone is trying to gain access to the server’s data, we could simply seal the Vault or restart the Vault service and the MariaDB server will not be accessible anymore.
Let’s unseal the Vault using any three of the unseal keys from the init.file file. The following must be done on the Vault server.
$ vault operator unseal XldsS4IgI9tAMGCIirCWPRmRWuIJACR7N8oHYpIuhziI Key Value --- ----- Seal Type shamir Initialized true Sealed true Total Shares 5 Threshold 3 Unseal Progress 1/3 Unseal Nonce 4abaf148-536e-bba9-a150-6611ff46e4bf Version 1.8.5 Storage Type file HA Enabled false $ vault operator unseal T4fT4Hx1LblhUryjSoMteBBT3EPgKsJZUbsXPYEhWesX Key Value --- ----- Seal Type shamir Initialized true Sealed true Total Shares 5 Threshold 3 Unseal Progress 2/3 Unseal Nonce 4abaf148-536e-bba9-a150-6611ff46e4bf Version 1.8.5 Storage Type file HA Enabled false $ vault operator unseal dSOmLJLBZDOxCBzd3CkDkmCH+hmYgdOKsnQJoZIOEf7O Key Value --- ----- Seal Type shamir Initialized true Sealed false Total Shares 5 Threshold 3 Version 1.8.5 Storage Type file Cluster Name vault-cluster-0c50f20b Cluster ID 0c12ab12-d8da-35f6-ea39-2f3802f7ccda HA Enabled false
Once unsealed we can restart MariaDB service now and it should be successful.
Let’s restart the MariaDB server using:
systemctl restart mariadb
The SHOW PLUGINS statement should now show hashicorp_key_management as active:
MariaDB [(none)]> show plugins; +-------------------------------+----------+---------------------+-----------------------------+---------+ | Name | Status | Type | Library | License | +-------------------------------+----------+---------------------+-----------------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | .... | | | | | | .... | | | | | | .... | | | | | | hashicorp_key_management | ACTIVE | ENCRYPTION | hashicorp_key_management.so | GPL | +-------------------------------+----------+---------------------+-----------------------------+---------+
Verify the plugin variables are set based on our configuration:
MariaDB [(none)]> SHOW GLOBAL variables WHERE Variable_name LIKE 'hashicorp%'; +-----------------------------------------------+---------------------------------------+ | Variable_name | Value | +-----------------------------------------------+---------------------------------------+ | hashicorp_key_management_caching_enabled | ON | | hashicorp_key_management_max_retries | 3 | | hashicorp_key_management_timeout | 15 | | hashicorp_key_management_use_cache_on_timeout | ON | | hashicorp_key_management_vault_ca | | | hashicorp_key_management_vault_url | http://192.168.56.101:8200/v1/mariadb | +-----------------------------------------------+---------------------------------------+ 6 rows in set (0.002 sec)
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 | 2 | 2 |
| 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.001 sec)
All the above tables under mysql.* and, testdb.*, containing data have been successfully encrypted.
Let’s repeat our simple encryption test once more:
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. Let’s view the data stored inside the raw InnoDB IBD file /var/lib/mysql/testdb/employee.ibd.
$ strings employee.ibd | head -10 V42.V ]xR_ V_jj :i:`i `Ku@ dR^5 ^'Px2 9re/ "B|+= dg-_
We can see that even though the user can query the table using SELECT and return readable results, the actual data in the data files are encrypted and unreadable.
If someone wanted to use these data files within their own server, it would be impossible because these files are now encrypted using a secret key, and the encryption keys are secure in a Vault sitting in another server.
Even if someone steals the entire server, the thief will not be able to start it because of the missing connection to the Vault which holds the encryption keys. This makes using MariaDB encryption with the Hashicorp Vault plugin the most secure solution for database encryption at rest.
Rotating the Encryption Keys
Remember, earlier we updated the key version to 2 when we first created the keys? That is why the following output shows that the key version in use for these encrypted tables is 2.
MariaDB [none]> SELECT NAME, CURRENT_KEY_VERSION from information_schema.INNODB_TABLESPACES_ENCRYPTION; +----------------------------+---------------------+ | NAME | CURRENT_KEY_VERSION | +----------------------------+---------------------+ | innodb_system | 2 | | mysql/innodb_table_stats | 2 | | mysql/innodb_index_stats | 2 | | mysql/gtid_slave_pos | 2 | | mysql/transaction_registry | 2 | | testdb/employee | 2 | | testdb/tab | 2 | +----------------------------+---------------------+ 7 rows in set (0.005 sec)
We can do a quick check and verify on the Vault server. Indeed the current latest version is 2 which is being used for encryption of the tables.
$ vault kv get mariadb/1
vault kv get mariadb/1
====== Metadata ======
Key Value
--- -----
created_time 2021-11-13T18:34:28.813686083Z
deletion_time n/a
destroyed false
version 2
=== Data ===
Key Value
--- -----
data 0123456789ABCDEF0123456789ABCDEF
Let’s rotate the keys to a new value which will create a new version of the key. Once the key is ready, MariaDB will automatically start to re-encrypt all the tables.
To re-encrypt, MariaDB needs the old key to un-encrypt the existing tables before encrypting them again using the new key version. Let’s test it out.
$ vault kv put /mariadb/1 data=$(openssl rand -hex 32) Key Value --- ----- created_time 2021-11-16T18:21:07.288583549Z deletion_time n/a destroyed false version 3 $ vault kv get mariadb/1 ====== Metadata ====== Key Value --- ----- created_time 2021-11-16T18:21:07.288583549Z deletion_time n/a destroyed false version 3 ==== Data ==== Key Value --- ----- data 0148316caf8f5e72b991ade5ba26c4630786d736e3c47c89b88130cc8b6dc5d9
We have just rotated the `mariadb/1` key to version 3, let’s check the MariaDB side of things. The following output confirms that the key was rotated successfully, and MariaDB is now using the new key for encryption of all the tables. MariaDB Vault plugin will always fetch the latest key version from the Vault.
MariaDB [none]> SELECT NAME, CURRENT_KEY_VERSION from information_schema.INNODB_TABLESPACES_ENCRYPTION; +----------------------------+---------------------+ | NAME | CURRENT_KEY_VERSION | +----------------------------+---------------------+ | innodb_system | 3 | | mysql/innodb_table_stats | 3 | | mysql/innodb_index_stats | 3 | | mysql/gtid_slave_pos | 3 | | mysql/transaction_registry | 3 | | testdb/employee | 3 | | testdb/tab | 3 | +----------------------------+---------------------+ 7 rows in set (0.006 sec)
Restarting The Vault
What happens when the Vault is restarted? As discussed earlier, the Vault is sealed automatically whenever the service is restarted or an API call is made to seal it forcefully. When this happens, an operator must unseal the Vault before MariaDB can work again.
Once the Vault is unsealed and MariaDB has been restarted, the server is up and running without any errors. This shows that without access to the Vault, the MariaDB server will not start and all the data files are encrypted and useless.
Removing TDE
Please note 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, just as with other plugins, we can remove encryption for InnoDB tables by simply setting the values of the following parameters to OFF :
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
Restart the MariaDB server and monitor the progress of the decryption using the same SQL as previously:
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;
Verify that all the tables under all the databases have been decrypted, and then:
- Remove all the encryption related configuration from the
server.cnffile. - Restart the server with
systemctl restart mariadband the encryption is gone.
Different procedures are used for Aria tables.
Conclusion
Hashicorp Vault is very powerful and using it with the MariaDB Hashicorp Vault KMS plugin protects encryption keys from unauthorized access, lets one easily rotate the encryption keys, and also protects against data theft. With the Hashicorp Vault KMS plugin, an extra layer of protection is added to encryption because the encryption keys can be held in an external server or a cluster of servers. Data will be kept encrypted and no one will have access to it without connecting to the Vault.
Thank you!
For More Information
- HashiCorp Vault Encryption Plugin
- Vault Download: https://www.vaultproject.io/downloads
- Plugin Variables: https://mariadb.com/kb/en/hashicorp-key-management-plugin/
- Many simple step by step tutorials are available on the vault project downloads website including Introduction to Vault
- Blog: Transparent Data Encryption (TDE) Using MariaDB’s File Key Management Encryption Plugin
