PostgreSQL 암호화 및 암호화 컬럼 조사

새로운 직장에서 AWS RDS 환경을 사용하며 재미있게 업무를 하고 있는 와중, ORACLE to PostgreSQL 이행을 위한 준비작업으로 암호화에 대한 내용을 정리하며 수행한 작업들을 정리하였다.
.
AWS RDS를 사용하며 느끼는 점은 될 것 같은건 찾아서 하면 거의 되는데 의외의 부분에서 안되는 부분이 튀어나오고, 사용한지 5달 가량 되었는데 커뮤니티 버전 PostgreSQL에서는 상상도 못한 버그가 툭 튀어나오고는 AWS에서 해결해주는 것만을 기다려야 할 수 있다는 점이다.(단기간에 해결할 것 같지 않지만..) 다만 SCALE OUT,CLONE 등의 기능들이 편하다는 부분과 시간을 많이 아낄 수 있다는 점은 큰 장점이다.
.
암호화 얘기로 돌아오면, 기존 ASIS 시스템은 현재는 사용하면 안전하다고 할 수 없는 AES128 방식의 암호화를 진행하였고 BASE64가 아닌 HEX 방식으로 값을 저장해두었다. 그래서 TOBE에서는 AES256 방식을 사용하며 암호화가 되는 방식도 변경하기로 결정하였다.
.
실제 찾아가는 과정은 며칠 걸렸지만, 결론만 말하자면 기존 ASIS 시스템이 ORACLE에서 제공하는 DBMS_CRYPTO 패키지의 함수들을 사용하였기 때문에 간단하게 RDS에서 제공하는 PG_CRYPTO 확장 프로그램으로 대체가 가능함을 확인하였다.(구성을 돌이켜 보면 ASIS 기준 보안적인 측면으로는 처참했지만.. 덕분에 편하게 작업했다.)
.
다만 생각치 못했던 부분은 기존 ASIS에서 정확하게 어떤 컬럼들이 암호화된 값을 담고 있는지에 대한 정보를 관리하고 있지 않았다는 점이었다.
.
MAX,LENGTH 등의 함수들을 이용하여 컬럼들의 목록을 취합하였는데,AWS DMS로 이관을 테스트하던 도중 추가적으로 암호화된 컬럼이 있다는 것을 발견하였다.
.
그 이후 하나하나 조사하는 것이 현실적으로 불가능함을 깨닳고(컬럼 개수 2만개 넘음) 최대한 빠르게 암호화컬럼을 찾을 방법을 간구하였고, 그에 필요하다고 생각하는 부분들을 완료된 시점에서 정리해두었다. 굳이 암호화 컬럼을 찾는 작업이 아니어도 응용할 부분이 있어 보인다.

.
.
.

본 내용을 설명하기 전에 그 기반이 되는 조건들에 대한 조사를 하였다.
1.ASIS 오라클 암호화가 된 후에 담기는 방식이 base64가 아닌 hex 방식이었다.
이 부분에 대해서는 암호화 컬럼을 1차로 찾아서 대상을 줄일때 사용하였다. 즉 hex 방식으로 데이터를 인코딩한다고 하면 0~F 까지의 문자로 이루어지면서 길이가 32의 배수일 수 있는 것이기 때문에 정규표현식으로 해당 컬럼의 데이터를 확인하면 1차적으로 암호화된 값이 담긴 컬럼을 걸러낼 수 있다는 이야기이다.<[0-9a-fA-F]{32}>
.
2.암호화된 값이 담기는 컬럼의 데이터 타입은 문자 기반이어야 한다.
PostgreSQL 기준 데이터 타입이 TEXT인 컬럼이나 varchar(32) 이상인 컬럼인 경우 이 기준을 만족한다.
.
3.이렇게 해서 선별된 컬럼의 리스트에 ASIS의 복호화 함수를 PGCRYPTO 확장 프로그램의 함수를 사용하여 동일하게 구현한 복호화 함수를 만드는데, 이 복호화 함수에는 기본적으로 암호화 알고리즘, IV값, 암호화에 사용되었던 키 등이 필요하다. 그리고 이 복호화 함수를 호출하기 전에 기존 암호화 함수에서 어떤 방식의 인코딩을 사용하여 암호화된 값을 담고 있는지를 알아야 DECODE 함수 등을 통해 해당 암호화된 값을 복호화 함수를 호출할 때 암호화된 값을 디코딩해주는 작업이 필요하다. 그 이후에도 다시 CONVERT_FROM 함수를 사용하여 다시 UTF-8 등의 읽을 수 있는 문자로 변환하는 작업이 요구된다. 예시는 다음과 같다.
.

