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.patch
diff --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