설치·접속: PostgreSQL 설치와 접속
부제: 앱 프레임워크 없이 굴리는 내부 도구에서 비밀번호 저장·민감 컬럼 암호화·감사 로그 변조 탐지를 SQL만으로 처리할 때
내부 관리 툴을 급하게 하나 만들었는데 bcrypt 라이브러리도, 암호화 미들웨어도 없다. 그렇다고 비밀번호를 평문이나 md5()로 저장할 수는 없다. pgcrypto 하나로 세 가지를 DB 안에서 끝낸다: 비밀번호는 bcrypt로, 민감 컬럼은 PGP 대칭키로, 감사 로그는 HMAC로.
CREATE EXTENSION IF NOT EXISTS pgcrypto;1단계 — 비밀번호: crypt() + gen_salt('bf')
gen_salt('bf', 8)가 bcrypt salt를 만들고 crypt()가 salt를 섞은 adaptive 해시를 만든다. 저장 컬럼에는 salt까지 통째로 들어간다.
CREATE TABLE users (id serial primary key, username text, pwhash text);
INSERT INTO users(username, pwhash)
VALUES ('alice', crypt('secret123', gen_salt('bf', 8)));
SELECT left(pwhash, 4) AS algo, length(pwhash) AS len FROM users; algo | len
------+-----
$2a$ | 60
(1 row)$2a$는 bcrypt 식별자, 길이 60은 bcrypt 해시 표준 형식이다. 검증은 저장된 해시를 salt로 다시 써서 입력값을 같은 방식으로 해싱해 비교한다 — pwhash = crypt(입력, pwhash).
SELECT username,
(pwhash = crypt('secret123', pwhash)) AS login_right,
(pwhash = crypt('wrongpw', pwhash)) AS login_wrong
FROM users; username | login_right | login_wrong
----------+-------------+-------------
alice | t | f
(1 row)맞는 비밀번호는 t, 틀린 건 f. md5(pw)처럼 salt 없는 빠른 해시로 저장하면 레인보우 테이블·무차별 대입에 뚫린다. bcrypt는 의도적으로 느려서 대입 공격 비용을 올린다.
2단계 — 민감 컬럼: pgp_sym_encrypt / pgp_sym_decrypt
카드번호 같은 컬럼은 해시가 아니라 복호화가 되는 암호화가 필요하다. pgp_sym_encrypt(data, password)는 표준 PGP 포맷(무결성·압축 포함) bytea를 만든다.
ALTER TABLE users ADD COLUMN card bytea;
UPDATE users SET card = pgp_sym_encrypt('4111-1111-1111-1111', 'k3y-pass')
WHERE username='alice';
SELECT left(encode(card,'hex'), 24) AS ciphertext_hex FROM users; ciphertext_hex
--------------------------
c30d0407030290d09bc99a42
(1 row)저장된 실제 바이트는 이렇게 암호문이다. 권한 있는 조회에서만 키를 넣어 복호화한다.
SELECT pgp_sym_decrypt(card, 'k3y-pass') AS card_plain FROM users; card_plain
---------------------
4111-1111-1111-1111
(1 row)키가 틀리면 복호화 자체가 실패한다.
SELECT pgp_sym_decrypt(card, 'wrong-key') FROM users;ERROR: Wrong key or corrupt data3단계 — 무결성: hmac()로 변조 탐지
감사 로그가 우회 UPDATE로 몰래 바뀌었는지 알고 싶다. digest()는 그냥 SHA 해시라 값을 바꾼 사람이 해시도 같이 갱신하면 들통나지 않는다. hmac(data, key, type)는 비밀키를 알아야 계산할 수 있는 키드 해시라, 키를 모르는 공격자는 유효한 MAC을 못 만든다.
CREATE TABLE audit (id serial primary key, event text, mac bytea);
INSERT INTO audit(event, mac)
VALUES ('user alice deleted', hmac('user alice deleted', 'server-secret', 'sha256'));
SELECT event, (mac = hmac(event, 'server-secret', 'sha256')) AS intact FROM audit; event | intact
--------------------+--------
user alice deleted | t
(1 row)누군가 로그 본문만 바꾸고 MAC은 그대로 두면 재계산 값이 어긋난다.
UPDATE audit SET event = 'user bob deleted' WHERE id = 1;
SELECT event, (mac = hmac(event, 'server-secret', 'sha256')) AS intact FROM audit; event | intact
------------------+--------
user bob deleted | f
(1 row)intact = f — 변조 탐지. 비밀키가 DB 밖(앱 설정)에 있어야 의미가 있다. 키까지 같은 테이블에 두면 공격자가 MAC을 다시 만들 수 있으니 무의미하다.
결론 — 언제 이 조합을 쓰나
세 함수 모두 평문이 클라이언트와 서버 사이를 오간다. pgp_sym_encrypt의 원본, crypt에 넣는 비밀번호, 복호화 결과 전부 연결 위로 흐르므로 SSL 연결이 없으면 암호화가 무의미하다. 암호화 키/HMAC 키를 SQL 리터럴로 쓰면 pg_stat_statements나 로그에 남을 수 있으니 파라미터 바인딩으로 넘긴다.
역할 분담: 앱 프레임워크(Rails/Django/Spring)가 있으면 비밀번호 해싱은 보통 앱단 bcrypt에 맡기는 게 표준이다. pgcrypto가 빛나는 곳은 (1) 프레임워크 없는 내부 도구·배치, (2) 앱을 거치지 않는 경로까지 막아야 하는 민감 컬럼 암호화, (3) DB단에서 완결돼야 하는 감사 로그 무결성이다. 키 관리(로테이션·보관)는 DB가 대신해 주지 않으니 그 책임은 여전히 애플리케이션 쪽에 남는다.
이렇게도 쓴다
비밀번호 강도를 올린다 — bcrypt cost를 8에서 12로. 로그인 검증 시간이 늘어 대입 공격이 더 비싸진다.
INSERT INTO users(username, pwhash)
VALUES ('bob', crypt('pw', gen_salt('bf', 12)));
민감 컬럼을 비대칭(공개키)으로 암호화한다 — 암호화 서버는 공개키만, 복호화는 개인키 보유 서버만. (조합: pgp_pub_encrypt)
-- pgp_pub_encrypt(data, dearmor(public_key)) / pgp_pub_decrypt(msg, dearmor(secret_key))
digest로 파일/문자열 지문을 만든다 — 키가 필요 없는 순수 해시. 무결성 아닌 중복 판별·캐시 키 용도.
SELECT encode(digest('hello', 'sha256'), 'hex');
-- 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824
감사 로그에 HMAC 검증을 트리거로 강제한다. (조합: BEFORE UPDATE 트리거)
-- BEFORE UPDATE 트리거에서 OLD.mac = hmac(OLD.event, key, 'sha256') 를 확인해
-- 어긋나면 RAISE EXCEPTION 으로 이미 변조된 행의 갱신을 거부암호화가 필요 없고 "같은 값인지"만 보면 된다면 crypt/hmac 쪽으로, 반드시 원문을 되살려야 하면 pgp_sym/pub_encrypt 쪽으로 간다. 저장 후 절대 복원할 필요가 없는 비밀번호에 대칭 암호화를 쓰는 건 키 유출 위험만 늘리는 실수다.