using Amazon Key Management Service Jan Lindstrm, Principal - PowerPoint PPT Presentation

Transparent tablespace and log encryption on MariaDB 10.1 using Amazon Key Management Service Jan Lindström, Principal Engineer, MariaDB Corporation Amsterdam, Netherlands | October 5, 2016

Agenda 1. Introduction 2. Concepts 3. InnoDB/XtraDB 4. Encryption Plugins 5. Amazon AWS KMS 6. Configuration 2

Introduction

What is transparent encryption? • Transparent to application • Application does’t know anything about keys, algorithm, etc • Anyone that can connect to MariaDB can dump data • Not data-in-transit encryption (SSL/TLS) • Not per-column encryption • Not application-side encryption • No encryption functions needed (AES_ENCRYPT()) 4

All data written to disk should be encrypted • InnoDB tablespaces (per-file and system) • InnoDB log files • Aria tables • Temporary files • Temporary tables • Binary log • No mysqlbinlog, though! 5

What’s missing? • Aria logs • Audit log • Error log • Slow query log • General query log • MyISAM tables • CONNECT, CSV, et. Al. • Galera gcache 6

Implementation • MariaDB has a new interface for encryption plugins • Key management • Encryption/decryption • Implemented co-operation together with Google and Eperi • https://mariadb.com/kb/en/mariadb/encryption-plugins/ 7

Concepts

Concepts • Key ID • ID 1 for system data, like InnoDB redo logs, binary logs, etc • ID 2 (if available) for temporary data, like temporary files and temporary tables • Other Ids as configured when creating tables, etc. • Key Version (for rotation) • Encryption algorithm • Default AES_CBC • Support for these items may vary across plugins! 9

InnoDB/XtraDB

InnoDB/XtraDB • ON/OFF/FORCE • innodb-encrypt-tables = [ON | OFF | FORCE]; • Encrypt log • innodb-encrypt-log = [ON | OFF]; • Monitoring (IS) • innodb-tablespaces-scrubbing • innodb-tablespaces-encryption 11

InnoDB/XtraDB • Optional background rotation • innodb-encryption-threads = n; • innodb-encryption-rotate-key-age= n; ” Age ” in key versions - • innodb-encryption-rotation-iops = n; • Optional data scrubbing • innodb-background-scrub-data-compressed = [ON | OFF]; • innodb-background-scrub-data-uncompressed = [ON | OFF]; • innodb-immediate-scrub-data-uncompressed = [ON | OFF]; • innodb-scrub-log = [ON|OFF]; • innodb-scrub-log-speed=n; • innodb-background-scrub-data-check-interval=n; https://mariadb.com/kb/en/mariadb/xtradb-innodb-data-scrubbing/ 12

Encryption plugins

Encryption plugins • File key management • https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/#file_key_management- plugin • AWS KMS plugin • https://mariadb.com/kb/en/mariadb/aws-key-management-encryption-plugin/ • Eperi plugin • http://eperi.de/en/products/database-encryption/ • Custom plugins to meet customer needs? 14

File_key_management • Keys stored in a local file (note that this file could be on USB stick) • No support for key rotation/version • Key file itself can be encrypted (but used key in my.cnf) • Do you feel good having your encryption keys sitting next to your data ? 15

Eperi plugin • Separate Eperi gateway software • Licenses and downloads from Eperi’s web portal • KMS • Plugin opens listener that the KMS connects to in order to authenticate the connecting MariaDB instance • Page encryption server • InnoDB actually sends pages to the Eperi gateway node to be encrypted! 16

Amazon KMS Encryption Plugin

AWS KMS Encryption Plugin • Amazon Web Services Key Management Service • CloadTrail & CloudWatch • Logging • Auditing • Notifications • Identity and Access Management (IAM) • Interesting possibilities • MFA for MariaDB startup • IAM roles to read keys • AWS logging & alerts 18

Requirements • You need to sign up for Amazon Web Services • You need to create IAM user • MariaDB server will use these credentials to authenticate AWS server • You need to create a master encryption key • Used to encrypt the actual encryption keys that will be used by MariaDB • You will need to configure AWS credentials • You will need to configure MariaDB (naturally) 19

AWS KMS Plugin • Writes enrypted keys to local disk • MariaDB must connect to KMS to decrypt keys - MariaDB startup - Creating a table that uses a new key • Supports key rotation • Limited platform support due to C++11 requirement of AWS SDK • Requires C++11 compiler: gcc4.7+, clang 3.3+ or VS2013+ • RHEL • CentOS 7 • ~600 lines • Great reference for people who want to write their own plugins 20

