| 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 |
| 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 |