pg.ddx.io  pgaudit/pgaudit GitHub issues and pull requests  
help / color / mirror / Atom feed
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.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.

view thread (9+ messages)  latest in thread

Message-ID: <gh-pgaudit-pgaudit-323@github.com>
Permalink:  ../gh-pgaudit-pgaudit-323@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