준비사항 :
-- 암호화 패키지 설치
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;
'DataBase > PostgreSQL' 카테고리의 다른 글
[PostgreSQL] 데이터 해시화, 암호화, 복호화에 대해 알아보자 (0) | 2023.09.27 |
---|