pg.ddx.io  pgaudit/pgaudit GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgaudit/pgaudit] PR #328: Do not audit permission checks that are not enforcing.
6+ messages / 2 participants
[nested] [flat]

* [pgaudit/pgaudit] PR #328: Do not audit permission checks that are not enforcing.
@ 2026-07-28 03:01 "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  0 siblings, 0 replies; 6+ messages in thread

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

A caller that passes ereport_on_violation false to ExecCheckPermissions() is asking whether it may proceed, not executing a statement against the relations, so there is nothing to audit. RI_Initial_Check() does this to decide whether a foreign key can be validated with a single query, and it runs while the utility command's own stack item is on top of the audit stack. The check was therefore audited as if the ALTER TABLE had read the constrained tables itself:

    AUDIT: SESSION,104,1,READ,SELECT,TABLE,public.fk_pk,ALTER TABLE fk_fk ADD CONSTRAINT ...
    AUDIT: SESSION,104,1,READ,SELECT,TABLE,public.fk_fk,ALTER TABLE fk_fk ADD CONSTRAINT ...

Worse, log_select_dml() marks the entry logged, so the DDL record was skipped at the end of pgaudit_ProcessUtility_hook() and lost entirely. The statement was not audited at all. With the fix only the validation query and the DDL record are reported:

    AUDIT: SESSION,104,1,READ,SELECT,TABLE,public.fk_fk,"SELECT fk.""id"" FROM ONLY ..."
    AUDIT: SESSION,104,1,READ,SELECT,TABLE,public.fk_pk,"SELECT fk.""id"" FROM ONLY ..."
    AUDIT: SESSION,104,2,DDL,ALTER TABLE,,,ALTER TABLE fk_fk ADD CONSTRAINT ...

Skip the audit work for such checks. This is not specific to pgaudit.log_rows, and applies to both commands that validate a foreign key, i.e. adding a constraint and validating one that was added NOT VALID.

Note that only the check itself is skipped. The validation query that RI_Initial_Check() goes on to run is executed through the executor like any other query and remains audited, so the read of both tables is still recorded. This is not ideal but a fix for it in pgAudit would be invasive. It would be better to address it upstream in PostgreSQL.

