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

Commit87b2ebd

Browse files
committed
Always require SELECT permission for ON CONFLICT DO UPDATE.
The update path of an INSERT ... ON CONFLICT DO UPDATE requires SELECTpermission on the columns of the arbiter index, but it failed to checkfor that in the case of an arbiter specified by constraint name.In addition, for a table with row level security enabled, it failed tocheck updated rows against the table's SELECT policies when the updatepath was taken (regardless of how the arbiter index was specified).Backpatch to 9.5 where ON CONFLICT DO UPDATE and RLS were introduced.Security:CVE-2017-15099
1 parentc66b438 commit87b2ebd

File tree

8 files changed

+194
-11
lines changed

8 files changed

+194
-11
lines changed

‎src/backend/catalog/pg_constraint.c

Lines changed: 98 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -805,6 +805,104 @@ get_relation_constraint_oid(Oid relid, const char *conname, bool missing_ok)
805805
returnconOid;
806806
}
807807

808+
/*
809+
* get_relation_constraint_attnos
810+
*Find a constraint on the specified relation with the specified name
811+
*and return the constrained columns.
812+
*
813+
* Returns a Bitmapset of the column attnos of the constrained columns, with
814+
* attnos being offset by FirstLowInvalidHeapAttributeNumber so that system
815+
* columns can be represented.
816+
*
817+
* *constraintOid is set to the OID of the constraint, or InvalidOid on
818+
* failure.
819+
*/
820+
Bitmapset*
821+
get_relation_constraint_attnos(Oidrelid,constchar*conname,
822+
boolmissing_ok,Oid*constraintOid)
823+
{
824+
Bitmapset*conattnos=NULL;
825+
Relationpg_constraint;
826+
HeapTupletuple;
827+
SysScanDescscan;
828+
ScanKeyDataskey[1];
829+
830+
/* Set *constraintOid, to avoid complaints about uninitialized vars */
831+
*constraintOid=InvalidOid;
832+
833+
/*
834+
* Fetch the constraint tuple from pg_constraint. There may be more than
835+
* one match, because constraints are not required to have unique names;
836+
* if so, error out.
837+
*/
838+
pg_constraint=heap_open(ConstraintRelationId,AccessShareLock);
839+
840+
ScanKeyInit(&skey[0],
841+
Anum_pg_constraint_conrelid,
842+
BTEqualStrategyNumber,F_OIDEQ,
843+
ObjectIdGetDatum(relid));
844+
845+
scan=systable_beginscan(pg_constraint,ConstraintRelidIndexId, true,
846+
NULL,1,skey);
847+
848+
while (HeapTupleIsValid(tuple=systable_getnext(scan)))
849+
{
850+
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tuple);
851+
Datumadatum;
852+
boolisNull;
853+
ArrayType*arr;
854+
int16*attnums;
855+
intnumcols;
856+
inti;
857+
858+
/* Check the constraint name */
859+
if (strcmp(NameStr(con->conname),conname)!=0)
860+
continue;
861+
if (OidIsValid(*constraintOid))
862+
ereport(ERROR,
863+
(errcode(ERRCODE_DUPLICATE_OBJECT),
864+
errmsg("table \"%s\" has multiple constraints named \"%s\"",
865+
get_rel_name(relid),conname)));
866+
867+
*constraintOid=HeapTupleGetOid(tuple);
868+
869+
/* Extract the conkey array, ie, attnums of constrained columns */
870+
adatum=heap_getattr(tuple,Anum_pg_constraint_conkey,
871+
RelationGetDescr(pg_constraint),&isNull);
872+
if (isNull)
873+
continue;/* no constrained columns */
874+
875+
arr=DatumGetArrayTypeP(adatum);/* ensure not toasted */
876+
numcols=ARR_DIMS(arr)[0];
877+
if (ARR_NDIM(arr)!=1||
878+
numcols<0||
879+
ARR_HASNULL(arr)||
880+
ARR_ELEMTYPE(arr)!=INT2OID)
881+
elog(ERROR,"conkey is not a 1-D smallint array");
882+
attnums= (int16*)ARR_DATA_PTR(arr);
883+
884+
/* Construct the result value */
885+
for (i=0;i<numcols;i++)
886+
{
887+
conattnos=bms_add_member(conattnos,
888+
attnums[i]-FirstLowInvalidHeapAttributeNumber);
889+
}
890+
}
891+
892+
systable_endscan(scan);
893+
894+
/* If no such constraint exists, complain */
895+
if (!OidIsValid(*constraintOid)&& !missing_ok)
896+
ereport(ERROR,
897+
(errcode(ERRCODE_UNDEFINED_OBJECT),
898+
errmsg("constraint \"%s\" for table \"%s\" does not exist",
899+
conname,get_rel_name(relid))));
900+
901+
heap_close(pg_constraint,AccessShareLock);
902+
903+
returnconattnos;
904+
}
905+
808906
/*
809907
* get_domain_constraint_oid
810908
*Find a constraint on the specified domain with the specified name.

‎src/backend/parser/parse_clause.c

Lines changed: 19 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3164,9 +3164,26 @@ transformOnConflictArbiter(ParseState *pstate,
31643164

31653165
pstate->p_namespace=save_namespace;
31663166

3167+
/*
3168+
* If the arbiter is specified by constraint name, get the constraint
3169+
* OID and mark the constrained columns as requiring SELECT privilege,
3170+
* in the same way as would have happened if the arbiter had been
3171+
* specified by explicit reference to the constraint's index columns.
3172+
*/
31673173
if (infer->conname)
3168-
*constraint=get_relation_constraint_oid(RelationGetRelid(pstate->p_target_relation),
3169-
infer->conname, false);
3174+
{
3175+
Oidrelid=RelationGetRelid(pstate->p_target_relation);
3176+
RangeTblEntry*rte=pstate->p_target_rangetblentry;
3177+
Bitmapset*conattnos;
3178+
3179+
conattnos=get_relation_constraint_attnos(relid,infer->conname,
3180+
false,constraint);
3181+
3182+
/* Make sure the rel as a whole is marked for SELECT access */
3183+
rte->requiredPerms |=ACL_SELECT;
3184+
/* Mark the constrained columns as requiring SELECT access */
3185+
rte->selectedCols=bms_add_members(rte->selectedCols,conattnos);
3186+
}
31703187
}
31713188

