ééçããŒã¿æå·åïŒTDEïŒã¯ãMySQLããã³MySQLçšã®PerconaServerã§é·ãé䜿çšãããŠããŸãããããããå éšã§ã©ã®ããã«æ©èœããTDEããµãŒããŒã«ã©ã®ãããªåœ±é¿ãäžããå¯èœæ§ãããã®ãââçåã«æã£ãããšã¯ãããŸãããïŒãã®ã·ãªãŒãºã®èšäºã§ã¯ãTDEãå éšã§ã©ã®ããã«æ©èœããããèŠãŠãããŸããæå·åãæ©èœããããã«å¿ èŠãªã®ã§ãããŒã®ä¿åããå§ããŸãããã次ã«ãPercona Server for MySQL / MySQLã§æå·åãã©ã®ããã«æ©èœããããããã³Percona Server forMySQLã§å©çšã§ããè¿œå æ©èœã«ã€ããŠè©³ããèŠãŠãããŸãã
MySQLããŒãªã³ã°
ããŒãªã³ã°ã¯ããµãŒããŒãããŒã«ã«ãã¡ã€ã«ïŒkeyring_fileïŒãŸãã¯ãªã¢ãŒããµãŒããŒïŒHashiCorp Vaultãªã©ïŒã§ããŒãç §äŒãäœæãããã³åé€ã§ããããã«ãããã©ã°ã€ã³ã§ããããŒã¯åžžã«ããŒã«ã«ã«ãã£ãã·ã¥ãããååŸãé«éåããŸãã
ãã©ã°ã€ã³ã¯ã次ã®2ã€ã®ã«ããŽãªã«åé¡ã§ããŸãã
- ããŒã«ã«ã¹ãã¬ãŒãžãããšãã°ãããŒã«ã«ãã¡ã€ã«ïŒããããã¡ã€ã«ããŒã¹ã®ããŒãªã³ã°ãšåŒã³ãŸãïŒã
- ãªã¢ãŒãã¹ãã¬ãŒãžãããšãã°ãVault ServerïŒããããµãŒããŒããŒã¹ã®ããŒãªã³ã°ãšåŒã³ãŸãïŒã
ããŒã®ä¿åãšååŸæã ãã§ãªããèµ·åæããã¹ãã¬ãŒãžã®çš®é¡ã«ãã£ãŠåäœããããã«ç°ãªãããããã®åé¢ã¯éèŠã§ãã
ãã¡ã€ã«ã¹ãã¬ãŒãžã䜿çšããå Žåãèµ·åæã«ãã¹ãã¬ãŒãžã®å å®¹å šäœïŒããŒIDãããŒãŠãŒã¶ãŒãããŒã¿ã€ããããã³ããŒèªäœïŒããã£ãã·ã¥ã«ããŒããããŸãã
ããã¯ãšã³ãããŒã«ãïŒVaultãµãŒããŒãªã©ïŒã®å Žåãèµ·åæã«ããŒIDãšããŒãŠãŒã¶ãŒã®ã¿ãèªã¿èŸŒãŸããããããã¹ãŠã®ããŒãååŸããŠãèµ·åãé ããªãããšã¯ãããŸãããããŒã¯é 延ããŒããããŸããã€ãŸããããŒèªäœã¯ãå®éã«å¿ èŠãªå Žåã«ã®ã¿VaultããããŒããããŸããããŒãããããšãããŒã¯ã¡ã¢ãªã«ãã£ãã·ã¥ããããããå°æ¥ãVaultãµãŒããŒãžã®TLSæ¥ç¶ãä»ããŠããŒã«ã¢ã¯ã»ã¹ããå¿ èŠã¯ãããŸããã次ã«ãããŒã¹ãã¢ã«ã©ã®ãããªæ å ±ãååšããããèŠãŠã¿ãŸãããã
éèŠãªæ å ±ã¯æ¬¡ã®ãšããã§ãã
- key id â , :
INNODBKey-764d382a-7324-11e9-ad8f-9cb6d0d5dc99-1
- key type â , , : «AES», «RSA» «DSA».
- key length â , AES: 16, 24 32, RSA 128, 256, 512 DSA 128, 256 384.
- user â . , , Master Key, . keyring_udf, .
ããŒã¯ãkey_idãuserã®ãã¢ã«ãã£ãŠäžæã«èå¥ãããŸãã
ããŒã®ä¿ç®¡ãšå»æ£ã«ãéãããããŸãã
ãã¡ã€ã«ã¹ãã¬ãŒãžã¯ããé«éã§ããããŒã¹ãã¢ã¯ãã¡ã€ã«ãžã®ããŒã®åçŽãª1åéãã®æžã蟌ã¿ã§ãããšæ³å®ã§ããŸãããããã§ã¯ãããŸãããããã§ã¯ããã«å€ãã®æäœãè¡ãããŠããŸãããã¡ã€ã«ã¹ãã¬ãŒãžãå€æŽãããšãæåã«ãã¹ãŠã®ã³ã³ãã³ãã®ããã¯ã¢ãããäœæãããŸãããã¡ã€ã«ã®ååãmy_biggest_secretsã§ãããšãããšãããã¯ã¢ããã¯my_biggest_secrets.backupã«ãªããŸãã次ã«ããã£ãã·ã¥ãå€æŽããïŒããŒãè¿œå ãŸãã¯åé€ããïŒããã¹ãŠãæåãããšããã£ãã·ã¥ããã¡ã€ã«ã«ãã©ãã·ã¥ãããŸãããµãŒããŒãã¯ã©ãã·ã¥ãããªã©ã®ãŸããªã±ãŒã¹ã§ã¯ããã®ããã¯ã¢ãããã¡ã€ã«ã衚瀺ãããå ŽåããããŸããããã¯ã¢ãããã¡ã€ã«ã¯ã次ã«ããŒãããŒãããããšãïŒéåžžã¯ãµãŒããŒã®åèµ·ååŸïŒã«åé€ãããŸãã
ãµãŒããŒãªããžããªã«ããŒãä¿åãŸãã¯åé€ããå Žåããªããžããªã¯ãããŒã®éä¿¡ã/ãããŒã®åé€ã®èŠæ±ãã³ãã³ãã䜿çšããŠMySQLãµãŒããŒã«æ¥ç¶ããå¿ èŠããããŸãã
ãµãŒããŒã®èµ·åé床ã«æ»ããŸããããã¹ãã¬ãŒãžèªäœãèµ·åé床ã«åœ±é¿ãäžãããšããäºå®ã«å ããŠãèµ·åæã«ã¹ãã¬ãŒãžããååŸããå¿ èŠã®ããããŒã®æ°ã®åé¡ããããŸãããã¡ãããããã¯ããã¯ãšã³ãã¹ãã¬ãŒãžã«ãšã£ãŠç¹ã«éèŠã§ããèµ·åæã«ããµãŒããŒã¯æå·åãããããŒãã«/ããŒãã«ã¹ããŒã¹ã«å¿ èŠãªããŒã確èªããã¹ãã¬ãŒãžã«ããŒãèŠæ±ããŸãããã¹ã¿ãŒããŒïŒæå·åïŒãåãããã¯ãªãŒã³ãªããµãŒããŒã«ã¯ãã¹ãã¬ãŒãžããååŸããå¿ èŠã®ãããã¹ã¿ãŒããŒã1ã€å¿ èŠã§ãããã ãããã©ã€ããªãµãŒããŒããããã¯ã¢ãããµãŒããŒã«ããã¯ã¢ããã埩å ããå Žåãªã©ãããå€ãã®ããŒãå¿ èŠã«ãªãå ŽåããããŸãããã®ãããªå Žåããã¹ã¿ãŒããŒã®ããŒããŒã·ã§ã³ãæäŸããå¿ èŠããããŸããããã«ã€ããŠã¯ä»åŸã®èšäºã§è©³ãã説æããŸãããããã§ãµãŒããŒãè€æ°ã®ãã¹ã¿ãŒããŒã䜿çšãããšãç¹ã«ãµãŒããŒåŽã®ããŒã¹ãã¢ã䜿çšããå Žåãéå§ã«å°ãæéããããå ŽåããããŸãã
ããã§ã¯ãkeyring_fileã«ã€ããŠããå°ã話ããŸãããã keyring_fileãéçºããŠãããšãããµãŒããŒã®å®è¡äžã«keyring_fileã®å€æŽã確èªããæ¹æ³ã«ã€ããŠãå¿é ããŠããŸããã 5.7ã§ã¯ããã§ãã¯ã¯ãã¡ã€ã«çµ±èšã«åºã¥ããŠå®è¡ãããŸããããããã¯çæ³çãªãœãªã¥ãŒã·ã§ã³ã§ã¯ãããŸããã§ãããã8.0ã§ã¯SHA256ãã§ãã¯ãµã ã«çœ®ãæããããŸããã
keyring_fileãåããŠå®è¡ãããšããã¡ã€ã«çµ±èšãšãã§ãã¯ãµã ãèšç®ãããŠãµãŒããŒã«ãã£ãŠèšæ¶ãããå€æŽã¯äžèŽããå Žåã«ã®ã¿é©çšãããŸãããã§ãã¯ãµã ã¯ããã¡ã€ã«ãå€æŽããããšæŽæ°ãããŸãã
ããŒã¹ãã¢ã«é¢ããå€ãã®è³ªåã«ã€ããŠã¯ããã§ã«èª¬æããŸããããã ããå¿ããããã誀解ããããããããšãå€ããã1ã€ã®éèŠãªãããã¯ããããŸããããã¯ããµãŒããŒéã§ã®ããŒã®å ±æã§ãã
ç§ãæå³ããã®ã¯ïŒã¯ã©ã¹ã¿å ã®åãµãŒããŒïŒããšãã°ãPerconaãµãŒããŒïŒã¯ãPerconaãµãŒããŒãããŒãæ ŒçŽããå¿ èŠãããVaultãµãŒããŒäžã®åå¥ã®å Žæã«ããå¿ èŠããããŸããããŒã«ãã«ä¿åãããŠããåãã¹ã¿ãŒããŒã«ã¯ããã®èå¥åå ã«PerconaãµãŒããŒã®GUIDãå«ãŸããŠããŸããã©ãããŠãããéèŠã§ããïŒ Vault Serverã1ã€ã ãã§ãã¯ã©ã¹ã¿ãŒå ã®ãã¹ãŠã®PerconaãµãŒããŒããã®åäžã®VaultServerã䜿çšããŠãããšããŸããåé¡ã¯æãããªããã§ãããã¹ãŠã®PerconaãµãŒããŒãäžæã®èå¥åãªãã§ãã¹ã¿ãŒããŒã䜿çšããŠããå ŽåïŒããšãã°ãid = 1ãid = 2ãªã©ïŒãã¯ã©ã¹ã¿ãŒå ã®ãã¹ãŠã®ãµãŒããŒã¯åããã¹ã¿ãŒããŒã䜿çšããŸãããããGUIDãæäŸãããã®ã§ããããµãŒããŒéã®éãã§ããäžæã®GUIDããã§ã«ååšããã®ã«ããªããµãŒããŒéã§ããŒãå ±æããããšã«ã€ããŠè©±ãã®ã§ããïŒãã1ã€ã®ãã©ã°ã€ã³ããããŸã-keyring_udfããã®ãã©ã°ã€ã³ã䜿çšãããšããµãŒããŒãŠãŒã¶ãŒã¯èªåã®ããŒãVaultãµãŒããŒã«ä¿åã§ããŸãããã®åé¡ã¯ããŠãŒã¶ãŒãããšãã°server1ã§ããŒãäœæããŠãããããšãã°æ¬¡ã®ããã«server2ã§åãIDã®ããŒãäœæããããšãããšãã«çºçããŸãã
--server1:
select keyring_key_store('ROB_1','AES',"123456789012345");
1
--1
--server2:
select keyring_key_store('ROB_1','AES',"543210987654321");
1
åŸ ã€ãäž¡æ¹ã®ãµãŒããŒãåãVaultãµãŒããŒã䜿çšããŠããŸãããserver2ã§keyring_key_storeé¢æ°ã倱æããã¹ãã§ã¯ãããŸãããïŒèå³æ·±ãããšã«ãåããµãŒããŒã§åãããšãå®è¡ããããšãããšããšã©ãŒãçºçããŸãã
--server1:
select keyring_key_store('ROB_1','AES',"123456789012345");
1
select keyring_key_store('ROB_1','AES',"543210987654321");
0
ããã§ããROB_1ã¯ãã§ã«ååšããŠããŸãã
æåã«2çªç®ã®äŸã«ã€ããŠèª¬æããŸããããåã«è¿°ã¹ãããã«ãkeyring_vaultãŸãã¯ãã®ä»ã®keyringãã©ã°ã€ã³ã¯ããã¹ãŠã®ããŒIDãã¡ã¢ãªã«ãã£ãã·ã¥ããŸãããããã£ãŠãæ°ããããŒãäœæããåŸãROB_1ãserver1ã«è¿œå ããããã®ããŒãVaultã«éä¿¡ããã ãã§ãªããããŒããã£ãã·ã¥ã«è¿œå ãããŸããããã§ãåãããŒãããäžåºŠè¿œå ããããšãããšãkeyring_vaultã¯ãã®ããŒããã£ãã·ã¥ã«ååšãããã©ããã確èªãããšã©ãŒãã¹ããŒããŸãã
æåã®ã±ãŒã¹ã§ã¯ãç¶æ³ãç°ãªããŸãã Server1ãšserver2ã«ã¯å¥ã ã®ãã£ãã·ã¥ããããŸãã server1ãšVaultã®ããŒãã£ãã·ã¥ã«ROB_1ãè¿œå ããåŸãserver2ã®ããŒãã£ãã·ã¥ãåæããŠããŸããã server2ã®ãã£ãã·ã¥ã«ROB_1ããŒããããŸããããããã£ãŠãROB_1ããŒã¯keyring_key_storeãšVaultãµãŒããŒã«æžã蟌ãŸããå®éã«ã¯åã®å€ãäžæžããããŸãïŒïŒïŒãçŸåšãVaultãµãŒããŒã®ããŒROB_1ã¯543210987654321ã§ããèå³æ·±ãããšã«ãVaultãµãŒããŒã¯ãã®ãããªã¢ã¯ã·ã§ã³ããããã¯ãããå€ãå€ãç°¡åã«äžæžãããŸãã
ããã§ãVaultããšã®ãµãŒããŒã«ããåå²ãéèŠã«ãªãçç±ãããããŸãããkeyring_udfã䜿çšããŠããŠãVaultã«ããŒãä¿åããå Žåã§ãã VaultãµãŒããŒã§ãã®åé¢ãã©ã®ããã«æäŸããŸããïŒ
Vaultã«åå²ããæ¹æ³ã¯2ã€ãããŸãããµãŒããŒããšã«ç°ãªãããŠã³ããã€ã³ããäœæããããšããåãããŠã³ããã€ã³ãå ã§ç°ãªããã¹ã䜿çšããããšãã§ããŸããããã¯äŸã§æããã説æãããŠããŸããããã§ã¯ãæåã«åã ã®ããŠã³ããã€ã³ããèŠãŠã¿ãŸãããã
--server1:
vault_url = http://127.0.0.1:8200
secret_mount_point = server1_mount
token = (...)
vault_ca = (...)
--server2:
vault_url = http://127.0.0.1:8200
secret_mount_point = sever2_mount
token = (...)
vault_ca = (...)
ããã§ã¯ãserver1ãšserver2ãç°ãªãããŠã³ããã€ã³ãã䜿çšããŠããããšãããããŸãããã¹ãåå²ããå Žåãæ§æã¯æ¬¡ã®ããã«ãªããŸãã
--server1:
vault_url = http://127.0.0.1:8200
secret_mount_point = mount_point/server1
token = (...)
vault_ca = (...)
--server2:
vault_url = http://127.0.0.1:8200
secret_mount_point = mount_point/sever2
token = (...)
vault_ca = (...)
ãã®å Žåãäž¡æ¹ã®ãµãŒããŒã¯åãmount_pointã䜿çšããŸããããã¹ã¯ç°ãªããŸãããã®ãã¹ã«æ²¿ã£ãŠserver1ã«æåã®ã·ãŒã¯ã¬ãããäœæããããšãVaultã¯èªåçã«ãserver1ããã£ã¬ã¯ããªãäœæããŸãã server2ã®å Žåããã¹ãŠãåãã§ãã mount_point / server1ãŸãã¯mount_point / server2ã®æåŸã®ã·ãŒã¯ã¬ãããåé€ãããšãVaultãµãŒããŒããããã®ãã£ã¬ã¯ããªãåé€ããŸãããã¹åå²ã䜿çšããŠããå Žåã¯ãããŠã³ããã€ã³ãã1ã€äœæãããµãŒããŒãåå¥ã®ãã¹ã䜿çšããããã«æ§æãã¡ã€ã«ãå€æŽããã ãã§æžã¿ãŸããããŠã³ããã€ã³ãã¯ãHTTPãªã¯ãšã¹ãã䜿çšããŠäœæã§ããŸãã CURLã䜿çšãããšã次ã®ããã«å®è¡ã§ããŸãã
curl -L -H "X-Vault-Token: TOKEN" âcacert VAULT_CA
--data '{"type":"generic"}' --request POST VAULT_URL/v1/sys/mounts/SECRET_MOUNT_POINT
ãã¹ãŠã®ãã£ãŒã«ãïŒTOKENãVAULT_CAãVAULT_URLãSECRET_MOUNT_POINTïŒã¯ãæ§æãã¡ã€ã«ã®ãã©ã¡ãŒã¿ãŒã«å¯Ÿå¿ããŸãããã¡ãããVaultãŠãŒãã£ãªãã£ã䜿çšããŠåãããšãè¡ãããšãã§ããŸãããã ããããã«ãããããŠã³ããã€ã³ãã®äœæãç°¡åã«èªååã§ããŸãããã®æ å ±ãã圹ã«ç«ãŠã°å¹žãã§ãããã®ã·ãªãŒãºã®æ¬¡ã®èšäºã§ãäŒãããŸãããã
ç¶ããèªãïŒ