pg.ddx.io pgaudit/pgaudit GitHub issues and pull requests
help / color / mirror / Atom feed[pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
10+ messages / 2 participants
[nested] [flat]
* [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-06-25 05:13 "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
0 siblings, 0 replies; 10+ messages in thread
From: dwsteele (@dwsteele) @ 2026-06-25 05:13 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
With log_rows on, table-form COPY was logged with zero rows and lost its relation-level detail. COPY permissions are checked via ExecutorCheckPerms, which with log_rows defers the audit entry to ExecutorEnd so the row count can be attached, but table-form COPY runs entirely within DoCopy() and never reaches the executor. The deferred entry was therefore never emitted and only a bare session record (command COPY, no relation, zero rows) survived.
Flush the deferred entry from the ProcessUtility hook once the command completes, using the processed count from the QueryCompletion as the rows affected.
COPY (query) TO runs its inner query through the executor, which already logs a select/dml entry carrying the relation and, via ExecutorEnd, the row count. The COPY statement itself is logged separately by the ProcessUtility hook, but its stack item never has rangeTabls set (permissions are checked against the inner query's stack item, not the COPY's), so it falls outside the flush above and was logged with zero rows. Apply the QueryCompletion count to that entry as well so both forms report the rows affected.
Also report COPY as COPY rather than SELECT or INSERT. log_select_dml() derives the command tag per relation from the required permissions (SELECT for COPY TO, INSERT for COPY FROM) to set the READ/WRITE class, which caused COPY to be logged under those tags. Restore the COPY command tag after the class has been determined so the log reflects the statement that was actually run. This also corrects the tag in the non-log_rows case, so COPY is reported consistently regardless of the setting.
Add tests covering table- and query-form COPY, with and without log_rows.
Reported-by: Daisuke (higuchi-daisuke)
inline patch 1: expected/pgaudit.out, pgaudit.c, sql/pgaudit.sql (+99/-10 lines) download inline-1.patch
diff --git a/expected/pgaudit.out b/expected/pgaudit.out
index eafe356..8570d03 100644
--- a/expected/pgaudit.out
+++ b/expected/pgaudit.out
@@ -781,7 +781,7 @@ NOTICE: AUDIT: SESSION,7,1,DDL,CREATE SCHEMA,SCHEMA,test,CREATE SCHEMA test,<no
--
-- Copy account to stdout
COPY account TO stdout;
-NOTICE: AUDIT: SESSION,8,1,READ,SELECT,TABLE,public.account,COPY account TO stdout,<none>
+NOTICE: AUDIT: SESSION,8,1,READ,COPY,TABLE,public.account,COPY account TO stdout,<none>
1 regress_user1 HASH2 yada, yada
--
-- Create a table from a query
@@ -797,7 +797,7 @@ SELECT *
--
-- Copy from stdin to account copy
COPY test.account_copy from stdin;
-NOTICE: AUDIT: SESSION,10,1,WRITE,INSERT,TABLE,test.account_copy,COPY test.account_copy from stdin,<none>
+NOTICE: AUDIT: SESSION,10,1,WRITE,COPY,TABLE,test.account_copy,COPY test.account_copy from stdin,<none>
--
-- Test prepared statement
PREPARE pgclassstmt (oid) AS
@@ -1412,12 +1412,25 @@ NOTICE: AUDIT: SESSION,93,1,READ,SELECT,TABLE,public.h_0,SELECT * FROM h_0,<non
1 | 1
(1 row)
+COPY h FROM stdin;
+NOTICE: AUDIT: SESSION,94,1,WRITE,COPY,TABLE,public.h,COPY h FROM stdin,<none>
+COPY h TO stdout;
+NOTICE: AUDIT: SESSION,95,1,READ,COPY,TABLE,public.h,COPY h TO stdout,<none>
+1 1
+2 2
+3 3
+COPY (SELECT * FROM h) TO stdout;
+NOTICE: AUDIT: SESSION,96,1,READ,SELECT,TABLE,public.h,COPY (SELECT * FROM h) TO stdout,<none>
+1 1
+2 2
+3 3
+NOTICE: AUDIT: SESSION,96,2,READ,COPY,,,COPY (SELECT * FROM h) TO stdout,<none>
CREATE INDEX h_idx ON h (x);
-NOTICE: AUDIT: SESSION,94,1,DDL,CREATE INDEX,,,CREATE INDEX h_idx ON h (x),<none>
+NOTICE: AUDIT: SESSION,97,1,DDL,CREATE INDEX,,,CREATE INDEX h_idx ON h (x),<none>
DROP INDEX h_idx;
-NOTICE: AUDIT: SESSION,95,1,DDL,DROP INDEX,,,DROP INDEX h_idx,<none>
+NOTICE: AUDIT: SESSION,98,1,DDL,DROP INDEX,,,DROP INDEX h_idx,<none>
DROP TABLE h;
-NOTICE: AUDIT: SESSION,96,1,DDL,DROP TABLE,,,DROP TABLE h,<none>
+NOTICE: AUDIT: SESSION,99,1,DDL,DROP TABLE,,,DROP TABLE h,<none>
--
-- Test rows retrived or affected by statements
\connect - :current_user
@@ -1926,7 +1939,8 @@ COPY account TO stdout;
1 regress_user1 HASH4 yada, yada3
1 regress_user2 HASH4 yada, yada3
1 user3 HASH4 yada, yada3
-NOTICE: AUDIT: SESSION,51,1,READ,COPY,,,COPY account TO stdout,<none>,0
+NOTICE: AUDIT: OBJECT,51,1,READ,COPY,TABLE,public.account,COPY account TO stdout,<none>,3
+NOTICE: AUDIT: SESSION,51,1,READ,COPY,TABLE,public.account,COPY account TO stdout,<none>,3
--
-- Drop a table from a query
DROP TABLE test.account_copy;
@@ -1948,7 +1962,7 @@ SELECT *
--
-- Copy from stdin to account copy
COPY test.account_copy from stdin;
-NOTICE: AUDIT: SESSION,54,1,WRITE,COPY,,,COPY test.account_copy from stdin,<none>,0
+NOTICE: AUDIT: SESSION,54,1,WRITE,COPY,TABLE,test.account_copy,COPY test.account_copy from stdin,<none>,1
--
-- Test prepared statement
PREPARE pgclassstmt1 (oid) AS
@@ -2588,12 +2602,25 @@ NOTICE: AUDIT: SESSION,145,1,READ,SELECT,TABLE,public.h_0,SELECT * FROM h_0,<no
1 | 1
(1 row)
+COPY h FROM stdin;
+NOTICE: AUDIT: SESSION,146,1,WRITE,COPY,TABLE,public.h,COPY h FROM stdin,<none>,2
+COPY h TO stdout;
+1 1
+2 2
+3 3
+NOTICE: AUDIT: SESSION,147,1,READ,COPY,TABLE,public.h,COPY h TO stdout,<none>,3
+COPY (SELECT * FROM h) TO stdout;
+1 1
+2 2
+3 3
+NOTICE: AUDIT: SESSION,148,1,READ,SELECT,TABLE,public.h,COPY (SELECT * FROM h) TO stdout,<none>,3
+NOTICE: AUDIT: SESSION,148,2,READ,COPY,,,COPY (SELECT * FROM h) TO stdout,<none>,3
CREATE INDEX h_idx ON h (x);
-NOTICE: AUDIT: SESSION,146,1,DDL,CREATE INDEX,,,CREATE INDEX h_idx ON h (x),<none>,0
+NOTICE: AUDIT: SESSION,149,1,DDL,CREATE INDEX,,,CREATE INDEX h_idx ON h (x),<none>,0
DROP INDEX h_idx;
-NOTICE: AUDIT: SESSION,147,1,DDL,DROP INDEX,,,DROP INDEX h_idx,<none>,0
+NOTICE: AUDIT: SESSION,150,1,DDL,DROP INDEX,,,DROP INDEX h_idx,<none>,0
DROP TABLE h;
-NOTICE: AUDIT: SESSION,148,1,DDL,DROP TABLE,,,DROP TABLE h,<none>,0
+NOTICE: AUDIT: SESSION,151,1,DDL,DROP TABLE,,,DROP TABLE h,<none>,0
--
-- Change configuration of user 1 so that full statements are not logged
\connect - :current_user
diff --git a/pgaudit.c b/pgaudit.c
index 12d5c29..b1590a0 100644
--- a/pgaudit.c
+++ b/pgaudit.c
@@ -1117,11 +1117,21 @@ log_select_dml(Oid auditOid, List *rangeTabls, List *permInfos)
ListCell *lr;
bool first = true;
bool found = false;
+ bool isCopy;
/* Do not log if this is an internal statement */
if (internalStatement)
return;
+ /*
+ * Table-form COPY is audited through this function (its permissions are
+ * checked via ExecutorCheckPerms) but the command tag is derived per
+ * relation from the required permissions below, which would report COPY as
+ * SELECT or INSERT. Remember that this is a COPY so the command tag can be
+ * restored after the class has been determined.
+ */
+ isCopy = auditEventStack->auditEvent.commandTag == T_CopyStmt;
+
foreach(lr, rangeTabls)
{
Oid relOid;
@@ -1225,6 +1235,18 @@ log_select_dml(Oid auditOid, List *rangeTabls, List *permInfos)
auditEventStack->auditEvent.command = CMDTAG_UNKNOWN;
}
+ /*
+ * Restore the COPY command tag clobbered above. The required
+ * permissions still set the correct READ/WRITE class (COPY TO checks
+ * SELECT, COPY FROM checks INSERT) but the audit log should report the
+ * command as COPY rather than SELECT or INSERT.
+ */
+ if (isCopy)
+ {
+ auditEventStack->auditEvent.commandTag = T_CopyStmt;
+ auditEventStack->auditEvent.command = CMDTAG_COPY;
+ }
+
/* Use the relation type to assign object type */
switch (rte->relkind)
{
@@ -1780,6 +1802,34 @@ pgaudit_ProcessUtility_hook(PlannedStmt *pstmt,
*/
stack_valid(stackId);
+ /*
+ * If row tracking is on and a select/dml audit entry was deferred for
+ * this utility command then log it here. Table-form COPY is checked
+ * via ExecutorCheckPerms (which saves rangeTabls on the stack item)
+ * but is executed entirely within DoCopy() and never runs ExecutorEnd,
+ * so the deferred entry would otherwise be lost. Use the processed
+ * count from the completed command as the rows affected.
+ */
+ if (auditLogRows && stackItem->auditEvent.rangeTabls != NULL)
+ {
+ stackItem->auditEvent.rows = qc ? qc->nprocessed : 0;
+
+ log_select_dml(stackItem->auditEvent.auditOid,
+ stackItem->auditEvent.rangeTabls,
+ stackItem->auditEvent.permInfos);
+ }
+ /*
+ * Query-form COPY (COPY (query) TO) runs its inner query through the
+ * executor, which logs a separate select/dml entry with the row count,
+ * and then the COPY statement itself is logged by log_audit_event()
+ * below. Apply the processed count so that entry also reports the rows
+ * affected instead of zero. (Table-form COPY is handled by the flush
+ * above, which marks the entry logged, so it is skipped here.)
+ */
+ else if (auditLogRows &&
+ stackItem->auditEvent.commandTag == T_CopyStmt)
+ stackItem->auditEvent.rows = qc ? qc->nprocessed : 0;
+
/*
* Log the utility command if logging is on, the command has not
* already been logged by another hook, and the transaction is not
diff --git a/sql/pgaudit.sql b/sql/pgaudit.sql
index a4f2169..62d613c 100644
--- a/sql/pgaudit.sql
+++ b/sql/pgaudit.sql
@@ -968,6 +968,12 @@ CREATE TABLE h_1 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 1);
INSERT INTO h VALUES(1,1);
SELECT * FROM h;
SELECT * FROM h_0;
+COPY h FROM stdin;
+2 2
+3 3
+\.
+COPY h TO stdout;
+COPY (SELECT * FROM h) TO stdout;
CREATE INDEX h_idx ON h (x);
DROP INDEX h_idx;
DROP TABLE h;
@@ -1627,6 +1633,12 @@ CREATE TABLE h_1 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 1);
INSERT INTO h VALUES(1,1);
SELECT * FROM h;
SELECT * FROM h_0;
+COPY h FROM stdin;
+2 2
+3 3
+\.
+COPY h TO stdout;
+COPY (SELECT * FROM h) TO stdout;
CREATE INDEX h_idx ON h (x);
DROP INDEX h_idx;
DROP TABLE h;
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-06-25 05:13 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dwsteele (@dwsteele) @ 2026-06-25 05:13 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
@dwsteele assigned @dwsteele.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-06-25 05:13 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dwsteele (@dwsteele) @ 2026-06-25 05:13 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
@dwsteele applied label "bug".
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-07-02 19:12 ` "pgguru (@pgguru)" <noreply+pgguru@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: pgguru (@pgguru) @ 2026-07-02 19:12 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
How does this operate with partitioned tables?
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-07-03 03:23 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dwsteele (@dwsteele) @ 2026-07-03 03:23 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
> How does this operate with partitioned tables?
It works just like inserts or selects but with COPY as the command. I've added some tests in 840a4c5233b3 to demonstrate this.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-07-20 20:48 ` "pgguru (@pgguru)" <noreply+pgguru@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: pgguru (@pgguru) @ 2026-07-20 20:48 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
So `COPY table TO STDOUT` and `COPY (SELECT * FROM table) TO STDOUT` will both effectively produce the same output? (i.e., `COPY` tag, number of rows, etc.). Or do we expect deviation here?
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-07-21 04:51 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dwsteele (@dwsteele) @ 2026-07-21 04:51 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
The output will be different in terms of audit entries but should be the same in rows reported:
```
COPY h TO stdout;
AUDIT: SESSION,147,1,READ,COPY,TABLE,public.h,COPY h TO stdout,<none>,3
```
vs.
```
COPY (SELECT * FROM h) TO stdout;
AUDIT: SESSION,148,1,READ,SELECT,TABLE,public.h,COPY (SELECT * FROM h) TO stdout,<none>,3
AUDIT: SESSION,148,2,READ,COPY,,,COPY (SELECT * FROM h) TO stdout,<none>,0
```
This case did expose yet another bug, though, which is fixed in ca82d891dea so the corrected output is:
```
COPY (SELECT * FROM h) TO stdout;
AUDIT: SESSION,148,1,READ,SELECT,TABLE,public.h,COPY (SELECT * FROM h) TO stdout,<none>,3
AUDIT: SESSION,148,2,READ,COPY,,,COPY (SELECT * FROM h) TO stdout,<none>,3
```
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgaudit/pgaudit] PR #321 (approved): Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-07-23 14:56 ` "pgguru (@pgguru)" <noreply+pgguru@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: pgguru (@pgguru) @ 2026-07-23 14:56 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-07-27 01:51 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dwsteele (@dwsteele) @ 2026-07-27 01:51 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
@dwsteele closed this.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
@ 2026-07-27 01:51 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dwsteele (@dwsteele) @ 2026-07-27 01:51 UTC (permalink / raw)
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
@dwsteele merged this (commit 9ceed0cd119f).
^ permalink raw [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-07-27 01:51 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-25 05:13 [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled. "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-06-25 05:13 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-06-25 05:13 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-02 19:12 ` "pgguru (@pgguru)" <noreply+pgguru@github.com>
2026-07-03 03:23 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-20 20:48 ` "pgguru (@pgguru)" <noreply+pgguru@github.com>
2026-07-21 04:51 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-23 14:56 ` Re: [pgaudit/pgaudit] PR #321 (approved): Report rows affected by COPY when pgaudit.log_rows is enabled. "pgguru (@pgguru)" <noreply+pgguru@github.com>
2026-07-27 01:51 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-27 01:51 ` "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