설계로 구현하는 ALCOA+: 코드 속 데이터 무결성

📍 현재 위치: 5부 · 신뢰(Trust) — 20장. 이제 플랫폼은 배치(batch)를 수집하고, 저장하고, 맥락화하고, 시각화한다. 이 장은 데이터를 설계 자체로 신뢰할 수 있게(trustworthy by construction) 만든다. 모든 검사관이 관심을 갖는 데이터 무결성(data integrity) 규칙을 PostgreSQL 스키마(schema), 트리거(trigger), 해시 체인(hash chain)에 곧바로 새겨 넣고, 그 보장이 실제로 성립함을 증명하는 테스트를 작성한다.

쉽게 말하면

모든 페이지에 번호가 매겨져 있고, 지워지지 않는 잉크로 작성되며, 작성하면서 서명과 날짜를 기록하고, 너무 단단히 제본되어 있어서 페이지를 찢어내거나 몰래 끼워 넣거나 순서를 뒤바꾸면 반드시 표가 나는 실험 노트를 상상해 보라. 누군가 실제로 페이지를 찢어내거나 순서를 뒤섞으면 페이지 번호가 맞지 않게 되어 모두가 알아챌 것이다. 우리가 여기서 코드로 만드는 것이 바로 그것이다. 규제 대상 기록(regulated record)에 대한 모든 변경은 (덮어쓰지 않고) 추가만 되며, 누가, 언제, 왜 변경했는지가 기록되고, 직전 변경과 암호학적으로 연결된다. 그래서 이력의 순서 가 변조 입증 가능(tamper-evident) 해진다. 한계도 분명히 짚어 두자. 이 링크 검사는 삭제되거나, 순서가 뒤바뀌거나, 끼워 넣어진 항목은 잡아내지만, 링크를 그대로 둔 채 항목 내용 만 몰래 수정한 경우는 잡지 못한다. 더구나 어떤 SQL 파일도 약속할 수 없는 변조 방지(tamper-proof) 와도 거리가 멀다. 그렇지만 변조 입증 가능 은 규제 당국이 실제로 요구하는 수준이며, 이 코드가 어디까지 닿는지를 정확히 밝히겠다.

이 장에서 다루는 내용

지난 열아홉 장에 걸쳐 우리는 데이터를 센서에서 깨끗하고 맥락화되며 질의 가능한 플랫폼으로 옮겨 왔다. 이제 그 데이터를 규제 당국이 신뢰하도록 만들어야 한다. 그 신뢰를 줄여서 부르는 말이 ALCOA+ 이다. 데이터는 Attributable(귀속 가능), Legible(판독 가능), Contemporaneous(동시 기록), Original(원본 보존), Accurate(정확)해야 하며, 여기에 Complete(완전), Consistent(일관), Enduring(지속), Available(가용)이 더해진다 [1]. 이는 FDA의 데이터 무결성 기대 사항 [2], EU Annex 11 [3], 그리고 PIC/S 검사관 프레임워크 [4]의 근간을 이룬다.

ALCOA+ 를 사후에 감사하는 체크리스트로 다루고 싶은 유혹이 든다. 이 장은 그 반대를 주장한다. 무결성은 설계 단계에 새겨 넣을(engineered in) 때 가장 저렴하고 가장 강력하다. 우리는 다음을 수행한다.

• 각 ALCOA+ 속성을 구체적인 스키마 또는 파이프라인(pipeline) 메커니즘(추가 전용, 품질 플래그, 귀속 메타데이터)에 매핑한다.
• 규제 대상 테이블에 대한 모든 변경의 이전 값/새 값/누가/언제/왜를 기록하는 트리거 기반 감사 추적(audit trail) 을 PostgreSQL에 구축한다.
• 그 감사 로그를 해시 체인 으로 묶어, 사후 수정이 탐지 가능해지도록 만든다.
• 그리고 보장을 기계적으로 단언하는 pytest 테스트 모음 을 실행한다. 추가 전용 동작, 끊기지 않은 체인, 포착된 갱신을 검증한다.

여기에 나오는 모든 코드 조각은 동반 저장소(companion repo)에 있는 실제 테스트된 두 파일에서 가져온 것이다. make seed 로 적용되는 examples/platform/db/50-alcoa.sql 의 DDL, 그리고 make test 로 실행되는 examples/tests/test_db.py 의 단언문이다. 어느 것도 예시용이 아니다. 여러분의 노트북에서 직접 체인을 끊고 테스트가 빨갛게 실패하는 것을 지켜볼 수 있다.

