Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit814b9e9

Browse files
committed
Fix RLS with COPY (col1, col2) FROM tab
Attempting to COPY a subset of columns from a table with RLS enabledwould fail due to an invalid query being constructed (using a singleColumnRef with the list of fields to exact in 'fields', but that's forthe different levels of an indirection for a single column, not forspecifying multiple columns).Correct by building a ColumnRef and then RestTarget for each columnbeing requested and then adding those to the targetList for the selectquery. Include regression tests to hopefully catch if this is brokenagain in the future.Patch-By: Adam BrightwellReviewed-By: Michael Paquier
1 parent83c2492 commit814b9e9

File tree

3 files changed

+192
-12
lines changed

3 files changed

+192
-12
lines changed

‎src/backend/commands/copy.c

Lines changed: 51 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -871,28 +871,67 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, uint64 *processed)
871871
ColumnRef*cr;
872872
ResTarget*target;
873873
RangeVar*from;
874+
List*targetList=NIL;
874875

875876
if (is_from)
876877
ereport(ERROR,
877878
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
878879
errmsg("COPY FROM not supported with row-level security"),
879880
errhint("Use INSERT statements instead.")));
880881

881-
/* Build target list */
882-
cr=makeNode(ColumnRef);
883-
882+
/*
883+
* Build target list
884+
*
885+
* If no columns are specified in the attribute list of the COPY
886+
* command, then the target list is 'all' columns. Therefore, '*'
887+
* should be used as the target list for the resulting SELECT
888+
* statement.
889+
*
890+
* In the case that columns are specified in the attribute list,
891+
* create a ColumnRef and ResTarget for each column and add them to
892+
* the target list for the resulting SELECT statement.
893+
*/
884894
if (!stmt->attlist)
895+
{
896+
cr=makeNode(ColumnRef);
885897
cr->fields=list_make1(makeNode(A_Star));
886-
else
887-
cr->fields=stmt->attlist;
898+
cr->location=-1;
899+
900+
target=makeNode(ResTarget);
901+
target->name=NULL;
902+
target->indirection=NIL;
903+
target->val= (Node*)cr;
904+
target->location=-1;
888905

889-
cr->location=1;
906+
targetList=list_make1(target);
907+
}
908+
else
909+
{
910+
ListCell*lc;
890911

891-
target=makeNode(ResTarget);
892-
target->name=NULL;
893-
target->indirection=NIL;
894-
target->val= (Node*)cr;
895-
target->location=1;
912+
foreach(lc,stmt->attlist)
913+
{
914+
/*
915+
* Build the ColumnRef for each column. The ColumnRef
916+
* 'fields' property is a String 'Value' node (see
917+
* nodes/value.h) that corresponds to the column name
918+
* respectively.
919+
*/
920+
cr=makeNode(ColumnRef);
921+
cr->fields=list_make1(lfirst(lc));
922+
cr->location=-1;
923+
924+
/* Build the ResTarget and add the ColumnRef to it. */
925+
target=makeNode(ResTarget);
926+
target->name=NULL;
927+
target->indirection=NIL;
928+
target->val= (Node*)cr;
929+
target->location=-1;
930+
931+
/* Add each column to the SELECT statement's target list */
932+
targetList=lappend(targetList,target);
933+
}
934+
}
896935

897936
/*
898937
* Build RangeVar for from clause, fully qualified based on the
@@ -903,7 +942,7 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, uint64 *processed)
903942

904943
/* Build query */
905944
select=makeNode(SelectStmt);
906-
select->targetList=list_make1(target);
945+
select->targetList=targetList;
907946
select->fromClause=list_make1(from);
908947

909948
query= (Node*)select;

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

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -460,9 +460,87 @@ select * from check_con_tbl;
460460

461461
(2 rows)
462462

