[PostgreSQL] 감사 로그 싸게 남기기 — 문장 트리거 + transition table + JSONB + 이벤트 트리거

설치·접속: PostgreSQL 설치와 접속

부제: 만 건 배치 UPDATE도 누가 무엇을 바꿨는지 감사 테이블에 남겨야 하는데, 행 트리거를 걸었더니 배치가 기어갈 때

계정 잔액을 바꾸는 만 건짜리 배치 UPDATE가 있고, 규정상 변경 전후를 모두 감사 테이블에 남겨야 한다. 흔히 하듯 FOR EACH ROW 트리거를 걸면 트리거 함수가 만 번 발화한다. 여기서는 문장 트리거 한 번으로 벌크 기록하고, no-op은 걸러내고, DDL 변경까지 잡는 감사 파이프라인을 단계별로 얹는다.

1단계 — 문장 트리거 + transition table로 만 건을 1회 발화로 기록

FOR EACH STATEMENT 트리거는 영향 행 수와 무관하게 문장당 딱 1회 실행된다. REFERENCING OLD TABLE / NEW TABLE로 "이번 문장이 바꾼 행 전체"를 두 개의 관계처럼 조회할 수 있어, 이 둘을 조인해 to_jsonb() 스냅샷을 한 번의 INSERT ... SELECT로 벌크 적재한다.

CREATE TABLE acct (id int PRIMARY KEY, name text, balance numeric);
INSERT INTO acct SELECT g, 'user'||g, 1000 FROM generate_series(1,10000) g;

CREATE TABLE audit (
  acct_id int, before jsonb, after jsonb, changed_at timestamptz DEFAULT now()
);

CREATE FUNCTION log_stmt() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit(acct_id, before, after)
  SELECT n.id, to_jsonb(o), to_jsonb(n)
  FROM   newtab n JOIN oldtab o USING (id)
  WHERE  o IS DISTINCT FROM n;
  RAISE NOTICE 'statement trigger fired ONCE (audited % rows)',
    (SELECT count(*) FROM newtab);
  RETURN NULL;
END $$;

CREATE TRIGGER audit_upd AFTER UPDATE ON acct
  REFERENCING OLD TABLE AS oldtab NEW TABLE AS newtab
  FOR EACH STATEMENT EXECUTE FUNCTION log_stmt();

UPDATE acct SET balance = balance + 50;   -- 만 건

NOTICE:  statement trigger fired ONCE (audited 10000 rows)
UPDATE 10000

 audit_rows
------------
      10000

 acct_id |                   before                    |                    after
---------+---------------------------------------------+---------------------------------------------
       1 | {"id": 1, "name": "user1", "balance": 1000} | {"id": 1, "name": "user1", "balance": 1050}
       2 | {"id": 2, "name": "user2", "balance": 1000} | {"id": 2, "name": "user2", "balance": 1050}

만 건을 UPDATE했는데 NOTICE는 한 줄만 찍혔다. 트리거가 1회 발화하면서 그 안에서 만 건의 감사 행을 벌크로 넣었다. to_jsonb(o)/to_jsonb(n)이 변경 전후를 통째로 JSONB로 스냅샷하니 컬럼이 늘어도 감사 스키마를 안 바꿔도 된다.

2단계 — 행 트리거였다면 몇 번 발화했나

같은 UPDATE를 FOR EACH ROW 트리거로 카운트해 보면 차이가 극명하다.

CREATE TABLE fire (n int); INSERT INTO fire VALUES (0);
CREATE FUNCTION count_row() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN UPDATE fire SET n = n + 1; RETURN NEW; END $$;
CREATE TRIGGER row_fire BEFORE UPDATE ON acct
  FOR EACH ROW EXECUTE FUNCTION count_row();

UPDATE acct SET balance = balance + 1;
SELECT n AS row_trigger_fired FROM fire;

 row_trigger_fired
-------------------
             10000

행 트리거는 만 번 발화했다. 문장 트리거 1회 vs 행 트리거 10,000회 — 감사 로그를 행마다 INSERT하던 구조가 배치를 죽이는 이유가 여기 있다.

3단계 — 값이 안 바뀐 행은 애초에 안 넣기

