Fix exclude generated columns in COPY TO with RLS
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]https://www.postgresql.org/docs/devel/sql-copy.html#SQL-COPY-PARAMS-COLUMN-NAME 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
https://www.postgresql.org/docs/devel/sql-copy.html#SQL-COPY-PARAMS-COLUMN-NAME
Hi Ayoub
On 04/11/2026 13:58, Ayoub Kazar wrote:
The attached patch uses the existing attnums to build targetlist based
on it, in the case of RLS.
I tested the patch and it solves the reported issue. It also changes how
COPY TO handles columns that the user has no select privileges on,but
I'd argue this is an improvement:
create user u login;
create table t(a int, b int generated always as (a+40) stored, c text);
insert into t (a,c) values (2,'foo');
grant select (a,c) on t to u;
alter table t enable row level security;
create policy p on t using (true);
== master ==
postgres=# set role u;
SET
postgres=> copy t to stdout;
ERROR: permission denied for table t
== patched ==
postgres=# set role u;
SET
postgres=> copy t to stdout;
2 foo
postgres=>
I guess this test could also be added to the regression tests.
BTW, is there another reason to not allow generated columns in COPY TO
other than being consistent with COPY FROM?
Thanks!
Best, Jim
On 05/11/2026 12:34, Jim Jones wrote:
It also changes how COPY TO handles columns that the user has no select privileges on,but
I'd argue this is an improvement:create user u login;
create table t(a int, b int generated always as (a+40) stored, c text);
insert into t (a,c) values (2,'foo');
grant select (a,c) on t to u;
alter table t enable row level security;
create policy p on t using (true);== master ==
postgres=# set role u;
SET
postgres=> copy t to stdout;
ERROR: permission denied for table t== patched ==
postgres=# set role u;
SET
postgres=> copy t to stdout;
2 foo
postgres=>I guess this test could also be added to the regression tests.
You're right! Done in v2.
BTW, is there another reason to not allow generated columns in COPY TO
other than being consistent with COPY FROM?
I don't see a reason other than having a consistent behavior for a round
trip of COPY TO/FROM (It makes sense though...)
At least for users who insist on emitting them in output, they can still
do something like COPY (SELECT * FROM t) TO ...
Thanks for the review Jim.
Regards,
Ayoub Kazar