ENCODE(ENCRYPT(SOCIAL_SECURITY_NUMBER::bytea, 'ENCRYPTKEYWORD'::bytea, 'aes-cbc/pad:pkcs'::text), 'hex')

.
물론 이 부분을 별도의 함수로 만들었고, 해당 함수는 암호화 컬럼 파라미터 부분에 암호화되지 않은 값이 들어오면 받은 값을 그냥 리턴하도록 작성하여, 기존의 데이터가 암호화가 제대로 되어있는 데이터와 되어있지 않은 데이터가 한 컬럼에 있더라도, 복호화된 값만 추후에 만들 컬럼에는 담기도록 하였다.
.
.
.
1.PostgreSQL에서 모든 컬럼에 대한 정보는 INFORMATION_SCHEMA.COLUMNS 뷰에 담겨 있다.
.

SELECT * FROM
  INFORMATION_SCHEMA.COLUMNS
  WHERE 1=1
    AND DATA_TYPE IN ('TEXT','CHARACTER VARYING')
    AND CHARACTER_MAXIMUM_LENGTH >= 32
    AND TABLE_SCHEMA = 'etisiare';

.
.
2.PostgreSQL에서 특정 테이블의 랜덤한 ROW를 읽어오기 위해서 TABLESAMPLE + SYSTEM() 구문을 사용하였다.

.

• 처음에는 SYSTEM() 구문과 limit절을 사용하였지만 나중에는 SYSTEM_ROWS(100) 구문으로 대체하였다
  (tsm_system_rows 확장 프로그램(extension) 설치 필요)
• SYSTEM() 기반으로 만든 익스텐션이라고 하며 더 랜덤한 값을 빠르게 리턴하게 해준다(1~10ms)
  출처: [Getting random rows faster. Very much faster. – /techblog (redpill-linpro.com)]
  .
  해당 테이블의 랜덤한 블록(페이지)을 시작점으로 100줄을 가져옴.
  SELECT ... from ... TABLESAMPLE SYSTEM_ROWS(100);
  .
  .

3.해당 TABLESAMPLE + SYSTEM_ROWS() 구문을 사용하면 가져오는 ROW들 중 정규표현식 만족 여부 확인을 위해 인라인뷰를 활용하였다.

• REGEXP_MATCH() 함수는 두번째 파라미터에서 정의한 정규표현식을 만족하면 파라미터로 받은 값을 리턴하고 그렇지 않은 경우 NULL을 리턴한다.
  .

  SELECT a.cc FROM (
  SELECT 
    (regexp_match(column_name,'[0-9a-fA-F]{32}'))[1] AS cc
  FROM table_name
    TABLESAMPLE SYSTEM_ROWS(100) a 
    WHERE a.cc IS NOT NULL 

  .
  .

4.이렇게 한 값들을 FOR 문으로 먼저 언급한 복호화 함수를 수행하여 값이 복호화 함수 수행 전 값과 다른 경우 해당 컬럼과 테이블명을 기록하도록 하였다. 이를 위해 테이블을 하나 생성하였으며, FOR문에서 1000번씩 반복 수행하게 하여 최대한 확률을 높이는 방식으로 작업하였다.
.
5.이런 일련의 작업들에 대한 결과로 기존에 조사하였던 컬럼을 모두 포함하고 대략 30개 가량의 추가적인 암호화 컬럼을 찾을 수 있었다.

• TABLESAMPLE 구문을 적극 활용한 결과 1TB가 넘고 2만개가 넘는 컬럼을 모두 검사하는 함수를 호출하는데 50분 가량의 시간이 걸렸다.

이렇게 수동으로 찾아야 했던 이유는 다음과 같다.
.
1.ASIS 암호화 컬럼에 암호화되지 않은 데이터와 암호화된 데이터가 혼재되어 있다는 점
2.과거 기존 운영 인력들이 생성한 방식을 파악하지 못하는 암호화 데이터 존재(암호화된 값이지만 ASIS 복호화 함수로 복호화가 불가능)
3.추후에 이행 시점에서 암호화 컬럼이 현재와 변경될 여지가 남아 있을 수 있는 부분이 존재
4.아무도 어떤 컬럼이 암호화 컬럼인지에 대한 신뢰할 수 있는 목록을 관리하지 않고 있다는 점