[DB] PostgreSQL pgcrypto 이해하고 AES256 암복호화 활용

해당 글에서는 PostgreSQL pgcrypto 내부 모듈을 이해하고 AES256 암복호화 활용하는 방법에 대해서 알아봅니다.

 

1) pgcrypto

---

💡 pgcrypto

- PostgreSQL 내장 확장 모듈로, DB 레벨에서 암호화/해시/난수 생성 기능을 제공합니다.
- 확장 모듈이기에 스키마 단위로 설치가 가능합니다.

 

 

2) 암호화 이해하기

---

 

[Security] 암호화/복호화 이해하기 -1 : 기초, 암호화 종류(단방향, 양방향)

 

1. 단방향 암호화

---

💡 단방향 암호화(One-Way Encryption)

- 암호화만 할 수 있고 복호화할 수 없는 암호화 기술을 의미합니다. 즉, 암호화된 데이터를 원래의 평문으로 되돌릴 수 없는 방식의 암호화를 의미합니다.
- 해당 암호화 과정에서 정보가 손실되기에 한번 해시 함수를 거친 데이터는 복호화가 불가능합니다.
- 단방향 암호화는 데이터의 기밀성보다는 무결성과 인증을 보장하는데 중점을 둡니다

 

 

 

2. 대칭형 암호(=비공개 키 암호화)

---

💡 대칭형 암호(=비공개 키 암호화)

- 평문 데이터를 암호화하거나 복호화할 때 ‘동일한 키’를 사용하는 암호화 방식을 의미합니다.
- 이 방식에서는 송신자와 수신자가 동일한 비밀 키를 공유해야 하며, 이를 이용하여 데이터를 암호화, 복호화합니다.
- 대표적인 대칭형 암호화 알고리즘으로는 AES, DES, 3DES 등이 있습니다.

 

 

3. 비 대칭형 암호화(=공개 키 암호화)

---

💡 비 대칭형 암호화(=공개 키 암호화)

- 평문 데이터를 암호화할 때와 복호화할 때 ‘서로 다른 암호화 키’를 사용하는 암호화 방식을 의미합니다.
- 이 방식에서는 누구나 접근이 가능한 ‘공개 키’로 암호화를 하며, 특정 개인이 가지고 있는 ‘비밀 키’로만 복호화가 가능합니다.
- 대표적인 비 대칭형 알고리즘으로는 RSA, DSA, ECC 등이 있습니다.

 

2) 설치 방법

---

 

1. 설치를 합니다

---

💡 설치를 합니다.

- 설치를 할 때, public 스키마가 아닌 지정된 스키마로 되어 있는지 확인해야 합니다. 일반적으로 실행을 하면 public 스키마에 확장이 설치가 됩니다.

# 확장 추가
CREATE EXTENSION IF NOT EXISTS pgcrypto;

# or 

# 확장 제거
DROP EXTENSION IF EXISTS pgcrypto;

 

 

2. 스키마 단위에서 Extension을 확인하여 설치 확인을 할 수 있습니다.

---

 

💡 [참고] 아래와 같이 SQL문으로 확인이 가능합니다.

 

 

3) 주요 함수

---

 

1. pgcrypto 제공 함수

---

카테고리	함수	설명	반환 타입
General Hashing	digest(data text, type text)	해시값 생성 (md5, sha1, sha224, sha256, sha384, sha512 + OpenSSL 지원 알고리즘)	bytea
	digest(data bytea, type text)	바이너리 데이터 해시	bytea
	hmac(data text, key text, type text)	키 기반 MAC 해시	bytea
	hmac(data bytea, key bytea, type text)	바이너리 키 기반 MAC 해시	bytea
Password Hashing	crypt(password text, salt text)	crypt(3) 스타일 패스워드 해싱/검증	text
	gen_salt(type text [, iter_count integer])	솔트 생성 (des, xdes, md5, bf, sha256crypt, sha512crypt)	text
PGP 대칭키	pgp_sym_encrypt(data text, psw text [, options text])	PGP 대칭키 암호화	bytea
	pgp_sym_encrypt_bytea(data bytea, psw text [, options text])	바이너리 PGP 대칭키 암호화	bytea
	pgp_sym_decrypt(msg bytea, psw text [, options text])	PGP 대칭키 복호화	text
	pgp_sym_decrypt_bytea(msg bytea, psw text [, options text])	바이너리 PGP 대칭키 복호화	bytea
PGP 비대칭키	pgp_pub_encrypt(data text, key bytea [, options text])	공개키 암호화	bytea
	pgp_pub_encrypt_bytea(data bytea, key bytea [, options text])	바이너리 공개키 암호화	bytea
	pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text]])	개인키 복호화	text
	pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text]])	바이너리 개인키 복호화	bytea
PGP 키/Armor	pgp_key_id(bytea)	PGP 키 ID 추출 (SYMKEY, ANYKEY 특수값 반환 가능)	text
	armor(data bytea [, keys text[], values text[]])	바이너리 → PGP ASCII Armor 변환	text
	dearmor(data text)	PGP ASCII Armor → 바이너리 변환	bytea
	pgp_armor_headers(data text, key out text, value out text)	Armor 헤더 key/value 추출	setof record
