[Bigquery] Postgresql AES 암호화 데이터 Bigquery에서 복호화하기

여기서는 bigquery에서 AES 복호화 하는 방법을 설명합니다.

Postgresql AES 암호화 설명

postgresql에서 AES로 암호화는 pgcrypto 라는 extension을 설치해야 사용할 수 있습니다. postgresql 암호화는 https://brownbears.tistory.com/545 에서 설명되어 있습니다.

pgcrypto에서 encrypt 함수를 사용해 AES 암호화를 진행한다면 AES-128만 가능합니다. 또한 padding은 pkcs라고만 되어 있는데 정확하게는 pkcs7입니다. 마지막으로 iv(initial vector)는 0x00을 16바이트로 채워지게 됩니다.

• 초기 벡터는 원본의 길이가 벡터 길이의 배수보다 모자랄 때, 나머지를 채우는 용도로 사용됩니다.
• AES 알고리즘은 16바이트 고정 블록 단위이기 때문에 AES 128, 192, 256 모두 16바이트 크기의 결과를 반환합니다. 그러므로 iv 또한 16바이트 고정입니다.

AEAD를 사용해 AES 데이터 복호화하기

먼저 아래와 같이 진행할 수 있습니다.

-- 예시
SELECT
  AEAD.DECRYPT_STRING(
    KEYS.ADD_KEY_FROM_RAW_BYTES(
      b'', 'AES_CBC_PKCS', FROM_BASE64('dfrBArd8b6YZFDGTYrZtQa==')),
    FROM_BASE64('/FCM1XMvr+rHwZx'), '');

-- 테스트
    WITH EncryptedInput AS (
  SELECT FROM_HEX('deed2a88e73dccaa30a9e6e296f62be27db30db16f76d3f42c85d31db3f46376') AS ciphertext,
    b'1234567890123456' AS key UNION ALL
  SELECT FROM_HEX('deed2a88e73dccaa30a9e6e296f62be2ea3f4c2ac2c8863306fd9ff87e10497b61d86111fafd0d0fe0046d7e199044ec'),
    b'1234567890123456' UNION ALL
  SELECT FROM_HEX('0102030405060708090a0b0c0d0e0f1073d8712936ea9899952e97284288c1cd7b7cbfff0a53ae87a19454f7d84082a07a25fc01031b5e08c6b7ce6520989b82'),
    b'98765432101234567890123456789012' UNION ALL
  SELECT NULL, b'1234567890123456' UNION ALL
  SELECT FROM_HEX('deed2a88e73dccaa30a9e6e296f62be27db30db16f76d3f42c85d31db3f46376'),
    NULL
)
SELECT AEAD.DECRYPT_STRING(KEYS.ADD_KEY_FROM_RAW_BYTES(b'', 'AES_CBC_PKCS', key), ciphertext, '') AS plaintext
FROM EncryptedInput;

-- 만약 key가 11dd1c786102... 와 같이 hex string 타입이면 아래와 같이 변환해야 한다.
-- select CAST("11dd1c786102..." AS BYTES FORMAT 'HEX');
---- 또는
-- select FROM_HEX('11dd1c786102...');

하지만 postgresql로 암화한 데이터는 복호화가 되지 않습니다. 아직 확실한 이유는 알 수 없지만 아래와 같이 두 가지 케이스를 의심하고 있습니다.

iv값 지정을 할 수가 없음

1. 암호화 텍스트의 가장 첫 바이트에 iv가 포함되었는지 모르고 복호화할 때, 지정하는 곳이 없음
2. https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#aeaddecrypt_bytes

AES 알고리즘이 다름

1. bigquery에서는 AES-256만 지원하지만 pgcrypto에서 encrypt 함수에서 지원하는 AES는-128임

이러한 이유로 자바스크립트 UDF로 복호화를 진행합니다.

자바스크립트 UDF

bigquery에서는 유저가 함수를 정의할 수 있는데 sql과 자바스크립트 방식 2가지를 지원합니다. 또한 자바스크립트의 경우, GCS에 외부 라이브러리 패키지가 존재한다면 이를 연결해 사용할 수도 있습니다.

