Hi,
I noticed an issue where COPY TO unexpectedly exports generated columns
when row-level security (RLS) is active on the table.
CREATE ROLE rlsu LOGIN;
CREATE TABLE rlst (a int, b int GENERATED ALWAYS AS (a*2) STORED, c text);
INSERT INTO rlst(a, c) VALUES (1, 'x'), (2, 'y');
GRANT SELECT ON rlst TO rlsu;
ALTER TABLE rlst ENABLE ROW LEVEL SECURITY;
CREATE POLICY p ON rlst FOR SELECT USING (a > 0);
-- Superuser / RLS bypassed:
COPY rlst TO STDOUT;
1 x
2 y
-- RLS user:
SET ROLE rlsu;
COPY rlst TO STDOUT;
1 2 x
2 4 y
The documentation [1] says: "If no column list is specified, all columns
of the table except generated columns will be copied."
Because COPY FROM does not accept generated columns, COPYing a table
produced under COPY TO with RLS fails.
Comment from CopyGetAttnums:
"* We don't include generated columns in the generated full list and we
don't
* allow them to be specified explicitly. They don't make sense for COPY
* FROM, but we could possibly allow them for COPY TO. But this way
it's at
* least ensured that whatever we copy out can be copied back in."
In DoCopy(), the non-RLS path uses CopyGetAttnums(), which explicitly
filters out dropped and generated columns.
However, when RLS is active, it rewrites the statement into a
SelectStmt. When no explicit column
list was specified, it builds an A_Star target list. And that expands
all non-dropped columns including generated
columns.
The attached patch uses the existing attnums to build targetlist based
on it, in the case of RLS.
Thoughts?
Regards,
Ayoub Kazar
[1]:
https://www.postgresql.org/docs/devel/sql-copy.html#SQL-COPY-PARAMS-COLUMN-NAME
From f4583fdf24522cbd4212f76fdcb1584b80151ebc Mon Sep 17 00:00:00 2001
From: AyoubKAZ <[email protected]>
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
