PostgreSQL에서 해시 연쇄로 변조 감지 가능한 감사 추적 만들기

일반 감사 로그는 왜 쉽게 문제시되는가

감사 추적(audit trail)은 이상한 환불, 아무도 기억하지 못하는 권한 변경, 또는 고객 레코드가 “사라진” 경우처럼 뭔가 잘못됐을 때 되돌아보는 기록입니다. 감사 기록을 편집할 수 있다면 그것은 증거가 아니라 누군가가 다시 쓸 수 있는 또 다른 데이터가 됩니다.

많은 “감사 로그”는 단지 일반 테이블일 뿐입니다. 행을 업데이트하거나 삭제할 수 있다면 그 사건의 기록도 업데이트되거나 삭제될 수 있습니다.

중요한 차이: 편집을 막는 것과 편집을 감지 가능하게 만드는 것은 다릅니다. 권한으로 변경을 줄일 수는 있지만 충분한 접근 권한을 가진 사람(또는 도난당한 관리자 자격증명)이 역사를 바꿀 수 있습니다. 변조 감지는 그 현실을 받아들이고, 모든 변경을 막진 못하더라도 변경이 분명한 지문을 남기게 합니다.

일반 감사 로그가 논쟁거리가 되는 이유는 예측 가능합니다. 권한이 있는 사용자가 사후에 로그를 “수정”할 수 있습니다. 침해된 애플리케이션 계정이 정상 트래픽처럼 보이는 믿을 만한 항목을 쓸 수 있습니다. 타임스탬프를 뒤채워 늦은 변경을 숨길 수 있고, 누군가는 가장 손해가 큰 행만 삭제할 수도 있습니다.

"변조 감지(tamper-evident)"란 작은 수정(필드 하나 변경, 행 하나 제거, 이벤트 재정렬 등)도 나중에 탐지되도록 감사 추적을 설계한다는 뜻입니다. 마법을 약속하는 것이 아니라, 누군가 “이 로그가 진짜인지 어떻게 알죠?”라고 물을 때 로그가 건드려졌는지 보여줄 수 있는 검사를 실행할 수 있음을 약속하는 것입니다.

무엇을 증명해야 할지 결정하기

변조 감지 감사 추적은 나중에 마주할 질문에 답할 수 있을 때만 유용합니다: 누가 무엇을 했는가, 언제 했는가, 무엇이 변경되었는가.

비즈니스에 중요한 이벤트부터 시작하세요. 데이터 변경(생성, 업데이트, 삭제)이 기본이지만, 조사는 보안과 접근 관련 이벤트(로그인, 비밀번호 재설정, 권한 변경, 계정 잠금)로 판가름 나는 경우가 많습니다. 결제를 다룬다면 환불, 크레딧, 지급 같은 금전 이동을 단순한 행 업데이트의 부수 효과로 다루지 말고 일급 이벤트로 취급하세요.

다음으로 어떤 것이 이벤트를 신뢰할 수 있게 만드는지 결정하세요. 감사자는 보통 행위자(유저 또는 서비스), 서버 측 타임스탬프, 수행된 동작, 영향을 받은 객체를 기대합니다. 업데이트의 경우에는 변경 전/후 값(또는 적어도 민감한 필드)을 저장하고, 여러 작은 DB 변경을 하나의 사용자 행동에 연결할 수 있도록 요청 id 또는 상관 id(request/correlation id)를 함께 저장하세요.

마지막으로 시스템에서 "불변(immutable)"이 무엇을 의미하는지 명확히 하세요. 가장 간단한 규칙은: 감사 행을 절대 업데이트하거나 삭제하지 말고, 오직 INSERT만 하라는 것입니다. 잘못된 점이 있으면 기존을 덮어쓰지 말고 이를 바로잡는 새 이벤트를 작성하고 원본은 그대로 남겨두세요.

추가 전용(append-only) 감사 테이블 만들기

감사 데이터를 일반 테이블과 분리하세요. 전용 audit 스키마는 실수로 수정하는 일을 줄이고 권한 관리를 더 쉽게 만듭니다.

목표는 간단합니다: 행은 추가할 수 있지만 변경되거나 제거되면 안 됩니다. PostgreSQL에서는 권한(privileges)과 테이블 설계의 몇 가지 안전장치로 이를 강제할 수 있습니다.

