From: dwsteele (@dwsteele) <noreply+dwsteele@github.com>
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
Subject: [pgaudit/pgaudit] PR #321: Report rows affected by COPY when pgaudit.log_rows is enabled.
Date: Thu, 25 Jun 2026 05:13:35 +0000
Message-ID: <gh-pgaudit-pgaudit-321@github.com> (raw)
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.patchdiff --git a/expected/pgaudit.out b/expected/pgaudit.outindex 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.cindex 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.sqlindex 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;