개인정보 접속기록 보관기간 1년 vs 2년 판정표와 로그 설계 실무 가이드

(주)예시커머스
├─ 회원/주문 시스템   → 회원 12만 명 → Q1 Yes → 2년
├─ 사내 인사(HR) 시스템 → 임직원 300명, 주민번호 보유 → Q2 Yes → 2년
└─ 마케팅 이벤트 DB    → 응모자 8천 명, 이름·연락처만 → 전부 No → 1년

CREATE TABLE access_log (
    id             BIGSERIAL PRIMARY KEY,
    -- ① 계정: 개인정보취급자 식별자 (공용계정 금지)
    actor_account  VARCHAR(64)  NOT NULL,
    actor_emp_no   VARCHAR(32),              -- 퇴사 후에도 인사번호로 추적
    -- ② 접속일시: UTC 저장 + 표시 시 KST 변환 (혼용 금지)
    accessed_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
    -- ③ 접속지 정보
    src_ip         INET         NOT NULL,
    user_agent     TEXT,
    session_id     VARCHAR(64),
    -- ④ 처리한 정보주체 정보 (원문 대신 내부 ID/해시)
    subject_ref    VARCHAR(128),             -- 예: user:830112 또는 sha256 해시
    subject_type   VARCHAR(32),              -- member / patient / applicant
    -- ⑤ 수행업무
    action         VARCHAR(24)  NOT NULL,    -- READ/UPDATE/DELETE/EXPORT/PRINT
    resource       VARCHAR(128) NOT NULL,    -- 예: /admin/members/detail
    target_count   INTEGER      NOT NULL DEFAULT 1,  -- 대량조회 탐지 핵심
    result         VARCHAR(16)  NOT NULL DEFAULT 'SUCCESS',
    detail         JSONB
) PARTITION BY RANGE (accessed_at);

CREATE TABLE access_log_2026_07 PARTITION OF access_log
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE INDEX idx_access_log_actor ON access_log (actor_account, accessed_at DESC);
CREATE INDEX idx_access_log_subject ON access_log (subject_ref, accessed_at DESC);
CREATE INDEX idx_access_log_action ON access_log (action, accessed_at DESC);

{
  "ts": "2026-07-28T09:14:22.481Z",
  "tz_display": "Asia/Seoul",
  "actor": { "account": "kim.cs", "emp_no": "E20231045", "role": "CS_AGENT" },
  "src": { "ip": "10.20.5.31", "xff": "203.0.113.44", "session": "s_9f2a..." },
  "action": "EXPORT",
  "resource": "/admin/members/export",
  "subject": { "type": "member", "refs": ["m_10023", "m_10024"], "count": 2 },
  "target_count": 2,
  "result": "SUCCESS",
  "reason": "CS-2026-4412 환불 민원 처리"
}

# Before
log.info(f"{user.id} viewed member detail page")

# After
audit.write(
    actor_account=user.account, action="READ",
    resource="/admin/members/detail",
    subject_type="member", subject_refs=[member.id], target_count=1,
)

# Before (Nginx access.log — 접속기록이 아님)
203.0.113.44 - - [28/Jul/2026:09:14:22 +0900] "GET /admin/members?page=3 HTTP/1.1" 200

# After (수행업무 + 정보주체 + 건수 포함)
{"ts":"...","actor":"kim.cs","action":"READ","resource":"/admin/members",
 "subject":{"type":"member","count":50},"target_count":50}

# Before
rows = repo.search(keyword)          # 12,000건 반환, 로그엔 "search" 한 줄

# After
rows = repo.search(keyword)
audit.write(action="READ", resource="/admin/members/search",
            target_count=len(rows), detail={"keyword_hash": h(keyword)})

-- 애플리케이션 계정은 삽입만 가능
REVOKE ALL ON access_log FROM app_user;
GRANT INSERT ON access_log TO app_user;
GRANT USAGE, SELECT ON SEQUENCE access_log_id_seq TO app_user;

-- 점검자는 읽기만
CREATE ROLE auditor LOGIN PASSWORD '...';
GRANT SELECT ON access_log TO auditor;