다음은 실용적인 시작 테이블 예시입니다:

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE audit.events (
  id            bigserial PRIMARY KEY,
  entity_type   text        NOT NULL,
  entity_id     text        NOT NULL,
  event_type    text        NOT NULL CHECK (event_type IN ('INSERT','UPDATE','DELETE')),
  actor_id      text,
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  request_id    text,
  before_data   jsonb,
  after_data    jsonb,
  notes         text
);

조사 시 특히 유용한 몇몇 필드:

• occurred_at은 DEFAULT now()로 데이터베이스에서 시간을 찍게 하여 클라이언트가 아닌 서버가 시간을 기록하도록 합니다.
• entity_type과 entity_id는 하나의 레코드를 변경 이력 전체에서 추적할 수 있게 합니다.
• request_id는 하나의 사용자 행동이 여러 행으로 나뉜 경우 이를 추적하는 데 도움됩니다.

역할(role)로 잠그세요. 애플리케이션 역할은 audit.events에 대해 INSERT와 SELECT는 할 수 있지만 UPDATE나 DELETE는 못하게 하세요. 스키마 변경과 더 강력한 권한은 앱에서 사용하지 않는 관리자 역할에만 두세요.

트리거로 변경 캡처하기(간단하고 예측 가능하게)

변조 감지 감사 추적을 원한다면 변경을 캡처하기에 가장 신뢰할 수 있는 장소는 데이터베이스입니다. 애플리케이션 로그는 건너뛰거나 필터링되거나 재작성될 수 있습니다. 트리거는 어떤 앱, 스크립트, 관리자 도구가 테이블을 건드려도 항상 실행됩니다.

트리거는 지루하게 유지하세요. 트리거의 임무는 하나뿐입니다: 중요한 테이블에서 INSERT, UPDATE, DELETE가 발생할 때마다 감사 이벤트를 추가하는 것.

실용적인 감사 레코드는 보통 테이블 이름, 작업 유형, 기본 키, 변경 전후 값, 타임스탬프, 그리고 관련 변경을 그룹화할 수 있는 식별자(트랜잭션 id와 상관 id)를 포함합니다.

상관 id는 "20개 행이 업데이트되었다"와 "이건 버튼 클릭 하나였다"의 차이를 만듭니다. 애플리케이션은 요청 당 한 번 상관 id를 설정할 수 있고(예: DB 세션 설정), 트리거는 이를 읽어 저장할 수 있습니다. 상관 id가 없을 때도 그룹화할 수 있도록 txid_current()를 저장하세요.

다음은 감사 테이블에만 INSERT하기 때문에 예측 가능성을 유지하는 간단한 트리거 패턴입니다(스키마 이름을 맞게 조정하세요):

CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger AS $$
DECLARE
  corr_id text;
BEGIN
  corr_id := current_setting('app.correlation_id', true);

  INSERT INTO audit_events(
    occurred_at, table_name, op, row_pk,
    old_row, new_row, db_user, txid, correlation_id
  ) VALUES (
    now(), TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id),
    to_jsonb(OLD), to_jsonb(NEW), current_user, txid_current(), corr_id
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

트리거에 많은 일을 하려는 유혹을 참으세요. 추가 쿼리, 네트워크 호출, 복잡한 분기문은 피하세요. 작은 트리거는 테스트하기 쉽고 실행 속도가 빠르며 검토 시 논쟁의 여지가 적습니다.

편집이 흔적을 남기도록 해시 연쇄 추가하기

추가 전용 테이블은 도움이 되지만 충분한 권한을 가진 누군가는 여전히 과거 행을 다시 쓸 수 있습니다. 해시 연쇄는 그런 수정을 눈에 띄게 만듭니다.

각 감사 행에 두 개의 컬럼을 추가하세요: prev_hash와 row_hash(때때로 chain_hash라고도 함). prev_hash는 동일 체인 내 이전 행의 해시를 저장하고, row_hash는 현재 행의 데이터와 prev_hash를 포함해 계산한 현재 행의 해시를 저장합니다.

무엇을 해싱하느냐가 중요합니다. 동일한 행이 항상 같은 해시를 만들어내도록 안정적이고 반복 가능한 입력을 원합니다.

실용적인 접근은 고정된 컬럼(타임스탬프, 행위자, 동작, 엔티티 id)으로 구성된 표준 문자열과 일관된 직렬화(종종 jsonb) 및 prev_hash를 해시 입력으로 사용하는 것입니다.

공백, JSON 키 순서, 로케일별 포맷처럼 의미 없이 바뀔 수 있는 세부사항에 주의하세요. 타입을 일관되게 유지하고 예측 가능한 방식으로 직렬화하세요.

