Percona Tech Days are free, half-day events dedicated to the most popular open source database technologies: PostgreSQL, MongoDB and MySQL.
This talk for MySQL was related to MySQL Encryption with Percona Server for MySQL Using Vault streamed on December 7th, 2022. (I understand this post is delayed a lot but better late than never.)
In today’s world, data security is more important than ever. With cyber attacks becoming increasingly common, it’s essential to ensure that sensitive information is protected from unauthorized access. One way to do this is through data encryption, which involves transforming data into a format that can only be read by authorized parties.
In the realm of open source database technologies, Percona Server for MySQL is a popular choice for many organizations. In this blog, we’ll take a closer look at a recent Percona Tech Days talk that focused on MySQL encryption with Percona Server for MySQL using Vault.
Specifically, we’ll explore encrypting data in transit and at rest, with a particular focus on the latter. We’ll cover the basics of transparent data encryption and walk you through the setup of encryption using the keyring_vault plugin in Percona Server. We’ll also touch on other aspects of data encryption for MySQL database objects, such as backups, logs, and tuple. So if you’re interested in learning more about how to secure your MySQL data, watch the recording!
Review the talk and do comment if you need further information on this subject.
Subscribe to Percona Blogs to stay up-to-date.
Work Log for the Demo included in above talk is included below.
Link: https://www.percona.com/events
# Install Vault
[root@ip-172-31-86-35 ~]# sudo yum install -y yum-utils epel-release
Loaded plugins: fastestmirror
Determining fastest mirrors
* base: download.cf.centos.org
* extras: download.cf.centos.org
* updates: download.cf.centos.org
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
percona-release-noarch | 1.5 kB 00:00:00
percona-release-x86_64 | 2.9 kB 00:00:00
prel-release-noarch | 1.5 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/2): updates/7/x86_64/primary_db | 18 MB 00:00:00
(2/2): percona-release-x86_64/7/primary_db | 1.3 MB 00:00:02
Package yum-utils-1.1.31-54.el7_8.noarch already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package epel-release.noarch 0:7-11 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================================
Installing:
epel-release noarch 7-11 extras 15 k
Transaction Summary
====================================================================================================================================================
Install 1 Package
Total download size: 15 k
Installed size: 24 k
Downloading packages:
epel-release-7-11.noarch.rpm | 15 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : epel-release-7-11.noarch 1/1
Verifying : epel-release-7-11.noarch 1/1
Installed:
epel-release.noarch 0:7-11
Complete!
[root@ip-172-31-86-35 ~]# sudo yum-config-manager --add-repo https://rpm.releases.hashicorp.com/RHEL/hashicorp.repo
Loaded plugins: fastestmirror
adding repo from: https://rpm.releases.hashicorp.com/RHEL/hashicorp.repo
grabbing file https://rpm.releases.hashicorp.com/RHEL/hashicorp.repo to /etc/yum.repos.d/hashicorp.repo
repo saved to /etc/yum.repos.d/hashicorp.repo
[root@ip-172-31-86-35 ~]# sudo yum -y install vault jq
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
epel/x86_64/metalink | 22 kB 00:00:00
* base: download.cf.centos.org
* epel: dl.fedoraproject.org
* extras: download.cf.centos.org
* updates: download.cf.centos.org
http://mirror.es.its.nyu.edu/epel/7/x86_64/repodata/repomd.xml: [Errno 12] Timeout on http://mirror.es.its.nyu.edu/epel/7/x86_64/repodata/repomd.xml: (28, 'Connection timed out after 30001 milliseconds')
Trying other mirror.
epel | 4.7 kB 00:00:00
hashicorp | 1.4 kB 00:00:00
(1/4): hashicorp/7/x86_64/primary | 134 kB 00:00:00
(2/4): epel/x86_64/group_gz | 98 kB 00:00:00
(3/4): epel/x86_64/updateinfo | 1.0 MB 00:00:00
(4/4): epel/x86_64/primary_db | 7.0 MB 00:00:00
hashicorp 948/948
Resolving Dependencies
--> Running transaction check
---> Package jq.x86_64 0:1.6-2.el7 will be installed
--> Processing Dependency: libonig.so.5()(64bit) for package: jq-1.6-2.el7.x86_64
---> Package vault.x86_64 0:1.12.1-1 will be installed
--> Running transaction check
---> Package oniguruma.x86_64 0:6.8.2-2.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================================
Installing:
jq x86_64 1.6-2.el7 epel 167 k
vault x86_64 1.12.1-1 hashicorp 81 M
Installing for dependencies:
oniguruma x86_64 6.8.2-2.el7 epel 181 k
Transaction Summary
====================================================================================================================================================
Install 2 Packages (+1 Dependent package)
Total download size: 81 M
Installed size: 205 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/epel/packages/jq-1.6-2.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Public key for jq-1.6-2.el7.x86_64.rpm is not installed
(1/3): jq-1.6-2.el7.x86_64.rpm | 167 kB 00:00:00
(2/3): oniguruma-6.8.2-2.el7.x86_64.rpm | 181 kB 00:00:00
warning: /var/cache/yum/x86_64/7/hashicorp/packages/vault-1.12.1-1.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID a3219f7b: NOKEY 00:00:00 ETA
Public key for vault-1.12.1-1.x86_64.rpm is not installed
(3/3): vault-1.12.1-1.x86_64.rpm | 81 MB 00:00:01
----------------------------------------------------------------------------------------------------------------------------------------------------
Total 52 MB/s | 81 MB 00:00:01
Retrieving key from https://rpm.releases.hashicorp.com/gpg
Importing GPG key 0xA3219F7B:
[req]
Userid : "HashiCorp Security (HashiCorp Package Signing) <security+packaging@hashicorp.com>"
Fingerprint: e8a0 32e0 94d8 eb4e a189 d270 da41 8c88 a321 9f7b
From : https://rpm.releases.hashicorp.com/gpg
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Importing GPG key 0x352C64E5:
Userid : "Fedora EPEL (7) <epel@fedoraproject.org>"
Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5
Package : epel-release-7-11.noarch (@extras)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oniguruma-6.8.2-2.el7.x86_64 1/3
Installing : jq-1.6-2.el7.x86_64 2/3
Installing : vault-1.12.1-1.x86_64 3/3Generating Vault TLS key and self-signed certificate...
Generating a 4096 bit RSA private key
...............................................................................................................................................++
.........................................................................++
writing new private key to 'tls.key'
-----
Vault TLS key and self-signed certificate have been generated in '/opt/vault/tls'.
Verifying : vault-1.12.1-1.x86_64 1/3
Verifying : oniguruma-6.8.2-2.el7.x86_64 2/3
Verifying : jq-1.6-2.el7.x86_64 3/3
Installed:
jq.x86_64 0:1.6-2.el7 vault.x86_64 0:1.12.1-1
Dependency Installed:
oniguruma.x86_64 0:6.8.2-2.el7
Complete!
[root@ip-172-31-86-35 ~]## Generate keyfiles, ssl and vault config
[root@ip-172-31-86-35 ~]# mkdir /etc/sslkeys
[root@ip-172-31-86-35 ~]# cd /etc/sslkeys
[root@ip-172-31-86-35 sslkeys]# cat ssl.conf
[req]
distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no
[req_distinguished_name]
C = US
ST = NC
L = R
O = Percona
CN = *
[v3_req]
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid,issuer
basicConstraints = CA:TRUE
subjectAltName = @alt_names
[alt_names]
IP = 172.31.86.35
[root@ip-172-31-86-35 sslkeys]# openssl req -config ssl.conf -x509 -days 365 -batch -nodes -newkey rsa:2048 -keyout vault.key -out vault.crt
Generating a 2048 bit RSA private key
# Full configuration options can be found at https://www.vaultproject.io/docs/configuration
...................................+++
# Full configuration options can be found at https://www.vaultproject.io/docs/configuration
...............................+++
# Full configuration options can be found at https://www.vaultproject.io/docs/configuration
writing new private key to 'vault.key'
-----
[root@ip-172-31-86-35 sslkeys]# cat vault.key vault.crt > vault.pem
[root@ip-172-31-86-35 sslkeys]# ls -lhtr
total 16K
-rw-r--r--. 1 root root 314 Nov 21 09:57 ssl.conf
-rw-r--r--. 1 root root 1.7K Nov 21 09:58 vault.key
-rw-r--r--. 1 root root 1.3K Nov 21 09:58 vault.crt
-rw-r--r--. 1 root root 2.9K Nov 21 09:58 vault.pem
[root@ip-172-31-86-35 sslkeys]# cat /etc/vault.d/vault.hcl | grep -v "#"
ui = true
# .bash_profile
disable_mlock = true
storage "file" {
path = "/opt/vault/data"
}
listener "tcp" {
address = "172.31.86.35:8200"
tls_cert_file = "/etc/sslkeys/vault.crt"
tls_key_file = /etc/sslkeys/vault.key"
}
# I had wrong IP in ssl.conf while generating the certificates earlier. Stopped vault, regenerated certs, started vault
# Vault initialization
[root@ip-172-31-86-35 sslkeys]# vault operator init
Get "https://172.31.86.35:8200/v1/sys/seal-status": x509: certificate is valid for 10.0.2.18, not 172.31.86.35
[root@ip-172-31-86-35 sslkeys]# vi /etc/vault.d/vault.
vault.env vault.hcl
[root@ip-172-31-86-35 sslkeys]# vi /etc/vault.d/vault.
vault.env vault.hcl
[root@ip-172-31-86-35 sslkeys]# vi /etc/vault.d/vault.hcl
[root@ip-172-31-86-35 sslkeys]# vi /etc/vault.d/vault.env
[root@ip-172-31-86-35 sslkeys]# systemctl stop vaulkt
Failed to stop vaulkt.service: Unit vaulkt.service not loaded.
[root@ip-172-31-86-35 sslkeys]# systemctl stop vault
[root@ip-172-31-86-35 sslkeys]# systemctl status vault.service
● vault.service - "HashiCorp Vault - A tool for managing secrets"
Loaded: loaded (/usr/lib/systemd/system/vault.service; disabled; vendor preset: disabled)
Active: inactive (dead)
Docs: https://www.vaultproject.io/docs/
Nov 21 10:15:18 ip-172-31-86-35.ec2.internal vault[28624]: 2022-11-21T10:15:18.769Z [INFO] http: TLS handshake error from 172.31.86.35:562...ficate
Nov 21 10:21:51 ip-172-31-86-35.ec2.internal vault[28624]: 2022-11-21T10:21:51.385Z [INFO] http: TLS handshake error from 172.31.86.35:562...ficate
Nov 21 10:22:29 ip-172-31-86-35.ec2.internal systemd[1]: Stopping "HashiCorp Vault - A tool for managing secrets"...
Nov 21 10:22:29 ip-172-31-86-35.ec2.internal systemd[1]: Stopped "HashiCorp Vault - A tool for managing secrets".
Nov 21 10:22:29 ip-172-31-86-35.ec2.internal systemd[1]: [/usr/lib/systemd/system/vault.service:7] Unknown lvalue 'StartLimitIntervalSec' ... 'Unit'
Nov 21 10:22:29 ip-172-31-86-35.ec2.internal systemd[1]: [/usr/lib/systemd/system/vault.service:8] Unknown lvalue 'StartLimitBurst' in sec... 'Unit'
Nov 21 10:22:29 ip-172-31-86-35.ec2.internal systemd[1]: [/usr/lib/systemd/system/vault.service:7] Unknown lvalue 'StartLimitIntervalSec' ... 'Unit'
Nov 21 10:22:29 ip-172-31-86-35.ec2.internal systemd[1]: [/usr/lib/systemd/system/vault.service:8] Unknown lvalue 'StartLimitBurst' in sec... 'Unit'
Nov 21 10:22:34 ip-172-31-86-35.ec2.internal systemd[1]: [/usr/lib/systemd/system/vault.service:7] Unknown lvalue 'StartLimitIntervalSec' ... 'Unit'
Nov 21 10:22:34 ip-172-31-86-35.ec2.internal systemd[1]: [/usr/lib/systemd/system/vault.service:8] Unknown lvalue 'StartLimitBurst' in sec... 'Unit'
Hint: Some lines were ellipsized, use -l to show in full.
[root@ip-172-31-86-35 sslkeys]# openssl req -config ssl.conf -x509 -days 365 -batch -nodes -newkey rsa:2048 -keyout vault.key -out vault.crt
Generating a 2048 bit RSA private key
......................................................................................................+++
....................................................................................................................+++
writing new private key to 'vault.key'
-----
[root@ip-172-31-86-35 sslkeys]# cat vault.key vault.crt > vault.pem
[root@ip-172-31-86-35 sslkeys]# systemctl start vault
[root@ip-172-31-86-35 sslkeys]# vault operator init
Unseal Key 1: QUQSR/uyijp+hkdsRDJRaR6FTltQeSU0OOSCKYxRGqo9
Unseal Key 2: gh2b+wQ5DpGgwArXaGEVmtdZGJ5JxsRxItP+B+ADSoPT
Unseal Key 3: KFYSvC5k/c/ZtBQ07F1mVvcJ94Gm0GHCzXBQvcs2i5Qu
Unseal Key 4: wOV2yMa0hgh5pSz8LGxym3Hxw7+WExdWkRvOhgT0OsST
Unseal Key 5: NYyn6iovdhdS1vDW8t/aX2OtpHEOiqy0w1BLeTr4fspv
Initial Root Token: hvs.OL17owxJNxq7cf84VCAhdcVF
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 root key. Without at least 3 keys to
reconstruct the root 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.
[root@ip-172-31-86-35 sslkeys]# netstat -tupan | grep 820
tcp 0 0 172.31.86.35:8200 0.0.0.0:* LISTEN 28755/vault
# Unseal tokens, vault login, prepare policy
# Unseal all the tokens (5) received earlier in vault init command
[root@ip-172-31-86-35 sslkeys]# vault operator unseal
Unseal Key (will be hidden):
Key Value
--- -----
Seal Type shamir
Initialized true
Sealed false
Total Shares 5
Threshold 3
Version 1.12.1
Build Date 2022-10-27T12:32:05Z
Storage Type file
Cluster Name vault-cluster-cbc9f102
Cluster ID 5f8422e4-14b9-8b52-819c-8cb72cb4f550
HA Enabled false
# login to vault
[root@ip-172-31-86-35 sslkeys]# vault login
Token (will be hidden):
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 hvs.OL17owxJNxq7cf84VCAhdcVF
token_accessor ivTNmfi4Ehh20GsSADdk515h
token_duration ∞
token_renewable false
token_policies ["root"]
identity_policies []
policies ["root"]
[root@ip-172-31-86-35 sslkeys]#
[root@ip-172-31-86-35 sslkeys]# mkdir -p /etc/vault/policy
[root@ip-172-31-86-35 sslkeys]# cat /etc/vault/policy/mysql.hcl
path "secret/*" {
capabilities = ["list"]
}
path "secret/mysql/*" {
capabilities = ["create", "read", "delete", "update", "list"]
}
[root@ip-172-31-86-35 sslkeys]# vault policy write mysql-secrets /etc/vault/policy/mysql.hcl
Success! Uploaded policy: mysql-secrets
[root@ip-172-31-86-35 sslkeys]# vault token create -policy=mysql-secrets > ~/mysql-vault-token
[root@ip-172-31-86-35 sslkeys]# cat ~/mysql-vault-token
Key Value
--- -----
token hvs.CAESIA0simB1I_i8PB6li5OMui42YAMe4GLkS9G7OntdZDq3Gh4KHGh2cy5haWZtZGpoTlRzTkdETDFCZ2dXMmhYemo
token_accessor DvcJVAGOC1yhzhn1aNAr9pJk
token_duration 768h
token_renewable true
token_policies ["default" "mysql-secrets"]
identity_policies []
policies ["default" "mysql-secrets"]
[root@ip-172-31-86-35 sslkeys]#
[root@ip-172-31-86-35 sslkeys]# vault secrets enable -path=secret kv
Success! Enabled the kv secrets engine at: secret/
[root@ip-172-31-86-35 sslkeys]#
# MySQL side configuration
# Ship the vault cert file to MySQL servers for authentication:
On Percona MySQL Servers:
mkdir /etc/vault_ca
scp 172.31.86.35:/etc/sslkeys/vault.pem /etc/vault_ca/vault.pem
chown mysql:mysql /etc/vault_ca -R
# Enable plugin and add keyring config to my.cnf
[mysqld]
early-plugin-load="keyring_vault=keyring_vault.so"
loose-keyring_vault_config="/var/lib/mysql-keyring/keyring_vault.conf"
# Create vault configuration file
[root@ip-172-31-80-134 ~]# cat /var/lib/mysql-keyring/keyring_vault.conf
vault_url = https://172.31.86.35:8200
secret_mount_point = secret/mysql/master
token = hvs.CAESIA0simB1I_i8PB6li5OMui42YAMe4GLkS9G7OntdZDq3Gh4KHGh2cy5haWZtZGpoTlRzTkdETDFCZ2dXMmhYemo
vault_ca = /etc/vault_ca/vault.pem
# The vault plugin in enabled
mysql> show plugins;
+-------------------------------+----------+--------------------+------------------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+------------------+---------+
| keyring_vault | ACTIVE | KEYRING | keyring_vault.so | GPL |
...
# Encrypt a table
mysql> alter table backup_test encryption='Y';
Query OK, 30 rows affected (0.07 sec)
Records: 30 Duplicates: 0 Warnings: 0
mysql> use test; show create table backup_test\G
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
*************************** 1. row ***************************
Table: backup_test
Create Table: CREATE TABLE `backup_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=latin1 ENCRYPTION='Y'
1 row in set (0.00 sec)
mysql> select * from backup_test;
+----+
| id |
+----+
| 1 |
| 3 |
| 6 |
...
| 77 |
| 80 |
| 83 |
| 86 |
+----+
30 rows in set (0.00 sec)
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';
+--------------+-------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+-------------+----------------+
| test | backup_test | ENCRYPTION="Y" |
+--------------+-------------+----------------+
1 row in set (0.16 sec)
Errors during setup:
2022-11-21T13:18:56.842070Z 0 [ERROR] Plugin keyring_vault reported: 'keyring_vault initialization failure.
Please check that the keyring_vault_config_file points to readable keyring_vault configuration file.
Please also make sure Vault is running and accessible. The keyring_vault will stay unusable until correct configuration file gets provided.'
2022-11-21T13:37:52.844008Z 0 [ERROR] Plugin keyring_vault reported: 'CURL returned this error code: 77 with error message : Problem with the SSL CA cert (path? access rights?)'
2022-11-21T13:37:52.844066Z 0 [Note] Plugin keyring_vault reported: 'Probing secret for being a mount point unsuccessful - skipped.'
2022-11-21T13:37:52.844280Z 0 [ERROR] Plugin keyring_vault reported: 'CURL returned this error code: 77 with error message : Problem with the SSL CA cert (path? access rights?)'
...
2022-11-21T13:37:52.844317Z 0 [Note] Plugin keyring_vault reported: 'Probing secret/mysql for being a mount point unsuccessful - skipped.'
2022-11-21T13:37:52.844825Z 0 [ERROR] Plugin keyring_vault reported: 'Error while loading keyring content. The keyring might be malformed'
2022-11-21T13:37:52.844830Z 0 [ERROR] Plugin keyring_vault reported: 'keyring_vault initialization failure.
Please check that the keyring_vault_config_file points to readable keyring_vault configuration file. Please also make sure Vault is running and accessible. The keyring_vault will stay unusable until correct configuration file gets provided.
References:
