[Bigquery] 특정 column 데이터 암호화 및 복호화

개요

개인정보와 관련된 데이터를 암호화하여 보관하고 특별한 상황일 때, 이를 복호화하여 사용하기 위해 조사를 합니다.

가격

가격 책정 | Cloud Key Management Service | Google Cloud

Google Cloud Pricing Calculator

활성화된 키 개수당 한 달에 0.06$이고 암호화 또는 복호화 함수를 10,000번 사용당 0.03$입니다.

만약 1개의 키만 활성화하여 컬럼들에 암호화/복호화 함수를 사용한다고 하면(이때 사용량은 5억건이라 가정) 달마다 1,500.06$가 나가게 됩니다.

KMS 키 생성하기

Quickstart: Create encryption keys with Cloud KMS | Cloud KMS Documentation | Google Cloud

KMS 키를 사용하여 bigquery 컬럼 암호화/복호화하기

Cloud KMS 키를 사용한 SQL 열 수준 암호화 | BigQuery | Google Cloud

여기서 중요한 점은 SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS raw_keyset 로 생성한 raw_keyset을 base64로 디코드한 값을 저장해야 합니다. 만약 base64로 디코드하지 않으면 암호화할 때 오류가 발생합니다.

또한 위 문서대로 따라하면 od 명령어에서 오류가 발생하는데 아래와 같이 수정해서 진행해야 합니다.

# 문서엔 아래처럼 나와있으나 에러남
$ od -An --format=o1 /tmp/bankaccounts_wrapped |tr -d '\n'|tr ' ' '\'

# 아래처럼 진행
$ od -An -t o1 /tmp/bankaccounts_wrapped |tr -d '\n'|tr -s ' ' '\'
\012\044\000\034\000\332\127\066\144\324\270\........\045\%

위와 같이 나오는데 가장 마지막인 \%는 제외한 값을 복사하여 FIRST_LEVEL_KEYSET 값에 넣어줍니다.

의문점

굳이 KMS를 써야되나 생각이 듭니다. 암호화를 진행하려면 SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS raw_keyset 로 생성한 다음, 키 체인을 만들어 암호화/복호화하는데 해당 키는 KMS와 상관이 없습니다.

또한 해당 키를 분실하면 이전에 암호화된 데이터를 복호화할 수 없습니다. 추가로 KMS의 키를 rotation 설정할 수도 없습니다.

KMS를 사용하지 않고 빅쿼리에서 제공하는 AEAD 함수를 사용하여 암호화/복호화는 아래처럼 진행할 수 있습니다.

KMS를 사용하지 않은 암호화/복호화

AEAD 암호화 함수 | BigQuery | Google Cloud

암호화할 데이터 생성

CREATE TABLE `{프로젝트}.tmp.CustomerKeysets` AS
SELECT
  1 AS customer_id,
  KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,
  'jaguar' AS favorite_animal
UNION ALL
SELECT
  2 AS customer_id,
  KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,
  'zebra' AS favorite_animal
UNION ALL
SELECT
  3 AS customer_id,
  KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,
  'nautilus' AS favorite_animal;

데이터 암호화

CREATE TABLE `{프로젝트}.tmp.EncryptedCustomerData` AS
SELECT
  customer_id,
  AEAD.ENCRYPT(keyset, favorite_animal, CAST(customer_id AS STRING))
   AS encrypted_animal
FROM
  `{프로젝트}.tmp.CustomerKeysets` AS ck;

데이터 복호화하여 조회

SELECT
    ecd.customer_id
  , AEAD.DECRYPT_STRING((
                            SELECT
                                ck.keyset
                            FROM
                                `{프로젝트}.tmp.CustomerKeysets` AS ck
                            WHERE
                                ecd.customer_id = ck.customer_id
                        ), ecd.encrypted_animal, CAST(customer_id AS STRING)) AS favorite_animal
FROM
    `{프로젝트}.tmp.EncryptedCustomerData` AS ecd
;

오류 사항

여기서 알게된 점은 KMS를 사용하지 않으면 생성한 keyset을 테이블에 꼭 저장해야 합니다. 발급된 키 (문자열 타입)를 bytes로 cast 함수로 써서 변경하려 해도 오류가 나옵니다.

# 아래 조회한 결과를 KMS_RESOURCE_NAME 변수에 저장
SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256');

DECLARE KMS_RESOURCE_NAME BYTES;
SET KMS_RESOURCE_NAME = b"CL3u8v0BEmQKWAowdHlwZS5nb29nbGVhcGlzLmNvbS9nb29nbGUuY3J5cHRvLnRpbmsuQWVzR2NtS2V5EiIaIO5VCYuRJgmDQ/wdutkHHtA0DKMTcCgzD.......";

SELECT
  customer_id,
  AEAD.ENCRYPT(KMS_RESOURCE_NAME, favorite_animal, CAST(customer_id AS STRING))
   AS encrypted_animal
FROM
  `{프로젝트}.tmp.CustomerKeysets2` AS ck;

위처럼 조회하면 keyset 을 읽다가 오류가 발생합니다.

Query error: AEAD.ENCRYPT failed: Keyset deserialization failed: Error reading keyset data: Could not parse the input stream as a Keyset-proto. at [25:1] 이렇게 keyset을 byte 타입으로 변환하려고 하면 KMS의 키가 필요합니다.

결론

위에서 KMS를 사용하지 않으면 생성한 keyset을 테이블에 꼭 저장해야 합니다. 발급된 키 (문자열 타입)를 bytes로 cast 함수로 써서 변경하려 해도 오류가 나옵니다.

keyset 생성 후 특정 테이블에 저장

CREATE TABLE `{프로젝트}.tmp.keysey` AS
SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256') as keyset;

암호화할 테이블 생성

CREATE TABLE `{프로젝트}.tmp.CustomerKeysets2` AS
SELECT
  1 AS customer_id,
  'jaguar' AS favorite_animal
UNION ALL
SELECT
  2 AS customer_id,
  'zebra' AS favorite_animal
UNION ALL
SELECT
  3 AS customer_id,
  'nautilus' AS favorite_animal;

암호화 생성

CREATE TABLE `{프로젝트}.tmp.EncryptedCustomerData2` AS
SELECT
  customer_id,
  AEAD.ENCRYPT((select keyset from `{프로젝트}.tmp.keysey`), favorite_animal, CAST(customer_id AS STRING))
   AS encrypted_animal
FROM
  `{프로젝트}.tmp.CustomerKeysets2` AS ck;

복호화하여 데이터 추출

SELECT
    ecd.customer_id
  , AEAD.DECRYPT_STRING((select keyset from `{프로젝트}.tmp.keysey`), ecd.encrypted_animal, CAST(customer_id AS STRING)) AS favorite_animal
FROM
    `{프로젝트}.tmp.EncryptedCustomerData2` AS ecd

결론

빅쿼리에서 column 암호화를 위해 KMS를 꼭 써야 되는지 잘 모르겠음

KMS를 사용하지 않고 열 암호화를 진행할 순 있으나 발급받은 키는 꼭 테이블로 관리가 되어야함