전체 DB가 아닌 스트림별 체인

모든 감사 이벤트를 하나의 글로벌 시퀀스로 체인하면 쓰기 병목이 될 수 있습니다. 많은 시스템은 테넌트별, 엔티티 타입별, 또는 비즈니스 객체별 같은 "스트림" 내에서 체인합니다.

각 새로운 행은 자신의 스트림에 대한 최신 row_hash를 조회해 prev_hash로 저장한 다음 자신의 row_hash를 계산합니다.

-- Requires pgcrypto
-- digest() returns bytea; store hashes as bytea
row_hash = digest(
  concat_ws('|',
    stream_key,
    occurred_at::text,
    actor_id::text,
    action,
    entity,
    entity_id::text,
    payload::jsonb::text,
    encode(prev_hash, 'hex')
  ),
  'sha256'
);

체인 헤드를 스냅샷으로 저장하기

검토 속도를 높이려면 스트림별로 최신 row_hash(“체인 헤드”)를 주기적으로 작은 스냅샷 테이블에 저장하세요(예: 하루 단위). 조사 시 전체 이력을 한 번에 스캔하지 않고도 각 스냅샷까지 체인을 검증할 수 있습니다. 스냅샷은 내보낸 것들을 비교해 의심스러운 간격을 찾기도 쉽습니다.

동시성 및 순서 문제 처리

실제 트래픽에서는 해시 연쇄가 까다로워집니다. 두 트랜잭션이 동시에 감사 행을 쓰고 둘 다 같은 prev_hash를 사용하면 포크가 발생할 수 있습니다. 이는 단일한 깨끗한 시퀀스를 증명하는 능력을 약화시킵니다.

먼저 체인이 무엇을 나타내는지 결정하세요. 하나의 글로벌 체인은 설명하기 쉽지만 경쟁이 가장 심합니다. 여러 체인은 경쟁을 줄이지만 각 체인이 무엇을 증명하는지 명확히 해야 합니다.

어떤 모델을 선택하든 단조 증가하는 이벤트 id(보통 시퀀스로 지원되는 id)로 엄격한 순서를 정의하세요. 타임스탬프는 충돌할 수 있고 조작될 수 있기 때문에 충분하지 않습니다.

prev_hash를 계산할 때의 레이스 컨디션을 피하려면 각 스트림에 대해 "마지막 해시 가져오기 + 다음 행 삽입"을 직렬화하세요. 일반적인 방법은 스트림 헤드를 나타내는 단일 행을 잠그거나 스트림 id로 어드바이저리 락(advisory lock)을 사용하는 것입니다. 목표는 같은 스트림의 두 작성자가 동시에 같은 마지막 해시를 읽지 못하게 하는 것입니다.

파티셔닝과 샤딩은 "마지막 행"이 어디에 있는지에 영향을 줍니다. 감사 데이터를 파티셔닝할 것으로 예상하면 스트림 키와 동일한 파티션 키를 사용해 각 체인이 하나의 파티션 안에 완전히 포함되도록 하세요(예: 테넌트 id). 이렇게 하면 테넌트 체인은 나중에 서버 간 이동하더라도 검증 가능하게 유지됩니다.

조사 중 체인을 검증하는 방법

해시 연쇄는 누군가 물었을 때 체인이 여전히 유효함을 증명할 수 있어야만 도움이 됩니다. 가장 안전한 방법은 읽기 전용 검증 쿼리(또는 작업)를 실행해 저장된 데이터로부터 각 행의 해시를 재계산하고 기록된 값과 비교하는 것입니다.

on-demand로 실행할 수 있는 간단한 검증기

검증기는 각 행에 대해 예상 해시를 재빌드하고, 각 행이 이전 행과 연결되는지 확인하며, 이상한 점을 플래그해야 합니다.

다음은 윈도우 함수를 사용하는 일반적인 패턴입니다. 컬럼 이름을 자신의 테이블에 맞게 조정하세요.