위 함수는 WHERE o IS DISTINCT FROM n으로 이미 no-op을 걸렀다. IS DISTINCT FROM은 NULL도 안전하게 비교한다. 컬럼 하나만 관심 있다면 WHERE o.balance IS DISTINCT FROM n.balance로 좁히면 된다. no-op UPDATE가 섞여 들어와도 감사 테이블이 불어나지 않는다.

4단계 — DDL까지 잡기: 이벤트 트리거로 위험 스키마 변경 차단

일반 트리거는 DML(행 변경)만, 그것도 테이블 단위다. "누가 프로덕션에서 DROP TABLE 했나"는 못 잡는다. 이벤트 트리거는 DB 전역에서 DDL을 훅킹한다. ddl_command_start에서 예외를 던지면 DDL 자체가 실행 전에 막힌다.

CREATE FUNCTION ddl_guard() RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
  IF tg_tag IN ('DROP TABLE','ALTER TABLE') THEN
    RAISE EXCEPTION 'DDL blocked by guard: % is not allowed during business hours', tg_tag;
  END IF;
END $$;

CREATE EVENT TRIGGER guard ON ddl_command_start
  EXECUTE FUNCTION ddl_guard();

DROP TABLE acct;   -- 차단되어야 한다

ERROR:  DDL blocked by guard: DROP TABLE is not allowed during business hours
CONTEXT:  PL/pgSQL function ddl_guard() line 4 at RAISE

DROP TABLE이 실행되기 전에 예외로 튕겨 나갔다. DML 감사(문장 트리거)와 DDL 가드(이벤트 트리거)를 한 DB에 얹으면 "데이터가 바뀐 이력"과 "스키마가 바뀐 이력"을 둘 다 손에 쥔다.

결론 — 언제 이 조합인가

대량 배치가 도는 테이블에 감사 로그가 필수인데 행 트리거로 성능이 무너질 때가 정확히 이 조합의 자리다. 문장 트리거 + transition table로 발화 횟수를 행 수에서 문장 수로 떨어뜨리고, to_jsonb로 스키마 독립적인 스냅샷을 남기고, IS DISTINCT FROM으로 no-op을 거르고, 이벤트 트리거로 DDL 계층까지 덮는다.

이렇게도 쓴다

ddl_command_end에서 pg_event_trigger_ddl_commands()로 무엇이 바뀌었는지 감사 기록. (조합: 이벤트 트리거 감사)

CREATE FUNCTION ddl_audit() RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    INSERT INTO ddl_log(tag, obj) VALUES (r.command_tag, r.object_identity);
  END LOOP;
END $$;
CREATE EVENT TRIGGER audit_ddl ON ddl_command_end EXECUTE FUNCTION ddl_audit();
-- CREATE TABLE probe (x int); →  ('CREATE TABLE', 'public.probe') 기록됨

 

INSERT/DELETE도 각각 transition table로 감사. INSERT는 NEW TABLE만, DELETE는 OLD TABLE만.

CREATE TRIGGER audit_ins AFTER INSERT ON acct
  REFERENCING NEW TABLE AS newtab
  FOR EACH STATEMENT EXECUTE FUNCTION log_ins();

 

특정 컬럼 변경만 감사하고 싶으면 조인 WHERE를 좁힌다.

INSERT INTO audit(acct_id, before, after)
SELECT n.id, to_jsonb(o), to_jsonb(n)
FROM newtab n JOIN oldtab o USING (id)
WHERE o.balance IS DISTINCT FROM n.balance;   -- 잔액이 바뀐 행만

 

행 트리거가 꼭 필요하면 WHEN으로 함수 호출 자체를 건너뛰어 비용을 줄인다. (조합: WHEN 조건)

CREATE TRIGGER audit_row AFTER UPDATE ON acct
  FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
  EXECUTE FUNCTION log_one();

 

롤 기반으로 예외를 둔다 — 배포 롤만 DDL 허용.

IF tg_tag = 'DROP TABLE' AND current_user <> 'deployer' THEN
  RAISE EXCEPTION 'only deployer may DROP TABLE (got %)', current_user;
END IF;