This is the first of a two-part series on using the keyring_vault plugin with Percona Server for MySQL 5.7. The second part, Backing up Percona Server for MySQL with keyring_vault plugin enabled, walks through how to use Percona Xtrabackup to backup from this instance and restore to another server and set it up as a slave with keyring_vault plugin.
What is the keyring_vault plugin?
The keyring_vault is a plugin that allows the database to interface with a Hashicorp Vault server to store and secure encryption keys. The Vault server then acts as a centralized encryption key management solution which is critical for security and for compliance with various security standards.
Configuring Vault
Create SSL certificates to be used by Vault. You can use the sample ssl.conf template below to generate the necessary files.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [root@vault1 ~]# cat /etc/sslkeys/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 = 192.168.0.114 |
Then run the two commands below to generate the cert and key files and the certificate chain:
1 2 | $ openssl req -config ssl.conf -x509 -days 365 -batch -nodes -newkey rsa:2048 -keyout vault.key -out vault.crt $ cat vault.key vault.crt > vault.pem |
Once the SSL certificates are created start Vault with the sample configuration below. Take note that you should follow the suggested best practices when deploying Vault in production, this example is to get us by with a simple working setup.
1 2 3 4 5 6 7 8 9 10 | [root@vault1 ~]# cat /etc/vault.hcl listener "tcp" { address = "192.168.0.114:8200" tls_cert_file="/etc/sslkeys/vault.crt" tls_key_file="/etc/sslkeys/vault.key" } storage "file" { path = "/var/lib/vault" } |
Assuming Vault started up fine and you are able to unseal Vault, the next step is to create the policy file. For more details on initializing and unsealing Vault please read the manual here.
1 2 3 4 5 6 7 8 | [root@vault1 ~]# cat /etc/vault/policy/dc1.hcl path "secret/*" { capabilities = ["list"] } path "secret/dc1/*" { capabilities = ["create", "read", "delete", "update", "list"] } |
Create a Vault policy named dc1-secrets using the dc1.hcl file like this:
1 2 | [root@vault1 ~]# vault policy write dc1-secrets /etc/vault/policy/dc1.hcl -ca-cert=/etc/sslkeys/vault.pem Success! Uploaded policy: dc1-secrets |
Next, create a token associated with the newly created policy:
1 2 3 4 5 6 7 8 9 10 11 | [root@vault1 ~]# vault token create -policy=dc1-secrets -ca-cert=/etc/sslkeys/vault.pem > dc1-token [root@vault1 ~]# cat dc1-token Key Value --- ----- token be515093-b1a8-c799-b237-8e04ea90ad7a token_accessor 4c1ba5c5-3fed-e9bb-d230-5bf1392e2d7e token_duration 8760h token_renewable true token_policies ["dc1-secrets" "default"] identity_policies [] policies ["dc1-secrets" "default"] |
Setting up MySQL
The following instructions should work starting from Percona Server for MySQL 5.7.20-18 and through later versions.
Configure my.cnf with the following variables:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | early-plugin-load="keyring_vault=keyring_vault.so" loose-keyring_vault_config="/var/lib/mysql-keyring/keyring_vault.conf" encrypt_binlog=ON innodb_encrypt_online_alter_logs=ON innodb_encrypt_tables=ON innodb_temp_tablespace_encrypt=ON master_verify_checksum=ON binlog_checksum=CRC32 log_bin=mysqld-bin binlog_format=ROW server-id=1 log-slave-updates |
Create the keyring_vault.conf file in the path above with the following contents:
1 2 3 4 5 | [root@mysql1 ~]# cat /var/lib/mysql-keyring/keyring_vault.conf vault_url = https://192.168.0.114:8200 secret_mount_point = secret/dc1/master token = be515093-b1a8-c799-b237-8e04ea90ad7a vault_ca = /etc/vault_ca/vault.pem |
Here we are using the vault.pem file generated by combining the vault.crt and vault.key files. Observe that our secret_mount_point is secret/dc1/master. We want to make sure that this mount point is unique across all servers, this is in fact advised in the manual here.
Ensure that the CA certificate is owned by mysql user:
1 2 3 4 5 | [root@mysql1 ~]# ls -la /etc/vault_ca/ total 24 drwxr-xr-x 2 mysql mysql 41 Jul 14 11:39 . drwxr-xr-x 63 root root 4096 Jul 14 13:17 .. -rw------- 1 mysql mysql 1139 Jul 14 11:39 vault.pem |
Initialize the MySQL data directory on the Master:
1 | [root@mysql1 ~]# mysqld --initialize-insecure --datadir=/var/lib/mysql --user=mysql |
For production systems, we do not recommend using --initialize-insecure option, this is just to skip additional steps in this tutorial.
Finally, start mysqld instance and then test the setup by creating an encrypted table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [root@mysql1 ~]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled) Active: active (running) since Sat 2018-07-14 23:53:16 UTC; 2s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 1401 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 1383 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 1403 (mysqld) CGroup: /system.slice/mysqld.service └─1403 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid Jul 14 23:53:16 mysql1 systemd[1]: Starting MySQL Server... Jul 14 23:53:16 mysql1 systemd[1]: Started MySQL Server. |
At this point, you should have Percona Server for MySQL instance with tablespace encryption using Vault.
Researching database security?
You might also enjoy this pre-recorded webinar securing your database servers from external attacks presented by my colleague Colin Charles.
all the information you have shared are very much useful for all the computer user. I get to know proper ideas about the SQL. keep sharing this type of ideas.
We are providing best Steel wall & House stud framing services in Australia. A stud wall includes a frame of timber or metal studs secured to the floor, ceiling and walls, which is then covered with plasterboard. We are 24*7 available for the customer help. For more information just visit our site: http://www.newworldconstruction.com.au/steel-wall-and-house-stud-framing.html
Thanks for the writeup, Jericho! Just wanted to share that it looks like as of mysql 5.7.23-23, the keyring_vault plugin is using v1 API of Vault’s kv engine, but currently by default Vault uses v2 when you create a new kv secrets engine. MySQL wasn’t able to store the secrets into Vault until I changed the API version of back to v1.
But other than that everything still work as described here. Thanks again for the nice writeup!
I followed all the points, but at my start the percona gives such a message:
[ERROR] Plugin keyring_vault reported: 'CURL returned this error code: 7 with error message : Failed connect to 127.0.0.1:8200; Connection refused'pers_1 | 2020-01-09T21:39:49.936418Z 0 [ERROR] Plugin keyring_vault reported: 'Could not retrieve list of keys from Vault.'
pers_1 | 2020-01-09T21:39:49.936423Z 0 [ERROR] Plugin keyring_vault reported: 'Error while loading keyring content. The keyring might be malformed'
pers_1 | 2020-01-09T21:39:49.936427Z 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.'
Maybe someone knows what could be the problem?
When starting the vault server I use tls_disable = 1, because as a server I use a localhost that does not support https.
Also my db is launched through docker.
thank you all in advance.
Stas, this line from the log sounds like your Vault is not listening on 127.0.0.1:8200.
“[ERROR] Plugin keyring_vault reported: ‘CURL returned this error code: 7 with error message : Failed connect to 127.0.0.1:8200; Connection refused'”
Will,
Indeed only Vault kv v1 is supported as stated here: https://www.percona.com/doc/percona-server/LATEST/security/vault.html. An added step after unsealing Vault is to create the secrets engine with
vault secrets enable -path=secret -version=1 kv
I’m glad that the write-up is helpful.
I am getting this error message :
2020-04-14T08:43:47.410773Z 0 [Warning] unknown variable ‘loose_keyring_vault_config=/var/lib/mysql-keyring/keyring_vault.conf’
My Percona Mysql version is : 5.7.29-32 Percona Server (GPL)
I have created secret with version 1 of kv. In percona 8.0(last version), it always show: [ERROR] Plugin keyring_vault reported: ‘Plugin keyring_vault reported: ‘CURL returned this error code: 7 with error message : Failed to connect to 10.211.55.4: Permission denied’”
I has similar problem. For me it had to do with SELinux.
setsebool mysql_connect_any on
setsebool mysql_connect_http on
but,In the same server, I use curl in cli.it works. the comment text like this:
curl –cacert /etc/vault_ca/vault.pem -X GET -H “X-Vault-Request: true” -H “X-Vault-Token:*************” https://10.211.55.4:8200/v1/secret/dc
{“request_id”:”3d33fef2-6c39-39ff-b794-b71f05859f3a”,”lease_id”:””,”renewable”:false,”lease_duration”:2764800,”data”:{“master”:”asdfasddfasdf”},”wrap_info”:null,”warnings”:null,”auth”:null}
reuslt:
“request_id”:”3d33fef2-6c39-39ff-b794-b71f05859f3a”,”lease_id”:””,”renewable”:false,”lease_duration”:2764800,”data”:{“master”:”asdfasddfasdf”},”wrap_info”:null,”warnings”:null,”auth”:null}
this is my “keyring_vault.conf” :
vault_url=https://10.211.55.4:8200
secret_mount_point =v1/secret/dc/master
token = s.7YGc0gCXGJsmoT44txWvWr2M
vault_ca = /etc/vault_ca/vault.pem