WITH ordered AS (
  SELECT
    id,
    created_at,
    actor_id,
    action,
    entity,
    entity_id,
    payload,
    prev_hash,
    row_hash,
    LAG(row_hash) OVER (ORDER BY created_at, id) AS expected_prev_hash,
    /* expected row hash, computed the same way as in your insert trigger */
    encode(
      digest(
        coalesce(prev_hash, '') || '|' ||
        id::text || '|' ||
        created_at::text || '|' ||
        coalesce(actor_id::text, '') || '|' ||
        action || '|' ||
        entity || '|' ||
        entity_id::text || '|' ||
        payload::text,
        'sha256'
      ),
      'hex'
    ) AS expected_row_hash
  FROM audit_log
)
SELECT
  id,
  created_at,
  CASE
    WHEN prev_hash IS DISTINCT FROM expected_prev_hash THEN 'BROKEN_LINK'
    WHEN row_hash IS DISTINCT FROM expected_row_hash THEN 'HASH_MISMATCH'
    ELSE 'OK'
  END AS status
FROM ordered
WHERE prev_hash IS DISTINCT FROM expected_prev_hash
   OR row_hash IS DISTINCT FROM expected_row_hash
ORDER BY created_at, id;

"깨졌는가 아닌가"를 넘어서, 범위 내 누락된 id(갭), 순서가 뒤바뀐 링크, 실제 워크플로와 맞지 않는 의심스러운 중복 등을 확인하는 것이 좋습니다.

검증 결과를 불변 이벤트로 기록하기

쿼리를 실행하고 결과를 티켓에 묻어두지 마세요. 검증 결과는 별도의 추가 전용 테이블(예: audit_verification_runs)에 실행 시간, 검증기 버전, 누가 트리거했는지, 점검한 범위, 깨진 링크와 해시 불일치 수 등을 포함해 저장하세요.

이렇게 하면 두 번째 추적 기록이 생깁니다: 감사 로그가 온전할 뿐 아니라 정기적으로 점검해왔다는 사실을 증명할 수 있습니다.

실용적인 주기는: 감사 로직에 영향을 주는 배포 후, 활성 시스템은 매일, 계획된 감사 전에는 항상 검증을 실행하는 것입니다.

변조 감지를 깨는 흔한 실수들

대부분 실패는 해시 알고리즘 자체의 문제가 아니라 예외와 간격(gap) 때문입니다. 사람들에게 반박할 여지를 주는 예외와 간격이 문제가 됩니다.

신뢰를 잃는 가장 빠른 방법은 감사 행을 업데이트하도록 허용하는 것입니다. "이번 한 번만"이라도 허용하면 전례와 실제로 역사를 다시 쓰는 경로를 만든 것입니다. 수정이 필요하면 기존을 덮어쓰기보다 수정 사실을 설명하는 새 이벤트를 추가하고 원본을 유지하세요.

해시 연쇄는 불안정한 데이터를 해싱할 때도 실패합니다. JSON은 흔한 함정입니다. JSON 문자열을 해시하면 키 순서, 공백, 숫자 포맷의 사소한 차이로 해시가 달라지고 검증이 시끄러워집니다. 정규화된 형태, jsonb, 또는 다른 일관된 직렬화를 선호하세요.

방어 가능한 추적을 약화시키는 다른 패턴들:

• 컨텍스트(타임스탬프, 행위자, 객체 id, 동작)를 건너뛰고 페이로드만 해싱하는 것.
• 변경을 애플리케이션에서만 캡처하고 데이터베이스가 항상 일치한다고 가정하는 것.
• 비즈니스 데이터를 작성할 수 있고 감사 기록도 변경할 수 있는 단일 데이터베이스 역할을 사용하는 것.
• 체인 내에서 prev_hash에 대해 NULL을 허용하되 명확한 문서화된 규칙이 없는 것.

권한 분리(separation of duties)가 중요합니다. 동일한 역할이 감사 이벤트를 삽입하면서 동시에 수정할 수 있다면 변조 감지는 통제책이 아니라 단지 약속에 불과해집니다.

방어 가능한 감사 추적 체크리스트

방어 가능한 감사 추적은 변경하기 어렵고 검증하기 쉬워야 합니다.

권한 관리로 시작하세요: 감사 테이블은 실제로 추가 전용이어야 합니다. 애플리케이션 역할은 삽입(및 보통은 조회)만 할 수 있고 업데이트나 삭제는 못 하게 하세요. 스키마 변경은 엄격히 제한하세요.

각 행이 조사자가 물을 질문에 답할 수 있도록 하세요: 누가 했는가, 언제(서버 측) 했는가, 무슨 일이 있었는가(명확한 이벤트 이름과 동작), 무엇을 건드렸는가(엔티티 이름과 id), 그리고 어떻게 연결되는가(요청/상관 id 및 트랜잭션 id).