ALCOA+ 는 포스터가 아니라 설계 요구 사항의 집합이다

코드를 보기 전에, ALCOA+ 를 공학적 요구 사항의 목록으로 읽어 보면 도움이 된다. 컴플라이언스 벽지 정도로 취급하기를 멈추는 순간 ALCOA+ 가 바로 그것이 되기 때문이다. MHRA의 가이던스는 이 속성들과, 메타데이터가 기록의 일부 라는 핵심 요점을 가장 깔끔하게 진술한다. 값 주변의 누가 와 언제 는 값 그 자체만큼이나 규제 대상이다 [1].

ALCOA+ 속성	시스템에 요구하는 것	코드에서 강제하는 위치
Attributable(귀속 가능)	모든 값이 사람 또는 장치로 추적된다	감사 로그의 db_user / app_user 컬럼
Legible(판독 가능)	기록이 읽을 수 있고 영구적이다	jsonb 이전/이후 스냅샷, 평문 SQL, 내구성 있는 저장소
Contemporaneous(동시 기록)	사건이 일어난 시점에 기록된다	각 행의 clock_timestamp() 서버 타임스탬프
Original(원본 보존)	최초 수집본이 덮어써지지 않고 보존된다	추가 전용 change_log; UPDATE/DELETE 없음
Accurate(정확)	값이 올바르고 품질 플래그가 붙는다	모든 판독값의 OPC UA quality 플래그
Complete(완전)	변경을 포함해 아무것도 몰래 누락되지 않는다	모든 INSERT/UPDATE/DELETE 에서 트리거 작동
Consistent(일관)	순서가 온전하고 정렬되어 있다	단조 증가 seq 식별자 + 해시 체인
Enduring / Available(지속 / 가용)	살아남고 검색 가능하다	PostgreSQL 내구성 + 보존 정책(23장)

이 가운데 둘은 앞 장에서 이미 값을 치렀다. Accurate 는 히스토리안(historian)이 모든 판독값에 싣고 다니는 quality 플래그에 올라타 있다. examples/platform/db/20-historian.sql 의 컬럼을 떠올려 보라. OPC UA 상태 코드(192 Good, 64 Uncertain, 0 Bad)가 값과 나란히 저장되어, 하위 소비자가 Bad 판독값을 신뢰할 수 있는 값으로 결코 오인하지 못한다. Contemporaneous 는 컬렉터(collector)가 각 판독값을 삽입 시점이 아니라 취득 시점에 타임스탬프를 찍는다는 사실에 올라타 있다. 이 장의 임무는 더 어려운 셋, 즉 Attributable, Original, Complete, 그리고 이들을 한데 묶어 Consistent 를 완성하는 변조 입증성이다.

감사 추적: 규제 대상 테이블을 감시하는 트리거

이 메커니즘은 GMP 기록을 담은 테이블에 부착된 단일 PostgreSQL 트리거 함수다. PostgreSQL은 행 수준(row-level) 트리거에게 행의 OLD 와 NEW 버전, 그리고 TG_OP 를 통한 연산 유형에 대한 접근을 제공하는데, 이것이 바로 감사 추적이 필요로 하는 원재료다 [5]. 다음은 examples/platform/db/50-alcoa.sql 에서 가져온 추가 전용 로그 테이블이다.

CREATE TABLE audit.change_log (
    seq        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ts         timestamptz NOT NULL DEFAULT clock_timestamp(),
    db_user    text NOT NULL DEFAULT current_user,
    app_user   text,                              -- set via SET app.user = '...'
    table_name text NOT NULL,
    action     text NOT NULL,                     -- INSERT | UPDATE | DELETE
    row_key    text,
    old_row    jsonb,
    new_row    jsonb,
    reason     text,                              -- set via SET app.reason = '...'
    prev_hash  text,
    row_hash   text NOT NULL
);

이 테이블을 컬럼으로 표현된 ALCOA+ 로 읽어 보라. db_user 와 app_user 는 변경을 Attributable 하게 만든다. 앞의 것은 데이터베이스 역할(role)로, 뒤의 것은 애플리케이션이 인증한 사람으로 귀속시킨다(우리는 이를 SET app.user = '...' 로 설정하며, 21장에서 이를 실제 Keycloak 신원에 연결한다). clock_timestamp() 로 기본값이 설정되는 ts 는 그것을 Contemporaneous 하게 만든다. now() 가 아니라 clock_timestamp() 라는 점에 주목하라. now() 는 트랜잭션 시작 시점에 고정되지만, clock_timestamp() 는 행이 기록되는 실제 벽시계 순간이다. jsonb 형식의 old_row 와 new_row 는 기록을 Legible 하고 Original 하게 유지한다. 차이(diff)만이 아니라 행의 변경 전후 전체 이미지가 보존된다. 그리고 이 테이블은 사용 관행상 추가 전용 이다. 시스템의 어느 부분도 이 테이블에 UPDATE 나 DELETE 를 실행하지 않는다. reason 컬럼은 규제 당국이 가장 좋아하는 것으로, 모든 변경 뒤의 왜 를 포착한다.

