PostgreSQLの機能にpgcryptという暗号関数を提供するライブラリがあります。pgcryptで暗号化したデータをBigQueryにそのまま連携し複合化をしたいということがあったのでその方法を紹介します。

概要と結論

今回pgcryptではencryptという関数にてAESを利用してデータの暗号化を行いました、以下がそのデータを復号化するBigQueryのクエリとなります。

CREATE TEMPORARY FUNCTION decrypt(_text STRING) RETURNS STRING LANGUAGE js AS
"""
 if (_text === null) { return _text}
 let bas64_text = CryptoJS.enc.Base64.stringify(CryptoJS.enc.Hex.parse(_text))
 let key = CryptoJS.enc.Utf8.parse('PASSWORD\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000')
 let iv = CryptoJS.enc.Utf8.parse('')
 let options = { iv: iv, mode: CryptoJS.mode.CBC, padding: CryptoJS.pad.Pkcs7 };
 let _decrypt = CryptoJS.AES.decrypt(bas64_text, key, options);
 return _decrypt.toString(CryptoJS.enc.Utf8);
""" OPTIONS (library="gs://xxxxxxxxx/crypto-js-4.1.1/crypto-js.js");

SELECT decrypt(substring('\\xba9a0a4189f691d761c431c89f42e319', 3));

今回使うPostgreSQLの暗号化関数

encrypt関数

今回例として使う pgcrypt の暗号化関数として encrypt 関数を例として扱います。pgcryptには、PGP暗号化の導入もあり、暗号化のみの関数はあまり使用されませんが、今回は引っかかりポイントが多かったのと単純化のためこの関数を利用します。

暗号化方式

encrypt 関数の利用時にオプションとしてアルゴリズムやモード等を選択できます。今回はアルゴリズムをAES、その他すべてデフォルト値を利用します。また、パスフレーズはPASSWORDとします。まとめると以下のようになります。

アルゴリズム:　AES(Rijndael-128)
モード: CBC
パディング: pkcs
パスフレーズ: PASSWORD

以下がencryptを利用した例となります

sselect encrypt('text', 'PASSWORD', 'aes');

結果

BA 9A 0A 41 89 F6 91 D7 61 C4 31 C8 9F 42 E3 19

BigQueryでの復号化

UDFの利用

BigQueryでの復号化にはUDFを利用します。UDFはJavascriptで自前の関数を定義しBigQueryで利用できるようにする機能です。

crypto-js

復号化のために、UDFの中で crypto-js というライブラリを利用しました。 crypto-js はJavascriptの暗号化ライブラリです。
これを事前にGCSの特定のバケットに登録を行なっておきます。

復号化

改めて以下が、復号化するBigQueryのクエリです。

CREATE TEMPORARY FUNCTION decrypt(_text STRING) RETURNS STRING LANGUAGE js AS
"""
 if (_text === null) { return _text}
 let bas64_text = CryptoJS.enc.Base64.stringify(CryptoJS.enc.Hex.parse(_text))
 let key = CryptoJS.enc.Utf8.parse('PASSWORD\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000')
 let iv = CryptoJS.enc.Utf8.parse('')
 let options = { iv: iv, mode: CryptoJS.mode.CBC, padding: CryptoJS.pad.Pkcs7 };
 let _decrypt = CryptoJS.AES.decrypt(bas64_text, key, options);
 return _decrypt.toString(CryptoJS.enc.Utf8);
""" OPTIONS (library="gs://xxxxxxxxx/crypto-js-4.1.1/crypto-js.js");

SELECT decrypt(substring('\\xba9a0a4189f691d761c431c89f42e319', 3));

結果は以下のようになります。

text

無事復号化に成功しました。

解説

前提

まず前提として、PostgreSQLのデータをBigQueryに連携したときにString型に変換しています。Stringに変換すると先程の例だと \xba9a0a4189f6 91d761c431c89f42e319　といった形になりますこれは、暗号化データの16進数文字列になります。

16進数文字列の `\x` を削除

substring('\\xba9a0a4189f691d761c431c89f42e319', 3)

まず最初に、16進数文字列に邪魔な \x が入っているので substring 関数で削除します。

16進数文字列をbase64変換

 let bas64_text = CryptoJS.enc.Base64.stringify(CryptoJS.enc.Hex.parse(_text))

次に複合化関数に渡すデータがbase64になっている必要があるため、16進数文字列をbyte変換後にbase64を行います。

keyを16byteにする

 let key = CryptoJS.enc.Utf8.parse('PASSWORD\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000')

続いて、keyを定義します。このときPostgreSQL側でパスフレーズとして、 PASSWORD を使いました。ただし、AES(Rijndael-128)では、keyとして16byteが求められます。実はpgcryptoのencrypt関数では、バイト数が足りない場合パスフレーズの後続に0をパディングしたものがkeyをして利用されていました。そのため \u0000 で残りの8byteを埋めています。

ivを０にする

 let iv = CryptoJS.enc.Utf8.parse('')

AESのCBCモードを利用する場合iv(初期化ベクトル)が必要になりますが、PostgreSQLでは指定していませんでした。この場合、PostgreSQLではドキュメントにある通り0で埋められます。

正確にブロック長でない場合、切り詰められるか、もしくはゼロで埋められます

このあたりも、pgcryptoのencrypt関数を直接利用しないほうがいい要因になるかと考えています。

オプションを指定して復号化

 let options = { iv: iv, mode: CryptoJS.mode.CBC, padding: CryptoJS.pad.Pkcs7 };
 let _decrypt = CryptoJS.AES.decrypt(bas64_text, key, options);
 return _decrypt.toString(CryptoJS.enc.Utf8);

最後に CryptoJS.AES.decrypt 関数を利用してdecryptを行います。このとき、PostgreSQLで利用した、アルゴリズムやモードなどを指定します。最後にそれを文字列にキャストしたら完了です。

まとめ

本記事では、PostgreSQLのpgcrypto(encrypt関数)で暗号化したデータをBigQueryで復号化する方法を紹介しました。