# pgcrypto로 컬럼 암호화하고 마스킹 뷰로 역할별 출력 나누기: 키가 서버 로그에 남는 문제까지

> 공식 postgres 이미지에서 pgcrypto의 pgp_sym_encrypt로 전화번호 컬럼을 암호화해 저장하고, 상담원 역할은 마스킹 뷰만, 관리자 역할은 복호화 뷰까지 읽게 GRANT를 나눕니다. 키를 SQL 문에 직접 쓰면 log_statement 로그와 pg_stat_statements에 남는 것을 확인하고, 바인드 매개변수와 로그 설정으로 줄이는 방법, 암호문 검색과 인덱스의 한계를 함께 봅니다.

- 주소: https://copyandrun.com/blog/postgres-pgcrypto-column-encrypt-masking-view/
- 분야: 개인정보·컴플라이언스
- 발행일: 2026-11-02
- 태그: PostgreSQL, pgcrypto, 컬럼 암호화, 마스킹 뷰, GRANT

고객 전화번호를 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. 컨테이너, 표, 암호화한 데이터 준비

<!-- files: start.sh, setup.sql, seed.sql -->

`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`로 바꿉니다. 마스킹 값을 미리 저장하는 이유는 다음 단계에서 설명합니다.

```bash
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"
```

![전화번호가 평문이 아니라 c30d04...로 시작하는 암호문으로 저장되고 마스킹 값이 따로 있는 것을 확인합니다.](https://copyandrun.com/img/postgres-pgcrypto-column-encrypt-masking-view/01-setup-ciphertext.png)

`seed.sql`의 `:'key'`는 psql이 클라이언트에서 문자열로 펼친 뒤 서버로 보냅니다. 이 점이 4단계의 문제로 이어집니다.

## 2. 역할과 뷰로 보이는 값 나누기

<!-- files: roles.sql, agent_check.sql, admin_safe.sql -->

`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단계에서 이유를 봅니다).

```bash
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
```

![상담원은 마스킹 뷰만 읽고 표와 복호화 뷰는 권한 오류가 나며, 관리자는 복호화된 전화번호를 읽는 것을 확인합니다.](https://copyandrun.com/img/postgres-pgcrypto-column-encrypt-masking-view/02-agent-vs-admin.png)

상담원이 `customers`와 `customers_plain`을 읽으려 하면 `permission denied`가 납니다. 뷰는 소유자(여기서는 postgres) 권한으로 표를 읽기 때문에, 상담원에게 표 권한을 주지 않아도 마스킹 뷰는 동작합니다. 이 컨테이너에서는 소켓 접속이 비밀번호 없이 통과하는 기본 설정이라 역할에 비밀번호를 두지 않았습니다. 실제 서버에서는 인증 방식을 따로 정해야 합니다.

## 3. 암호문은 같은 값도 매번 다르다

같은 전화번호를 같은 키로 두 번 암호화해서 비교하면 결과가 다릅니다. 공식 문서 설명대로 데이터 앞에 무작위 바이트가 붙고 S2K 솔트도 무작위이기 때문입니다. 6단계 `search_check.sql` 출력의 `same_ciphertext`가 `f`로 나옵니다. 이 성질은 암호화 관점에서는 장점이지만 검색에는 방해가 되며, 자세한 내용은 6단계에서 봅니다.

## 4. 키를 SQL 문에 쓰면 서버 로그에 남는다

<!-- files: admin_naive.sql -->

`admin_naive.sql`은 흔히 쓰는 방식입니다. `SET app.key = '키';`로 세션에 키를 넣고 조회합니다. 이 실행 뒤에, 앞 단계의 `seed.sql`과 2단계의 `admin_safe.sql`(기본 로그 설정 상태)까지 포함해 로그에 키가 어디에 남았는지 확인합니다.

```bash
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 문, 바인드 매개변수 DETAIL 줄, SET app.key 문이 키와 함께 남고 pg_stat_statements에도 SET 문이 남은 것을 확인합니다.](https://copyandrun.com/img/postgres-pgcrypto-column-encrypt-masking-view/03-key-in-log.png)

확인한 것은 네 가지입니다.

- `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. 키를 로그에 덜 남기는 방법

<!-- files: admin_safe.sql, log_params_off.sql -->

시험해 본 완화 방법은 두 가지를 함께 쓰는 것입니다.

1. 키를 SQL 문 글자가 아니라 바인드 매개변수로 넘깁니다. `admin_safe.sql`은 `\getenv key LAB_KEY`로 환경 변수를 읽고 `SELECT set_config('app.key', $1, false) ... \bind :key \g`로 보냅니다. 로그에는 `$1`만 남습니다. 키를 `:'key'`로 감싸면 따옴표까지 값에 들어가 키가 달라지므로 `\bind :key`로 씁니다.
2. 서버에서 `log_parameter_max_length = 0`으로 바인드 매개변수 값을 로그에 쓰지 않게 합니다. 공식 문서 기준 이 설정은 `log_statement` 등 오류가 아닌 문장 로그의 매개변수 값에 적용됩니다. `log_params_off.sql`이 `ALTER SYSTEM`과 `pg_reload_conf()`로 반영합니다.

```bash
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로 같고, 새 로그에는 $1만 있는 execute 줄이 남은 것을 확인합니다.](https://copyandrun.com/img/postgres-pgcrypto-column-encrypt-masking-view/04-key-not-logged.png)

설정 변경 뒤 같은 명령을 다시 실행해도 키가 들어 있는 줄 수는 4로 그대로입니다. 이전 줄은 로그에 남아 있으므로, 이미 키가 로그에 남았다면 그 키는 바꿔야 합니다.

남는 한계가 있습니다. `-e LAB_KEY=...`로 넘기면 명령 줄에 키가 보입니다. 이 글에서는 연습용 값이라 그대로 썼습니다. 운영에서는 비밀 저장소나 파일에서 읽어 오는 방식이 필요하며 그 부분은 시험하지 않았습니다. 또 `log_statement=all`을 켜면 문장에 직접 들어 있는 전화번호 같은 데이터 값도 로그에 남습니다.

## 6. 암호문 컬럼은 검색과 인덱스에 불리하다

<!-- files: search_check.sql -->

`search_check.sql`은 세 가지를 확인합니다. 표를 직접 읽어야 해서 `postgres` 역할로 실행합니다.

```bash
docker exec -i -e LAB_KEY=lab-key-2026 cr-p22-db psql -U postgres < search_check.sql
```

![같은 값의 암호문이 서로 다르고 직접 비교는 0건이며, 복호화 조건은 Seq Scan, hmac 조건은 인덱스를 쓰는 것을 확인합니다.](https://copyandrun.com/img/postgres-pgcrypto-column-encrypt-masking-view/05-search-limits.png)

- 같은 값을 두 번 암호화한 결과는 다릅니다(`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

```bash
#!/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

```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

```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

```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

```sql
-- 상담원 역할로 실행합니다. 첫 문장만 성공하고 나머지 둘은 권한 오류가 나야 합니다.
SELECT * FROM customers_masked ORDER BY id;
SELECT * FROM customers;
SELECT * FROM customers_plain;
```

### admin_naive.sql

```sql
-- 관리자 역할, 키를 SQL 문에 직접 쓰는 방식입니다. 서버 로그에 키가 남습니다.
SET app.key = 'lab-key-2026';
SELECT * FROM customers_plain ORDER BY id;
```

### admin_safe.sql

```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

```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

```sql
-- 바인드 매개변수 값을 서버 로그에 남기지 않도록 합니다(0 이면 매개변수 값을 기록하지 않음).
ALTER SYSTEM SET log_parameter_max_length = 0;
SELECT pg_reload_conf();
```