Credentials Management • Identify and Access Management (IAM) policy for keys • Authorized source addresses • IAM users w/ restricted privileges • Multi-Factor Authentication (2FA/MFA) • AWS SDK • Config file, environment variables, etc. • Flexible wrapper program • EC2 (Elastic Compute Cloud) instance IAM role 21

Configuration

Install, enable, and configure $ cat /etc/my.cnf.d/aws_key_management.cnf [mariadb] plugin-load-add=aws_key_management.so aws-key-management aws-key-management-master-key-id = alieas/mariadb2 # aws_key_management_log_level = Trace ignore-db-dirs=.pki !include /etc/my.cnf.d/enable_encryption.preset 23

Turn on encryption settings $ cat /etc/my.cnf.d/enable_encryption.preset [mariadb] aria-encrypt-tables encrypt-binlog encrypt-tmp-disk-tables encrypt-tmp-files loose-innodb-encrypt-log loose-innodb-encrypt-tables 24

Encrypted system tablespace $ sudo – u mysql mysql_install_db … 2016-09-29 11:40:00 [Note] AWK KMS plugin: generated encrypted datakey for key id=1, version=1 2016-09-29 11:40:00 [Note] AWK KMS plugin: loaded key 1, version 1, key length 128 bit … 2016-09-29 11:40:01 [Note] AWK KMS plugin: generated encrypted datakey for key id=2, version=1 2016-09-29 11:40:01 [Note] AWK KMS plugin: loaded key 2, version 1, key length 128 bit 2016-09-29 11:40:01 [Note] Using encryption key id 2 for temporary files … 25

Why encrypt data ? MariaDB [db]> create table client_credit_card(id int not null primary key, credit_card varchar(20)) engine=innodb encrypted=no ; MariaDB [db]> insert into client_credit_cards values (20071992, ’5275 -0000- 0000- 0000’): … $ sudo strings /var/lib/mysql/db/client_credit_cards.ibd infimum supremum 5275-0000-0000-0000 26

Automatic key generation MariaDB [db]> create table client_credit_card(id int not null primary key, credit_card varchar(20)) engine=innodb encrypted=yes encryption_key_id=3 ; MariaDB [db]> insert into client_credit_card values (20071992, ’5275 -0000-0000- 0000’): … $ sudo strings /var/lib/mysql/db/client_credit_card.ibd {7fgh k6klj B_0= … 27

I_S table for encryption info MariaDB [(none)]> select * from information_schema.innodb_tablespaces_encryption where name='db/client_credit_card'\G *************************** 1. row *************************** SPACE: 6 NAME: db/client_credit_card ENCRYPTION_SCHEME: 1 KEYSERVER_REQUESTS: 1 MIN_KEY_VERSION: 1 CURRENT_KEY_VERSION: 1 KEY_ROTATION_PAGE_NUMBER: NULL KEY_ROTATION_MAX_PAGE_NUMBER: NULL CURRENT_KEY_ID: 3 1 row in set (0.00 sec) 28

Key rotation MariaDB [(none)]> show variables like 'aws%'; +----------------------------------+----------------+ | Variable_name | Value | +----------------------------------+----------------+ | aws_key_management_key_spec | AES_128 | | aws_key_management_log_level | Off | | aws_key_management_master_key_id | alias/mariadb2 | | aws_key_management_rotate_key | 0 | +----------------------------------+----------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> set global aws_key_management_rotate_key=3; Query OK, 0 rows affected (0.27 sec) 29

Key rotation MariaDB [db]> set global innodb_encryption_threads=4; Query OK, 0 rows affected (0.00 sec) MariaDB [db]> set global innodb_encryption_rotate_key_age=0; Query OK, 0 rows affected (0.00 sec) MariaDB [db]> select * from information_schema.innodb_tablespaces_encryption where name like 'db/c%'\G *************************** 1. row *************************** SPACE: 6 NAME: db/client_credit_card ENCRYPTION_SCHEME: 1 KEYSERVER_REQUESTS: 2 MIN_KEY_VERSION: 2 CURRENT_KEY_VERSION: 2 KEY_ROTATION_PAGE_NUMBER: NULL KEY_ROTATION_MAX_PAGE_NUMBER: NULL CURRENT_KEY_ID: 3 1 row in set (0.00 sec) 30

Documentation • https://mariadb.com/kb/en/mariadb-enterprise/mariadb-enterprise-aws-kms- encryption-plugin-setup-guide/ • https://mariadb.com/kb/en/mariadb-enterprise/mariadb-enterprise-aws-kms- encryption-plugin-advanced-usage/ • https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/ • https://mariadb.com/kb/en/mariadb/xtradb-innodb-data-scrubbing/ 31

Q/A