#!/usr/bin/env bash
# /usr/local/bin/log-checksum.sh — 전일 로그 해시를 append-only 파일에 기록
set -euo pipefail
DAY=$(date -d 'yesterday' +%F)
OUT=/var/log/audit/chain.log
PREV=$(tail -n1 "$OUT" 2>/dev/null | awk '{print $3}' || echo "GENESIS")
HASH=$(psql -qAt -c "SELECT md5(string_agg(id::text||actor_account||action, '|' ORDER BY id))
        FROM access_log WHERE accessed_at::date = '$DAY'")
echo "$DAY $PREV ${PREV:0:8}$HASH" >> "$OUT"
chattr +a "$OUT" 2>/dev/null || true   # append-only 속성

SELECT actor_account, accessed_at, resource, target_count, src_ip
FROM access_log
WHERE accessed_at >= date_trunc('month', now() - interval '1 month')
  AND accessed_at <  date_trunc('month', now())
  AND action IN ('EXPORT', 'PRINT', 'DOWNLOAD')
ORDER BY target_count DESC, accessed_at;

WITH baseline AS (
  SELECT actor_account, AVG(daily_cnt) AS avg_cnt
  FROM (SELECT actor_account, accessed_at::date d, SUM(target_count) daily_cnt
        FROM access_log
        WHERE accessed_at >= now() - interval '90 days'
        GROUP BY 1,2) t
  GROUP BY 1
)
SELECT l.actor_account, l.accessed_at::date AS d,
       SUM(l.target_count) AS today_cnt, ROUND(b.avg_cnt) AS avg_cnt
FROM access_log l JOIN baseline b USING (actor_account)
WHERE l.accessed_at >= date_trunc('month', now() - interval '1 month')
  AND l.accessed_at <  date_trunc('month', now())
GROUP BY 1,2,4
HAVING SUM(l.target_count) > GREATEST(500, b.avg_cnt * 3)
ORDER BY today_cnt DESC;

SELECT actor_account, accessed_at, src_ip, action, resource, target_count
FROM access_log
WHERE accessed_at >= date_trunc('month', now() - interval '1 month')
  AND accessed_at <  date_trunc('month', now())
  AND (
        EXTRACT(hour FROM accessed_at AT TIME ZONE 'Asia/Seoul') NOT BETWEEN 8 AND 20
     OR EXTRACT(dow  FROM accessed_at AT TIME ZONE 'Asia/Seoul') IN (0, 6)
     OR NOT (src_ip << ANY (ARRAY['10.0.0.0/8'::inet, '172.16.0.0/12'::inet]))
      )
ORDER BY accessed_at;

# /etc/cron.d/privacy-audit  — 매월 1일 07:00 전월 점검 리포트 생성·발송
0 7 1 * * auditor /usr/local/bin/monthly_audit.sh >> /var/log/audit/cron.log 2>&1

#!/usr/bin/env bash
# monthly_audit.sh
set -euo pipefail
PERIOD=$(date -d 'last month' +%Y-%m)
OUT="/var/log/audit/report_${PERIOD}.csv"
for q in export_activity bulk_read offhours_access; do
  echo "== ${q} ==" >> "$OUT"
  psql -qA -F',' -f "/opt/audit/sql/${q}.sql" >> "$OUT"
done
mail -s "[개인정보] ${PERIOD} 접속기록 점검 결과" -a "$OUT" \
     [email protected] < /opt/audit/mail_body.txt

# 3개월 지난 파티션 아카이브 → S3 이관 → DROP
TBL=access_log_2026_04
psql -c "\copy ${TBL} TO PROGRAM 'gzip > /tmp/${TBL}.csv.gz' CSV HEADER"
aws s3 cp /tmp/${TBL}.csv.gz s3://corp-audit-archive/access_log/ \
      --storage-class STANDARD_IA
psql -c "DROP TABLE ${TBL};"

-- 파기 기록 테이블 (삭제 대상 아님)
CREATE TABLE audit_disposal_log (
  id BIGSERIAL PRIMARY KEY,
  target_table VARCHAR(64), period_from DATE, period_to DATE,
  row_count BIGINT, disposed_at TIMESTAMPTZ DEFAULT now(),
  operator VARCHAR(64), method VARCHAR(32)   -- DROP PARTITION / S3 DELETE
);