maria-discuss team mailing list archive

[obsa@xxxxx]

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?