From: dwsteele (@dwsteele) <noreply+dwsteele@github.com>
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
Subject: [pgaudit/pgaudit] PR #323: Do not log inserted or updated columns against the SELECT grant.
Date: Fri, 26 Jun 2026 03:42:11 +0000
Message-ID: <gh-pgaudit-pgaudit-323@github.com> (raw)
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.outindex 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.cindex 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.sqlindex 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.