PostgreSQLのpgcrypto(encrypt関数)でAES暗号化したデータをBigQueryで復号化する

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

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

```sql
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を利用した例となります

```sql
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で利用できるようにする機能です。

https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions

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

https://github.com/brix/crypto-js

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

```sql
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` を削除
```sql
substring('\\xba9a0a4189f691d761c431c89f42e319', 3)
```

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

### 16進数文字列をbase64変換
```sql
 let bas64_text = CryptoJS.enc.Base64.stringify(CryptoJS.enc.Hex.parse(_text))
```

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

### keyを16byteにする
```sql
 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を０にする
```sql
 let iv = CryptoJS.enc.Utf8.parse('')
```

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

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

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

### オプションを指定して復号化
```sql
 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で復号化する方法を紹介しました。