먼저 자바스크립트로 aes 암호화를 복호화 하는 테스트를 먼저 진행합니다. 여기서 사용한 라이브러리는 cryptojs 입니다.

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

→ 빅쿼리에서 사용을 편하게 하기 위해 모든 라이브러리를 포함하여 접근하도록 진행함.

const CryptoJS = require("crypto-js");

// 만약 키가 11dd1c786102... 와 같이 hex string 타입이라면 아래와 같이 parse 진행
// const key = CryptoJS.enc.Hex.parse("11dd1c786102...");
const key = CryptoJS.enc.Utf8.parse('1234567890123456');

const options = {
    mode: CryptoJS.mode.CBC,
    padding: CryptoJS.pad.Pkcs7,
    iv: CryptoJS.enc.Hex.parse('00000000000000000000000000000000')
};

// Encrypt
const cipherText = CryptoJS.AES.encrypt('암호 테스트', key, options).toString();
console.log('암호화 결과: ', cipherText);

// Decrypt
const decryptText = CryptoJS.AES.decrypt(cipherText, key, options);
const originalText = decryptText.toString(CryptoJS.enc.Utf8);

console.log('복호화 결과: ', originalText);

iv값을 보면 0을 32개 입력했는데 16진수를 입력해야 하므로 32개를 입력한 형태입니다.

위의 예제를 따라하면 pgcrypto에서 aes 암호화를 진행한 키와 암호화 결과 데이터를 복호화한 테스트는 성공하는 것을 볼 수 있습니다.

이제 이 코드를 bigquery의 자바스크립트 UDF로 표현을 해줍니다.

https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions?hl=ko#javascript-udf-structure

위의 테스트한 cryptojs 라이브러리를 로컬에 설치한 다음, 해당 폴더를 GCS에 업로드 해줍니다.

• 업로드한 경로는 다음 자바스크립트 UDF에서 사용해야 하므로 기억하고 있어야 합니다.

다음, 아래와 같이 임시 udf를 설정하고 내부에 위의 복호화 로직을 넣어 테슽 해봅니다.

CREATE TEMPORARY FUNCTION decrypt(ciphertext STRING) RETURNS STRING LANGUAGE js 
OPTIONS (
    library=["gs://{라이브러리넣은버킷경로}/crypto-js/crypto-js.js"]
  )
  AS
r"""
  const key = CryptoJS.enc.Utf8.parse('1234567890123456');
  let options = { mode: CryptoJS.mode.CBC, padding: CryptoJS.pad.Pkcs7, iv: CryptoJS.enc.Hex.parse('00000000000000000000000000000000')};
  let _decrypt = CryptoJS.AES.decrypt(ciphertext, key, options).toString(CryptoJS.enc.Utf8);
  return _decrypt;
""";

select decrypt("VaTZ2jlp38fAg/g4Uj6aliOITx8xe/1zeaOR85zIbs8=");

결과가 잘 나오는 것을 확인할 수 있습니다.

마지막으로 매번 복호화 할 때마다 임시 udf를 생성할 순 없으니 영구적으로 UDF를 등록합니다.

CREATE OR REPLACE FUNCTION `{project}.{dataset}.decrypt` (ciphertext STRING) RETURNS STRING LANGUAGE js 
OPTIONS (
    library=["gs://{라이브러리넣은버킷경로}/crypto-js/crypto-js.js"]
  )
  AS
r"""
  const key = CryptoJS.enc.Utf8.parse('1234567890123456');
  let options = { mode: CryptoJS.mode.CBC, padding: CryptoJS.pad.Pkcs7, iv: CryptoJS.enc.Hex.parse('00000000000000000000000000000000')};
  let _decrypt = CryptoJS.AES.decrypt(ciphertext, key, options).toString(CryptoJS.enc.Utf8);
  return _decrypt;
""";

성능

외부 라이브러리를 사용한 자바스크립트 UDF는 아래 누군가가 벤치마킹한 표를 보면 성능은 좋지 않은 것을 볼 수 있습니다.

https://stackoverflow.com/questions/53274323/field-level-encryption-in-big-query

레퍼런스

https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions

https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions?hl=ko#javascript-udf-structure

https://www.postgresql.kr/docs/9.5/pgcrypto.html

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

https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions?hl=ko#javascript-udf-structure