31723189
/*

‎src/backend/rewrite/rowsecurity.c

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -310,6 +310,8 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
310310
{
311311
List*conflict_permissive_policies;
312312
List*conflict_restrictive_policies;
313+
List*conflict_select_permissive_policies=NIL;
314+
List*conflict_select_restrictive_policies=NIL;
313315

314316
/* Get the policies that apply to the auxiliary UPDATE */
315317
get_policies_for_relation(rel,CMD_UPDATE,user_id,
@@ -339,9 +341,6 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
339341
*/
340342
if (rte->requiredPerms&ACL_SELECT)
341343
{
342-
List*conflict_select_permissive_policies=NIL;
343-
List*conflict_select_restrictive_policies=NIL;
344-
345344
get_policies_for_relation(rel,CMD_SELECT,user_id,
346345
&conflict_select_permissive_policies,
347346
&conflict_select_restrictive_policies);
@@ -362,6 +361,21 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
362361
withCheckOptions,
363362
hasSubLinks,
364363
false);
364+
365+
/*
366+
* Add ALL/SELECT policies as WCO_RLS_UPDATE_CHECK WCOs, to ensure
367+
* that the final updated row is visible when taking the UPDATE
368+
* path of an INSERT .. ON CONFLICT DO UPDATE, if SELECT rights
369+
* are required for this relation.
370+
*/
371+
if (rte->requiredPerms&ACL_SELECT)
372+
add_with_check_options(rel,rt_index,
373+
WCO_RLS_UPDATE_CHECK,
374+
conflict_select_permissive_policies,
375+
conflict_select_restrictive_policies,
376+
withCheckOptions,
377+
hasSubLinks,
378+
true);
365379
}
366380
}
367381

