트리거로 테이블 감사하기

감사가 필요한 이유

감사 테이블

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  user_name TEXT NOT NULL DEFAULT CURRENT_USER,
  table_name TEXT NOT NULL,
  action TEXT NOT NULL,             -- INSERT, UPDATE, DELETE
  row_id TEXT,
  old_data JSONB,
  new_data JSONB
);

감사 트리거 함수

CREATE OR REPLACE FUNCTION audit_row()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, row_id, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id::TEXT, OLD.id::TEXT),
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

테이블에 연결하기

CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_row();

to_jsonb(NEW)

작업 주체 추적

-- App sets:
SET LOCAL app.user_id = '42';

-- Trigger reads:
INSERT INTO audit_log (... actor_id ...) VALUES (..., current_setting('app.user_id', true)::BIGINT);

특정 열 감사

CREATE TRIGGER trg_audit_role_change
AFTER UPDATE OF role ON users
FOR EACH ROW WHEN (OLD.role IS DISTINCT FROM NEW.role)
EXECUTE FUNCTION audit_role_change();

테이블별 감사 테이블

변경분만 감사하기

INSERT INTO audit_log (table_name, action, changes)
VALUES (
  TG_TABLE_NAME, TG_OP,
  (SELECT jsonb_object_agg(key, value)
   FROM jsonb_each(to_jsonb(NEW))
   WHERE NEW.* IS DISTINCT FROM OLD.* AND to_jsonb(NEW)->key IS DISTINCT FROM to_jsonb(OLD)->key)
);

감사 테이블 성능

CREATE TABLE audit_log (...) PARTITION BY RANGE (ts);
CREATE TABLE audit_log_2024_q1 PARTITION OF audit_log
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

성능 절충

DB 트리거를 사용하지 않을 때

복습

빠른 확인