[Postgresql] 테이블 컬럼 암호화 및 암호화 컬러럼 데이터 INSERT, UPDATE 관련 Trigger 생성 방법

Postgresql에서 테이블을 암호화 하고자 한다면 Trigger를 생성해서 사용하면 더욱 편하게 사용할 수 있다.

 

 

아래 코드는 PostgreSQL 데이터베이스에서 test01.test테이블에 저장되는 민감한 정보를 암호화하고 관리하는 방법을 구현한 것이다.

 

01. 암호화 대상 테이블

create table test01.test(
key_fl varchar(20),
os_password varchar(50),
db_password varchar(50),
name varchar(10)
)

 

1. 기존 테이블 백업 및 새로운 암호화 테이블 생성:

   - 기존 테이블을 백업하고 새로운 암호화된 테이블을 생성합니다.
   - 암호화된 필드(os_password, db_password)를 AES-256 알고리즘을 사용하여 암호화합니다.

-- 기존 테이블 백업
CREATE TABLE test01.test_bak AS SELECT * FROM test01.test;

-- 암호화된 테이블 생성
CREATE TABLE test01.test_enc AS SELECT * FROM test01.test_bak;

-- 기존 테이블 삭제
DROP TABLE test01.test CASCADE;

 

 

 

 

 

2. 암호화 된 테이블에서 복호화 된 데이터를 보여주는 VIEW 생성:

  - test01.test 뷰에서는 암호화된 필드를 복호화하여 원본 데이터를 제공합니다.

CREATE OR REPLACE VIEW test01.test AS
(SELECT key_fl,
test01.pgp_sym_decrypt(os_password,encode(test01.get_encryption_key(),'hex')) AS os_password,
test01.pgp_sym_decrypt(db_password,encode(test01.get_encryption_key(),'hex')) AS db_password,
name
FROM test01.test_enc);

[참고] test01.get_encryption_key()는 암호화 키를 호출하는 함수로 생성 방법은 해당 포스팅 하단 링크 참조

 

 

3. INSERT 트리거 생성:

   - test01.test 뷰에 INSERT 작업이 발생하면, 대신 test01.test_enc 테이블에 암호화된 데이터를 저장합니다.

CREATE OR REPLACE FUNCTION test01.encrypt_test() RETURNS TRIGGER AS $$
DECLARE
os_password_enc bytea
db_password_enc bytea;
BEGIN
os_password_enc = test01.pgp_sym_encrypt(NEW.os_password,encode(test01.get_encryption_key(),'hex'),'cipher-algo=aes256'); -- test01.get_encryption_key() 암호화 키 호출 함수
db_password_enc = test01.pgp_sym_encrypt(NEW.db_password,encode(test01.get_encryption_key(),'hex'),'cipher-algo=aes256');
INSERT INTO test01.test_enc (key_fl, os_password, db_password, name)
VALUES (NEW.key_fl, os_password_enc, db_password_enc, NEW.name);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_insert
INSTEAD OF INSERT ON test01.test
FOR EACH ROW
EXECUTE FUNCTION test01.encrypt_test();

 

4. UPDATE 트리거 생성:

   - os_password 및 db_password 필드가 업데이트될 때마다 암호화되어 test01.test_enc 테이블에 저장됩니다.

-- db_password 업데이트 트리거
CREATE OR REPLACE FUNCTION test01.encrypt_test_db_password() RETURNS TRIGGER AS $$
BEGIN
NEW.db_password= pgportal.pgp_sym_encrypt(convert_from(NEW.db_password,'utf8'),encode(pgportal.get_encryption_key(),'hex'),'cipher-algo=aes256');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_db_password
BEFORE UPDATE OF db_password ON test01.test_enc
FOR EACH ROW
EXECUTE FUNCTION test01.encrypt_test_db_password();

-- os_password 업데이트 트리거
CREATE OR REPLACE FUNCTION test01.encrypt_test_os_password() RETURNS TRIGGER AS $$
BEGIN
NEW.os_password= pgportal.pgp_sym_encrypt(convert_from(NEW.os_password,'utf8'),encode(pgportal.get_encryption_key(),'hex'),'cipher-algo=aes256');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_os_password
BEFORE UPDATE OF os_password ON test01.test_enc
FOR EACH ROW
EXECUTE FUNCTION test01.encrypt_test_os_password();

이렇게 구성된 코드를 사용하여 test01.test 테이블의 민감한 정보를 안전하게 암호화하여 저장하고, 필요할 때 복호화하여 조회할 수 있습니다. 


[참고] test01.get_encryption_key() 암호화 키 호출 함수 생성 방법

2023.04.25 - [IT] - [postgresql] 양방향 암호화를 위한 암호화 키 생성 및 암호화 수행

[postgresql] 양방향 암호화를 위한 암호화 키 생성 및 암호화 수행