463+
-- test with RLS enabled.
464+
CREATE ROLE regress_rls_copy_user;
465+
CREATE ROLE regress_rls_copy_user_colperms;
466+
CREATE TABLE rls_t1 (a int, b int, c int);
467+
COPY rls_t1 (a, b, c) from stdin;
468+
CREATE POLICY p1 ON rls_t1 FOR SELECT USING (a % 2 = 0);
469+
ALTER TABLE rls_t1 ENABLE ROW LEVEL SECURITY;
470+
ALTER TABLE rls_t1 FORCE ROW LEVEL SECURITY;
471+
GRANT SELECT ON TABLE rls_t1 TO regress_rls_copy_user;
472+
GRANT SELECT (a, b) ON TABLE rls_t1 TO regress_rls_copy_user_colperms;
473+
-- all columns
474+
COPY rls_t1 TO stdout;
475+
141
476+
232
477+
323
478+
414
479+
COPY rls_t1 (a, b, c) TO stdout;
480+
141
481+
232
482+
323
483+
414
484+
-- subset of columns
485+
COPY rls_t1 (a) TO stdout;
486+
1
487+
2
488+
3
489+
4
490+
COPY rls_t1 (a, b) TO stdout;
491+
14
492+
23
493+
32
494+
41
495+
-- column reordering
496+
COPY rls_t1 (b, a) TO stdout;
497+
41
498+
32
499+
23
500+
14
501+
SET SESSION AUTHORIZATION regress_rls_copy_user;
502+
-- all columns
503+
COPY rls_t1 TO stdout;
504+
232
505+
414
506+
COPY rls_t1 (a, b, c) TO stdout;
507+
232
508+
414
509+
-- subset of columns
510+
COPY rls_t1 (a) TO stdout;
511+
2
512+
4
513+
COPY rls_t1 (a, b) TO stdout;
514+
23
515+
41
516+
-- column reordering
517+
COPY rls_t1 (b, a) TO stdout;
518+
32
519+
14
520+
RESET SESSION AUTHORIZATION;
521+
SET SESSION AUTHORIZATION regress_rls_copy_user_colperms;
522+
-- attempt all columns (should fail)
523+
COPY rls_t1 TO stdout;
524+
ERROR: permission denied for relation rls_t1
525+
COPY rls_t1 (a, b, c) TO stdout;
526+
ERROR: permission denied for relation rls_t1
527+
-- try to copy column with no privileges (should fail)
528+
COPY rls_t1 (c) TO stdout;
529+
ERROR: permission denied for relation rls_t1
530+
-- subset of columns (should succeed)
531+
COPY rls_t1 (a) TO stdout;
532+
2
533+
4
534+
COPY rls_t1 (a, b) TO stdout;
535+
23
536+
41
537+
RESET SESSION AUTHORIZATION;
463538
DROP TABLE forcetest;
464539
DROP TABLE vistest;
465540
DROP FUNCTION truncate_in_subxact();
466541
DROP TABLE x, y;
542+
DROP TABLE rls_t1 CASCADE;
543+
DROP ROLE regress_rls_copy_user;
544+
DROP ROLE regress_rls_copy_user_colperms;
467545
DROP FUNCTION fn_x_before();
468546
DROP FUNCTION fn_x_after();

‎src/test/regress/sql/copy2.sql

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -327,9 +327,72 @@ copy check_con_tbl from stdin;
327327
\.
328328
select*from check_con_tbl;
329329

330+
-- test with RLS enabled.
331+
CREATE ROLE regress_rls_copy_user;
332+
CREATE ROLE regress_rls_copy_user_colperms;
333+
CREATETABLErls_t1 (aint, bint, cint);
334+
335+
COPY rls_t1 (a, b, c)from stdin;
336+
141
337+
232
338+
323
339+
414
340+
\.
341+
342+
CREATE POLICY p1ON rls_t1 FORSELECT USING (a %2=0);
343+
ALTERTABLE rls_t1 ENABLE ROW LEVEL SECURITY;
344+
ALTERTABLE rls_t1 FORCE ROW LEVEL SECURITY;
345+
346+
GRANTSELECTON TABLE rls_t1 TO regress_rls_copy_user;
347+
GRANTSELECT (a, b)ON TABLE rls_t1 TO regress_rls_copy_user_colperms;
348+
349+
-- all columns
350+
COPY rls_t1 TO stdout;
351+
COPY rls_t1 (a, b, c) TO stdout;
352+
353+
-- subset of columns
354+
COPY rls_t1 (a) TO stdout;
355+
COPY rls_t1 (a, b) TO stdout;
356+
357+
-- column reordering
358+
COPY rls_t1 (b, a) TO stdout;
359+
360+
SET SESSION AUTHORIZATION regress_rls_copy_user;
361+
362+
-- all columns
363+
COPY rls_t1 TO stdout;
364+
COPY rls_t1 (a, b, c) TO stdout;
365+
366+
-- subset of columns
367+
COPY rls_t1 (a) TO stdout;
368+
COPY rls_t1 (a, b) TO stdout;
369+
370+
-- column reordering
371+
COPY rls_t1 (b, a) TO stdout;
372+
373+
RESET SESSION AUTHORIZATION;
374+
375+
SET SESSION AUTHORIZATION regress_rls_copy_user_colperms;
376+
377+
-- attempt all columns (should fail)
378+
COPY rls_t1 TO stdout;
379+
COPY rls_t1 (a, b, c) TO stdout;
380+
381+
-- try to copy column with no privileges (should fail)
382+
COPY rls_t1 (c) TO stdout;
383+
384+
-- subset of columns (should succeed)
385+
COPY rls_t1 (a) TO stdout;
386+
COPY rls_t1 (a, b) TO stdout;
387+
388+
RESET SESSION AUTHORIZATION;
389+
330390
DROPTABLE forcetest;
331391
DROPTABLE vistest;
332392
DROPFUNCTION truncate_in_subxact();
333393
DROPTABLE x, y;
394+
DROPTABLE rls_t1 CASCADE;
395+
DROP ROLE regress_rls_copy_user;
396+
DROP ROLE regress_rls_copy_user_colperms;
334397
DROPFUNCTION fn_x_before();
335398
DROPFUNCTION fn_x_after();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp