From: dwsteele (@dwsteele) <noreply+dwsteele@github.com>
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
Subject: [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
Date: Mon, 27 Jul 2026 08:46:03 +0000
Message-ID: <gh-pgaudit-pgaudit-327@github.com> (raw)
"Report rows affected by COPY when pgaudit.log_rows is enabled" (9ceed0cd) added a flush to pgaudit_ProcessUtility_hook() so that table-form COPY, which is executed entirely within DoCopy() and never reaches ExecutorEnd, still logs the select/dml entry that ExecutorCheckPerms deferred. The flush was gated on rangeTabls being set on the stack item, which is broader than intended, since COPY is not the only utility command that can leave a range table there.
DoCopy() is not the only caller of the permissions hook outside the executor. RI_Initial_Check(), reached from ALTER TABLE ... ADD FOREIGN KEY when the constraint must be validated against existing rows, also calls ExecCheckPermissions() directly, and does so while the utility command's own stack item is on top of the stack. With pgaudit.log_rows enabled the range table was therefore left on the ALTER TABLE entry and flushed by the hook, rewriting that entry as a SELECT against the constrained tables:
AUDIT: SESSION,156,2,READ,SELECT,TABLE,public.fk_pk,ALTER TABLE fk_fk ...
AUDIT: SESSION,156,2,READ,SELECT,TABLE,public.fk_fk,ALTER TABLE fk_fk ...
Worse, log_select_dml() marks the entry logged, so the DDL record was skipped at the end of the hook and lost entirely. The statement was not audited at all. With the fix it is reported as it was before the flush was added:
AUDIT: SESSION,156,2,DDL,ALTER TABLE,,,ALTER TABLE fk_fk ...
Gate the flush on the command tag instead. Utility commands other than COPY once again discard the deferred entry, which is what they did before the flush existed.
Add a test covering foreign key validation with pgaudit.log_rows enabled, both by adding a constraint and by validating one added NOT VALID. The tables must be populated, since the constraint is not validated against existing rows otherwise and RI_Initial_Check() is never reached. The existing foreign key test uses CREATE TABLE ... REFERENCES, which does not exercise this path.
inline patch 1: expected/pgaudit.out, pgaudit.c, sql/pgaudit.sql (+47/-6 lines) download inline-1.patchdiff --git a/expected/pgaudit.out b/expected/pgaudit.outindex 8570d03..af7619d 100644--- a/expected/pgaudit.out+++ b/expected/pgaudit.out@@ -2622,6 +2622,34 @@ NOTICE: AUDIT: SESSION,150,1,DDL,DROP INDEX,,,DROP INDEX h_idx,<none>,0
DROP TABLE h;
NOTICE: AUDIT: SESSION,151,1,DDL,DROP TABLE,,,DROP TABLE h,<none>,0
--
+-- Test that foreign key validation is not logged against the ALTER TABLE.+-- The tables must be populated so the constraint is validated by+-- RI_Initial_Check(), which checks permissions outside the executor. Both+-- commands that validate reach it, i.e. adding a constraint and validating one+-- that was added NOT VALID.+CREATE TABLE fk_pk (id int PRIMARY KEY);+NOTICE: AUDIT: SESSION,152,1,DDL,CREATE TABLE,,,CREATE TABLE fk_pk (id int PRIMARY KEY),<none>,0+INSERT INTO fk_pk VALUES (1);+NOTICE: AUDIT: SESSION,153,1,WRITE,INSERT,TABLE,public.fk_pk,INSERT INTO fk_pk VALUES (1),<none>,1+CREATE TABLE fk_fk (id int);+NOTICE: AUDIT: SESSION,154,1,DDL,CREATE TABLE,,,CREATE TABLE fk_fk (id int),<none>,0+INSERT INTO fk_fk VALUES (1);+NOTICE: AUDIT: SESSION,155,1,WRITE,INSERT,TABLE,public.fk_fk,INSERT INTO fk_fk VALUES (1),<none>,1+ALTER TABLE fk_fk ADD CONSTRAINT fk_fk_id_fkey FOREIGN KEY (id) REFERENCES fk_pk(id);+NOTICE: AUDIT: SESSION,156,1,READ,SELECT,TABLE,public.fk_fk,"SELECT fk.""id"" FROM ONLY ""public"".""fk_fk"" fk LEFT OUTER JOIN ONLY ""public"".""fk_pk"" pk ON ( pk.""id"" OPERATOR(pg_catalog.=) fk.""id"") WHERE pk.""id"" IS NULL AND (fk.""id"" IS NOT NULL)",<none>,0+NOTICE: AUDIT: SESSION,156,1,READ,SELECT,TABLE,public.fk_pk,"SELECT fk.""id"" FROM ONLY ""public"".""fk_fk"" fk LEFT OUTER JOIN ONLY ""public"".""fk_pk"" pk ON ( pk.""id"" OPERATOR(pg_catalog.=) fk.""id"") WHERE pk.""id"" IS NULL AND (fk.""id"" IS NOT NULL)",<none>,0+NOTICE: AUDIT: SESSION,156,2,DDL,ALTER TABLE,,,ALTER TABLE fk_fk ADD CONSTRAINT fk_fk_id_fkey FOREIGN KEY (id) REFERENCES fk_pk(id),<none>,0+ALTER TABLE fk_fk ADD CONSTRAINT fk_fk_id_fkey_nv FOREIGN KEY (id) REFERENCES fk_pk(id) NOT VALID;+NOTICE: AUDIT: SESSION,157,1,DDL,ALTER TABLE,,,ALTER TABLE fk_fk ADD CONSTRAINT fk_fk_id_fkey_nv FOREIGN KEY (id) REFERENCES fk_pk(id) NOT VALID,<none>,0+ALTER TABLE fk_fk VALIDATE CONSTRAINT fk_fk_id_fkey_nv;+NOTICE: AUDIT: SESSION,158,1,READ,SELECT,TABLE,public.fk_fk,"SELECT fk.""id"" FROM ONLY ""public"".""fk_fk"" fk LEFT OUTER JOIN ONLY ""public"".""fk_pk"" pk ON ( pk.""id"" OPERATOR(pg_catalog.=) fk.""id"") WHERE pk.""id"" IS NULL AND (fk.""id"" IS NOT NULL)",<none>,0+NOTICE: AUDIT: SESSION,158,1,READ,SELECT,TABLE,public.fk_pk,"SELECT fk.""id"" FROM ONLY ""public"".""fk_fk"" fk LEFT OUTER JOIN ONLY ""public"".""fk_pk"" pk ON ( pk.""id"" OPERATOR(pg_catalog.=) fk.""id"") WHERE pk.""id"" IS NULL AND (fk.""id"" IS NOT NULL)",<none>,0+NOTICE: AUDIT: SESSION,158,2,DDL,ALTER TABLE,,,ALTER TABLE fk_fk VALIDATE CONSTRAINT fk_fk_id_fkey_nv,<none>,0+DROP TABLE fk_fk;+NOTICE: AUDIT: SESSION,159,1,DDL,DROP TABLE,,,DROP TABLE fk_fk,<none>,0+DROP TABLE fk_pk;+NOTICE: AUDIT: SESSION,160,1,DDL,DROP TABLE,,,DROP TABLE fk_pk,<none>,0+--
-- Change configuration of user 1 so that full statements are not logged
\connect - :current_user
ALTER ROLE regress_user1 RESET pgaudit.log_relation;
diff --git a/pgaudit.c b/pgaudit.cindex a7960f0..8656f16 100644--- a/pgaudit.c+++ b/pgaudit.c@@ -1803,13 +1803,10 @@ pgaudit_ProcessUtility_hook(PlannedStmt *pstmt,
stack_valid(stackId);
/*
- * The executor hooks do not supply the rows affected for a utility- * command that defers a select/dml audit entry, nor for COPY. Use the- * processed count from the completed command instead.+ * Neither form of COPY gets the rows affected from the executor hooks.+ * Use the processed count from the completed command instead.
*/
- if (auditLogRows &&- (stackItem->auditEvent.rangeTabls != NULL ||- stackItem->auditEvent.commandTag == T_CopyStmt))+ if (auditLogRows && stackItem->auditEvent.commandTag == T_CopyStmt)
{
stackItem->auditEvent.rows = qc ? qc->nprocessed : 0;
diff --git a/sql/pgaudit.sql b/sql/pgaudit.sqlindex 62d613c..b2fca5c 100644--- a/sql/pgaudit.sql+++ b/sql/pgaudit.sql@@ -1643,6 +1643,22 @@ CREATE INDEX h_idx ON h (x);
DROP INDEX h_idx;
DROP TABLE h;
+--+-- Test that foreign key validation is not logged against the ALTER TABLE.+-- The tables must be populated so the constraint is validated by+-- RI_Initial_Check(), which checks permissions outside the executor. Both+-- commands that validate reach it, i.e. adding a constraint and validating one+-- that was added NOT VALID.+CREATE TABLE fk_pk (id int PRIMARY KEY);+INSERT INTO fk_pk VALUES (1);+CREATE TABLE fk_fk (id int);+INSERT INTO fk_fk VALUES (1);+ALTER TABLE fk_fk ADD CONSTRAINT fk_fk_id_fkey FOREIGN KEY (id) REFERENCES fk_pk(id);+ALTER TABLE fk_fk ADD CONSTRAINT fk_fk_id_fkey_nv FOREIGN KEY (id) REFERENCES fk_pk(id) NOT VALID;+ALTER TABLE fk_fk VALIDATE CONSTRAINT fk_fk_id_fkey_nv;+DROP TABLE fk_fk;+DROP TABLE fk_pk;+
--
-- Change configuration of user 1 so that full statements are not logged
\connect - :current_user