Skip to content

Commit 53a53ea

Browse files
tglsfdctglsfdc
committed
Ensure COPY TO on an RLS-enabled table copies no more than it should.
The COPY documentation is quite clear that "COPY relation TO" copies rows from only the named table, not any inheritance children it may have. However, if you enabled row-level security on the table then this stopped being true, because the code forgot to apply the ONLY modifier in the "SELECT ... FROM relation" query that it constructs in order to allow RLS predicates to be attached. Fix that. Report and patch by Antonin Houska (comment adjustments and test case by me). Back-patch to all supported branches. Discussion: https://postgr.es/m/3472.1675251957@antos
1 parent d811d74 commit 53a53ea

File tree

4 files changed

+77
-3
lines changed

4 files changed

+77
-3
lines changed

‎src/backend/commands/copy.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -244,11 +244,14 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
244244

245245
/*
246246
* Build RangeVar for from clause, fully qualified based on the
247-
* relation which we have opened and locked.
247+
* relation which we have opened and locked. Use "ONLY" so that
248+
* COPY retrieves rows from only the target table not any
249+
* inheritance children, the same as when RLS doesn't apply.
248250
*/
249251
from = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
250252
pstrdup(RelationGetRelationName(rel)),
251253
-1);
254+
from->inh = false; /* apply ONLY */
252255

253256
/* Build query */
254257
select = makeNode(SelectStmt);

‎src/backend/commands/copyto.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -508,8 +508,8 @@ BeginCopyTo(ParseState *pstate,
508508
/*
509509
* With row-level security and a user using "COPY relation TO", we
510510
* have to convert the "COPY relation TO" to a query-based COPY (eg:
511-
* "COPY (SELECT * FROM relation) TO"), to allow the rewriter to add
512-
* in any RLS clauses.
511+
* "COPY (SELECT * FROM ONLY relation) TO"), to allow the rewriter to
512+
* add in any RLS clauses.
513513
*
514514
* When this happens, we are passed in the relid of the originally
515515
* found relation (which we have locked). As the planner will look up

‎src/test/regress/expected/rowsecurity.out

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3255,6 +3255,42 @@ ERROR: permission denied for table copy_rel_to
32553255
SET row_security TO ON;
32563256
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
32573257
ERROR: permission denied for table copy_rel_to
3258+
-- Check behavior with a child table.
3259+
RESET SESSION AUTHORIZATION;
3260+
SET row_security TO ON;
3261+
CREATE TABLE copy_rel_to_child () INHERITS (copy_rel_to);
3262+
INSERT INTO copy_rel_to_child VALUES (1, 'one'), (2, 'two');
3263+
-- Check COPY TO as Superuser/owner.
3264+
RESET SESSION AUTHORIZATION;
3265+
SET row_security TO OFF;
3266+
COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3267+
1,c4ca4238a0b923820dcc509a6f75849b
3268+
SET row_security TO ON;
3269+
COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3270+
1,c4ca4238a0b923820dcc509a6f75849b
3271+
-- Check COPY TO as user with permissions.
3272+
SET SESSION AUTHORIZATION regress_rls_bob;
3273+
SET row_security TO OFF;
3274+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3275+
ERROR: query would be affected by row-level security policy for table "copy_rel_to"
3276+
SET row_security TO ON;
3277+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3278+
-- Check COPY TO as user with permissions and BYPASSRLS
3279+
SET SESSION AUTHORIZATION regress_rls_exempt_user;
3280+
SET row_security TO OFF;
3281+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3282+
1,c4ca4238a0b923820dcc509a6f75849b
3283+
SET row_security TO ON;
3284+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3285+
1,c4ca4238a0b923820dcc509a6f75849b
3286+
-- Check COPY TO as user without permissions. SET row_security TO OFF;
3287+
SET SESSION AUTHORIZATION regress_rls_carol;
3288+
SET row_security TO OFF;
3289+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3290+
ERROR: permission denied for table copy_rel_to
3291+
SET row_security TO ON;
3292+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3293+
ERROR: permission denied for table copy_rel_to
32583294
-- Check COPY FROM as Superuser/owner.
32593295
RESET SESSION AUTHORIZATION;
32603296
SET row_security TO OFF;
@@ -3285,6 +3321,7 @@ ERROR: permission denied for table copy_t
32853321
RESET SESSION AUTHORIZATION;
32863322
DROP TABLE copy_t;
32873323
DROP TABLE copy_rel_to CASCADE;
3324+
NOTICE: drop cascades to table copy_rel_to_child
32883325
-- Check WHERE CURRENT OF
32893326
SET SESSION AUTHORIZATION regress_rls_alice;
32903327
CREATE TABLE current_check (currentid int, payload text, rlsuser text);

‎src/test/regress/sql/rowsecurity.sql

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1265,6 +1265,40 @@ COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
12651265
SET row_security TO ON;
12661266
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
12671267

1268+
-- Check behavior with a child table.
1269+
RESET SESSION AUTHORIZATION;
1270+
SET row_security TO ON;
1271+
CREATE TABLE copy_rel_to_child () INHERITS (copy_rel_to);
1272+
INSERT INTO copy_rel_to_child VALUES (1, 'one'), (2, 'two');
1273+
1274+
-- Check COPY TO as Superuser/owner.
1275+
RESET SESSION AUTHORIZATION;
1276+
SET row_security TO OFF;
1277+
COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
1278+
SET row_security TO ON;
1279+
COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
1280+
1281+
-- Check COPY TO as user with permissions.
1282+
SET SESSION AUTHORIZATION regress_rls_bob;
1283+
SET row_security TO OFF;
1284+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
1285+
SET row_security TO ON;
1286+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1287+
1288+
-- Check COPY TO as user with permissions and BYPASSRLS
1289+
SET SESSION AUTHORIZATION regress_rls_exempt_user;
1290+
SET row_security TO OFF;
1291+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1292+
SET row_security TO ON;
1293+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1294+
1295+
-- Check COPY TO as user without permissions. SET row_security TO OFF;
1296+
SET SESSION AUTHORIZATION regress_rls_carol;
1297+
SET row_security TO OFF;
1298+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
1299+
SET row_security TO ON;
1300+
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
1301+
12681302
-- Check COPY FROM as Superuser/owner.
12691303
RESET SESSION AUTHORIZATION;
12701304
SET row_security TO OFF;

0 commit comments

Comments
 (0)