Database Security – Backup Encryption In-Transit & At-Rest

Securing your data is one of the most important tasks that we should prioritize. The emergence of regulations that requires security compliance such as GDPR, HIPAA, PCI DSS, or PHI requires that your data should be stored securely or transmitted over the wire.

In ClusterControl, we value the importance of security and offer a number of features to secure database access and data stored. One of those is securing your database backups, both when at-rest and in-transit. In-transit is when the backup is being transferred through the internet or network from source to its destination, while at-rest is when data is stored on persistent storage. In this blog, we’ll show you how you can use ClusterControl to encrypt your backup data at-rest and in-transit.

Encryption In-Transit

When managing backups through ClusterControl, using mysqldump or Percona Xtrabackup/Mariabackup are managed by default without encryption when transmitted over the wire. However, using TLS/SSL for encryption of data transmission is supported by MySQL/MariaDB/Percona Server so does Percona Xtrabackup which offers SSL options when invoking the command.

ClusterControl does enable SSL by default when you deploy a cluster but it does not have the control to switch instantly and make your data encrypted over the wire during backup creation. You can check out our previous blogs for the manual approach using ClusterControl when creating your cluster or using the old fashioned way to manually setup TLS/SSL in MySQL.

In ClusterControl, when you deploy a cluster, you can check the security tab or this icon .

Clicking the button will allow you to replace the certificate which is currently being used or deployed by ClusterControl during deployment of your newly provisioned cluster. You can check this blog “Updated: Become a ClusterControl DBA – SSL Key Management and Encryption of MySQL Data in Transit” for which we showed how we handle the keys. Basically, you can go through left-side navigation of the ClusterControl and click “Key Management”.

Below is an example of Key Management in which you can create and import your existing keys.

When creating a backup using mysqldump as your logical backup, to encrypt your data, make sure that you have your SSL options set accordingly.

What’s next?

Since we have our created certificates, we need to enable and setup SSL accordingly. To ensure this, you can check via ClusterControl or mysql command prompt. See images below:

or you can check also under Performance tab and click DB Variables as seen below:

Take note that it might take some time to populate under the Performance -> DB Variables. So if you want to check manually, you can use mysql command prompt just like below:

Defining your ssl_cipher can add more security hardening. There’s a list of cipher suite if you run SHOW GLOBAL STATUS LIKE ‘Ssl_cipher_list’G or check here. A cipher suite is a combination of authentication, encryption and message authentication code (MAC) algorithms. These are used during negotiation of security settings for a TLS/SSL connection as well as for the secure transfer of data.

Since ClusterControl will run mysqldump locally into that host, adding the following parameters (see below) in your MySQL configuration file (/etc/my.cnf, /etc/mysql/my.cnf, /usr/etc/my.cnf, ~/.my.cnf) will encrypt any actions for SQL dump. See below:

[mysqldump]
socket=/var/lib/mysql/mysql.sock
max_allowed_packet = 512M
host=127.0.0.1
ssl-cert=/var/lib/mysql/client-cert.pem
ssl-key=/var/lib/mysql/client-key.pem

You can try to monitor using tcpdump and you can see below compared to an unencrypted vs encrypted layer using TLS.

With TLS/SSL:

Without TLS/SSL:

When using Percona XtraBackup or Mariabackup under ClusterControl, there’s no TLS/SSL support as of this time when backup is transmitted over the network. It uses socat which basically just opens a port into another node as a means of communication.

e.g.

[21:24:46]: 192.168.10.20: Launching ( ulimit -n 256000 && LC_ALL=C /usr/bin/mariabackup --defaults-file=/etc/my.cnf --backup --galera-info --parallel 1 --stream=xbstream --no-timestamp | gzip -6 - | socat - TCP4:192.168.10.200:9999 ) 2>&1.
[21:24:46]: 192.168.10.20: The xtrabackup version is 2.4.12 / 2004012.
[21:24:46]: 192.168.10.20:3306: Checking xtrabackup version.
[21:24:46]: 192.168.10.20: Streaming backup to 192.168.10.200
[21:24:46]: 192.168.10.200: Netcat started, error log is in '192.168.10.200:/tmp/netcat.log'.
[21:24:43]: 192.168.10.200: Starting socat -u tcp-listen:9999,reuseaddr stdout > /home/vagrant/backups/BACKUP-71/backup-full-2018-11-12_132443.xbstream.gz 2>/tmp/netcat.log.

If you need secure layer during transport, you can do this manually using –ssl* options during command invocation. Checkout here for the Percona XtraBackup manual for more info. We still recommend to obtain your certificate/key from a registered certificate authority.

Alternatively, using ClusterControl, you can encrypt your data before sending via the network, the packets being sent are not raw but encrypted data. Although, the encryption relies on at-rest, we’ll discuss in the next section regarding this.

Encryption At-Rest

In ClusterControl, when creating your backup, you have the option to make your data encrypted. See below:

When encrypted, ClusterControl will use “Advance Encryption Standard” AES-256-CBC. See the sample log below:

[22:12:49]: 192.168.10.30: Launching ( ulimit -n 256000 && LC_ALL=C /usr/bin/mariabackup --defaults-file=/etc/my.cnf --backup --galera-info --parallel 1 --stream=xbstream --no-timestamp | gzip -6 - | openssl enc -aes-256-cbc -pass file:/var/tmp/cmon-002471-32758-24c456ca6b087837.tmp | socat - TCP4:192.168.10.200:9999 ) 2>&1.

If you are storing your backup into the cloud, for example with AWS S3, S3 offers three different modes of server-side encryption (SSE). These are SSE-S3, SSE-C, or SSE-KMS. Amazon has great SSE features to offer which handles encryption of data at rest. You can start here which tackles how S3 uses AWS KMS. If you are moving your backup to a volume or block-based storage, AWS has EBS encryption as well using AWS KMS. You can check over here for more info about this.

Microsoft Azure has cool features as well when handling data at rest. Check out the page on “Azure Storage Service Encryption for data at rest”. Azure handles the keys in their Azure Key Vault, same as AWS KMS. For Google encryption for data at rest, you can check here.

When storing data backups on-prem, you can use LUKS (Linux Unified Key Setup) with combination of crypt or dmcrypt. I’ll not discuss about this on this blog but this is a good source to look at: MySQL Encryption at Rest – Part 1 (LUKS). Fore more info about disk encryption, this ArchLinux page “Disk encryption” is a great source to start .

Most importantly, while your data has been encrypted at rest and in-transit, always verify that your backup works. A backup that has not been tested is not a backup if it does not work when recovery is needed. Storing your backup as well while encrypted must be decrypted without any problems, thus, your keys must be stored privately and in the most secure way as possible. Additionally, adding encryption to your data as well such as InnoDB encryption or SST (for Galera) adds more security, but we won’t cover these in this blog.

Conclusion

Encrypting backup data at rest and in-transit are vital components for compliance with PHI, HIPAA, PCI DSS or GDPR, to ensure that sensitive data transmitted over the wire or saved on disks are not readable by any user or application without a valid key. Some compliance regulations such as PCI DSS and HIPAA require that data at rest be encrypted throughout the data lifecycle.

Here, we show how ClusterControl offers these options to the end-user. Compliance is a huge task though, touching many different areas. Regulations are also updated on a frequent basis, and processes/tools also need to be updated accordingly. We are continuously improving different areas in ClusterControl to facilitate compliance. We would like to hear your thoughts on this and how we can improve.