‎src/include/catalog/pg_constraint_fn.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,6 +69,8 @@ extern char *ChooseConstraintName(const char *name1, const char *name2,
6969
externvoidAlterConstraintNamespaces(OidownerId,OidoldNspId,
7070
OidnewNspId,boolisType,ObjectAddresses*objsMoved);
7171
externOidget_relation_constraint_oid(Oidrelid,constchar*conname,boolmissing_ok);
72+
externBitmapset*get_relation_constraint_attnos(Oidrelid,constchar*conname,
73+
boolmissing_ok,Oid*constraintOid);
7274
externOidget_domain_constraint_oid(Oidtypid,constchar*conname,boolmissing_ok);
7375

7476
externBitmapset*get_primary_key_attnos(Oidrelid,booldeferrableOk,

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

Lines changed: 14 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -488,10 +488,22 @@ ERROR: permission denied for relation atest5
488488
INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT)
489489
ERROR: permission denied for relation atest5
490490
-- Check that the columns in the inference require select privileges
491-
-- Error. No privs on four
492-
INSERT INTO atest5(three) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 10;
491+
INSERT INTO atest5(four) VALUES (4); -- fail
493492
ERROR: permission denied for relation atest5
494493
SET SESSION AUTHORIZATION regress_user1;
494+
GRANT INSERT (four) ON atest5 TO regress_user4;
495+
SET SESSION AUTHORIZATION regress_user4;
496+
INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- fails (due to SELECT)
497+
ERROR: permission denied for relation atest5
498+
INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- fails (due to SELECT)
499+
ERROR: permission denied for relation atest5
500+
INSERT INTO atest5(four) VALUES (4); -- ok
501+
SET SESSION AUTHORIZATION regress_user1;
502+
GRANT SELECT (four) ON atest5 TO regress_user4;
503+
SET SESSION AUTHORIZATION regress_user4;
504+
INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- ok
505+
INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- ok
506+
SET SESSION AUTHORIZATION regress_user1;
495507
REVOKE ALL (one) ON atest5 FROM regress_user4;
496508
GRANT SELECT (one,two,blue) ON atest6 TO regress_user4;
497509
SET SESSION AUTHORIZATION regress_user4;

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

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3807,9 +3807,10 @@ DROP TABLE r1;
38073807
--
38083808
SET SESSION AUTHORIZATION regress_rls_alice;
38093809
SET row_security = on;
3810-
CREATE TABLE r1 (a int);
3810+
CREATE TABLE r1 (a int PRIMARY KEY);
38113811
CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
38123812
CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true);
3813+
CREATE POLICY p3 ON r1 FOR INSERT WITH CHECK (true);
38133814
INSERT INTO r1 VALUES (10);
38143815
ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
38153816
ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
@@ -3836,6 +3837,18 @@ ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
38363837
-- Error
38373838
UPDATE r1 SET a = 30 RETURNING *;
38383839
ERROR: new row violates row-level security policy for table "r1"
3840+
-- UPDATE path of INSERT ... ON CONFLICT DO UPDATE should also error out
3841+
INSERT INTO r1 VALUES (10)
3842+
ON CONFLICT (a) DO UPDATE SET a = 30 RETURNING *;
3843+
ERROR: new row violates row-level security policy for table "r1"
3844+
-- Should still error out without RETURNING (use of arbiter always requires
3845+
-- SELECT permissions)
3846+
INSERT INTO r1 VALUES (10)
3847+
ON CONFLICT (a) DO UPDATE SET a = 30;
3848+
ERROR: new row violates row-level security policy for table "r1"
3849+
INSERT INTO r1 VALUES (10)
3850+
ON CONFLICT ON CONSTRAINT r1_pkey DO UPDATE SET a = 30;
3851+
ERROR: new row violates row-level security policy for table "r1"
38393852
DROP TABLE r1;
38403853
-- Check dependency handling
38413854
RESET SESSION AUTHORIZATION;

