pgcrypto 확장 모듈을 이용한 oracle DBMS_CRYPTO 패키지 이용하는 함수들 마이그레이션

2. DBMS이야기/01. PostgreSQL

pgcrypto 확장 모듈을 이용한 oracle DBMS_CRYPTO 패키지 이용하는 함수들 마이그레이션

ioseph 2016. 3. 28. 18:37

1. pgcrypto 확장 모듈 설치

데이터베이스 관리자 권한으로 해당 데이터베이스에 접속해서,

CREATE EXTENSION pgcrypto

쿼리문을 실행

기본적으로 해당 확장 모듈에 포함된 함수들은 public 스키마에 만들어짐

2. 기본 사용법

postgres=# \dx+ pgcrypto
        "pgcrypto" 확장 기능 안에 포함된 객체들
                       객체 설명                       
-------------------------------------------------------
 function armor(bytea)
 function armor(bytea,text[],text[])
 function crypt(text,text)
 function dearmor(text)
 function decrypt(bytea,bytea,text)
 function decrypt_iv(bytea,bytea,bytea,text)
 function digest(bytea,text)
 function digest(text,text)
 function encrypt(bytea,bytea,text)
 function encrypt_iv(bytea,bytea,bytea,text)
 function gen_random_bytes(integer)
 function gen_random_uuid()
 function gen_salt(text)
 function gen_salt(text,integer)
 function hmac(bytea,bytea,text)
 function hmac(text,text,text)
 function pgp_armor_headers(text)
...
(36개 행)

함수 설명

armor, dearmor, pgp_*() 함수는 openpgp 구현 관계 쪽으로 오라클 DBMS_CRYPTO 하고 상관 없음, 여기서는 설명 생략

crypt, digest, hmac : 사용자 비밀번호 문자열 뭉개는 함수 (역함수 없음)

encrypt, encrypt_iv, decrypt, decrypt_iv : 단일키 기반 자료 암호화, 복호화

gen_salt : 임의의 salt 만드는 함수

gen_random_* : 부가 함수.

pgp (개인 메시지(email) 서명 및 암복호화에 대한 한 방법) 기반 비대칭 암복화까지를 고려하지 않는다면,

평문 뭉개는 기능(digest, hmac)과 단일키 기반 암복호화 크게 두가지로 나뉨

2.1 사용자 비밀번호 뭉개기

postgres=# select digest('mypass', 'sha256');
                               digest                               
--------------------------------------------------------------------
 \xea71c25a7a602246b4c39824b855678894a96f43bb9b71319c39700a1e045222
(1개 행)

기업마다 보안정책이 달라서 이 암호문 뭉개기 작업에 대한 정책에서 임의의 저장된 key가 있어야 하는 경우는 hmac() 함수를 사용함.

한편 뭉개진 문자열 안에 그 키(이때는 소금이라 함)를 포함시키는 전통적인 crypt() 함수는 gen_salt() 함수와 함께 사용함,

gen_salt() 에서 만드는 salt 형태에 따라 뭉개는 방식을 달리함

postgres=# select crypt('mypass',gen_salt('bf'));
                            crypt                             
--------------------------------------------------------------
 $2a$06$dRBVXc2WpqOZQZj4tmAZzur0tQ2owGnI74BKoaaNuu8uboNsaa6tW
(1개 행)

그럼 사용자가 입력한 비밀번호가 맞는지 확인하려면?

당연히 입력한 문자열을 똑같은 방식, 똑같은 키(소금)으로 뭉개서 그 뭉개진 결과가 같으면 같다라고 판단 함

팀: 즉, 그 누구도 DB에 저장된 자료를 복호화해서는 안되는 자료는 이 방식으로 뭉개야 함

비밀번호는 새로 발급 되어야 하지, 예전 비밀번호를 찾아주는 서비스를 제공하면 안됨

2.2 자료 암호화 복호화

postgres=# select encrypt('안녕하세요','내키', 'aes');
              encrypt               
------------------------------------
 \x7e1dba6095d28477fb6d30e45569f780
(1개 행)
postgres=# select decrypt(decode('7e1dba6095d28477fb6d30e45569f780','hex'), '내키', 'aes');
             decrypt              
----------------------------------
 \xec9588eb8595ed9598ec84b8ec9a94
(1개 행)
postgres=# select convert_from(decrypt(decode('7e1dba6095d28477fb6d30e45569f780','hex'), '내키', 'aes'), 'utf-8');
 convert_from 
