고객 전화번호를 DB에 평문으로 두면 백업 파일이나 읽기 권한이 있는 계정만으로도 그대로 보입니다. 이 글은 전화번호 컬럼을 pgp_sym_encrypt로 암호화해 저장하고, 상담원 역할에는 가운데가 가려진 값만, 관리자 역할에는 복호화한 값을 보여 주는 구성을 만들어 봅니다. 그리고 이 방식이 지키지 못하는 부분(서버 로그에 남는 키, 암호문 검색 불가)도 직접 확인합니다.
Windows Git Bash에서 Docker Desktop으로 실행했습니다. 고객 데이터는 직접 만든 가짜 값(이름, 010-5550-100x)이고, 키와 비밀번호는 연습용입니다. 실제 서비스에서는 이 키 이름과 값을 그대로 쓰지 않습니다. 컨테이너는 호스트 127.0.0.1의 18170번 포트에만 연결합니다.
준비물
| 항목 | 확인한 값 |
|---|---|
| 실행 환경 | Docker 29.5.3, Windows Git Bash |
| PostgreSQL | 공식 postgres:16 이미지, 서버 16.15 |
| psql | 16.15 (\getenv, \bind는 psql 16 기준) |
| pgcrypto | 확장 버전 1.3 (이미지에 포함, 추가 설치 없음) |
| pg_stat_statements | 확장 버전 1.10 |
작업 폴더는 아래 아홉 파일입니다. 전체 내용은 글 끝의 ## 전체 코드에 있습니다.
start.sh
setup.sql
seed.sql
roles.sql
agent_check.sql
admin_naive.sql
admin_safe.sql
search_check.sql
log_params_off.sql
이 글의 명령에는 /로 시작하는 경로가 없어서 Git Bash의 경로 변환(MSYS_NO_PATHCONV) 설정은 필요하지 않았습니다.
1. 컨테이너, 표, 암호화한 데이터 준비
start.sh는 postgres:16을 log_statement=all과 pg_stat_statements를 켠 채로 띄웁니다. 운영 서버는 보통 이 값을 다르게 두지만, 키가 어디에 남는지 눈으로 보려고 일부러 켰습니다. setup.sql은 pgcrypto 확장과 customers 표를 만들고, seed.sql은 가짜 고객 5명을 넣습니다. 전화번호는 세 갈래로 저장합니다.
phone_enc:pgp_sym_encrypt(전화번호, 키, 'cipher-algo=aes256')로 만든 암호문phone_idx: 같은 값 검색용hmac(전화번호, 키, 'sha256')phone_mask: 입력할 때 가운데를****로 바꿔 둔 값
공식 문서 기준 pgp_sym_encrypt의 기본 암호는 aes128이고 cipher-algo로 바꿉니다. 마스킹 값을 미리 저장하는 이유는 다음 단계에서 설명합니다.
sh start.sh
docker exec -i cr-p22-db psql -U postgres < setup.sql
docker exec -i cr-p22-db psql -U postgres -v key=lab-key-2026 < seed.sql
docker exec -i cr-p22-db psql -U postgres < roles.sql
docker exec cr-p22-db psql -U postgres -c "SELECT id, name, phone_mask, left(encode(phone_enc,'hex'),40) AS phone_enc_hex FROM customers ORDER BY id"

seed.sql의 :'key'는 psql이 클라이언트에서 문자열로 펼친 뒤 서버로 보냅니다. 이 점이 4단계의 문제로 이어집니다.
2. 역할과 뷰로 보이는 값 나누기
roles.sql은 두 역할을 만듭니다. cr_agent(상담원)와 cr_admin(관리자)이고, 표 customers는 PUBLIC 권한을 모두 회수했습니다. 뷰는 두 개입니다.
customers_masked: 이름 첫 글자 뒤를**로, 전화번호는phone_mask를 보여 줍니다. 상담원과 관리자가 읽습니다.customers_plain:pgp_sym_decrypt(phone_enc, current_setting('app.key'))로 복호화한 값을 보여 줍니다. 관리자만 읽습니다.
마스킹 뷰가 암호문을 풀지 않고 저장해 둔 phone_mask를 쓰는 이유는, 상담원 쪽 경로에 키가 필요 없게 하기 위해서입니다. 복호화 키는 뷰 정의에 쓰지 않고 세션 설정 app.key에서 읽습니다. 뷰 정의에 키를 쓰면 키가 DB 카탈로그 안에 그대로 저장됩니다. 관리자 쪽은 admin_safe.sql이 환경 변수 LAB_KEY에서 키를 읽어 app.key에 넣고 조회합니다(4단계에서 이유를 봅니다).
docker exec -i cr-p22-db psql -U cr_agent -d postgres < agent_check.sql
docker exec -i -e LAB_KEY=lab-key-2026 cr-p22-db psql -U cr_admin -d postgres < admin_safe.sql

