From f4583fdf24522cbd4212f76fdcb1584b80151ebc Mon Sep 17 00:00:00 2001 From: AyoubKAZ Date: Wed, 4 Nov 2026 10:50:47 +0100 Subject: [PATCH] Exclude generated columns in COPY TO with row-level security When COPY TO is executed on a relation with row-level security (RLS) active and without an explicit column list, DoCopy() rewrote the statement into an internal SELECT query using an A_Star target list ('*'). Because SELECT * expands all non-dropped columns including generated columns, the resulting COPY output unexpectedly included generated columns. This violated the documented behavior of COPY ("If no column list is specified, all columns of the table except generated columns will be copied"). Furthermore this breaks COPY TO / COPY FROM round-tripping because COPY FROM does not accept generated columns. Fix by building the SELECT statement's target list from the attnums list already computed by CopyGetAttnums() rather than using A_Star, so generated columns are excluded under RLS. --- src/backend/commands/copy.c | 31 ++++++++++++++--------- src/test/regress/expected/rowsecurity.out | 23 +++++++++++++++++ src/test/regress/sql/rowsecurity.sql | 22 ++++++++++++++++ 3 files changed, 64 insertions(+), 12 deletions(-) diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 95b58cd2c84..114249a1eff 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -259,9 +259,8 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, * Build target list * * If no columns are specified in the attribute list of the COPY - * command, then the target list is 'all' columns. Therefore, '*' - * should be used as the target list for the resulting SELECT - * statement. + * command, build an explicit target list from attnums (which + * excludes dropped and generated columns). * * In the case that columns are specified in the attribute list, * create a ColumnRef and ResTarget for each column and add them @@ -269,17 +268,25 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, */ if (!stmt->attlist) { - cr = makeNode(ColumnRef); - cr->fields = list_make1(makeNode(A_Star)); - cr->location = -1; + ListCell *lc; - target = makeNode(ResTarget); - target->name = NULL; - target->indirection = NIL; - target->val = (Node *) cr; - target->location = -1; + foreach(lc, attnums) + { + int attno = lfirst_int(lc); + Form_pg_attribute attr = TupleDescAttr(tupDesc, attno - 1); - targetList = list_make1(target); + cr = makeNode(ColumnRef); + cr->fields = list_make1(makeString(pstrdup(NameStr(attr->attname)))); + cr->location = -1; + + target = makeNode(ResTarget); + target->name = NULL; + target->indirection = NIL; + target->val = (Node *) cr; + target->location = -1; + + targetList = lappend(targetList, target); + } } else { diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 72d76405639..35a3990a824 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -4203,6 +4203,29 @@ RESET SESSION AUTHORIZATION; DROP TABLE copy_t; DROP TABLE copy_rel_to CASCADE; NOTICE: drop cascades to table copy_rel_to_child +-- Check COPY TO with RLS and generated columns +CREATE TABLE copy_rel_to_gen (a int, b int GENERATED ALWAYS AS (a * 2) STORED, c text); +CREATE POLICY p1 ON copy_rel_to_gen USING (a % 2 = 0); +ALTER TABLE copy_rel_to_gen ENABLE ROW LEVEL SECURITY; +GRANT ALL ON copy_rel_to_gen TO regress_rls_bob; +INSERT INTO copy_rel_to_gen (a, c) VALUES (1, 'one'), (2, 'two'); +-- Check COPY TO as Superuser/owner. +RESET SESSION AUTHORIZATION; +SET row_security TO OFF; +COPY copy_rel_to_gen TO STDOUT WITH DELIMITER ','; +1,one +2,two +SET row_security TO ON; +COPY copy_rel_to_gen TO STDOUT WITH DELIMITER ','; +1,one +2,two +-- Check COPY TO as user with permissions and RLS. +SET SESSION AUTHORIZATION regress_rls_bob; +SET row_security TO ON; +COPY copy_rel_to_gen TO STDOUT WITH DELIMITER ','; +2,two +RESET SESSION AUTHORIZATION; +DROP TABLE copy_rel_to_gen; -- Check WHERE CURRENT OF SET SESSION AUTHORIZATION regress_rls_alice; CREATE TABLE current_check (currentid int, payload text, rlsuser text); diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 2bdb0c70c5c..01271ed8d06 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -1883,6 +1883,28 @@ RESET SESSION AUTHORIZATION; DROP TABLE copy_t; DROP TABLE copy_rel_to CASCADE; +-- Check COPY TO with RLS and generated columns +CREATE TABLE copy_rel_to_gen (a int, b int GENERATED ALWAYS AS (a * 2) STORED, c text); +CREATE POLICY p1 ON copy_rel_to_gen USING (a % 2 = 0); +ALTER TABLE copy_rel_to_gen ENABLE ROW LEVEL SECURITY; +GRANT ALL ON copy_rel_to_gen TO regress_rls_bob; +INSERT INTO copy_rel_to_gen (a, c) VALUES (1, 'one'), (2, 'two'); + +-- Check COPY TO as Superuser/owner. +RESET SESSION AUTHORIZATION; +SET row_security TO OFF; +COPY copy_rel_to_gen TO STDOUT WITH DELIMITER ','; +SET row_security TO ON; +COPY copy_rel_to_gen TO STDOUT WITH DELIMITER ','; + +-- Check COPY TO as user with permissions and RLS. +SET SESSION AUTHORIZATION regress_rls_bob; +SET row_security TO ON; +COPY copy_rel_to_gen TO STDOUT WITH DELIMITER ','; + +RESET SESSION AUTHORIZATION; +DROP TABLE copy_rel_to_gen; + -- Check WHERE CURRENT OF SET SESSION AUTHORIZATION regress_rls_alice; -- 2.34.1