On 26 February 2015 at 09:27, Dean Rasheed <dean.a.rash...@gmail.com> wrote:
> I think this should probably be committed as 2 separate patches...
On closer inspection, the 2 parts are interrelated since the new
self-join test that tests the inheritance planner changes also
requires the rewriter changes, otherwise it falls over with an
infinite recursion error in the rewriter. I suppose the rewriter
changes could be committed first with the self-join without
inheritance test to test the fix to the infinite recursion problem,
but I think it's probably easier to just treat this as a single set of
related bug fixes.
Attached is an updated patch. There are no more code changes, but I've
added a few more regression tests.
Regards,
Dean
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
new file mode 100644
index b02a107..e4e4d80
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
*************** inheritance_planner(PlannerInfo *root)
*** 791,796 ****
--- 791,797 ----
{
Query *parse = root->parse;
int parentRTindex = parse->resultRelation;
+ Bitmapset *resultRTindexes = NULL;
int nominalRelation = -1;
List *final_rtable = NIL;
int save_rel_array_size = 0;
*************** inheritance_planner(PlannerInfo *root)
*** 816,822 ****
--- 817,837 ----
* (1) would result in a rangetable of length O(N^2) for N targets, with
* at least O(N^3) work expended here; and (2) would greatly complicate
* management of the rowMarks list.
+ *
+ * Note that any RTEs with security barrier quals will be turned into
+ * subqueries during planning, and so we must create copies of them too,
+ * except where they are target relations, which will each only be used
+ * in a single plan.
*/
+ resultRTindexes = bms_add_member(resultRTindexes, parentRTindex);
+ foreach(lc, root->append_rel_list)
+ {
+ AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
+ if (appinfo->parent_relid == parentRTindex)
+ resultRTindexes = bms_add_member(resultRTindexes,
+ appinfo->child_relid);
+ }
+
foreach(lc, root->append_rel_list)
{
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
*************** inheritance_planner(PlannerInfo *root)
*** 887,907 ****
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(lr);
! if (rte->rtekind == RTE_SUBQUERY)
{
Index newrti;
/*
* The RTE can't contain any references to its own RT
! * index, so we can save a few cycles by applying
! * ChangeVarNodes before we append the RTE to the
! * rangetable.
*/
newrti = list_length(subroot.parse->rtable) + 1;
ChangeVarNodes((Node *) subroot.parse, rti, newrti, 0);
ChangeVarNodes((Node *) subroot.rowMarks, rti, newrti, 0);
ChangeVarNodes((Node *) subroot.append_rel_list, rti, newrti, 0);
rte = copyObject(rte);
subroot.parse->rtable = lappend(subroot.parse->rtable,
rte);
}
--- 902,930 ----
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(lr);
! /*
! * Copy subquery RTEs and RTEs with security barrier quals
! * that will be turned into subqueries, except those that are
! * target relations.
! */
! if (rte->rtekind == RTE_SUBQUERY ||
! (rte->securityQuals != NIL &&
! !bms_is_member(rti, resultRTindexes)))
{
Index newrti;
/*
* The RTE can't contain any references to its own RT
! * index, except in the security barrier quals, so we can
! * save a few cycles by applying ChangeVarNodes before we
! * append the RTE to the rangetable.
*/
newrti = list_length(subroot.parse->rtable) + 1;
ChangeVarNodes((Node *) subroot.parse, rti, newrti, 0);
ChangeVarNodes((Node *) subroot.rowMarks, rti, newrti, 0);
ChangeVarNodes((Node *) subroot.append_rel_list, rti, newrti, 0);
rte = copyObject(rte);
+ ChangeVarNodes((Node *) rte->securityQuals, rti, newrti, 0);
subroot.parse->rtable = lappend(subroot.parse->rtable,
rte);
}
*************** preprocess_rowmarks(PlannerInfo *root)
*** 2271,2278 ****
newrc = makeNode(PlanRowMark);
newrc->rti = newrc->prti = i;
newrc->rowmarkId = ++(root->glob->lastRowMarkId);
! /* real tables support REFERENCE, anything else needs COPY */
if (rte->rtekind == RTE_RELATION &&
rte->relkind != RELKIND_FOREIGN_TABLE)
newrc->markType = ROW_MARK_REFERENCE;
else
--- 2294,2306 ----
newrc = makeNode(PlanRowMark);
newrc->rti = newrc->prti = i;
newrc->rowmarkId = ++(root->glob->lastRowMarkId);
! /*
! * Real tables support REFERENCE, anything else needs COPY. Since
! * RTEs with security barrier quals will become subqueries, they also
! * need COPY.
! */
if (rte->rtekind == RTE_RELATION &&
+ rte->securityQuals == NIL &&
rte->relkind != RELKIND_FOREIGN_TABLE)
newrc->markType = ROW_MARK_REFERENCE;
else
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
new file mode 100644
index 9d2c280..08ec13c
*** a/src/backend/rewrite/rewriteHandler.c
--- b/src/backend/rewrite/rewriteHandler.c
*************** fireRIRrules(Query *parsetree, List *act
*** 1714,1764 ****
activeRIRs = list_delete_first(activeRIRs);
}
}
- /*
- * If the RTE has row security quals, apply them and recurse into the
- * securityQuals.
- */
- if (prepend_row_security_policies(parsetree, rte, rt_index))
- {
- /*
- * We applied security quals, check for infinite recursion and
- * then expand any nested queries.
- */
- if (list_member_oid(activeRIRs, RelationGetRelid(rel)))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("infinite recursion detected in policy for relation \"%s\"",
- RelationGetRelationName(rel))));
-
- /*
- * Make sure we check for recursion in either securityQuals or
- * WITH CHECK quals.
- */
- if (rte->securityQuals != NIL)
- {
- activeRIRs = lcons_oid(RelationGetRelid(rel), activeRIRs);
-
- expression_tree_walker( (Node*) rte->securityQuals,
- fireRIRonSubLink, (void*)activeRIRs );
-
- activeRIRs = list_delete_first(activeRIRs);
- }
-
- if (parsetree->withCheckOptions != NIL)
- {
- WithCheckOption *wco;
- List *quals = NIL;
-
- wco = (WithCheckOption *) makeNode(WithCheckOption);
- quals = lcons(wco->qual, quals);
-
- activeRIRs = lcons_oid(RelationGetRelid(rel), activeRIRs);
-
- expression_tree_walker( (Node*) quals, fireRIRonSubLink,
- (void*)activeRIRs);
- }
-
- }
heap_close(rel, NoLock);
}
--- 1714,1719 ----
*************** fireRIRrules(Query *parsetree, List *act
*** 1780,1785 ****
--- 1735,1822 ----
query_tree_walker(parsetree, fireRIRonSubLink, (void *) activeRIRs,
QTW_IGNORE_RC_SUBQUERIES);
+ /*
+ * Apply any row level security policies. We do this last because it
+ * requires special recursion detection if the new quals have sublink
+ * subqueries, and if we did it in the loop above query_tree_walker
+ * would then recurse into those quals a second time.
+ */
+ rt_index = 0;
+ foreach(lc, parsetree->rtable)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+ Relation rel;
+ List *securityQuals;
+ List *withCheckOptions;
+ bool hasRowSecurity;
+ bool hasSubLinks;
+
+ ++rt_index;
+
+ /* Only normal relations can have RLS policies */
+ if (rte->rtekind != RTE_RELATION ||
+ rte->relkind != RELKIND_RELATION)
+ continue;
+
+ rel = heap_open(rte->relid, NoLock);
+
+ /*
+ * Fetch any new security quals that must be applied to this RTE.
+ */
+ get_row_security_policies(parsetree, rte, rt_index,
+ &securityQuals, &withCheckOptions,
+ &hasRowSecurity, &hasSubLinks);
+
+ if (securityQuals != NIL || withCheckOptions != NIL)
+ {
+ if (hasSubLinks)
+ {
+ /*
+ * Recursively process the new quals, checking for infinite
+ * recursion.
+ */
+ if (list_member_oid(activeRIRs, RelationGetRelid(rel)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("infinite recursion detected in policy for relation \"%s\"",
+ RelationGetRelationName(rel))));
+
+ activeRIRs = lcons_oid(RelationGetRelid(rel), activeRIRs);
+
+ expression_tree_walker( (Node*) securityQuals,
+ fireRIRonSubLink, (void*)activeRIRs );
+
+ expression_tree_walker( (Node*) withCheckOptions,
+ fireRIRonSubLink, (void*)activeRIRs );
+
+ activeRIRs = list_delete_first(activeRIRs);
+ }
+
+ /*
+ * Add the new security quals to the start of the RTE's list so
+ * that they get applied before any existing security quals (which
+ * might have come from a user-written security barrier view, and
+ * might contain malicious code).
+ */
+ rte->securityQuals = list_concat(securityQuals,
+ rte->securityQuals);
+
+ parsetree->withCheckOptions = list_concat(withCheckOptions,
+ parsetree->withCheckOptions);
+ }
+
+ /*
+ * Make sure the query is marked correctly if row level security
+ * applies, or if the new quals had sublinks.
+ */
+ if (hasRowSecurity)
+ parsetree->hasRowSecurity = true;
+ if (hasSubLinks)
+ parsetree->hasSubLinks = true;
+
+ heap_close(rel, NoLock);
+ }
+
return parsetree;
}
diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c
new file mode 100644
index 7669130..73a5794
*** a/src/backend/rewrite/rowsecurity.c
--- b/src/backend/rewrite/rowsecurity.c
***************
*** 58,64 ****
static List *pull_row_security_policies(CmdType cmd, Relation relation,
Oid user_id);
! static void process_policies(List *policies, int rt_index,
Expr **final_qual,
Expr **final_with_check_qual,
bool *hassublinks);
--- 58,64 ----
static List *pull_row_security_policies(CmdType cmd, Relation relation,
Oid user_id);
! static void process_policies(Query* root, List *policies, int rt_index,
Expr **final_qual,
Expr **final_with_check_qual,
bool *hassublinks);
*************** static bool check_role_for_policy(ArrayT
*** 73,88 ****
row_security_policy_hook_type row_security_policy_hook = NULL;
/*
! * Check the given RTE to see whether it's already had row security quals
! * expanded and, if not, prepend any row security rules from built-in or
! * plug-in sources to the securityQuals. The security quals are rewritten (for
! * view expansion, etc) before being added to the RTE.
*
! * Returns true if any quals were added. Note that quals may have been found
! * but not added if user rights make the user exempt from row security.
*/
! bool
! prepend_row_security_policies(Query* root, RangeTblEntry* rte, int rt_index)
{
Expr *rowsec_expr = NULL;
Expr *rowsec_with_check_expr = NULL;
--- 73,89 ----
row_security_policy_hook_type row_security_policy_hook = NULL;
/*
! * Get any row security quals and check quals that should be applied to the
! * specified RTE.
*
! * In addition hasRowSecurity is set to true if row level security is enabled
! * (even if this RTE doesn't have any row security quals), and hasSubLinks is
! * set to true if any of the quals returned contain sublinks.
*/
! void
! get_row_security_policies(Query* root, RangeTblEntry* rte, int rt_index,
! List **securityQuals, List **withCheckOptions,
! bool *hasRowSecurity, bool *hasSubLinks)
{
Expr *rowsec_expr = NULL;
Expr *rowsec_with_check_expr = NULL;
*************** prepend_row_security_policies(Query* roo
*** 96,103 ****
Oid user_id;
int sec_context;
int rls_status;
! bool defaultDeny = true;
! bool hassublinks = false;
/* This is just to get the security context */
GetUserIdAndSecContext(&user_id, &sec_context);
--- 97,109 ----
Oid user_id;
int sec_context;
int rls_status;
! bool defaultDeny = false;
!
! /* Defaults for the return values */
! *securityQuals = NIL;
! *withCheckOptions = NIL;
! *hasRowSecurity = false;
! *hasSubLinks = false;
/* This is just to get the security context */
GetUserIdAndSecContext(&user_id, &sec_context);
*************** prepend_row_security_policies(Query* roo
*** 113,126 ****
if (rte->relid < FirstNormalObjectId
|| rte->relkind != RELKIND_RELATION
|| (sec_context & SECURITY_ROW_LEVEL_DISABLED))
! return false;
/* Determine the state of RLS for this, pass checkAsUser explicitly */
rls_status = check_enable_rls(rte->relid, rte->checkAsUser, false);
/* If there is no RLS on this table at all, nothing to do */
if (rls_status == RLS_NONE)
! return false;
/*
* RLS_NONE_ENV means we are not doing any RLS now, but that may change
--- 119,132 ----
if (rte->relid < FirstNormalObjectId
|| rte->relkind != RELKIND_RELATION
|| (sec_context & SECURITY_ROW_LEVEL_DISABLED))
! return;
/* Determine the state of RLS for this, pass checkAsUser explicitly */
rls_status = check_enable_rls(rte->relid, rte->checkAsUser, false);
/* If there is no RLS on this table at all, nothing to do */
if (rls_status == RLS_NONE)
! return;
/*
* RLS_NONE_ENV means we are not doing any RLS now, but that may change
*************** prepend_row_security_policies(Query* roo
*** 134,151 ****
* be replanned if the environment changes (GUCs, role), but we
* are not adding anything here.
*/
! root->hasRowSecurity = true;
! return false;
}
- /*
- * We may end up getting called multiple times for the same RTE, so check
- * to make sure we aren't doing double-work.
- */
- if (rte->securityQuals != NIL)
- return false;
-
/* Grab the built-in policies which should be applied to this relation. */
rel = heap_open(rte->relid, NoLock);
--- 140,150 ----
* be replanned if the environment changes (GUCs, role), but we
* are not adding anything here.
*/
! *hasRowSecurity = true;
! return;
}
/* Grab the built-in policies which should be applied to this relation. */
rel = heap_open(rte->relid, NoLock);
*************** prepend_row_security_policies(Query* roo
*** 167,174 ****
defaultDeny = true;
/* Now that we have our policies, build the expressions from them. */
! process_policies(rowsec_policies, rt_index, &rowsec_expr,
! &rowsec_with_check_expr, &hassublinks);
/*
* Also, allow extensions to add their own policies.
--- 166,173 ----
defaultDeny = true;
/* Now that we have our policies, build the expressions from them. */
! process_policies(root, rowsec_policies, rt_index, &rowsec_expr,
! &rowsec_with_check_expr, hasSubLinks);
/*
* Also, allow extensions to add their own policies.
*************** prepend_row_security_policies(Query* roo
*** 191,204 ****
* enabled on the table, then we will ignore the internally-generated
* default-deny policy and use only the policies returned by the
* extension.
*/
if (row_security_policy_hook)
{
hook_policies = (*row_security_policy_hook)(root->commandType, rel);
/* Build the expression from any policies returned. */
! process_policies(hook_policies, rt_index, &hook_expr,
! &hook_with_check_expr, &hassublinks);
}
/*
--- 190,206 ----
* enabled on the table, then we will ignore the internally-generated
* default-deny policy and use only the policies returned by the
* extension.
+ *
+ * Any extension policies are applied after any internally-generated
+ * policies, so that extensions cannot bypass built-in RLS.
*/
if (row_security_policy_hook)
{
hook_policies = (*row_security_policy_hook)(root->commandType, rel);
/* Build the expression from any policies returned. */
! process_policies(root, hook_policies, rt_index, &hook_expr,
! &hook_with_check_expr, hasSubLinks);
}
/*
*************** prepend_row_security_policies(Query* roo
*** 230,236 ****
wco->viewname = RelationGetRelationName(rel);
wco->qual = (Node *) rowsec_with_check_expr;
wco->cascaded = false;
! root->withCheckOptions = lcons(wco, root->withCheckOptions);
}
/*
--- 232,238 ----
wco->viewname = RelationGetRelationName(rel);
wco->qual = (Node *) rowsec_with_check_expr;
wco->cascaded = false;
! *withCheckOptions = lappend(*withCheckOptions, wco);
}
/*
*************** prepend_row_security_policies(Query* roo
*** 244,250 ****
wco->viewname = RelationGetRelationName(rel);
wco->qual = (Node *) hook_with_check_expr;
wco->cascaded = false;
! root->withCheckOptions = lcons(wco, root->withCheckOptions);
}
}
--- 246,252 ----
wco->viewname = RelationGetRelationName(rel);
wco->qual = (Node *) hook_with_check_expr;
wco->cascaded = false;
! *withCheckOptions = lappend(*withCheckOptions, wco);
}
}
*************** prepend_row_security_policies(Query* roo
*** 254,264 ****
|| root->commandType == CMD_DELETE)
{
if (rowsec_expr)
! rte->securityQuals = lcons(rowsec_expr, rte->securityQuals);
if (hook_expr)
! rte->securityQuals = lcons(hook_expr,
! rte->securityQuals);
}
heap_close(rel, NoLock);
--- 256,265 ----
|| root->commandType == CMD_DELETE)
{
if (rowsec_expr)
! *securityQuals = lappend(*securityQuals, rowsec_expr);
if (hook_expr)
! *securityQuals = lappend(*securityQuals, hook_expr);
}
heap_close(rel, NoLock);
*************** prepend_row_security_policies(Query* roo
*** 267,283 ****
* Mark this query as having row security, so plancache can invalidate
* it when necessary (eg: role changes)
*/
! root->hasRowSecurity = true;
!
! /*
! * If we have sublinks added because of the policies being added to the
! * query, then set hasSubLinks on the Query to force subLinks to be
! * properly expanded.
! */
! root->hasSubLinks |= hassublinks;
! /* If we got this far, we must have added quals */
! return true;
}
/*
--- 268,276 ----
* Mark this query as having row security, so plancache can invalidate
* it when necessary (eg: role changes)
*/
! *hasRowSecurity = true;
! return;
}
/*
*************** pull_row_security_policies(CmdType cmd,
*** 383,389 ****
* qual_eval, with_check_eval, and hassublinks are output variables
*/
static void
! process_policies(List *policies, int rt_index, Expr **qual_eval,
Expr **with_check_eval, bool *hassublinks)
{
ListCell *item;
--- 376,382 ----
* qual_eval, with_check_eval, and hassublinks are output variables
*/
static void
! process_policies(Query* root, List *policies, int rt_index, Expr **qual_eval,
Expr **with_check_eval, bool *hassublinks)
{
ListCell *item;
*************** process_policies(List *policies, int rt_
*** 392,398 ****
/*
* Extract the USING and WITH CHECK quals from each of the policies
! * and add them to our lists.
*/
foreach(item, policies)
{
--- 385,392 ----
/*
* Extract the USING and WITH CHECK quals from each of the policies
! * and add them to our lists. We only want WITH CHECK quals if this
! * RTE is the query's result relation.
*/
foreach(item, policies)
{
*************** process_policies(List *policies, int rt_
*** 401,407 ****
if (policy->qual != NULL)
quals = lcons(copyObject(policy->qual), quals);
! if (policy->with_check_qual != NULL)
with_check_quals = lcons(copyObject(policy->with_check_qual),
with_check_quals);
--- 395,402 ----
if (policy->qual != NULL)
quals = lcons(copyObject(policy->qual), quals);
! if (policy->with_check_qual != NULL &&
! rt_index == root->resultRelation)
with_check_quals = lcons(copyObject(policy->with_check_qual),
with_check_quals);
*************** process_policies(List *policies, int rt_
*** 421,427 ****
* If we end up with only USING quals, then use those as
* WITH CHECK quals also.
*/
! if (with_check_quals == NIL)
with_check_quals = copyObject(quals);
/*
--- 416,422 ----
* If we end up with only USING quals, then use those as
* WITH CHECK quals also.
*/
! if (with_check_quals == NIL && rt_index == root->resultRelation)
with_check_quals = copyObject(quals);
/*
*************** process_policies(List *policies, int rt_
*** 450,457 ****
*/
if (list_length(with_check_quals) > 1)
*with_check_eval = makeBoolExpr(OR_EXPR, with_check_quals, -1);
! else
*with_check_eval = (Expr*) linitial(with_check_quals);
return;
}
--- 445,454 ----
*/
if (list_length(with_check_quals) > 1)
*with_check_eval = makeBoolExpr(OR_EXPR, with_check_quals, -1);
! else if (with_check_quals != NIL)
*with_check_eval = (Expr*) linitial(with_check_quals);
+ else
+ *with_check_eval = NULL;
return;
}
diff --git a/src/include/rewrite/rowsecurity.h b/src/include/rewrite/rowsecurity.h
new file mode 100644
index 8d19bfd..ac72427
*** a/src/include/rewrite/rowsecurity.h
--- b/src/include/rewrite/rowsecurity.h
*************** typedef List *(*row_security_policy_hook
*** 39,45 ****
extern PGDLLIMPORT row_security_policy_hook_type row_security_policy_hook;
! extern bool prepend_row_security_policies(Query* root, RangeTblEntry* rte,
! int rt_index);
#endif /* ROWSECURITY_H */
--- 39,46 ----
extern PGDLLIMPORT row_security_policy_hook_type row_security_policy_hook;
! extern void get_row_security_policies(Query* root, RangeTblEntry* rte, int rt_index,
! List **securityQuals, List **withCheckOptions,
! bool *hasRowSecurity, bool *hasSubLinks);
#endif /* ROWSECURITY_H */
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
new file mode 100644
index f41bef1..c864c12
*** a/src/test/regress/expected/rowsecurity.out
--- b/src/test/regress/expected/rowsecurity.out
*************** ALTER TABLE t1 DROP COLUMN junk1; --
*** 466,474 ****
--- 466,476 ----
GRANT ALL ON t1 TO public;
COPY t1 FROM stdin WITH (oids);
CREATE TABLE t2 (c float) INHERITS (t1);
+ GRANT ALL ON t2 TO public;
COPY t2 FROM stdin WITH (oids);
CREATE TABLE t3 (c text, b text, a int) WITH OIDS;
ALTER TABLE t3 INHERIT t1;
+ GRANT ALL ON t3 TO public;
COPY t3(a,b,c) FROM stdin WITH (oids);
CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number
CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number
*************** NOTICE: f_leak => yyyyyy
*** 1114,1135 ****
302 | 2 | yyyyyy | (2,yyyyyy)
(5 rows)
RESET SESSION AUTHORIZATION;
SET row_security TO OFF;
! SELECT * FROM t1;
a | b
---+-------------
1 | aaa
- 3 | ccc
- 2 | bbbbbb_updt
- 4 | dddddd_updt
1 | abc
- 3 | cde
- 2 | bcdbcd
- 4 | defdef
1 | xxx
! 3 | zzz
2 | yyyyyy
(11 rows)
SET SESSION AUTHORIZATION rls_regress_user1;
--- 1116,1325 ----
302 | 2 | yyyyyy | (2,yyyyyy)
(5 rows)
+ -- updates with from clause
+ EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3
+ WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
+ QUERY PLAN
+ ---------------------------------------------------------------
+ Update on t2 t2_1
+ -> Nested Loop
+ -> Subquery Scan on t2
+ Filter: f_leak(t2.b)
+ -> LockRows
+ -> Seq Scan on t2 t2_2
+ Filter: ((a = 3) AND ((a % 2) = 1))
+ -> Seq Scan on t3
+ Filter: (f_leak(b) AND (a = 2))
+ (9 rows)
+
+ UPDATE t2 SET b=t2.b FROM t3
+ WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
+ NOTICE: f_leak => cde
+ NOTICE: f_leak => xxx
+ NOTICE: f_leak => zzz
+ NOTICE: f_leak => yyyyyy
+ EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2
+ WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
+ QUERY PLAN
+ ---------------------------------------------------------------
+ Update on t1 t1_3
+ -> Nested Loop
+ -> Subquery Scan on t1
+ Filter: f_leak(t1.b)
+ -> LockRows
+ -> Seq Scan on t1 t1_4
+ Filter: ((a = 3) AND ((a % 2) = 0))
+ -> Subquery Scan on t2
+ Filter: f_leak(t2.b)
+ -> Seq Scan on t2 t2_3
+ Filter: ((a = 3) AND ((a % 2) = 1))
+ -> Nested Loop
+ -> Subquery Scan on t1_1
+ Filter: f_leak(t1_1.b)
+ -> LockRows
+ -> Seq Scan on t2 t2_4
+ Filter: ((a = 3) AND ((a % 2) = 0))
+ -> Subquery Scan on t2_1
+ Filter: f_leak(t2_1.b)
+ -> Seq Scan on t2 t2_5
+ Filter: ((a = 3) AND ((a % 2) = 1))
+ -> Nested Loop
+ -> Subquery Scan on t1_2
+ Filter: f_leak(t1_2.b)
+ -> LockRows
+ -> Seq Scan on t3
+ Filter: ((a = 3) AND ((a % 2) = 0))
+ -> Subquery Scan on t2_2
+ Filter: f_leak(t2_2.b)
+ -> Seq Scan on t2 t2_6
+ Filter: ((a = 3) AND ((a % 2) = 1))
+ (31 rows)
+
+ UPDATE t1 SET b=t1.b FROM t2
+ WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
+ EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1
+ WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
+ QUERY PLAN
+ ---------------------------------------------------------------------
+ Update on t2 t2_1
+ -> Nested Loop
+ -> Subquery Scan on t2
+ Filter: f_leak(t2.b)
+ -> LockRows
+ -> Seq Scan on t2 t2_2
+ Filter: ((a = 3) AND ((a % 2) = 1))
+ -> Subquery Scan on t1
+ Filter: f_leak(t1.b)
+ -> Result
+ -> Append
+ -> Seq Scan on t1 t1_1
+ Filter: ((a = 3) AND ((a % 2) = 0))
+ -> Seq Scan on t2 t2_3
+ Filter: ((a = 3) AND ((a % 2) = 0))
+ -> Seq Scan on t3
+ Filter: ((a = 3) AND ((a % 2) = 0))
+ (17 rows)
+
+ UPDATE t2 SET b=t2.b FROM t1
+ WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
+ NOTICE: f_leak => cde
+ -- updates with from clause self join
+ EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
+ WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
+ AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
+ QUERY PLAN
+ ---------------------------------------------------------------
+ Update on t2 t2_1_1
+ -> Nested Loop
+ Join Filter: (t2_1.b = t2_2.b)
+ -> Subquery Scan on t2_1
+ Filter: f_leak(t2_1.b)
+ -> LockRows
+ -> Seq Scan on t2 t2_1_2
+ Filter: ((a = 3) AND ((a % 2) = 1))
+ -> Subquery Scan on t2_2
+ Filter: f_leak(t2_2.b)
+ -> Seq Scan on t2 t2_2_1
+ Filter: ((a = 3) AND ((a % 2) = 1))
+ (12 rows)
+
+ UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
+ WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
+ AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
+ NOTICE: f_leak => cde
+ NOTICE: f_leak => cde
+ a | b | c | a | b | c | t2_1 | t2_2
+ ---+-----+-----+---+-----+-----+-------------+-------------
+ 3 | cde | 3.3 | 3 | cde | 3.3 | (3,cde,3.3) | (3,cde,3.3)
+ (1 row)
+
+ EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
+ WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
+ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
+ QUERY PLAN
+ ---------------------------------------------------------------
+ Update on t1 t1_1_3
+ -> Nested Loop
+ Join Filter: (t1_1.b = t1_2.b)
+ -> Subquery Scan on t1_1
+ Filter: f_leak(t1_1.b)
+ -> LockRows
+ -> Seq Scan on t1 t1_1_4
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Subquery Scan on t1_2
+ Filter: f_leak(t1_2.b)
+ -> Append
+ -> Seq Scan on t1 t1_2_3
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Seq Scan on t2 t1_2_4
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Seq Scan on t3 t1_2_5
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Nested Loop
+ Join Filter: (t1_1_1.b = t1_2_1.b)
+ -> Subquery Scan on t1_1_1
+ Filter: f_leak(t1_1_1.b)
+ -> LockRows
+ -> Seq Scan on t2 t1_1_5
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Subquery Scan on t1_2_1
+ Filter: f_leak(t1_2_1.b)
+ -> Append
+ -> Seq Scan on t1 t1_2_6
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Seq Scan on t2 t1_2_7
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Seq Scan on t3 t1_2_8
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Nested Loop
+ Join Filter: (t1_1_2.b = t1_2_2.b)
+ -> Subquery Scan on t1_1_2
+ Filter: f_leak(t1_1_2.b)
+ -> LockRows
+ -> Seq Scan on t3 t1_1_6
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Subquery Scan on t1_2_2
+ Filter: f_leak(t1_2_2.b)
+ -> Append
+ -> Seq Scan on t1 t1_2_9
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Seq Scan on t2 t1_2_10
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ -> Seq Scan on t3 t1_2_11
+ Filter: ((a = 4) AND ((a % 2) = 0))
+ (49 rows)
+
+ UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
+ WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
+ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
+ NOTICE: f_leak => dddddd_updt
+ NOTICE: f_leak => dddddd_updt
+ NOTICE: f_leak => defdef
+ NOTICE: f_leak => defdef
+ NOTICE: f_leak => dddddd_updt
+ NOTICE: f_leak => defdef
+ a | b | a | b | t1_1 | t1_2
+ ---+-------------+---+-------------+-----------------+-----------------
+ 4 | dddddd_updt | 4 | dddddd_updt | (4,dddddd_updt) | (4,dddddd_updt)
+ 4 | defdef | 4 | defdef | (4,defdef) | (4,defdef)
+ (2 rows)
+
RESET SESSION AUTHORIZATION;
SET row_security TO OFF;
! SELECT * FROM t1 ORDER BY a,b;
a | b
---+-------------
1 | aaa
1 | abc
1 | xxx
! 2 | bbbbbb_updt
! 2 | bcdbcd
2 | yyyyyy
+ 3 | ccc
+ 3 | cde
+ 3 | zzz
+ 4 | dddddd_updt
+ 4 | defdef
(11 rows)
SET SESSION AUTHORIZATION rls_regress_user1;
*************** NOTICE: f_leak => yyyyyy
*** 1187,1192 ****
--- 1377,1479 ----
(3 rows)
--
+ -- S.b. view on top of Row-level security
+ --
+ SET SESSION AUTHORIZATION rls_regress_user0;
+ CREATE TABLE b1 (a int, b text);
+ INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
+ CREATE POLICY p1 ON b1 USING (a % 2 = 0);
+ ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
+ GRANT ALL ON b1 TO rls_regress_user1;
+ SET SESSION AUTHORIZATION rls_regress_user1;
+ CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
+ GRANT ALL ON bv1 TO rls_regress_user2;
+ SET SESSION AUTHORIZATION rls_regress_user2;
+ EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
+ QUERY PLAN
+ ---------------------------------------------
+ Subquery Scan on bv1
+ Filter: f_leak(bv1.b)
+ -> Seq Scan on b1
+ Filter: ((a > 0) AND ((a % 2) = 0))
+ (4 rows)
+
+ SELECT * FROM bv1 WHERE f_leak(b);
+ NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
+ NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
+ NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
+ NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
+ NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
+ a | b
+ ----+----------------------------------
+ 2 | c81e728d9d4c2f636f067f89cc14862c
+ 4 | a87ff679a2f3e71d9181a67b7542122c
+ 6 | 1679091c5a880faf6fb5e6087eb1b2dc
+ 8 | c9f0f895fb98ab9159f51fd0297e236d
+ 10 | d3d9446802a44259755d38e6d163e820
+ (5 rows)
+
+ INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO
+ ERROR: new row violates WITH CHECK OPTION for "b1"
+ INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check
+ ERROR: new row violates WITH CHECK OPTION for "b1"
+ INSERT INTO bv1 VALUES (12, 'xxx'); -- ok
+ EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
+ QUERY PLAN
+ ---------------------------------------------------------------------------
+ Update on b1 b1_1
+ -> Subquery Scan on b1
+ Filter: f_leak(b1.b)
+ -> Subquery Scan on b1_2
+ -> LockRows
+ -> Seq Scan on b1 b1_3
+ Filter: ((a > 0) AND (a = 4) AND ((a % 2) = 0))
+ (7 rows)
+
+ UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
+ NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
+ EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
+ QUERY PLAN
+ ---------------------------------------------------------------------------
+ Delete on b1 b1_1
+ -> Subquery Scan on b1
+ Filter: f_leak(b1.b)
+ -> Subquery Scan on b1_2
+ -> LockRows
+ -> Seq Scan on b1 b1_3
+ Filter: ((a > 0) AND (a = 6) AND ((a % 2) = 0))
+ (7 rows)
+
+ DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
+ NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
+ SET SESSION AUTHORIZATION rls_regress_user0;
+ SELECT * FROM b1;
+ a | b
+ -----+----------------------------------
+ -10 | 1b0fd9efa5279c4203b7c70233f86dbf
+ -9 | 252e691406782824eec43d7eadc3d256
+ -8 | a8d2ec85eaf98407310b72eb73dda247
+ -7 | 74687a12d3915d3c4d83f1af7b3683d5
+ -6 | 596a3d04481816330f07e4f97510c28f
+ -5 | 47c1b025fa18ea96c33fbb6718688c0f
+ -4 | 0267aaf632e87a63288a08331f22c7c3
+ -3 | b3149ecea4628efd23d2f86e5a723472
+ -2 | 5d7b9adcbe1c629ec722529dd12e5129
+ -1 | 6bb61e3b7bce0931da574d19d1d82c88
+ 0 | cfcd208495d565ef66e7dff9f98764da
+ 1 | c4ca4238a0b923820dcc509a6f75849b
+ 2 | c81e728d9d4c2f636f067f89cc14862c
+ 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
+ 5 | e4da3b7fbbce2345d7772b0674a318d5
+ 7 | 8f14e45fceea167a5a36dedd4bea2543
+ 8 | c9f0f895fb98ab9159f51fd0297e236d
+ 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
+ 10 | d3d9446802a44259755d38e6d163e820
+ 12 | xxx
+ 4 | yyy
+ (21 rows)
+
+ --
-- ROLE/GROUP
--
SET SESSION AUTHORIZATION rls_regress_user0;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
new file mode 100644
index ed7adbf..4da5035
*** a/src/test/regress/sql/rowsecurity.sql
--- b/src/test/regress/sql/rowsecurity.sql
*************** COPY t1 FROM stdin WITH (oids);
*** 207,212 ****
--- 207,214 ----
\.
CREATE TABLE t2 (c float) INHERITS (t1);
+ GRANT ALL ON t2 TO public;
+
COPY t2 FROM stdin WITH (oids);
201 1 abc 1.1
202 2 bcd 2.2
*************** COPY t2 FROM stdin WITH (oids);
*** 216,221 ****
--- 218,225 ----
CREATE TABLE t3 (c text, b text, a int) WITH OIDS;
ALTER TABLE t3 INHERIT t1;
+ GRANT ALL ON t3 TO public;
+
COPY t3(a,b,c) FROM stdin WITH (oids);
301 1 xxx X
302 2 yyy Y
*************** UPDATE only t1 SET b = b WHERE f_leak(b)
*** 423,431 ****
UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
RESET SESSION AUTHORIZATION;
SET row_security TO OFF;
! SELECT * FROM t1;
SET SESSION AUTHORIZATION rls_regress_user1;
SET row_security TO ON;
--- 427,471 ----
UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
+ -- updates with from clause
+ EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3
+ WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
+
+ UPDATE t2 SET b=t2.b FROM t3
+ WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
+
+ EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2
+ WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
+
+ UPDATE t1 SET b=t1.b FROM t2
+ WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
+
+ EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1
+ WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
+
+ UPDATE t2 SET b=t2.b FROM t1
+ WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
+
+ -- updates with from clause self join
+ EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
+ WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
+ AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
+
+ UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
+ WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
+ AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
+
+ EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
+ WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
+ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
+
+ UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
+ WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
+ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
+
RESET SESSION AUTHORIZATION;
SET row_security TO OFF;
! SELECT * FROM t1 ORDER BY a,b;
SET SESSION AUTHORIZATION rls_regress_user1;
SET row_security TO ON;
*************** DELETE FROM only t1 WHERE f_leak(b) RETU
*** 436,441 ****
--- 476,514 ----
DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1;
--
+ -- S.b. view on top of Row-level security
+ --
+ SET SESSION AUTHORIZATION rls_regress_user0;
+ CREATE TABLE b1 (a int, b text);
+ INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
+
+ CREATE POLICY p1 ON b1 USING (a % 2 = 0);
+ ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
+ GRANT ALL ON b1 TO rls_regress_user1;
+
+ SET SESSION AUTHORIZATION rls_regress_user1;
+ CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
+ GRANT ALL ON bv1 TO rls_regress_user2;
+
+ SET SESSION AUTHORIZATION rls_regress_user2;
+
+ EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
+ SELECT * FROM bv1 WHERE f_leak(b);
+
+ INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO
+ INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check
+ INSERT INTO bv1 VALUES (12, 'xxx'); -- ok
+
+ EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
+ UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
+
+ EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
+ DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
+
+ SET SESSION AUTHORIZATION rls_regress_user0;
+ SELECT * FROM b1;
+
+ --
-- ROLE/GROUP
--
SET SESSION AUTHORIZATION rls_regress_user0;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
