HackToTech

Hack To Technology

PGAuditのログを減らしたくてバージョンごとの挙動の違いを見てたのでメモ

PostgreSQL PGAudit

github.com

  • PGAuditのissueとcommitを眺めていて、 個人的に悩みだった下記の問題が解決してそうだったのでバージョンごとの違いを確認する
    • INSERT時の外部キーの監査ログがWRITEとして出てしまう問題
      • 正直あまり意味のあるログではないので、出来る限り取りたくない
    • SELECT FOR UPDATEがWRITEのログとして出てしまう問題
      • WRITEだけログに出したいのに、キューとして使用しているテーブルのポーリングログ(SELECT FOR UPDATE)が出て邪魔になったりしている

検証結果

  • PostgreSQLの13以降を使用していて、且つ pgaudit.log が all とか read じゃなければ、
    オブジェクト監査ログで SELECT を取得しているテーブル以外は、これらのログを取得しなくても済むようになりそう

以下は、それぞれ検証したPostgreSQLのバージョン(PGAuditのバージョン)

12.17(1.4.3)

  • INSERTの外部キー監査ログ
2023-11-28 16:38:34.149 UTC [75] LOG:  AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.ref,"INSERT INTO ref values(1, 'insert');",<none>
2023-11-28 16:38:34.150 UTC [75] LOG:  AUDIT: SESSION,2,2,WRITE,UPDATE,TABLE,public.main,"SELECT 1 FROM ONLY ""public"".""main"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",1
  • SELECT FOR UPDATEのログ
2023-11-28 16:42:18.902 UTC [75] LOG:  AUDIT: SESSION,5,1,MISC,BEGIN,,,BEGIN;,<none>
2023-11-28 16:42:22.407 UTC [75] LOG:  AUDIT: SESSION,6,1,WRITE,UPDATE,TABLE,public.ref,SELECT * FROM ref WHERE main_id = 1 FOR UPDATE;,<none>
2023-11-28 16:42:35.654 UTC [75] LOG:  AUDIT: SESSION,7,1,WRITE,UPDATE,TABLE,public.ref,UPDATE ref SET memo = 'update' WHERE main_id = 1;,<none>
2023-11-28 16:42:39.230 UTC [75] LOG:  AUDIT: SESSION,8,1,MISC,COMMIT,,,COMMIT;,<none>

13.13(1.5.2)

  • INSERTの外部キー監査ログ
2023-11-28 17:01:04.890 UTC [42] LOG:  AUDIT: SESSION,4,1,WRITE,INSERT,TABLE,public.ref,"INSERT INTO ref values(1, 'insert');",<none>
2023-11-28 17:01:04.890 UTC [42] LOG:  AUDIT: SESSION,4,2,READ,SELECT,TABLE,public.main,"SELECT 1 FROM ONLY ""public"".""main"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",1
  • SELECT FOR UPDATEのログ
2023-11-28 17:02:49.548 UTC [42] LOG:  AUDIT: SESSION,5,1,MISC,BEGIN,,,BEGIN;,<none>
2023-11-28 17:02:49.548 UTC [42] LOG:  AUDIT: SESSION,6,1,READ,SELECT,TABLE,public.ref,SELECT * FROM ref WHERE main_id = 1 FOR UPDATE;,<none>
2023-11-28 17:02:49.549 UTC [42] LOG:  AUDIT: SESSION,7,1,WRITE,UPDATE,TABLE,public.ref,UPDATE ref SET memo = 'update' WHERE main_id = 1;,<none>
2023-11-28 17:02:49.549 UTC [42] LOG:  AUDIT: SESSION,8,1,MISC,COMMIT,,,COMMIT;,<none>

14.10(1.6.2)

  • INSERTの外部キー監査ログ
2023-11-28 17:13:31.837 UTC [41] LOG:  AUDIT: SESSION,4,1,WRITE,INSERT,TABLE,public.ref,"INSERT INTO ref values(1, 'insert');",<none>
2023-11-28 17:13:31.838 UTC [41] LOG:  AUDIT: SESSION,4,2,READ,SELECT,TABLE,public.main,"SELECT 1 FROM ONLY ""public"".""main"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",1
  • SELECT FOR UPDATEのログ
2023-11-28 17:13:57.347 UTC [41] LOG:  AUDIT: SESSION,5,1,MISC,BEGIN,,,BEGIN;,<none>
2023-11-28 17:13:57.347 UTC [41] LOG:  AUDIT: SESSION,6,1,READ,SELECT,TABLE,public.ref,SELECT * FROM ref WHERE main_id = 1 FOR UPDATE;,<none>
2023-11-28 17:13:57.347 UTC [41] LOG:  AUDIT: SESSION,7,1,WRITE,UPDATE,TABLE,public.ref,UPDATE ref SET memo = 'update' WHERE main_id = 1;,<none>
2023-11-28 17:13:57.347 UTC [41] LOG:  AUDIT: SESSION,8,1,MISC,COMMIT,,,COMMIT;,<none>

検証環境構築

バージョン違うだけでほぼ同じなので12のパターンだけ

docker run --name pg12.17 -e POSTGRES_PASSWORD=password -d postgres:12.17
docker exec -it pg12.17 /bin/bash

# PGAuditのbuildに必要なものとかを入れる
apt update &&\
apt install -y build-essential curl libssl-dev libkrb5-dev postgresql-server-dev-12

cd tmp &&\
curl -L https://github.com/pgaudit/pgaudit/archive/refs/tags/1.4.3.tar.gz | tar xz --strip 1 &&\
make install USE_PGXS=1
  • shared_preload_libraries を設定する
psql -Upostgres
ALTER SYSTEM SET shared_preload_libraries TO 'pgaudit';
  • 読み込ませる為にコンテナの再起動をする
docker restart pg12.17
  • 再起動後に、PGAuditの設定をする
CREATE EXTENSION pgaudit;
CREATE ROLE auditor;
ALTER SYSTEM SET pgaudit.role = 'auditor';
ALTER SYSTEM SET pgaudit.log = 'all';
ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.log_level = 'log';
ALTER SYSTEM SET pgaudit.log_parameter = on;
ALTER SYSTEM SET pgaudit.log_relation = on;
ALTER SYSTEM SET pgaudit.log_statement_once = on;
SELECT pg_reload_conf();
  • SQLを流してログを確かめる
CREATE TABLE main (
   id SERIAL PRIMARY KEY
);

CREATE TABLE ref (
   main_id SERIAL REFERENCES main,
   memo    TEXT
);

INSERT INTO main values(1);
INSERT INTO ref values(1, 'insert');

BEGIN;
SELECT * FROM ref WHERE main_id = 1 FOR UPDATE;
UPDATE ref SET memo = 'update' WHERE main_id = 1;
COMMIT;

PGAuditのPR