Add a test covering foreign key validation. It is placed in the section that runs with pgaudit.log_rows off, since that is where the check is logged directly rather than deferred. 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 (+54/-1 lines) download inline-1.patchdiff --git a/expected/pgaudit.out b/expected/pgaudit.out index 8570d03..4ba7260 100644 --- a/expected/pgaudit.out +++ b/expected/pgaudit.out @@ -1432,6 +1432,34 @@ NOTICE: AUDIT: SESSION,98,1,DDL,DROP INDEX,,,DROP INDEX h_idx,<none> DROP TABLE h; NOTICE: AUDIT: SESSION,99,1,DDL,DROP TABLE,,,DROP TABLE h,<none> -- +-- 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,100,1,DDL,CREATE TABLE,,,CREATE TABLE fk_pk (id int PRIMARY KEY),<none> +INSERT INTO fk_pk VALUES (1); +NOTICE: AUDIT: SESSION,101,1,WRITE,INSERT,TABLE,public.fk_pk,INSERT INTO fk_pk VALUES (1),<none> +CREATE TABLE fk_fk (id int); +NOTICE: AUDIT: SESSION,102,1,DDL,CREATE TABLE,,,CREATE TABLE fk_fk (id int),<none> +INSERT INTO fk_fk VALUES (1); +NOTICE: AUDIT: SESSION,103,1,WRITE,INSERT,TABLE,public.fk_fk,INSERT INTO fk_fk VALUES (1),<none> +ALTER TABLE fk_fk ADD CONSTRAINT fk_fk_id_fkey FOREIGN KEY (id) REFERENCES fk_pk(id); +NOTICE: AUDIT: SESSION,104,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> +NOTICE: AUDIT: SESSION,104,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> +NOTICE: AUDIT: SESSION,104,2,DDL,ALTER TABLE,,,ALTER TABLE fk_fk ADD CONSTRAINT fk_fk_id_fkey FOREIGN KEY (id) REFERENCES fk_pk(id),<none> +ALTER TABLE fk_fk ADD CONSTRAINT fk_fk_id_fkey_nv FOREIGN KEY (id) REFERENCES fk_pk(id) NOT VALID; +NOTICE: AUDIT: SESSION,105,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> +ALTER TABLE fk_fk VALIDATE CONSTRAINT fk_fk_id_fkey_nv; +NOTICE: AUDIT: SESSION,106,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> +NOTICE: AUDIT: SESSION,106,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> +NOTICE: AUDIT: SESSION,106,2,DDL,ALTER TABLE,,,ALTER TABLE fk_fk VALIDATE CONSTRAINT fk_fk_id_fkey_nv,<none> +DROP TABLE fk_fk; +NOTICE: AUDIT: SESSION,107,1,DDL,DROP TABLE,,,DROP TABLE fk_fk,<none> +DROP TABLE fk_pk; +NOTICE: AUDIT: SESSION,108,1,DDL,DROP TABLE,,,DROP TABLE fk_pk,<none> +-- -- Test rows retrived or affected by statements \connect - :current_user SET pgaudit.log = 'all'; diff --git a/pgaudit.c b/pgaudit.c index a7960f0..d598a62 100644 --- a/pgaudit.c +++ b/pgaudit.c @@ -1578,8 +1578,17 @@ pgaudit_ExecutorCheckPerms_hook(List *rangeTabls, * auditing via pgaudit.role might apply. This short-circuits the role * lookup and per-statement DML processing for configurations such as * pgaudit.log = 'ddl, role'. + * + * Also skip checks that are not enforcing. A caller passing + * ereport_on_violation false is asking whether it may proceed, not + * executing a statement against the relations, so there is nothing to + * audit. RI_Initial_Check() does this to decide whether it can validate a + * foreign key with a single query, and it runs while the utility command's + * own stack item is on top of the stack, so auditing the check would + * attribute the referenced relations to that command. The validation query + * it goes on to run is audited normally. */ - if (audit_executor_enabled() && + if (audit_executor_enabled() && ereport_on_violation && !IsAbortedTransactionBlockState() && !IsParallelWorker()) { /* Get the audit oid if the role exists */ diff --git a/sql/pgaudit.sql b/sql/pgaudit.sql index 62d613c..673cf89 100644 --- a/sql/pgaudit.sql +++ b/sql/pgaudit.sql @@ -978,6 +978,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; + -- -- Test rows retrived or affected by statements \connect - :current_user
^ permalink raw [nested|flat] 6+ messages in thread

* Re: [pgaudit/pgaudit] PR #328: Do not audit permission checks that are not enforcing.
@ 2026-07-28 03:01 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  4 siblings, 0 replies; 6+ messages in thread

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

@dwsteele assigned @dwsteele.

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

* Re: [pgaudit/pgaudit] PR #328: Do not audit permission checks that are not enforcing.
@ 2026-07-28 03:01 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  4 siblings, 0 replies; 6+ messages in thread

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

@dwsteele applied label "bug".

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

* Re: [pgaudit/pgaudit] PR #328 (approved): Do not audit permission checks that are not enforcing.
@ 2026-07-28 15:05 ` "pgguru (@pgguru)" <noreply+pgguru@github.com>
  4 siblings, 0 replies; 6+ messages in thread

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



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

* Re: [pgaudit/pgaudit] PR #328: Do not audit permission checks that are not enforcing.
@ 2026-07-29 01:47 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  4 siblings, 0 replies; 6+ messages in thread

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

@dwsteele closed this.

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

* Re: [pgaudit/pgaudit] PR #328: Do not audit permission checks that are not enforcing.
@ 2026-07-29 01:47 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  4 siblings, 0 replies; 6+ messages in thread

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

@dwsteele merged this (commit de003230440a).

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


end of thread, other threads:[~2026-07-29 01:47 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-28 03:01 [pgaudit/pgaudit] PR #328: Do not audit permission checks that are not enforcing. "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-28 03:01 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-28 03:01 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-28 15:05 ` Re: [pgaudit/pgaudit] PR #328 (approved): Do not audit permission checks that are not enforcing. "pgguru (@pgguru)" <noreply+pgguru@github.com>
2026-07-29 01:47 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-29 01:47 ` "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