상담원이 customers와 customers_plain을 읽으려 하면 permission denied가 납니다. 뷰는 소유자(여기서는 postgres) 권한으로 표를 읽기 때문에, 상담원에게 표 권한을 주지 않아도 마스킹 뷰는 동작합니다. 이 컨테이너에서는 소켓 접속이 비밀번호 없이 통과하는 기본 설정이라 역할에 비밀번호를 두지 않았습니다. 실제 서버에서는 인증 방식을 따로 정해야 합니다.
3. 암호문은 같은 값도 매번 다르다
같은 전화번호를 같은 키로 두 번 암호화해서 비교하면 결과가 다릅니다. 공식 문서 설명대로 데이터 앞에 무작위 바이트가 붙고 S2K 솔트도 무작위이기 때문입니다. 6단계 search_check.sql 출력의 same_ciphertext가 f로 나옵니다. 이 성질은 암호화 관점에서는 장점이지만 검색에는 방해가 되며, 자세한 내용은 6단계에서 봅니다.
4. 키를 SQL 문에 쓰면 서버 로그에 남는다
admin_naive.sql은 흔히 쓰는 방식입니다. SET app.key = '키';로 세션에 키를 넣고 조회합니다. 이 실행 뒤에, 앞 단계의 seed.sql과 2단계의 admin_safe.sql(기본 로그 설정 상태)까지 포함해 로그에 키가 어디에 남았는지 확인합니다.
docker exec -i cr-p22-db psql -U cr_admin -d postgres < admin_naive.sql
docker logs cr-p22-db 2>&1 | grep 'lab-key-2026' | cut -c1-150
docker exec cr-p22-db psql -U postgres -At -c "SELECT query FROM pg_stat_statements WHERE query LIKE '%lab-key%'"

확인한 것은 네 가지입니다.
seed.sql의 INSERT 문에 쓴 키가log_statement=all로그에 두 줄로 남았습니다.SET app.key = '...'도 문장 그대로 로그에 남았습니다.\bind로 키를 매개변수로 넘긴admin_safe.sql도 기본 설정에서는DETAIL: parameters: $1 = '...'줄로 값이 남았습니다. 매개변수로 넘기는 것만으로는 부족합니다.pg_stat_statements는 SELECT와 INSERT의 상수를$1형태로 바꿔 저장해 키가 보이지 않았지만,SET문은 바꾸지 않아 키가 그대로 있었습니다. 이 동작은 이 버전(1.10)에서 직접 본 결과입니다.
공식 문서도 log_statement 항목에서 기록된 문장에 평문 비밀번호가 들어 있을 수 있다고 경고합니다. 로그 외에도 psql 기록 파일, 애플리케이션 로그, 오류 메시지의 STATEMENT 줄에 키가 남을 수 있는데, 이 글에서는 위의 네 가지만 확인했습니다.
5. 키를 로그에 덜 남기는 방법
시험해 본 완화 방법은 두 가지를 함께 쓰는 것입니다.
- 키를 SQL 문 글자가 아니라 바인드 매개변수로 넘깁니다.
admin_safe.sql은\getenv key LAB_KEY로 환경 변수를 읽고SELECT set_config('app.key', $1, false) ... \bind :key \g로 보냅니다. 로그에는$1만 남습니다. 키를:'key'로 감싸면 따옴표까지 값에 들어가 키가 달라지므로\bind :key로 씁니다. - 서버에서
log_parameter_max_length = 0으로 바인드 매개변수 값을 로그에 쓰지 않게 합니다. 공식 문서 기준 이 설정은log_statement등 오류가 아닌 문장 로그의 매개변수 값에 적용됩니다.log_params_off.sql이ALTER SYSTEM과pg_reload_conf()로 반영합니다.
docker logs cr-p22-db 2>&1 | grep -c 'lab-key-2026'
docker exec -i cr-p22-db psql -U postgres < log_params_off.sql
sleep 1; docker exec -i -e LAB_KEY=lab-key-2026 cr-p22-db psql -U cr_admin -d postgres < admin_safe.sql
docker logs cr-p22-db 2>&1 | grep -c 'lab-key-2026'
docker logs cr-p22-db 2>&1 | tail -4 | cut -c1-130

설정 변경 뒤 같은 명령을 다시 실행해도 키가 들어 있는 줄 수는 4로 그대로입니다. 이전 줄은 로그에 남아 있으므로, 이미 키가 로그에 남았다면 그 키는 바꿔야 합니다.
남는 한계가 있습니다. -e LAB_KEY=...로 넘기면 명령 줄에 키가 보입니다. 이 글에서는 연습용 값이라 그대로 썼습니다. 운영에서는 비밀 저장소나 파일에서 읽어 오는 방식이 필요하며 그 부분은 시험하지 않았습니다. 또 log_statement=all을 켜면 문장에 직접 들어 있는 전화번호 같은 데이터 값도 로그에 남습니다.
6. 암호문 컬럼은 검색과 인덱스에 불리하다
search_check.sql은 세 가지를 확인합니다. 표를 직접 읽어야 해서 postgres 역할로 실행합니다.
docker exec -i -e LAB_KEY=lab-key-2026 cr-p22-db psql -U postgres < search_check.sql

- 같은 값을 두 번 암호화한 결과는 다릅니다(
same_ciphertext가f). 그래서 암호문끼리 비교하는WHERE phone_enc = ...는 0건입니다. WHERE pgp_sym_decrypt(phone_enc, ...) = '...'는 모든 행을 복호화해 비교합니다.enable_seqscan = off로 순차 스캔을 꺼도 계획이Seq Scan이어서, 이 조건에는 인덱스를 쓸 수 없습니다. 행이 5개뿐이라 시간은 의미가 없어서 계획만 확인했습니다.- 같은 값 검색용으로 둔
phone_idx = hmac(...)는customers_phone_idx인덱스를 씁니다. 다만 완전히 같은 값만 찾습니다. 앞부분 일치, 부분 검색, 범위 검색은 해시로 할 수 없습니다. 같은 전화번호가 같은 해시가 되므로 중복 여부가 드러나는 점도 알아 둡니다.
키 관리의 한계
이 구성은 DB 파일, 백업, 읽기 전용 권한만 가진 계정이 전화번호를 읽는 것을 어렵게 합니다. 그러나 다음은 해결하지 못합니다.
- 공식 문서는 pgcrypto 함수가 모두 DB 서버 안에서 실행되어 데이터와 비밀번호가 클라이언트와 pgcrypto 사이를 평문으로 오간다고 설명합니다. 그래서 로컬 접속이나 SSL 접속을 쓰고 시스템 관리자와 DB 관리자를 믿을 수 있어야 하며, 믿을 수 없다면 클라이언트 애플리케이션에서 암호화하라고 적혀 있습니다.
- 키를 아는 사람이 DB 안에서
SET app.key를 하면 복호화됩니다. 키를 DB 안(뷰 정의, 함수 본문, 컬럼 값)에 두면 DB를 가져간 사람이 키도 가져가므로 암호화의 의미가 크게 줄어듭니다. 이 글은 키를 DB에 저장하지 않았지만, 서버 로그, 환경 변수, 애플리케이션 설정에 키가 있으면 같은 위험이 다른 곳으로 옮겨 갑니다. - 이 글은 연습을 단순하게 하려고 암호화(
phone_enc)와 검색용phone_idx의hmac에 같은 키를 썼습니다. 전화번호는 가능한 값의 범위가 좁아서, 키가 새면 후보 번호를 하나씩hmac으로 계산해phone_idx와 맞춰 보는 방식으로 원래 번호를 쉽게 찾아낼 수 있습니다. 실제로 검색용hmac을 둔다면 암호화 키와 다른 검색용 키를 따로 만들어 따로 보관합니다. 이 글의 코드는 같은 키로 실행한 것이며, 키를 분리한 구성은 실행해 보지 않았습니다. - 마스킹 뷰는 권한 분리 수단이지 암호화가 아닙니다.
phone_mask는 평문 일부이므로 필요한 만큼만 남깁니다.
자주 막히는 곳
| 증상 | 원인과 해결 |
|---|---|
ERROR: Wrong key or corrupt data |
세션의 app.key가 암호화할 때의 키와 다름. \bind :'key'처럼 따옴표로 감싸면 값에 따옴표가 들어가서 이 오류가 났고, \bind :key로 고쳐 해결 |
permission denied for table customers |
상담원이 표를 직접 읽은 경우로 의도한 동작. 마스킹 뷰를 읽게 함 |
| 설정을 바꿨는데 키가 로그에 계속 보임 | 바뀌는 것은 이후에 쓰는 로그뿐이고 이전 줄은 그대로 남음 |
이어서 해 볼 것
- 키를 환경 변수 대신 파일이나 비밀 저장소에서 읽어 오는 방식으로 바꿔 봅니다.
pgp_pub_encrypt(공개키 방식)로 쓰기 쪽에는 공개키만 두는 구성을 시험합니다.- 마스킹 뷰에 행 단위 보안(RLS)을 더해 담당 고객만 보이게 합니다.
확인 범위
- 확인한 것: 위의 모든 명령과 출력은 Docker 컨테이너(postgres:16, 서버 16.15)에서 실제로 실행한 결과입니다. pgcrypto 함수와 옵션, 보안 한계는
https://www.postgresql.org/docs/current/pgcrypto.html에서,log_parameter_max_length와log_statement의 동작은https://www.postgresql.org/docs/16/runtime-config-logging.html에서 확인했습니다. pgcrypto 문서에는 서버 로그에 키가 남는다는 서술이 없었고, 로그 노출은 이 글에서 직접 실행해 확인한 내용입니다. - 하지 못한 것: 대량 데이터의 성능 측정, 공개키 방식, 키 저장소 연동, SSL 접속, 백업 파일에서의 노출 확인은 하지 않았습니다. Linux/macOS 셸에서는 실행하지 않았습니다(명령은 같은
docker,sh문법입니다). - 범위의 한계:
pg_stat_statements가SET문을 정규화하지 않는 동작은 확장 버전 1.10에서만 확인했습니다. 가짜 고객 5명만 썼고 개인정보 보호 법령상의 요건을 충족하는지는 다루지 않습니다.
전체 코드
작업 폴더 구조는 아래와 같습니다.
start.sh
setup.sql
seed.sql
roles.sql
agent_check.sql
admin_naive.sql
admin_safe.sql
search_check.sql
log_params_off.sql
start.sh
#!/bin/sh
# 연습용 PostgreSQL 16 을 띄웁니다. 키 노출 위험을 눈으로 보려고 log_statement=all 로 시작합니다.
docker volume create cr-p22-data >/dev/null
docker run -d --name cr-p22-db \
-e POSTGRES_PASSWORD=lab-admin-pw \
-p 127.0.0.1:18170:5432 \
-v cr-p22-data:/var/lib/postgresql/data \
postgres:16 \
-c log_statement=all \
-c shared_preload_libraries=pg_stat_statements
until docker exec cr-p22-db pg_isready -U postgres >/dev/null 2>&1; do sleep 1; done
sleep 2
docker exec cr-p22-db psql -U postgres -Atc "select version()" | cut -c1-40
setup.sql
-- 확장과 표를 만듭니다. 전화번호는 암호문(phone_enc), 검색용 해시(phone_idx), 마스킹 값(phone_mask)으로 나눠 저장합니다.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE TABLE customers (
id serial PRIMARY KEY,
name text NOT NULL,
phone_enc bytea NOT NULL,
phone_idx bytea NOT NULL,
phone_mask text NOT NULL
);
CREATE INDEX customers_phone_idx ON customers (phone_idx);
seed.sql
-- 가짜 고객 5명을 넣습니다. 실행할 때 psql -v key=... 로 키를 넘깁니다(:'key' 는 클라이언트가 문자열로 펼칩니다).
INSERT INTO customers (name, phone_enc, phone_idx, phone_mask)
SELECT n,
pgp_sym_encrypt(p, :'key', 'cipher-algo=aes256'),
hmac(p, :'key', 'sha256'),
regexp_replace(p, '^(\d{3}-)\d{4}(-\d{4})$', '\1****\2')
FROM (VALUES
('김하늘', '010-5550-1001'),
('이도윤', '010-5550-1002'),
('박서준', '010-5550-1003'),
('최지우', '010-5550-1004'),
('정민아', '010-5550-1005')
) AS t(n, p);
roles.sql
-- 상담원(cr_agent)은 마스킹 뷰만, 관리자(cr_admin)는 복호화 뷰까지 읽습니다.
CREATE ROLE cr_agent LOGIN;
CREATE ROLE cr_admin LOGIN;
REVOKE ALL ON customers FROM PUBLIC;
CREATE VIEW customers_masked AS
SELECT id, left(name, 1) || '**' AS name, phone_mask AS phone
FROM customers;
CREATE VIEW customers_plain AS
SELECT id, name, pgp_sym_decrypt(phone_enc, current_setting('app.key')) AS phone
FROM customers;
GRANT SELECT ON customers_masked TO cr_agent, cr_admin;
GRANT SELECT ON customers_plain TO cr_admin;
agent_check.sql
-- 상담원 역할로 실행합니다. 첫 문장만 성공하고 나머지 둘은 권한 오류가 나야 합니다.
SELECT * FROM customers_masked ORDER BY id;
SELECT * FROM customers;
SELECT * FROM customers_plain;
admin_naive.sql
-- 관리자 역할, 키를 SQL 문에 직접 쓰는 방식입니다. 서버 로그에 키가 남습니다.
SET app.key = 'lab-key-2026';
SELECT * FROM customers_plain ORDER BY id;
admin_safe.sql
-- 관리자 역할, 키를 환경 변수에서 읽어 바인드 매개변수로 넘기는 방식입니다(psql 16 이상).
\getenv key LAB_KEY
SELECT set_config('app.key', $1, false) IS NOT NULL AS key_set \bind :key \g
SELECT * FROM customers_plain ORDER BY id;
search_check.sql
-- 암호문 컬럼의 검색 한계를 확인합니다. 표를 직접 읽어야 하므로 DB 소유자(postgres)로 실행하고, 키는 환경 변수에서 읽습니다.
\getenv key LAB_KEY
SELECT set_config('app.key', $1, false) IS NOT NULL AS key_set \bind :key \g
-- 1) 같은 값을 두 번 암호화하면 암호문이 다릅니다.
SELECT pgp_sym_encrypt('010-5550-1001', current_setting('app.key'))
= pgp_sym_encrypt('010-5550-1001', current_setting('app.key')) AS same_ciphertext;
-- 2) 암호문 컬럼에 대한 직접 비교는 일치하는 행이 없습니다.
SELECT count(*) AS direct_match FROM customers
WHERE phone_enc = pgp_sym_encrypt('010-5550-1001', current_setting('app.key'));
-- 3) 복호화해서 비교하면 맞지만 모든 행을 풀어 봅니다(인덱스를 못 씁니다).
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id FROM customers
WHERE pgp_sym_decrypt(phone_enc, current_setting('app.key')) = '010-5550-1003';
-- 4) 해시 컬럼 phone_idx 로는 같은 값 검색에 인덱스를 씁니다.
EXPLAIN (COSTS OFF)
SELECT id FROM customers
WHERE phone_idx = hmac('010-5550-1003', current_setting('app.key'), 'sha256');
SELECT id FROM customers
WHERE phone_idx = hmac('010-5550-1003', current_setting('app.key'), 'sha256');
log_params_off.sql
-- 바인드 매개변수 값을 서버 로그에 남기지 않도록 합니다(0 이면 매개변수 값을 기록하지 않음).
ALTER SYSTEM SET log_parameter_max_length = 0;
SELECT pg_reload_conf();
이 글의 다음 단계
- ISMS-P 기술 점검 항목 일부를 스크립트로 확인해 양호, 취약, 수동확인 표 만들기일부러 허술하게 만든 nginx 시험 서버에서 계정 관리, 접근 로그의 권한과 보관 기간, TLS 버전, 저장 데이터 암호화를 파이썬 스크립트로 점검해 표를 만들고, 설정을 고친 뒤 취약 8건이 양호로 바뀌는 것을 확인합니다. 참고용 자동 점검이며 인증 심사를 대신하지 않습니다.
- 한국어 개인정보 찾기: 정규식과 Presidio 비교, 스캔 결과와 마스킹주민등록번호 형식, 휴대전화 번호, 이메일, 사업자등록번호, 카드 번호를 직접 만든 가짜 파일 3개에서 찾습니다. 형식만 보는 정규식, 검증 로직을 붙인 정규식, Presidio 기본, Presidio에 한국어 recognizer를 더한 방식을 같은 정답표로 채점해 탐지, 오탐, 미탐을 표로 비교하고 앞 6자리만 남기는 마스킹까지 해 봅니다.
이 내용을 팀 교육으로 진행하고 싶으신가요?
인원, 수준, 일정에 맞춰 실습 환경까지 준비해 진행합니다. 대상과 기간을 알려 주시면 구성을 제안해 드립니다.
교육 문의하기메일로 보내실 수도 있습니다: fankh111@gmail.com