[Maria-discuss] procedure to change database encryption with file_key_management plugin?
I've built mysqld -V mysqld Ver 10.2.14-MariaDB-log for Linux on x86_64 (Source distribution) I'm setting up encryption, following https://mariadb.com/kb/en/library/encryption/ https://mariadb.com/kb/en/library/data-at-rest-encryption/ I created my key file openssl rand -hex 32 b650adbc0c5df1bc3e766b4b65f26dc76c76ed81b955bbedaf50e1d4e16fc732 /etc/mariadb/keys.txt 1;b650adbc0c5df1bc3e766b4b65f26dc76c76ed81b955bbedaf50e1d4e16fc732 encrypted it openssl enc -aes-256-cbc -k 'test_passphrase' -md sha1 -in /etc/mariadb/keys.txt -out /etc/mariadb/keys.enc verified it openssl aes-256-cbc -d -md sha1 -k 'test_passphrase' -in /etc/mariadb/keys.enc 1;b650adbc0c5df1bc3e766b4b65f26dc76c76ed81b955bbedaf50e1d4e16fc732 I've enabled "everything" encryption using that keyfile [mysqld] plugin_dir=/opt/mariadb/lib/plugin plugin-load-add=file_key_management file-key-management file_key_management_encryption_algorithm=aes_ctr file_key_management_filekey = 'test_filekey' file_key_management_filename = /etc/mariadb/enc/keys.enc aria-encrypt-tables = 1 encrypt-binlog = 1 encrypt-tmp-disk-tables = 1 encrypt-tmp-files = 1 innodb_default_encryption_key_id = 1 innodb-encrypt-log = off innodb-encrypt-tables = on innodb-encryption-threads = 4 innodb-tablespaces-encryption = 1 verified the plugin loads mysql -e "show plugins;" | grep ENC INNODB_TABLESPACES_ENCRYPTION ACTIVE INFORMATION SCHEMA NULL BSD file_key_management ACTIVE ENCRYPTION file_key_management.so GPL on startup it looks like it starts up ok 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: 5.7.21 started; log sequence number 7206290786 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: Creating #1 encryption thread id 139727810316032 total threads 4. 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: Creating #2 encryption thread id 139727801923328 total threads 4. 2018-02-21 13:01:29 139727818708736 [Note] InnoDB: Loading buffer pool(s) from /home/data/db/ib_buffer_pool 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: Creating #3 encryption thread id 139727793530624 total threads 4. 2018-02-21 13:01:29 139729003899072 [Note] InnoDB: Creating #4 encryption thread id 139727785137920 total threads 4. 2018-02-21 13:01:29 139727818708736 [Note] InnoDB: Buffer pool(s) load completed at 180222 13:01:29 2018-02-21 13:01:29 139729003899072 [Note] Using encryption key id 1 for temporary files 2018-02-21 13:01:29 139729003899072 [Note] Server socket created on IP: '127.0.0.1'. 2018-02-21 13:01:29 139729003899072 [Note] Reading of all Master_info entries succeded 2018-02-21 13:01:29 139729003899072 [Note] Added new Master_info '' to hash table 2018-02-21 13:01:29 139729003899072 [Note] /opt/mariadb/bin/mysqld: ready for connections. Version: '10.2.14-MariaDB-log' socket: '/var/cache/mariadb/mariadb.sock' port: 3306 Source distribution and verified table encryption mysql -e "SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION;" +-------+-------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+ | SPACE | NAME | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING | +-------+-------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+ | 1375 | mysql/gtid_slave_pos | 1 | 1 | 1 | 1 | NULL | NULL | 1 | 0 | | 1465 | mysql/innodb_index_stats | 1 | 1 | 1 | 1 | NULL | NULL | 1 | 0 | | 1466 | mysql/innodb_table_stats | 1 | 1 | 1 | 1 | NULL | NULL | 1 | 0 | | 18999 | testdata/table0001 | 1 | 0 | 1 | 1 | NULL | NULL | 1 | 0 | ... ... ... | 0 | innodb_system | 1 | 1 | 1 | 1 | NULL | NULL | 1 | 0 | +-------+-------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+ reading Encryption key management MariaDB encryption supports multiple encryption keys, they are identified by a key identifier — a 32-bit integer. To support automatic key rotation every key additionally might have different versions. XtraDB and InnoDB can automatically re-encrypt the data from an older to a newer version of the same key. But how different keys are stored and rotated depends on the key management solution that you choose. but for this plugin file_key_management This plugin does not support key rotation — all keys always have the version 1. So I understand that I can't rotate the keys similar to what the AWS plugin provides. But if I need to change the key at any time, either just its encrypted form /etc/mariadb/keys.enc &/or the 'master' /etc/mariadb/keys.txt What's the procedure to re-key all the encrypted tables? Do I need to (1) stop the server (2) manually decrypt each table with its old key (3) reencrypt each table with the new key (4) restart the server ? True also for having used multiple keys for global/default, temp tables, and per-table? Is there any tool/procedure that automates that? I suppose that the AWS plugin takes care of that automated-rotation. Is there another non-commercial/open-source plugin with similar rotation capability?
Hi, obsa! On Feb 22, obsa@mm.st wrote:
Encryption key management
MariaDB encryption supports multiple encryption keys, they are identified by a key identifier — a 32-bit integer. To support automatic key rotation every key additionally might have different versions. XtraDB and InnoDB can automatically re-encrypt the data from an older to a newer version of the same key. But how different keys are stored and rotated depends on the key management solution that you choose.
but for this plugin
file_key_management This plugin does not support key rotation — all keys always have the version 1.
So I understand that I can't rotate the keys similar to what the AWS plugin provides.
But if I need to change the key at any time, either just its encrypted form &/or the 'master'
What's the procedure to re-key all the encrypted tables?
Do I need to (1) stop the server (2) manually decrypt each table with its old key (3) reencrypt each table with the new key (4) restart the server ?
Without key rotation, there's no automatic way, unfortunately. A, perhaps, more convenient approach could be: (1) add new key to the keys.txt - with a different ID. (2) restart the server (3) do ALTER TABLE...ENCRYPTION_KEY_ID=xxx for every encrypted table to switch it to the new key. Another possibility would be to add key rotation support to the file_key_management plugin. It is easier than it sounds - this plugin is quite simple. Regards, Sergei Chief Architect MariaDB and security@mariadb.org
hi sergei, On Thu, Feb 22, 2018, at 11:57 AM, Sergei Golubchik wrote:
Without key rotation, there's no automatic way, unfortunately.
:-/
A, perhaps, more convenient approach could be:
(1) add new key to the keys.txt - with a different ID. (2) restart the server (3) do ALTER TABLE...ENCRYPTION_KEY_ID=xxx for every encrypted table to switch it to the new key.
That 'conveinence' assumes that you've got single, or a very few, keys in play. For more/many keys, especially when you start getting per-table keys, it starts getting in-convenient fast. And more importantly, very end-user error-prone!
Another possibility would be to add key rotation support to the file_key_management plugin.
That'd be useful. Or a different plugin altogether. Depends on the answer to the question: Are there any non-commercial/FOSS, offline key-rotation capable key management plugins? I.e., specifically not AWS' ? In the same way that having encryption-ready mariadb-backup *from* MariaDB is really valuable, having a non-3rd-party encryption management solution is similarly valuable/important. Ideally, (easily) integrated with soft/inexpensive HSM. Eventually.
It is easier than it sounds - this plugin is quite simple.
famous last words ;-)
Sergei, Still looking for some alternative to AWS. I did find 'Vault', a well-established & active project https://www.vaultproject.io/intro/index.html which appears to be an on-premises alternative to AWS & HSMs with open sources here https://github.com/hashicorp/vault and a "MariaDB Secrets Engine" https://www.vaultproject.io/docs/secrets/databases/mysql-maria.html "The database secrets engine generates database credentials dynamically based on configured roles. It works with a number of different databases through a plugin interface. There are a number of builtin database types and an exposed framework for running custom database types for extendability. This means that services that need to access a database no longer need to hardcode credentials: they can request them from Vault, and use Vault's leasing mechanism to more easily roll keys." Has there been any work on a Vault-based MariaDB plugin with key management/rotation capabilities similar to that provided by the AWS offsite solution?
Hi Obsa, Thank you for sharing this link, sounds interesting. In case you succeed with this Vault implementation, kindly share us your experience (may be a blog post on this). I am also looking for implementing something similar without AWS KMS option. Thank you. On Tue, Feb 27, 2018 at 5:59 AM, <obsa@mm.st> wrote:
Sergei,
Still looking for some alternative to AWS.
I did find 'Vault', a well-established & active project
https://www.vaultproject.io/intro/index.html
which appears to be an on-premises alternative to AWS & HSMs
with open sources here
https://github.com/hashicorp/vault
and a "MariaDB Secrets Engine"
https://www.vaultproject.io/docs/secrets/databases/mysql-maria.html
"The database secrets engine generates database credentials dynamically based on configured roles. It works with a number of different databases through a plugin interface. There are a number of builtin database types and an exposed framework for running custom database types for extendability. This means that services that need to access a database no longer need to hardcode credentials: they can request them from Vault, and use Vault's leasing mechanism to more easily roll keys."
Has there been any work on a Vault-based MariaDB plugin with key management/rotation capabilities similar to that provided by the AWS offsite solution?
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Karthick Subramanian -
obsa＠mm.st -
Sergei Golubchik