다음으로 무결성 계층을 검증하세요. 빠른 테스트는 구간을 재생해 각 prev_hash가 이전 행의 해시와 일치하는지, 저장된 해시가 재계산된 해시와 일치하는지 확인하는 것입니다.

운영적으로 검증을 일반 작업처럼 취급하세요:

• 예약된 무결성 검사를 실행하고 통과/실패 결과와 범위를 저장하세요.
• 불일치, 갭, 깨진 링크에 대해 경고를 설정하세요.
• 보관 기간(retention) 동안 충분히 백업을 보관하고 보관 정책을 잠가 감사 기록이 조기에 "정리"되지 않게 하세요.

예시: 컴플라이언스 검토에서 의심스러운 수정을 찾기

자주 나오는 테스트 사례는 환불 분쟁입니다. 고객이 $250 환불이 승인되었다고 주장하는데 시스템에는 이제 $25로 보인다고 합시다. 지원팀은 승인 내용이 맞다고 주장하고 컴플라이언스는 답을 원합니다.

상관 id(주문 id, 티켓 id, 또는 refund_request_id)와 시간 범위를 사용해 검색 범위를 좁히세요. 해당 상관 id에 대한 감사 행을 가져와 승인 시간 주변을 브래킷하세요.

원하는 것은 전체 이벤트 집합입니다: 요청 생성, 환불 승인, 환불 금액 설정, 그리고 이후의 업데이트들. 변조 감지 설계가 되어 있다면 시퀀스가 온전했는지도 확인합니다.

간단한 조사 흐름:

• 상관 id에 대한 모든 감사 행을 시간 순으로 당겨옵니다.
• 저장된 필드(및 prev_hash)에서 각 행의 해시를 재계산합니다.
• 계산한 해시와 저장된 해시를 비교합니다.
• 첫 번째로 다른 행을 식별하고 이후 행들도 실패하는지 확인합니다.

누군가가 단일 감사 행을 편집(예: 금액을 250에서 25로 변경)했다면 그 행의 해시는 더 이상 맞지 않을 것입니다. 다음 행은 이전 해시를 포함하므로 불일치는 보통 앞으로 전파됩니다. 이 전파(cascade)가 지문입니다: 사후에 감사 기록이 변경되었다는 것을 보여줍니다.

체인이 알려줄 수 있는 것: 수정이 발생했는지, 체인이 처음 끊긴 위치, 영향을 받은 행의 범위. 체인만으로 알려주지 못하는 것: 누가 수정했는지, 덮어써진 경우 원래 값이 무엇이었는지, 다른 테이블들도 변경되었는지 여부 등입니다.

다음 단계: 안전하게 적용하고 유지 가능하게 만들기

감사 추적을 다른 보안 제어처럼 다루세요. 작은 단계로 롤아웃하고 작동을 증명한 뒤 확대하세요.

논쟁이 생기면 가장 큰 피해를 입을 액션(권한 변경, 지급, 환불, 데이터 내보내기, 수동 오버라이드)부터 시작하세요. 그걸 적용한 후 핵심 설계를 바꾸지 않고 위험이 낮은 이벤트들을 추가하세요.

감사 이벤트 계약을 문서화하세요: 어떤 필드를 기록하는지, 각 이벤트 타입이 무엇을 의미하는지, 해시가 어떻게 계산되는지, 검증을 어떻게 실행하는지 기록하고 데이터베이스 마이그레이션 옆에 문서를 두세요. 검증 절차를 반복 가능하게 유지하세요.

복원 연습(restore drills)은 중요합니다. 조사는 종종 라이브 시스템이 아닌 백업에서 시작됩니다. 정기적으로 테스트 DB로 복원해 체인 끝에서 끝까지 검증하세요. 복원 후 동일한 검증 결과를 재현할 수 없다면 변조 감지를 방어하기 어렵습니다.

내부 도구와 관리자 워크플로를 AppMaster (appmaster.io)로 빌드하는 경우, 서버 측에서 감사 이벤트 쓰기를 표준화하면 이벤트 스키마와 상관 id가 기능 전반에서 일관되게 유지되어 검증과 조사가 훨씬 간단해집니다.

이 시스템을 위한 유지보수 시간을 예약하세요. 팀이 새 기능을 배포하면서 이벤트를 추가하거나 해시 입력을 업데이트하거나 검증 작업과 복원 연습을 계속 실행하는 것을 잊으면 감사 추적은 조용히 실패합니다.