MariaDBのTDEでのhashicorp plugin利用(AlmaLinux8)

目的

  • AlmaLinux8で提供されているMariaDB 10.5でTransparent Data Encryptionを利用する
  • 鍵・暗号化プラグインとしてMariaDB 10.9向けのhashicorp key management pluginを利用する

環境

  • dockerのalmalinux:8イメージのコンテナ

方法

  1. dockerコンテナを作成してコンテナ内に移動

  2. 必要パッケージをインストール

# dnf -y install rpm-build yum-utils git
# dnf config-manager --set-enable powertools
  1. MariaDBのバージョンを10.3から10.5に切り替え
# dnf module reset mariadb
# dnf module enable mariadb:10.5
  1. 作業用の一般アカウントを作成して、MariaDBのソースパッケージを取得
# adduser dummy
# su - dummy
$ dnf download --source mariadb-server
  1. MariaDBのソースパッケージをインストール
$ rpm -ivh mariadb-10.5.13-1.module_el8.6.0+2761+593e5e59.src.rpm
  1. 作業用の一般アカウントからログアウトし、パッケージ作成に必要な各種ライブラリをインストール
$ exit
# dnf -y install Judy
# rpm --nodeps -ivh https://repo.almalinux.org/almalinux/8/PowerTools/x86_64/os/Packages/Judy-devel-1.0.5-18.module_el8.6.0+2867+72759d2f.x86_64.rpm
# dnf builddep /home/dummy/rpmbuild/SPECS/mariadb.spec
  1. hashicorp key management pluginで必要となるパッケージをインストール
# dnf -y install libcurl-devel
  1. specファイルからソースコードを展開し、パッチを適用
# su - dummy
$ rpmbuild --bp ./rpmbuild/SPECS/mariadb.spec
  1. MariaDBのリポジトリから10.9のブランチを取得し、hashicorp key management pluginをコピー
$ git clone -b 10.9 --single-branch --depth 1 https://github.com/MariaDB/server.git
$ cp -ar ./server/plugin/hashicorp_key_management ./rpmbuild/BUILD/mariadb-10.5.13-downstream_modified/plugin/
  1. hashicorp key management pluginをコンパイル
$ cd rpmbuild/BUILD/mariadb-10.5.13-downstream_modified/
$ cmake .
$ make hashicorp_key_management
  1. MariaDB Server 10.5をインストール
$ exit
# dnf -y install mariadb-server
  1. hashicorp key management pluginをプラグインディレクトリにコピー
# cp /home/dummy/rpmbuild/BUILD/mariadb-10.5.13-downstream_modified/plugin/hashicorp_key_management/hashicorp_key_management.so /usr/lib64/mariadb/plugin/
  1. hashicorp vaultをインストール
# dnf config-manager --add-repo https://rpm.releases.hashicorp.com/RHEL/hashicorp.repo
# dnf -y install vault
# setcap -r /usr/bin/vault
  1. hashicorp vaultの初期設定
# mkdir -p /root/vault/data
# cat <<EOF > /root/vault/config.hcl
storage "file" {
  path = "/root/vault/data"
}

# for normal docker only
disable_mlock = "true"

listener "tcp" {
  address       = "127.0.0.1:8200"
  tls_cert_file = "/root/vault/server.crt"
  tls_key_file  = "/root/vault/server.key"
}
EOF

# openssl genrsa 2048 > /root/vault/ca.key
# openssl req -new -key /root/vault/ca.key -subj "/CN=ca.local" > /root/vault/ca.csr
# openssl x509 -req -in /root/vault/ca.csr -signkey /root/vault/ca.key -days 365 -out /root/vault/ca.crt

# openssl genrsa 2048 > /root/vault/server.key
# echo 'subjectAltName = DNS:localhost, IP=127.0.0.1' > /root/vault/san.ext
# openssl req -new -key /root/vault/server.key -subj "/CN=localhost" > /root/vault/server.csr
# openssl x509 -req -in /root/vault/server.csr -CA /root/vault/ca.crt -CAkey /root/vault/ca.key  -CAcreateserial -days 365 -extfile /root/vault/san.ext -out /root/vault/server.crt
  1. hashicorp vaultの初期化
# vault server -config=/root/vault/config.hcl &
# VAULT_CACERT=/root/vault/ca.crt vault operator init

  1. 出力される「Unseal Key」と「Root Token」を記録

  2. hashicorp vaultを利用できるようにunsealを実施

# VAULT_CACERT=/root/vault/ca.crt vault operator unseal
Unseal Key (will be hidden): [Unseal Keyを入力]

※標準設定で3個のUnseal Keyの入力が必要なので、上記処理を異なるUnseal Keyで3回繰り返す
  1. MariaDBのTDEで利用するKVストアを有効化
# VAULT_CACERT=/root/vault/ca.crt vault login
Token (will be hidden): [Root Tokenを入力]

# VAULT_CACERT=/root/vault/ca.crt vault secrets enable --path /data -version=2 kv
  1. データ暗号化鍵を生成
# VAULT_CACERT=/root/vault/ca.crt vault kv put /data/1 data=$(openssl rand -hex 32)
  1. TDE用のアクセストークン設定
