pg.ddx.io  pgaudit/pgaudit GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
8+ messages / 2 participants
[nested] [flat]

* [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
@ 2026-07-27 08:46  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  0 siblings, 0 replies; 8+ messages in thread

From: dwsteele (@dwsteele) @ 2026-07-27 08:46 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

"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.out index 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.c index 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.sql index 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
^ permalink raw [nested|flat] 8+ messages in thread

* Re: [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
@ 2026-07-27 08:49  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  6 siblings, 0 replies; 8+ messages in thread

From: dwsteele (@dwsteele) @ 2026-07-27 08:49 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

@dwsteele assigned @dwsteele.

^ permalink  raw  [nested|flat] 8+ messages in thread

* Re: [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
@ 2026-07-27 08:49  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  6 siblings, 0 replies; 8+ messages in thread

From: dwsteele (@dwsteele) @ 2026-07-27 08:49 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

@dwsteele applied label "bug".

^ permalink  raw  [nested|flat] 8+ messages in thread

* Re: [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
@ 2026-07-27 19:33  "pgguru (@pgguru)" <noreply+pgguru@github.com>
  6 siblings, 0 replies; 8+ messages in thread

From: pgguru (@pgguru) @ 2026-07-27 19:33 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

Check my thoughts here: the root cause is that `pgaudit_ExecutorCheckPerms_hook` staples the query's `rangeTabls` onto whatever's on top of the audit stack and (with `log_rows` on) defers logging to `ExecutorEnd`. That assumes the stack-top is the statement whose `ExecutorEnd` will actually fire. Any utility command that checks table perms *out of band* (i.e., a bare `ExecCheckPermissions()` with no executor lifecycle behind it) breaks that assumption and orphans the entry on the DDL item.

`ALTER TABLE … ADD FOREIGN KEY` via `RI_Initial_Check()` is just the case someone happened to hit, but it would seem the same thing should reproduce on `ALTER TABLE … VALIDATE CONSTRAINT` for a `NOT VALID` FK due to exercising the same `RI_Initial_Check()` path, and the PR doesn't cover it. Probably worth a second test case there; I'd bet the fix already handles it but it's untested.

Also worth noting this is the *third* patch over the same design smell — there's already a special-case for `CREATE TABLE AS` right in the perms hook, and now COPY. FK validation is the third. Each new out-of-band perms path seems to need its own bespoke fix.  That said, not sure there is much else to do than identify and fix.

One thing that makes me a little nervous about the fix: gating on `T_CopyStmt` hard-codes "COPY is the only utility that checks perms via the hook but never reaches `ExecutorEnd`." I couldn't find another one today (TRUNCATE/LOCK use `pg_class_aclcheck`, CTAS/REFRESH MATVIEW go through a real `ExecutorEnd`), so it's probably complete. But the failure mode flipped: before, a mis-detected command got its DDL clobbered (loud, shows up in tests); now an undetected one would silently drop its SELECT/DML entry, which for an audit extension is arguably worse. A more robust fix would tag the stack item at deferral time with whether an `ExecutorEnd` is expected instead of enumerating command tags, but that's a bigger refactor than this PR's after.  (Not sure if there may be any 19-based considerations either, not super familiar with new nodes/operations here yet.)

^ permalink  raw  [nested|flat] 8+ messages in thread

* Re: [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
@ 2026-07-28 03:05  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  6 siblings, 0 replies; 8+ messages in thread

From: dwsteele (@dwsteele) @ 2026-07-28 03:05 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

> ALTER TABLE … ADD FOREIGN KEY via RI_Initial_Check() is just the case someone happened to hit

True enough. I added an additional test in dbcaa9a4.

> Also worth noting this is the third patch over the same design smell ... That said, not sure there is much else to do than identify and fix.

Agreed. We can't have anything like proper design when we are trying to put together the output from a bunch of different hooks that we have little control over. This has always been really tricky.

> One thing that makes me a little nervous about the fix: gating on T_CopyStmt hard-codes "COPY is the only utility that checks perms via the hook but never reaches ExecutorEnd."

Fair enough, and it turns out there is a more general way to do this: PR #328. Rather than enumerating command tags it uses ereport_on_violation to identify permission checks that are not enforcing. A statement's permission check has to error on violation, so anything passing false is a probe, and RI_Initial_Check() appears to be the in-tree caller that does, across all supported branches.

It also covers a case this PR doesn't: the same misattribution happens with pgaudit.log_rows off, through the non-deferred path, and predates the COPY change entirely. With that in, the T_CopyStmt gate is no longer what prevents the misattribution, so I propose dropping the gate change here and keeping only the tests.

To be clear, I'd prefer to go with PR #328 and drop this one entirely.

^ permalink  raw  [nested|flat] 8+ messages in thread

* Re: [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
@ 2026-07-28 15:05  "pgguru (@pgguru)" <noreply+pgguru@github.com>
  6 siblings, 0 replies; 8+ messages in thread

From: pgguru (@pgguru) @ 2026-07-28 15:05 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

Agreed #328 is the way to go.

^ permalink  raw  [nested|flat] 8+ messages in thread

* Re: [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
@ 2026-07-28 15:07  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  6 siblings, 0 replies; 8+ messages in thread

From: dwsteele (@dwsteele) @ 2026-07-28 15:07 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

Alright, I'll close this one. Thank you for the careful review and especially for pushing back. #328 is the better solution.

^ permalink  raw  [nested|flat] 8+ messages in thread

* Re: [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY.
@ 2026-07-28 15:07  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  6 siblings, 0 replies; 8+ messages in thread

From: dwsteele (@dwsteele) @ 2026-07-28 15:07 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

@dwsteele closed this.

^ permalink  raw  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2026-07-28 15:07 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-27 08:46 [pgaudit/pgaudit] PR #327: Only flush deferred select/dml audit entries for COPY. "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-27 08:49 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-27 08:49 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-27 19:33 ` "pgguru (@pgguru)" <noreply+pgguru@github.com>
2026-07-28 03:05 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-28 15:05 ` "pgguru (@pgguru)" <noreply+pgguru@github.com>
2026-07-28 15:07 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-28 15:07 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox