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

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 김상기