--------------
 안녕하세요
(1개 행)

중요한 부분은 decrypt() 함수 반환자료형이 bytea 형이라는 점

그래서, 그 원본 값이 문자열이었다면, convert_from() 함수로 적당한 문자열 인코딩으로 변환해 주어야 함

encrypt_iv() 함수는 암호화 과정에서 사용할 초기화 문자열을 임의로 지정할 수 있도록 하는 것임

2.3 그 외

http://postgresql.kr/docs/current/pgcrypto.html (pgcrypto 모듈 한글 설명서)

3. 오라클 DBMS_CRYPTO 패키지를 이용한 함수 마이그레이션

3.1 비밀번호 뭉개기

Oracle: dbms_crypto.hash()

PostgreSQL: digest(), 알고리즘은 oracle에서 썼던 숫자값을 적당한 문자열로 바꿔줌 (des, md4, md5, sha1...)

3.2. 단순 암복호화

오라클 dbms_crypto.encrypt(), decrypt() 함수도 PostgreSQL의 bytea 형과 같은 raw 형을 입력 자료형으로 받기 때문에, cast() 함수를 이용해서 적당한 형 변환을 해 주어야 함

Oracle: utl_raw.cast_to_raw('01234567890123456789012345678901') 또는 UTL_I18N.STRING_TO_RAW() 형태의 raw 변환 함수는

PostgreSQL: cast('01234567890123456789012345678901' as bytea)

형태로 바꾸고,

Oracle: DBMS_CRYPTO.DES_CBC_PKCS5 형태로 오는 type 인자값

PostgreSQL: encrypt, decrypt 함수의 알고리즘 인자에 '알고리즘-모드/pad:패딩' 형태로 지정함, 윗 예제라면, 'des-cbc/pad:pkcs' 가 됨

3.3 키가 외부에 있어, utl_file 패키지를 사용한 경우

일반적으로 oracle에서는 utl_file 패키지를 이용해서 directory 객체를 만들고, 그 안에 있는 특정 파일을 읽을 수 있도록 제공하지만,

PostgreSQL에서는 $PGDATA 디렉토리 안에 있는 파일만 읽을 수 있음.

즉, 해당 키 파일은 $PGDATA 디렉토리 안으로 옮겨와야 함

파일 읽는 함수는

Oracle:

utl_file.fopen('directory', 'keyfile', 'rb');

utl_file.get_raw(fh, key_str, 32 );

utl_file.fclose();

PostgreSQL: pg_read_binary_file('keyfile',0,32); (한 줄!)

4. 기존 응용 프로그램 코드 변경을 최소화 하는 법

해당 함수의 입출력 자료형을 최대한 맞추며, (convert_from, encode, decode 함수를 이용)

oracle 패키지 형태로 만들었다면, PostgreSQL에서는 스키마를 만들고, 그 안에 함수로 등록하면,

응용 프로그램 코드 변경을 최소화 할 수 있음

(물론 프로시져로 만들어, exec, call 형태로 호출하는 경우라면, select 구문으로 부득이 변경해야겠지만)

5. 샘플 코드 전체

CREATE SCHEMA mycrypto;
CREATE OR REPLACE FUNCTION mycrypto.encrypt(p_plain character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
declare
vkey bytea;
begin
vkey := pg_read_binary_file('keyfile',0,16);
return upper(encode(encrypt_iv(cast(p_plain as bytea), vkey, cast('0123456789012345' as bytea),'aes'),'hex'));
end;
$function$;

CREATE OR REPLACE FUNCTION mycrypto.decrypt(p_encrypt character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
declare
vkey bytea;
begin
vkey := pg_read_binary_file('keyfile',0,16);
return convert_from(decrypt_iv(decode(p_encrypt , 'hex'), vkey, cast('0123456789012345' as bytea),'aes'),'utf-8');
end;
$function$;

사용예:

postgres=# select mycrypto.encrypt('무궁화꽃이피었습니다');
                             encrypt                              
------------------------------------------------------------------
 1D6337A6A1EF052D66AB80C04DBD402E5DC78E5F14FCECA963CACB9EE6A2CD5F
(1개 행)
postgres=# select mycrypto.decrypt('1D6337A6A1EF052D66AB80C04DBD402E5DC78E5F14FCECA963CACB9EE6A2CD5F');
       decrypt        
----------------------
 무궁화꽃이피었습니다
(1개 행)

Posted by 김상기