‎src/test/regress/sql/privileges.sql

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -320,9 +320,24 @@ INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLU
320320
INSERT INTO atest5(two)VALUES (6)ON CONFLICT (two) DOUPDATEset three=EXCLUDED.three;
321321
INSERT INTO atest5(two)VALUES (6)ON CONFLICT (two) DOUPDATEset one=8;-- fails (due to UPDATE)
322322
INSERT INTO atest5(three)VALUES (4)ON CONFLICT (two) DOUPDATEset three=10;-- fails (due to INSERT)
323+
323324
-- Check that the columns in the inference require select privileges
324-
-- Error. No privs on four
325-
INSERT INTO atest5(three)VALUES (4)ON CONFLICT (four) DOUPDATEset three=10;
325+
INSERT INTO atest5(four)VALUES (4);-- fail
326+
327+
SET SESSION AUTHORIZATION regress_user1;
328+
GRANT INSERT (four)ON atest5 TO regress_user4;
329+
SET SESSION AUTHORIZATION regress_user4;
330+
331+
INSERT INTO atest5(four)VALUES (4)ON CONFLICT (four) DOUPDATEset three=3;-- fails (due to SELECT)
332+
INSERT INTO atest5(four)VALUES (4)ON CONFLICTONCONSTRAINT atest5_four_key DOUPDATEset three=3;-- fails (due to SELECT)
333+
INSERT INTO atest5(four)VALUES (4);-- ok
334+
335+
SET SESSION AUTHORIZATION regress_user1;
336+
GRANTSELECT (four)ON atest5 TO regress_user4;
337+
SET SESSION AUTHORIZATION regress_user4;
338+
339+
INSERT INTO atest5(four)VALUES (4)ON CONFLICT (four) DOUPDATEset three=3;-- ok
340+
INSERT INTO atest5(four)VALUES (4)ON CONFLICTONCONSTRAINT atest5_four_key DOUPDATEset three=3;-- ok
326341

327342
SET SESSION AUTHORIZATION regress_user1;
328343
REVOKE ALL (one)ON atest5FROM regress_user4;

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

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1674,10 +1674,11 @@ DROP TABLE r1;
16741674
--
16751675
SET SESSION AUTHORIZATION regress_rls_alice;
16761676
SET row_security=on;
1677-
CREATETABLEr1 (aint);
1677+
CREATETABLEr1 (aintPRIMARY KEY);
16781678

16791679
CREATE POLICY p1ON r1 FORSELECT USING (a<20);
16801680
CREATE POLICY p2ON r1 FORUPDATE USING (a<20) WITHCHECK (true);
1681+
CREATE POLICY p3ON r1 FOR INSERT WITHCHECK (true);
16811682
INSERT INTO r1VALUES (10);
16821683
ALTERTABLE r1 ENABLE ROW LEVEL SECURITY;
16831684
ALTERTABLE r1 FORCE ROW LEVEL SECURITY;
@@ -1699,6 +1700,17 @@ ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
16991700
-- Error
17001701
UPDATE r1SET a=30 RETURNING*;
17011702

1703+
-- UPDATE path of INSERT ... ON CONFLICT DO UPDATE should also error out
1704+
INSERT INTO r1VALUES (10)
1705+
ON CONFLICT (a) DOUPDATESET a=30 RETURNING*;
1706+
1707+
-- Should still error out without RETURNING (use of arbiter always requires
1708+
-- SELECT permissions)
1709+
INSERT INTO r1VALUES (10)
1710+
ON CONFLICT (a) DOUPDATESET a=30;
1711+
INSERT INTO r1VALUES (10)
1712+
ON CONFLICTONCONSTRAINT r1_pkey DOUPDATESET a=30;
1713+
17021714
DROPTABLE r1;
17031715

17041716
-- Check dependency handling

0 commit comments

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