Fix exclude generated columns in COPY TO with RLS

From: Ayoub Kazar <ayoub(dot)kazar(at)data-bene(dot)io>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Fix exclude generated columns in COPY TO with RLS
Date: 2026-11-04 12:58:01
Message-ID: 1915ae77-392a-4bdf-b3e2-157d63af320c@data-bene.io
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Exclude-generated-columns-in-COPY-TO-with-row-level-security.patch text/x-patch 5.5 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Zhijie Hou 2026-11-04 13:04:42 Avoid using raw xmin of frozen tuples in logicalrep conflict detection
Previous Message Jim Jones 2026-11-04 12:54:58 Re: possible bug - xmlconcat is badly marked as immutable