# VAULT_CACERT=/root/vault/ca.crt vault policy write tde-policy - <<EOF
path "data/*" {
  capabilities = ["read", "list"]
}
path "sys/mounts/data/*" {
  capabilities = ["read", "list"]
}
EOF

# VAULT_CACERT=/root/vault/ca.crt vault token create -field token -policy=tde-policy

※出力されるトークンを記録する
  1. MariaDBの起動
# dnf -y install sudo
# /usr/bin/mysql_install_db --user=mysql
# sudo -u mysql /usr/libexec/mysqld --basedir=/usr &
  1. 暗号化前の動作検証
# mysql -uroot -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

# mysql -uroot -e 'create database dummy'

# mysql -uroot -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| dummy              |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

# mysql -uroot dummy -e 'create table example(id int, name char(40), primary key(id))'
# mysql -uroot dummy -e 'insert into example values(1, "abcd")'
# mysql -uroot dummy -e 'insert into example values(2, "efgh")'
# mysql -uroot dummy -e 'select * from example'
+----+------+
| id | name |
+----+------+
|  1 | abcd |
|  2 | efgh |
+----+------+

# cat /var/lib/mysql/ib_logfile0 | strings | egrep 'abcd|efgh'
abcd
efgh
  1. TDEおよびhashicorp key management pluginを有効化
# cp /root/vault/ca.crt /etc/my.cnf.d/
# cat <<EOF > /etc/my.cnf.d/hashicorp.cnf
[mariadb]
plugin-load-add=hashicorp_key_management.so
hashicorp-key-management-vault-url="https://127.0.0.1:8200/v1/data"
hashicorp-key-management-token="[tokenを設定]"
hashicorp-key-management-vault-ca=/etc/my.cnf.d/ca.crt
hashicorp-key-management-timeout=15
hashicorp-key-management-max-retries=3

encrypt-binlog=1
encrypt-tmp-disk-tables=1
encrypt-tmp-files=1
innodb-default-encryption-key-id=1
innodb-encrypt-log=1
innodb-encrypt-tables=1
innodb-encrypt-temporary-tables=1
innodb-encryption-rotate-key-age=1
innodb-encryption-rotation-iops=100
innodb-encryption-threads=4
EOF
  1. 設定反映のためMariaDBを再起動
# kill $(pidof mysqld)
# sudo -u mysql /usr/libexec/mysqld --basedir=/usr &
  1. MariaDBのパラメータで暗号化状態を確認
# mysql -uroot -e 'show variables like "%encrypt%"'
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| aria_encrypt_tables              | OFF   |
| encrypt_binlog                   | ON    |
| encrypt_tmp_disk_tables          | ON    |
| encrypt_tmp_files                | ON    |
| innodb_default_encryption_key_id | 1     |
| innodb_encrypt_log               | ON    |
| innodb_encrypt_tables            | ON    |
| innodb_encrypt_temporary_tables  | ON    |
| innodb_encryption_rotate_key_age | 1     |
| innodb_encryption_rotation_iops  | 100   |
| innodb_encryption_threads        | 4     |
+----------------------------------+-------+

# mysql -uroot -e 'select * from information_schema.INNODB_TABLESPACES_ENCRYPTION\G'
*************************** 1. row ***************************
                       SPACE: 0
                        NAME: innodb_system
           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: 1
        ROTATING_OR_FLUSHING: 0
*************************** 2. row ***************************
                       SPACE: 2
                        NAME: mysql/innodb_index_stats
           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: 1
        ROTATING_OR_FLUSHING: 0
*************************** 3. row ***************************
                       SPACE: 5
                        NAME: dummy/example
           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: 1
        ROTATING_OR_FLUSHING: 0
*************************** 4. row ***************************
                       SPACE: 1
                        NAME: mysql/innodb_table_stats
           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: 1
        ROTATING_OR_FLUSHING: 0
*************************** 5. row ***************************
                       SPACE: 3
                        NAME: mysql/transaction_registry
           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: 1
        ROTATING_OR_FLUSHING: 0
*************************** 6. row ***************************
                       SPACE: 4
                        NAME: mysql/gtid_slave_pos
           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: 1
        ROTATING_OR_FLUSHING: 0
  1. ファイルの暗号化状態を確認
# cat /var/lib/mysql/ib_logfile0 | strings | egrep 'abcd|efgh'
#
# cat /var/lib/mysql/ib_logfile0 | strings | head -20
MariaDB 10.5.13
X`;a
B{9 K
YX0WA
mLos
w\q:
$Q{e
orGR
dX(m
,{^Ca
b)Q!
=9J<
tZeT
tu1v
}+m3
l7J     Z
]9Zx
BCI:
b57l%
}"?1

# cat /var/lib/mysql/dummy/example.ibd | strings | egrep 'abcd|efgh'
#
# cat /var/lib/mysql/dummy/example.ibd | strings | head -20
/:LS
fi?;
        k"I(
J(7O
\z?+
F8?,
5"3Z
rvP_
8?.CL
^:4+
{)}]i
P%F
)`tN
&Ywt
Wbce
2+Jjg6
l!0t
vxF6N
C.yL
v8bJ