[PostgreSQL] 단방향, 양방향 암호화를 해보자

준비사항 :

-- 암호화 패키지 설치
CREATE EXTENSION IF NOT EXISTS pgcrypto;

1. 테스트 테이블 생성

// 테스트 테이블 생성
CREATE TABLE public.tb_test (
	mem_id text NOT NULL,
	mem_name text NULL,
	mobile_no text NULL,
	CONSTRAINT tb_test_pkey PRIMARY KEY (mem_id)
);

 

2. 양방향 암호화

1). 암호화 프로시저 생성

CREATE OR REPLACE FUNCTION insert_encrypted_data(mem_id text, mem_name text, mobile_no text, encryption_key text)
RETURNS VOID AS $$
BEGIN
    INSERT INTO tb_test (mem_id, mem_name, mobile_no)
    VALUES (
        mem_id,
        encrypt(convert_to(mem_name, 'utf8'), encryption_key::BYTEA, 'aes'),
        encrypt(convert_to(mobile_no, 'utf8'), encryption_key::BYTEA, 'aes')
    );
END;
$$ LANGUAGE plpgsql;

//풀이 
encryption_key text = 데이터를 암호화하는 데 사용할 암호화 키입니다.
(convert_to(mem_name, 'utf8') = 입력된 mem_name 텍스트를 UTF-8 형식의 바이트 배열로 변환
encryption_key::BYTEA = encryption_key를 바이트 배열로 변환
'aes' = AES 알고리즘을 암호화 한 후 mem_name에 저장

 

2). 복호화 프로시저 생성

CREATE OR REPLACE FUNCTION decrypt_data(input_mem_id text, encryption_key text)
RETURNS TABLE (
    mem_id text,
    mem_name text,
    mobile_no text
) AS $$
BEGIN
    RETURN QUERY SELECT
        tb.mem_id,
        convert_from(decrypt(tb.mem_name::BYTEA, encryption_key::BYTEA, 'aes'), 'utf8') AS mem_name,
        convert_from(decrypt(tb.mobile_no::BYTEA, encryption_key::BYTEA, 'aes'), 'utf8') AS mobile_no
    FROM tb_test tb
    WHERE tb.mem_id = input_mem_id;
END;
$$ LANGUAGE plpgsql;

// 풀이
encryption_key text = 복호화 할 때 사용할 암호화 키, 암호화 시에 사용된 암호화 키 값과 동일해야됨
RETURN QUERY = 함수의 반환 결과, tb_test 테이블의 데이터를 조회
decrypt = 이진 데이터 복호화
convert_from = 이진 데이터를 텍스트로 변환
tb.mem_name::BYTEA = mem_name의 값은 'BYTEA' 타입으로 저장되어 있는데 이를 바이트 배열로 변환
encryption_key::BYTEA = 복호화 시 사용할 암호화 키

 

3). 암호화 테스트

// 암호화 프로시저 호출
SELECT insert_encrypted_data('1', 'Noory', '01000001111', 'myKey');

// 결과
mem_id|mem_name                          |mobile_no                         |
------+----------------------------------+----------------------------------+
1     |\xad8214feead130ee352bcb5e138cf9df|\x4d04e275e2ecda12c17d8b9d0b08867a|

 

4). 복호화 테스트

// 복호화 프로시저 호출
SELECT * FROM decrypt_data('1', 'myKey');

// 결과
mem_id|mem_name|mobile_no  |
------+--------+-----------+
1     |Noory   |01000001111|

3. 단방향 암호화

단방향은 복호화가 안되니 주의하자 !

1). 컬럼 업데이트

UPDATE public.tb_test
SET mobile_no = digest('', 'sha256')::text
WHERE mobile_no IS NULL;

 

2). 트리거 함수 생성

CREATE OR REPLACE FUNCTION hash_mobile_no()
RETURNS TRIGGER AS $$
BEGIN
    NEW.mobile_no = digest(NEW.mobile_no, 'sha256')::text;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

// 풀이
NEW.mobile_no 값을 digest() 함수를 사용하여 SHA-256 해시로 변환한 후 다시 텍스트로 변환하여 NEW.mobile.no에 할당한다.
RETURN NEW는 트리거 함수의 결과로 업데이트 된 데이터를 반환한다.

 

3). 트리거 생성

CREATE TRIGGER hash_mobile_no_trigger
BEFORE INSERT ON public.tb_test
FOR EACH ROW
EXECUTE FUNCTION hash_mobile_no();

// 풀이
BEFORE INSERT ON public.tb_test = 트리거 작동 시기를 말한다. 여기서는 tb_test 테이블에 삽입되기 전 트리거 작동
FOR EACH ROW = 각 행마다 트리거 실행
EXECUTE FUNCTION hash_mobile_no() = 트리거가 실행될 때 hash_mobile_no() 함수를 실행하도록 지정

 

4). 암호화 테스트

-- 예제 데이터 삽입
INSERT INTO public.tb_test (mem_id, mem_name, mobile_no) VALUES ('1', 'Kim', '123-456-7890');
INSERT INTO public.tb_test (mem_id, mem_name, mobile_no) VALUES ('2', 'Lee', '555-555-5555');
INSERT INTO public.tb_test (mem_id, mem_name, mobile_no) VALUES ('3', 'Park', NULL);

-- 조회
SELECT * FROM public.tb_test;

-- 결과
mem_id|mem_name|mobile_no                                                         |
------+--------+------------------------------------------------------------------+
1     |Kim     |\x29ec0a06044bedff0bec4d81516da5843c93b7b4cb8b3ec0bf379a78085747bd|
2     |Lee     |\x89a0af94167fe6b92b614c681cc5599cd23ff45f7e9cc7929ed5fabe26842468|
3     |Park    |                                                                  |

 

암호화에 대한 자세한 설명은 다음 글에서 정리해보도록 하겠다.

 

성공 !

 

10.01일 추가 사항!

로컬에서는 이상이 없었으나 OCI postgreSQL에서 pgcrypto 패키지 설치가 안된다.

ERROR:  could not open extension control file "/usr/pgsql-13/share/extension/pgcrypto.control": No such file or directory

인스턴스 접속해서 postgreSQL 13 관련 확장 패키지를 먼저 설치해주자

yum install postgresql13-contrib

-- 암호화 패키지 설치
CREATE EXTENSION IF NOT EXISTS pgcrypto;