Raw 암호화	encrypt(data bytea, key bytea, type text)	로우레벨 암호화 (bf, aes + cbc/cfb/ecb + pkcs/none)	bytea
	decrypt(data bytea, key bytea, type text)	로우레벨 복호화	bytea
	encrypt_iv(data bytea, key bytea, iv bytea, type text)	IV 지정 암호화	bytea
	decrypt_iv(data bytea, key bytea, iv bytea, type text)	IV 지정 복호화	bytea
Random	gen_random_bytes(count integer)	암호학적 랜덤 바이트 (최대 1024)	bytea
	gen_random_uuid()	UUID v4 생성 (PG 18에서는 코어 함수 래퍼, obsolete)	uuid
OpenSSL	fips_mode()	OpenSSL FIPS 모드 활성화 여부	boolean

 

F.26. pgcrypto — cryptographic functions

 

 

 

[ 더 알아보기 ]

💡IV (Initialization Vector): 초기화 벡터

- 암호화할 때 맨 처음 블록에 섞어주는 랜덤 값입니다.

[ IV가 없는 경우 ]

- 평문: "홍길동" → 암호화 → A1B2C3...
- 평문: "홍길동" → 암호화 → A1B2C3... ← 항상 동일
- 평문: "홍길동" → 암호화 → A1B2C3... ← 항상 동일


[ IV가 있는 경우]

- 평문: "홍길동" + IV(랜덤1) → 암호화 → A1B2C3...
- 평문: "홍길동" + IV(랜덤2) → 암호화 → X9Y8Z7... ← 다름
- 평문: "홍길동" + IV(랜덤3) → 암호화 → P3Q4R5... ← 다름

💡 CBC (Cipher Block Chaining): 블록 체인 암호화

- 평문을 일정 크기의 블록으로 쪼개서 앞 블록의 암호문을 다음 블록 암호화에 연쇄적으로 섞는 방식입니다.

- 아래와 같이 수행이 됩니다.
1. 첫 번째 블록은 IV와 XOR 후 암호화
2. 두 번째 블록은 앞 블록의 암호문과 XOR 후 암호화
3. 이게 계속 체인처럼 연결되며 수행이 됩니다.

[IV] ──────────────────────────────┐
↓ │ [평문 블록1] → XOR → [암호화] → [암호문 블록1]
↓ [평문 블록2] → XOR → [암호화] → [암호문 블록2]
↓ [평문 블록3] → XOR → [암호화] → [암호문 블록3]

 

 

4) 활용 예시 : ASE256 기반 암복호화

---

 💡 활용 예시

- 이메일을 ASE256으로 암호화하여 저장하고, 불러오는 과정을 SQL문에서 수행하도록 처리합니다

 

1. 현재 사용자 데이터 확인

---

💡 현재 사용자 데이터 확인

- 아래와 같은 간단한 TB_USER 테이블이 있습니다.
- 해당 컬럼 중에서 user_email 컬럼에 ASE256 암호화/복호화를 이용할 예정이며, 이를 위해서는 BYTEA 타입으로 지정을 해야 합니다.

컬럼명	NULL	PK	FK	기본 값	설명
USER_SQ	BIGSERIAL	N	✅	AUTO INCREMENT	사용자 시퀀스 (PK)
USER_ID	VARCHAR(50)	N		-	사용자 사번
USER_EMAIL	BYTEA	N			사용자 이메일
USER_PW	VARCHAR(255)	N		-	비밀번호
USE_YN	BOOLEAN	N		TRUE	사용여부
REG_TS	TIMESTAMP	N		NOW()	생성일시
MOD_TS	TIMESTAMP	N		NOW()	수정일시

 

💡 ASE256 암호화의 함수의 데이터 구조

# ASE256의 암호화(입력 TEXT -> 출력 BYTEA)
pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea

# ASE256의 암호화(입력 BYTEA -> 출력 BYTEA)
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea

# ASE256의 복호화(입력 BYTEA -> 출력 TEXT)
pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text

# ASE256의 복호화(입력 BYTEA -> 출력 BYTEA)
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea

https://postgresql.kr/docs/11/pgcrypto.html

 

 

2. 데이터 암호화 및 INSERT

---

💡 데이터 암호화 및 INSERT

- 위에 데이터구조 형태로 예시로 데이터를 INSERT 합니다.

INSERT INTO tb_user (user_id, user_email, user_pw, use_yn, reg_ts, mod_ts)
VALUES (
     'TEST1234',
     pgp_sym_encrypt('test1234@gmail.com', 'Symmetric Key', 'cipher-algo=aes256'),
     '54507c79d2132cf8b4817d6837a04357a164989fb73e176364f64b00be15a591',
     true,
     NOW(),
     NOW()
 );

 

💡 아래와 같이 데이터 ROW가 등록되었습니다.

 

 

3. 데이터 복호화 및 SELECT

---

💡 데이터 복호화 및 SELECT

- ASE256의 경우는 대칭형 암호화이기에 동일한 키를 사용해서 복호화를 합니다. 이를 기반으로 복호화를 하여서 수행합니다.

 

SELECT
  user_sq,
  user_id,
  pgp_sym_decrypt(user_email, 'Symmetric Key') AS user_email,
  user_pw,
  use_yn,
  reg_ts,
  mod_ts
FROM tb_user;

 

 

 

 

 

오늘도 감사합니다. 😀