실제 작업은 트리거 함수가 한다. 이 역시 examples/platform/db/50-alcoa.sql 에서 가져왔다.

CREATE OR REPLACE FUNCTION audit.log_change() RETURNS trigger AS $$
DECLARE
    v_prev  text;
    v_key   text;
    v_old   jsonb := CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END;
    v_new   jsonb := CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END;
    v_app   text  := current_setting('app.user', true);
    v_reason text := current_setting('app.reason', true);
    v_hash  text;
BEGIN
    SELECT row_hash INTO v_prev FROM audit.change_log ORDER BY seq DESC LIMIT 1;
    v_key := coalesce((v_new ->> 'batch_id'), (v_old ->> 'batch_id'),
                      (v_new ->> 'sample_id'), (v_old ->> 'sample_id'));
    -- chain hash = H(prev_hash || payload)
    v_hash := encode(digest(
        coalesce(v_prev, '') || TG_TABLE_NAME || TG_OP ||
        coalesce(v_old::text, '') || coalesce(v_new::text, '') ||
        coalesce(v_app, '') || clock_timestamp()::text, 'sha256'), 'hex');

    INSERT INTO audit.change_log(app_user, table_name, action, row_key,
                                 old_row, new_row, reason, prev_hash, row_hash)
    VALUES (v_app, TG_TABLE_NAME, TG_OP, v_key, v_old, v_new, v_reason, v_prev, v_hash);
    RETURN coalesce(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

세 줄은 좀 더 자세히 볼 만하다. to_jsonb(OLD) 와 to_jsonb(NEW) 는 전체 행을 JSON으로 직렬화하므로, 로그가 스키마에 구애받지 않는다. 동일한 함수가 별도 컬럼 코드 한 줄 없이 검사 결과, 배치, 또는 레시피(recipe) 파라미터를 모두 감사한다. current_setting('app.user', true) 는 애플리케이션이 트랜잭션 시작 시 설정하는 세션 변수를 읽는다. true 는 변수가 설정된 적이 없을 때 오류를 내지 않고 NULL 을 반환하게 한다. 그리고 digest(...,'sha256') 호출이 해시 체인이며, 이는 다음에서 풀어 설명한다.

이 트리거는 규제 대상 기록을 담은 바로 그 테이블에 부착된다. 역시 examples/platform/db/50-alcoa.sql 에 있다.

CREATE TRIGGER audit_result   AFTER INSERT OR UPDATE OR DELETE ON lab.result
    FOR EACH ROW EXECUTE FUNCTION audit.log_change();
CREATE TRIGGER audit_batch    AFTER INSERT OR UPDATE OR DELETE ON s88.batch
    FOR EACH ROW EXECUTE FUNCTION audit.log_change();
CREATE TRIGGER audit_recipe_p AFTER INSERT OR UPDATE OR DELETE ON s88.recipe_parameter
    FOR EACH ROW EXECUTE FUNCTION audit.log_change();

이는 검사관이 가장 신경 쓰는 세 테이블이다. 분석 결과(lab.result), 배치 기록 그 자체(s88.batch), 그리고 제품이 어떻게 만들어지는지를 정의하는 레시피 파라미터(s88.recipe_parameter, 24장에서 valid_from/valid_to 컬럼을 사용해 제자리 버전 관리한다)다. 트리거가 모든 INSERT OR UPDATE OR DELETE 에서 작동하므로 아무것도 빠져나가지 못한다. 그것이 기계적으로 구현된 Complete 속성이다.

해시 체인: 기록들을 서로 연결하기

추가 전용 로그는 그 아래의 저장소만큼만 정직하다. 테이블 접근 권한을 가진 작심한 내부자라면 원칙적으로 audit.change_log 에 손을 뻗어 행 순서를 바꾸거나, 중간 항목을 삭제하거나, 조작된 항목을 끼워 넣을 수 있다. 우리는 그 부류의 공격을 클라우드보다 오래된 기법으로 탐지 가능하게 만든다. 바로 연결된 해시 체인(linked hash chain) 이다. 이는 Haber와 Stornetta가 1991년에 디지털 문서에 타임스탬프를 찍어 그 순서가 조용히 재배열되거나 변경되지 못하게 하는 방법으로 처음 기술했다 [6]. 나중에 블록체인(blockchain)의 토대가 된 바로 그 구성이지만, 우리에게는 분산 합의 장치가 전혀 필요 없다. 연결만 있으면 된다.

발상은 단순하다. 트리거가 행을 기록할 때, 그 행의 페이로드(payload) 그리고 직전 행에 저장된 해시에 대해 SHA-256 해시를 계산한 뒤, 두 값을 모두 저장한다. 직전 행의 해시는 prev_hash 에, 새 다이제스트는 row_hash 에 저장한다. 개념적으로는 다음과 같다.

row_hash[n] = SHA256( row_hash[n-1] || table || op || old || new || app_user || clock_timestamp() )
prev_hash[n] = row_hash[n-1]

이 다이제스트에 대해 두 가지 정직한 메모를 남긴다. 이 장의 핵심이 코드가 실제로 전달하는 것만을 주장하는 데 있기 때문이다. 첫째, 해시는 트리거 내부에서 평가된 clock_timestamp() 를 접어 넣는다. 이는 ts 컬럼에 저장되는 값과 다른 벽시계 순간이다(ts 컬럼은 자체적인 독립적 clock_timestamp() 기본값을 가진다). 저장된 행의 어디에도 해시에 들어간 정확한 타임스탬프가 기록되지 않으므로, row_hash 는 저장된 컬럼들로부터 재현 불가능 하다. 둘째, 그 결과로, 배포된 검증기(verifier)는 페이로드로부터 row_hash 를 결코 재계산하지 않는다. 저장된 prev_hash 컬럼과 row_hash 컬럼 사이의 링크 가 일관적인지만 확인한다. 이것이 무엇을 잡고 무엇을 못 잡는지 정확히 살펴보겠다.

pgcrypto 확장(extension)은 그 SHA-256을 계산하고 16진수로 인코딩하는 데이터베이스 내장 digest() 와 encode() 함수를 제공한다 [7]. 이는 스택의 최상단인 examples/platform/db/00-init.sql 에서 한 번 활성화된다.

CREATE EXTENSION IF NOT EXISTS pgcrypto;     -- digest() for the ALCOA+ hash chain

각 행이 직전 행의 해시를 자신의 prev_hash 컬럼에 저장하므로, 로그는 링크를 확인할 수 있는 체인이 된다. 행을 삭제하거나, 행 순서를 바꾸거나, prev_hash/row_hash 값을 덮어쓰면 저장된 링크가 더 이상 맞아떨어지지 않는다. 그것이 검증기가 테스트하는 속성이며, 결정적으로, 검증기가 테스트하는 유일한 속성이다.

연결된 해시 체인으로서의 감사 로그: 각 행은 직전 행의 row_hash 를 자신의 prev_hash 컬럼에 저장하므로, 해시 링크 컬럼을 삭제하거나, 순서를 바꾸거나, 덮어쓰면 저장된 링크가 끊기고 verify_chain() 이 prev_hash 가 더 이상 직전 행의 row_hash 와 일치하지 않는 첫 seq 를 표시한다. 검증기는 링크 일관성만 확인한다는 점에 주의하라. 해시를 재계산하지 않으므로, 해시 컬럼은 그대로 둔 채 행 페이로드만 몰래 수정한 경우는 여기서 잡히지 않는다. Original diagram by the authors, created with AI assistance.

끊긴 링크를 탐지하는 것은 그 자체로 하나의 함수이며, examples/platform/db/50-alcoa.sql 의 마지막 블록이다.

-- Verify the chain is intact: returns rows where a stored prev_hash does not
-- equal the previous row's stored row_hash (a broken/reordered/deleted link).
CREATE OR REPLACE FUNCTION audit.verify_chain()
RETURNS TABLE(seq bigint, ok boolean) AS $$
    WITH chained AS (
        SELECT c.seq, c.row_hash, c.prev_hash,
               lag(c.row_hash) OVER (ORDER BY c.seq) AS expected_prev
        FROM audit.change_log c
    )
    SELECT seq, (prev_hash IS NOT DISTINCT FROM expected_prev) AS ok
    FROM chained
    WHERE prev_hash IS DISTINCT FROM expected_prev;
$$ LANGUAGE sql;

lag(...) OVER (ORDER BY c.seq) 윈도우 함수는 로그를 순서대로 훑으며, 각 행마다 직전 행에 저장된 row_hash 를 되돌아본다. 그것이 현재 행이 기록한 prev_hash 와 일치하지 않으면, 저장된 링크가 끊긴 것이다. 건강한 체인은 0개의 행 을 반환한다. 모든 링크가 일관적이다. 검증기가 반환하는 행이 있다면, 그것은 저장된 링크가 맞아떨어지지 않게 된 지점을 가리킨다. 그 seq 가 체인이 처음으로 어긋난 위치다. 범위를 정확히 하자. 이는 prev_hash 와 row_hash 컬럼만 비교한다. old_row/new_row/app_user 로부터 해시를 다시 도출하지 않으므로, 삭제되거나, 순서가 바뀌거나, 다시 연결된 행은 탐지하지만, 해시 컬럼을 건드리지 않은 채 행 페이로드만 몰래 수정한 경우는 탐지하지 못한다. 아래 데모에서는 검증기가 잡을 수 있는 바로 그 공격을 사용하고, 그것이 남기는 빈틈은 "실제 현장에서는" 에서 다시 다룬다. (이 함수에 대한 저장소 자체의 주석도 신중하게 일치시킨다. 검증기가 "저장된 prev_hash 가 직전 행의 저장된 row_hash 와 같지 않은 행을 반환한다" 고 적고, "이는 링크 일관성만 확인하며, 페이로드로부터 row_hash 를 재계산하지 않는다" 고 덧붙인다. 아무것도 재계산하지 않으므로, 일부러 해시를 재계산한다고 묘사하지 않는 것이다.)

전형적인 건강한 로그를 직접 질의하면 다음과 같이 보인다.

 seq |             ts             | app_user | table_name | action | row_key        |  prev_hash  |  row_hash
-----+----------------------------+----------+------------+--------+----------------+-------------+-------------
   1 | 2026-01-05 00:00:00.142+00 | seed     | batch      | INSERT | BATCH-2026-001 | (null)      | 9f2a...c41b
   2 | 2026-01-05 00:00:00.197+00 | seed     | result     | INSERT | S-001          | 9f2a...c41b | 1ce8...77a0
   3 | 2026-01-13 09:14:22.030+00 | jdoe     | result     | UPDATE | S-014          | 1ce8...77a0 | b430...e9f2

3행은 분석가가 1월 13일에 결과를 수정하는 것이다. app_user 는 jdoe, 동작은 UPDATE, 그리고 그 prev_hash 는 2행의 row_hash 와 같다. 링크가 맞아떨어지므로 verify_chain() 은 아무것도 반환하지 않는다.

약속이 아니라 테스트로 증명하기

테스트하지 않은 보장은 희망에 불과하다. 동반 저장소는 무결성 규칙을 실행 가능한 인수 기준(acceptance criteria)으로 다루며, pytest 테스트 모음으로 단언한다. pytest의 평이한 assert 재작성(rewriting) 덕분에 한 줄짜리 단언이 완전한 무결성 검사를 대신할 수 있다 [8]. 이 테스트들은 살아 있는 스택(make up && make seed && make load)을 대상으로 실행되며, 데이터베이스에 접근할 수 없으면 깔끔하게 건너뛴다. examples/tests/test_db.py 에서 가져왔다.

def test_alcoa_chain_intact(conn):
    assert _scalar(conn, "select count(*) from audit.change_log") > 0
    assert _scalar(conn, "select count(*) from audit.verify_chain()") == 0  # 0 broken links

그 두 번째 단언이 이 장 전체를 한 줄로 담는다. 체인에는 항목이 있고, 그 링크 가운데 0개 가 끊겨 있다. 다음 테스트가 더 흥미롭다. 실제로 수정을 가하고 그 추적이 그것을 포착했음을 증명하기 때문이다.

def test_audit_captures_update(conn):
    # an UPDATE must record old + new + who + why and keep the chain intact
    with conn.cursor() as cur:
        cur.execute("select set_config('app.user','pytest',false), "
                    "set_config('app.reason','test correction',false)")
        cur.execute("update lab.result set value = value where result_id = "
                    "(select result_id from lab.result limit 1)")
        conn.commit()
    last = _scalar(conn, "select action from audit.change_log "
                         "where app_user='pytest' order by seq desc limit 1")
    assert last == "UPDATE"
    assert _scalar(conn, "select count(*) from audit.verify_chain()") == 0

이것이 무엇을 증명하는지 따라가 보자. set_config('app.user','pytest', ...) 와 set_config('app.reason', ...) 는 귀속 가능한 누가 와 이유 왜 를 설정한다. 정확히 트리거가 읽는 세션 변수다. update lab.result set value = value 는 일부러 사소하게 만든 수정이지만(값을 자기 자신으로 설정한다), 트리거는 여전히 작동해 UPDATE 행을 기록한다. 감사 추적이 순(net) 차이만이 아니라 변경하는 행위 자체를 기록하기 때문이다. 그런 다음 테스트 모음은 pytest 가 남긴 가장 최근 행이 UPDATE 임을, 그리고 그 후에도 체인이 여전히 온전함을 단언한다. Attributable, Original, Complete, Consistent — 넷 모두가, 기계적으로, 노트북에서 검증된다.

검증기가 변조를 잡아내는 것을 보려면 새 코드가 필요 없다. 로그에 직접 손을 뻗어 링크를 끊으면 된다. verify_chain() 이 탐지하도록 만들어진 공격은 저장된 해시 컬럼을 흔드는 것, 즉 행을 삭제하거나, 순서를 바꾸거나, 체인을 끼워 넣는 것이다. 여기서는 한 행의 prev_hash 를 덮어써 링크를 끊는다.

-- Simulate an insider trying to splice the chain by relinking row 3.
UPDATE audit.change_log SET prev_hash = 'deadbeef'
WHERE seq = 3;
SELECT * FROM audit.verify_chain();

 seq | ok
-----+----
   3 | f

3행의 저장된 prev_hash 가 더 이상 2행의 저장된 row_hash 와 같지 않으므로 링크가 맞아떨어지지 않는다. 검증기는 seq = 3 을 첫 끊긴 링크로 표시하고, test_alcoa_chain_intact 는 다음 make test 에서 빨갛게 실패한다. 2행을 통째로 삭제하거나 seq 순서를 뒤바꿔도 링크는 같은 방식으로 끊기며 같은 방식으로 잡힌다. 변조는 숨은 채로 남지 못했다.

이 검증기가 잡지 못하는 것에 대해서도 똑같이 분명히 하자. 이것이 변조 입증 가능 과 우리가 뒷받침할 수 없는 주장의 차이이기 때문이다. 내부자가 링크를 끊는 대신 행 페이로드만 몰래 수정한다면 — UPDATE audit.change_log SET new_row = jsonb_set(new_row, '{value}', '99.9') WHERE seq = 2; — 그리고 prev_hash/row_hash 컬럼은 그대로 두면, verify_chain() 은 0개의 행 을 반환한다. 저장된 모든 링크가 여전히 맞아떨어지고, 수정은 탐지되지 않는다. 그 빈틈을 막으려면 페이로드로부터 각 row_hash 를 재계산해 저장된 값과 비교하는 검증기가 필요하다. 그런데 위에서 언급했듯 현재 스키마는 그것을 지원할 수 없다. 해시에 들어간 clock_timestamp() 가 결코 저장되지 않기 때문이다. 그 한계를 여기서 정직하게 밝히고, 그 해결책(저장된 ts 를 해시하고, 체인 헤드를 데이터베이스 밖에 저장)은 "실제 현장에서는" 과 21장에서 다시 다룬다.

왜 중요한가

데이터 무결성은 FDA 경고 서한(warning letter)과 EU GMP 지적 사항에서 단연 가장 흔한 주제다. 기업들이 데이터를 위조하려 작정해서가 아니라, 그들의 시스템이 무결성을 선택 사항 으로 만들기 때문이다. 끌 수 있는 감사 추적, 흔적 없이 덮어쓸 수 있는 값, 사용자가 임의로 설정할 수 있는 타임스탬프. ALCOA+ 는 규제 당국이 "신뢰할 수 있다" 가 무엇을 뜻하는지 설명할 때 쓰는 언어이며, Annex 11과 Part 11은 그것을 전산화 시스템에 대한 법으로 만든다 [3][9].

무결성을 스키마에 새겨 넣으면 기본값이 뒤집힌다. 트리거가 부착되어 있으면, 귀속 가능하고, 이유가 붙고, 타임스탬프가 찍히고, 해시로 연결된 감사 항목을 기록하지 않고서 규제 대상 행을 변경할 경로가 없다. 옳은 일이 데이터베이스가 허용하는 유일한 일이 된다. 그것이 "설계로 구현하는" 의 의미이며, 사람들에게 변경 사항을 문서화하는 것을 잊지 말라고 부탁하는 절차보다 훨씬 강력하다. FDA 자체의 데이터 무결성 가이던스는 감사 추적과 귀속 가능한 메타데이터를, 단지 약속하는 것이 아니라 반드시 구현해야 하는 CGMP 기대 사항으로 규정한다 [2]. 우리는 그것을 약 70줄의 SQL로 구현했다.

또한 이는 예외 기반 검토(review-by-exception)를 가능하게 한다. 검사관이나 QA 검토자는 단 한 번의 질의 — SELECT * FROM audit.change_log WHERE table_name = 'result' AND action = 'UPDATE' — 로 결과에 가해진 모든 수정 이력을, 변경 전 이미지, 변경 후 이미지, 분석가, 그리고 이유와 함께 볼 수 있다. 그것이 Part 11과 PIC/S PI 041이 기대하는 감사 추적 검토이며, 포렌식 작업이 아니라 하나의 질의다 [4].

실제 현장에서는

이 책이 약속하는 정직한 결산이 여기 있다. 우리가 구축한 패턴은 실제로 작동하고, 실행 가능하며, 표준에 부합한다. 트리거를 사용해 SQL:2011의 시스템 버전 이력(system-versioned history) 개념 — 행이 변경될 때 원본 행을 보존하는 것 — 을 구현한다. PostgreSQL은 (버전 18까지도) SQL:2011의 시스템 버전 시간 테이블(temporal table)을 네이티브로 구현하지 않으므로, 트리거가 그 동작을 얻는 관용적인 오픈 소스 방식이다 [10]. 해시 체인은 진짜 Haber–Stornetta 구성이다 [6]. 그리고 이 모든 것이 매 커밋마다 CI에서 테스트된다.

그러나 이것은 변조 입증 가능하지, 변조 방지는 아니며, 정직하게 짚어야 할 별개의 두 경계가 있다. 첫 번째는 검증기 자체 의 한계로, "어떤 수정이든 잡힌다" 는 강한 이야기보다 더 좁다. verify_chain() 은 저장된 prev_hash/row_hash 링크가 맞아떨어지는지만 확인하므로, 삭제되거나, 순서가 바뀌거나, 다시 연결된 행은 잡지만, 해시 컬럼을 그대로 둔 채 행의 old_row/new_row/app_user/reason 페이로드만 몰래 수정한 경우는 잡지 못한다. 더 나쁘게도, 현재 스키마는 재계산하는 검증기로 쉽게 업그레이드할 수 없다. 다이제스트가 행이 결코 저장하지 않는 새 clock_timestamp() 를 접어 넣기 때문이다. 그래서 올바른 재도출조차 row_hash 를 재현할 수 없다. 해결책은 21장을 위해 표시해 두는 작은 저장소 변경이다. 행의 저장된 ts 값을 해시하거나(또는 해시된 정확한 순간을 저장해) row_hash 를 재현 가능하게 만든 뒤, 저장된 컬럼들로부터 각 다이제스트를 재계산해 비교하는 검증기를 추가하는 것이다. 그래야만 "어떤 과거 항목을 변경하든 탐지된다" 가 열망이 아닌 참인 진술이 된다.

두 번째 경계는 운영적이며 더 크다. PostgreSQL 슈퍼유저(superuser) — 또는 audit.change_log 테이블의 소유자 — 는 DISABLE TRIGGER 를 실행해 감사 항목 없이 규제 대상 행을 수정하고, 수정 지점부터 앞으로 전체 해시 체인을 재계산한 뒤, 깨끗하게 검증되는 체인을 남길 수 있다. 우리의 방어는 우발적 변조를 가시화하고 고의적 변조의 비용을 막대하게 끌어올리지만, 데이터베이스 관리자를 이길 수는 없다. 그 빈틈을 막는 것은 코드 문제가 아니라 운영 문제다. 데이터베이스를 관리할 수 있는 사람과 데이터를 소유한 사람이 분리되는 직무 분리(segregation of duties), 잠긴 역할(role), 체인 헤드의 데이터베이스 밖 사본(주기적인 row_hash 를 23장에서 세우는 SeaweedFS WORM 버킷 같은 1회 기록(write-once) 저장소에 쓰거나, RFC 3161 신뢰 타임스탬프), 그리고 특권 세션을 포착하는 pgAudit. 이 모두는 21장과 22장의 검증(validation) 작업이 떠맡는다. 어떤 오픈 소스 구성 요소도 상자에서 꺼내자마자 21 CFR Part 11을 준수하지는 않으며, 이것도 예외가 아니다. 컴플라이언스는 검증된 시스템 그리고 그 절차 의 속성이지, 결코 SQL 파일의 속성이 아니다.

여기가 또한 오픈 소스 대 상용의 경계가 떨어지는 지점이다. 검증된 상용 히스토리안이나 MES(감사 하위 시스템을 갖춘 AVEVA PI, 벤더 MES 전자 배치 기록)는 여러분이 구축하는 대신 구성하는 감사 추적을 제공하며, 책임과 검증 패키지를 짊어지는 공급자가 그 뒤를 받친다. 우리 스택에서는 여러분 이 감사 로직을 소유한다. 그 말은 여러분이 그것을 검증하고, DDL을 버전 관리하고(Git에 들어 있어 변경 관리에 진짜 이점이다), 적격성 평가(qualification) 아래에서 방어하는 것을 소유한다는 뜻이다. NIIMBL의 SABRE 시설 — 2024년 4월에 착공한 NIIMBL / 델라웨어 대학교(University of Delaware) 파일럿 규모 cGMP 공장(cGMP는 현행(current) 우수 제조 관리 기준) — 은 바로 이런 감사 및 무결성 통제가 오픈 시스템과 상용 시스템에 두루 걸쳐야 하는 다중 벤더, 센서 밀집형 라인의 전형이다. SABRE는 데이터 표준이 아니라 시설이지만, 이 스택이 봉사하도록 만들어진 물리적 무대다.

핵심 용어

• ALCOA+ — 신뢰할 수 있는 GMP 기록을 정의하는 데이터 무결성 속성(Attributable, Legible, Contemporaneous, Original, Accurate, 더하여 Complete, Consistent, Enduring, Available).
• 감사 추적(audit trail) — 누가 무엇을, 언제, 왜 변경했는지에 대한 안전한 추가 전용 기록. 여기서는 트리거가 채우는 audit.change_log 테이블.
• 트리거 기반 감사(trigger-based audit) — 애플리케이션 코드에 의존하는 대신, PostgreSQL AFTER ... FOR EACH ROW 트리거를 사용해 모든 변경에서 OLD/NEW 행 이미지를 자동으로 포착하는 것.
• 시스템 버전(시간) 이력(system-versioned/temporal history) — 행이 변경될 때 이전 버전을 보존해 원본을 결코 잃지 않는 SQL:2011 패턴. 여기서는 트리거로 모방한다.
• 해시 체인(hash chain) — 각 행이 직전 행의 해시를 포함하는 SHA-256 해시를 저장하는 기록의 연속. 여기서는 삭제되거나, 순서가 바뀌거나, 다시 연결된 항목을 탐지 가능하게 만든다(해시 컬럼을 그대로 둔 채 행 페이로드만 몰래 수정한 경우는, 페이로드로부터 해시를 재계산하지 않는 한 탐지되지 않는다).
• 변조 입증 가능 대 변조 방지(tamper-evident vs tamper-proof) — 체인은 변경을 탐지 가능(입증 가능) 하게 만든다. 변경을 불가능(방지) 하게 만들지는 않으며, 특권을 가진 DBA는 여전히 이를 이길 수 있다.
• pgcrypto — 체인을 구축하는 데 쓰이는 digest()/SHA-256을 포함해 데이터베이스 내장 암호 함수를 제공하는 PostgreSQL 확장.
• 귀속 가능한 메타데이터(attributable metadata) — 값 주변에 기록되는 db_user/app_user/reason 맥락. 이 자체가 규제 대상 기록의 일부다.

다음 이야기

우리는 설계로 데이터를 변조 입증 가능하고 귀속 가능하게 만들었다. 그러나 감사 추적은 무엇이 변경되었는지 에는 답하지만, 누가 법적으로 유의미한 서명으로 그것을 공식 승인했는지 에는 아직 답하지 못한다. 21장 — 전자 기록 및 서명: 오픈 소스로 구현하는 Part 11 / Annex 11 에서는 이 장의 app.user 와 reason 훅(hook)을 가져와 실제 인증된 신원에 묶고, 특권 세션 로깅을 위한 pgAudit과 eLabFTW를 통한 암호학적 전자 서명 및 변경 이유 서비스를 추가한다. 그리고 오픈 소스가 어떤 Part 11 조항을 충족하고 어떤 조항이 여전히 절차나 상용 도구를 요구하는지 정확히 보여주는, 가차 없이 정직한 빈틈 명세서(gap register)를 그린다.