pg.ddx.io  pgaudit/pgaudit GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
9+ messages / 2 participants
[nested] [flat]

* [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
@ 2026-06-26 03:42  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  0 siblings, 0 replies; 9+ messages in thread

From: dwsteele (@dwsteele) @ 2026-06-26 03:42 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

log_select_dml() builds auditPerms as the union of all permissions the statement requires, which for INSERT ... RETURNING or UPDATE ... RETURNING includes ACL_SELECT alongside ACL_INSERT/ACL_UPDATE. The inserted- and updated-column checks passed this combined mask to audit_on_any_attribute(), so a column-level SELECT grant matched a column that was only written (inserted or updated) but never read. This produced a spurious audit record, e.g. GRANT SELECT (col2) caused INSERT INTO t (col1, col2) VALUES (...) RETURNING col1 to be logged even though col2 is not returned.

Check insertedCols against ACL_INSERT and updatedCols against ACL_UPDATE so each column set is tested only for the access it actually represents. The select-column check already used ACL_SELECT and is unchanged.

Add regression coverage with INSERT/UPDATE ... RETURNING cases that exercise both the written-but-not-returned (not logged) and returned (logged) paths.

Reported-by: @fabiobaiao
Reviewed-by: @pgguru

inline patch 1: expected/pgaudit.out, pgaudit.c, sql/pgaudit.sql (+159/-2 lines) download inline-1.patchdiff --git a/expected/pgaudit.out b/expected/pgaudit.out index 117a3a0..43ee0bb 100644 --- a/expected/pgaudit.out +++ b/expected/pgaudit.out @@ -383,6 +383,103 @@ WARNING: AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.test4,"UPDATE public.test4 update public.test4 set name = 'foo' where name = 'bar'; WARNING: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.test4,update public.test4 set name = 'foo' where name = 'bar',<not logged> -- +-- Create test5 to check that a SELECT grant on a column is not matched +-- against columns that are only inserted or updated (not read). +-- See https://github.com/pgaudit/pgaudit/issues/205. +CREATE TABLE test5 +( + col1 int, + col2 text +); +GRANT SELECT (col2) + ON TABLE public.test5 + TO regress_auditor; +-- +-- Not object logged: col2 is inserted but not returned, so the select (col2) +-- grant must not match the inserted (non-read) column. +INSERT INTO public.test5 (col1, col2) + VALUES (1, 'bar') + RETURNING col1; + col1 +------ + 1 +(1 row) + +-- +-- Object logged because of: +-- select (col2) on test5 (col2 is read via RETURNING) +INSERT INTO public.test5 (col1, col2) + VALUES (1, 'bar') + RETURNING col2; +WARNING: AUDIT: OBJECT,5,1,WRITE,INSERT,TABLE,public.test5,"INSERT INTO public.test5 (col1, col2) + VALUES (1, 'bar') + RETURNING col2",<not logged> + col2 +------ + bar +(1 row) + +-- +-- Not object logged: col2 is updated but not returned, so the select (col2) +-- grant must not match the updated (non-read) column. +UPDATE public.test5 + SET col2 = 'baz' + RETURNING col1; + col1 +------ + 1 + 1 +(2 rows) + +-- +-- Object logged because of: +-- select (col2) on test5 (col2 is read via RETURNING) +UPDATE public.test5 + SET col1 = 2 + RETURNING col2; +WARNING: AUDIT: OBJECT,6,1,WRITE,UPDATE,TABLE,public.test5,"UPDATE public.test5 + SET col1 = 2 + RETURNING col2",<not logged> + col2 +------ + baz + baz +(2 rows) + +-- +-- Object logged because of: +-- select (col2) on test5 (col2 is read via RETURNING even though it is also the +-- updated column; the update alone must not cause logging) +UPDATE public.test5 + SET col2 = 'baz' + RETURNING col2; +WARNING: AUDIT: OBJECT,7,1,WRITE,UPDATE,TABLE,public.test5,"UPDATE public.test5 + SET col2 = 'baz' + RETURNING col2",<not logged> + col2 +------ + baz + baz +(2 rows) + +-- +-- Object logged because of: +-- select (col2) on test5 (col2 is read via the RETURNING OLD/NEW references +-- added in PostgreSQL 18, which count as reads like a plain column reference) +UPDATE public.test5 + SET col1 = 3 + RETURNING old.col2, new.col2; +WARNING: AUDIT: OBJECT,8,1,WRITE,UPDATE,TABLE,public.test5,"UPDATE public.test5 + SET col1 = 3 + RETURNING old.col2, new.col2",<not logged> + col2 | col2 +------+------ + baz | baz + baz | baz +(2 rows) + +DROP TABLE test5; +-- -- Confirm that "long" parameter values will not be logged if pgaudit.log_parameter_max_size -- is set. \connect - :current_user diff --git a/pgaudit.c b/pgaudit.c index 1cab0fa..0086883 100644 --- a/pgaudit.c +++ b/pgaudit.c @@ -1303,7 +1303,7 @@ log_select_dml(Oid auditOid, List *rangeTabls, List *permInfos) auditEventStack->auditEvent.granted = audit_on_any_attribute(relOid, auditOid, perminfo->insertedCols, - auditPerms); + ACL_INSERT); /* * Check the update columns @@ -1313,7 +1313,7 @@ log_select_dml(Oid auditOid, List *rangeTabls, List *permInfos) auditEventStack->auditEvent.granted = audit_on_any_attribute(relOid, auditOid, perminfo->updatedCols, - auditPerms); + ACL_UPDATE); } /* diff --git a/sql/pgaudit.sql b/sql/pgaudit.sql index 645a5a3..18d7c22 100644 --- a/sql/pgaudit.sql +++ b/sql/pgaudit.sql @@ -300,6 +300,66 @@ UPDATE public.test4 -- update (name) takes precedence over select (name) due to ordering update public.test4 set name = 'foo' where name = 'bar'; +-- +-- Create test5 to check that a SELECT grant on a column is not matched +-- against columns that are only inserted or updated (not read). +-- See https://github.com/pgaudit/pgaudit/issues/205. +CREATE TABLE test5 +( + col1 int, + col2 text +); + +GRANT SELECT (col2) + ON TABLE public.test5 + TO regress_auditor; + +-- +-- Not object logged: col2 is inserted but not returned, so the select (col2) +-- grant must not match the inserted (non-read) column. +INSERT INTO public.test5 (col1, col2) + VALUES (1, 'bar') + RETURNING col1; + +-- +-- Object logged because of: +-- select (col2) on test5 (col2 is read via RETURNING) +INSERT INTO public.test5 (col1, col2) + VALUES (1, 'bar') + RETURNING col2; + +-- +-- Not object logged: col2 is updated but not returned, so the select (col2) +-- grant must not match the updated (non-read) column. +UPDATE public.test5 + SET col2 = 'baz' + RETURNING col1; + +-- +-- Object logged because of: +-- select (col2) on test5 (col2 is read via RETURNING) +UPDATE public.test5 + SET col1 = 2 + RETURNING col2; + +-- +-- Object logged because of: +-- select (col2) on test5 (col2 is read via RETURNING even though it is also the +-- updated column; the update alone must not cause logging) +UPDATE public.test5 + SET col2 = 'baz' + RETURNING col2; + +-- +-- Object logged because of: +-- select (col2) on test5 (col2 is read via the RETURNING OLD/NEW references +-- added in PostgreSQL 18, which count as reads like a plain column reference) +UPDATE public.test5 + SET col1 = 3 + RETURNING old.col2, new.col2; + +DROP TABLE test5; + -- -- Confirm that "long" parameter values will not be logged if pgaudit.log_parameter_max_size -- is set.
^ permalink raw [nested|flat] 9+ messages in thread

* Re: [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
@ 2026-06-26 03:42  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: dwsteele (@dwsteele) @ 2026-06-26 03:42 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

@dwsteele assigned @dwsteele.

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

* Re: [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
@ 2026-06-26 03:42  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: dwsteele (@dwsteele) @ 2026-06-26 03:42 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

@dwsteele applied label "bug".

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

* Re: [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
@ 2026-07-02 19:22  "pgguru (@pgguru)" <noreply+pgguru@github.com>
  7 siblings, 0 replies; 9+ messages in thread

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

(on sql/pgaudit.sql:342)

Are non-matching columns here significant?  i.e., would `SET col2 = ... returning col2` behave the same way?  Also do any OLD/NEW references matter here, or am I thinking of a trigger-only behavior?

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

* Re: [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
@ 2026-07-03 02:58  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  7 siblings, 0 replies; 9+ messages in thread

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

(on sql/pgaudit.sql:342)

> Are non-matching columns here significant?

Yes. We are trying to show that *only* the returning clause generates an audit record. 

> i.e., would SET col2 = ... returning col2 behave the same way?

It would, since the part triggering the audit is the returning clause. I added a test in 46255392ffa to demonstrate this.

> Also do any OLD/NEW references matter here, or am I thinking of a trigger-only behavior?

They are not significant here since we are only examining the column ACLs. The actual values are not relevant. I added a test in 46255392ffa to prove this.

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

* Re: [pgaudit/pgaudit] PR #323 (approved): Do not log inserted or updated columns against the SELECT grant.
@ 2026-07-20 14:38  "pgguru (@pgguru)" <noreply+pgguru@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: pgguru (@pgguru) @ 2026-07-20 14:38 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>



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

* Re: [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
@ 2026-07-21 01:37  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  7 siblings, 0 replies; 9+ messages in thread

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

@dwsteele merged this (commit 2e6ae7464019).

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

* Re: [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
@ 2026-07-21 01:37  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  7 siblings, 0 replies; 9+ messages in thread

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

@dwsteele closed this.

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

* Re: [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
@ 2026-07-21 02:49  "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  7 siblings, 0 replies; 9+ messages in thread

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

Amended in 3520a696777 to fix a comment.

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


end of thread, other threads:[~2026-07-21 02:49 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-26 03:42 [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant. "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-06-26 03:42 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-06-26 03:42 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-02 19:22 ` "pgguru (@pgguru)" <noreply+pgguru@github.com>
2026-07-03 02:58 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-20 14:38 ` Re: [pgaudit/pgaudit] PR #323 (approved): Do not log inserted or updated columns against the SELECT grant. "pgguru (@pgguru)" <noreply+pgguru@github.com>
2026-07-21 01:37 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-21 01:37 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-21 02:49 ` "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