pgsql-v9.2-fix-leaky-view-part-3.v1.patch
application/octet-stream
Filename: pgsql-v9.2-fix-leaky-view-part-3.v1.patch
Type: application/octet-stream
Part: 2
Message:
[v9.2] Fix Leaky View Problem
doc/src/sgml/rules.sgml | 66 ++++++++++++
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/plan/initsplan.c | 151 ++++++++++++++++++++++++++--
src/backend/optimizer/prep/prepjointree.c | 7 ++
src/backend/optimizer/util/clauses.c | 120 ++++++++++++++++++++++
src/backend/utils/cache/lsyscache.c | 19 ++++
src/include/nodes/primnodes.h | 1 +
src/include/optimizer/clauses.h | 1 +
src/include/utils/lsyscache.h | 1 +
src/test/regress/expected/select_views.out | 109 ++++++++++++++++++++
src/test/regress/sql/select_views.sql | 51 ++++++++++
14 files changed, 523 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 1b06519..ef3d2c3 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -1856,6 +1856,72 @@ SELECT * FROM phone_number WHERE tricky(person, phone);
</para>
<para>
+ In addition, you might be able to leak contents of invisible tuples
+ using the following scenario:
+<programlisting>
+CREATE VIEW your_credit AS
+ SELECT a.rolname, c.number, c.expire
+ FROM pg_authid a JOIN credit_cards c ON a.oid = c.id
+ WHERE a.rolname = getpgusername();
+</programlisting>
+ This view also might seem secure, since any <command>SELECT</command>
+ from <literal>your_credit</literal> shall be rewritten into a
+ <command>SELECT</command> from the join of <literal>pg_authid</>
+ and <literal>credit_cards</> with a qualifier that filters out
+ any entries except for your credit card number.
+
+ But if a user appends his or her own functions that references
+ only columns come from a particular side of join loop, the optimizer
+ shall relocate this qualifier into the most deep level, independent
+ from cost estimation of the function.
+<programlisting>
+postgres=> SELECT * FROM your_credit WHERE tricky(number, expire);
+NOTICE: 1111-2222-3333-4444 => Jan-01
+NOTICE: 5555-6666-7777-8888 => Feb-02
+NOTICE: 1234-5678-9012-3456 => Mar-03
+ rolname | number | expire
+---------+---------------------+--------
+ alice | 5555-6666-7777-8888 | Feb-02
+(1 row)
+</programlisting>
+ The reason is obvious from the result of <command>EXPLAIN</command>.
+<programlisting>
+postgres=> EXPLAIN SELECT * FROM your_credit WHERE tricky(number, expire);
+ QUERY PLAN
+------------------------------------------------------------------------
+ Hash Join (cost=1.03..20.38 rows=1 width=128)
+ Hash Cond: (c.id = a.oid)
+ -> Seq Scan on credit_cards c (cost=0.00..18.30 rows=277 width=68)
+ Filter: tricky(number, expire)
+ -> Hash (cost=1.01..1.01 rows=1 width=68)
+ -> Seq Scan on pg_authid a (cost=0.00..1.01 rows=1 width=68)
+ Filter: (rolname = getpgusername())
+(7 rows)
+</programlisting>
+ The supplied <function>tricky</function> only references
+ <literal>number</literal> and <literal>expire</literal> columns,
+ however, the qualifier to filter invisible tuples performs on
+ the scan of <literal>pg_authid</literal>.
+ Then, since the optimizer tries to minimize the number of tuples
+ being joined, the supplied qualifer got attached on the scan of
+ <literal>credit_cards</literal>.
+ In the result, it allows <function>tricky</function> to reference
+ contents of the <literal>credit_cards</literal> table.
+</para>
+<para>
+ The <literal>security_berrier</literal> option of views enables
+ to prevent both of the scenarios, instead of a bit performance
+ trade-off. If and when a particular view is defined with
+ <literal>security_berrier=TRUE</literal>, any exogenetic qualifiers
+ cannot be pushed-down except for a limited number of trusted
+ operators being transformed into index scan, even if the supplied
+ qualifier references only one-side of relation joins. In addition,
+ underlying qualifiers of security barrier view shall be launched
+ earlier than others, even if sub-queries are pulled-up and
+ qualifiers got merged due to the optimization.
+</para>
+
+<para>
Similar considerations apply to update rules. In the examples of
the previous section, the owner of the tables in the example
database could grant the privileges <literal>SELECT</>,
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 9fe6474..6c9c303 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1796,6 +1796,7 @@ _copyFromExpr(FromExpr *from)
COPY_NODE_FIELD(fromlist);
COPY_NODE_FIELD(quals);
+ COPY_SCALAR_FIELD(security_barrier);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index ac7a645..64fdffd 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -780,6 +780,7 @@ _equalFromExpr(FromExpr *a, FromExpr *b)
{
COMPARE_NODE_FIELD(fromlist);
COMPARE_NODE_FIELD(quals);
+ COMPARE_SCALAR_FIELD(security_barrier);
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index c860eae..3fa4eae 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1453,6 +1453,7 @@ _outFromExpr(StringInfo str, FromExpr *node)
WRITE_NODE_FIELD(fromlist);
WRITE_NODE_FIELD(quals);
+ WRITE_BOOL_FIELD(security_barrier);
}
/*****************************************************************************
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 21ec851..5af54d2 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1169,6 +1169,7 @@ _readFromExpr(void)
READ_NODE_FIELD(fromlist);
READ_NODE_FIELD(quals);
+ READ_BOOL_FIELD(security_barrier);
READ_DONE();
}
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 9cfc56e..ec7cfc2 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -46,7 +46,9 @@ static SpecialJoinInfo *make_outerjoininfo(PlannerInfo *root,
Relids left_rels, Relids right_rels,
Relids inner_join_rels,
JoinType jointype, List *clause);
-static void distribute_qual_to_rels(PlannerInfo *root, Node *clause,
+static void distribute_qual_to_rels(PlannerInfo *root,
+ Node *clause,
+ Node *jtnode,
bool is_deduced,
bool below_outer_join,
JoinType jointype,
@@ -58,7 +60,7 @@ static bool check_outerjoin_delay(PlannerInfo *root, Relids *relids_p,
static bool check_redundant_nullability_qual(PlannerInfo *root, Node *clause);
static void check_mergejoinable(RestrictInfo *restrictinfo);
static void check_hashjoinable(RestrictInfo *restrictinfo);
-
+static bool check_security_barrier(Expr *restrictinfo, Node *jtnode);
/*****************************************************************************
*
@@ -351,7 +353,7 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
{
Node *qual = (Node *) lfirst(l);
- distribute_qual_to_rels(root, qual,
+ distribute_qual_to_rels(root, qual, jtnode,
false, below_outer_join, JOIN_INNER,
*qualscope, NULL, NULL);
}
@@ -474,7 +476,7 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
{
Node *qual = (Node *) lfirst(l);
- distribute_qual_to_rels(root, qual,
+ distribute_qual_to_rels(root, qual, jtnode,
false, below_outer_join, j->jointype,
*qualscope,
ojscope, nonnullable_rels);
@@ -775,6 +777,7 @@ make_outerjoininfo(PlannerInfo *root,
* EquivalenceClasses.
*
* 'clause': the qual clause to be distributed
+ * 'jtnode' : either FromExpr or JoinExpr above 'clause' being chained
* 'is_deduced': TRUE if the qual came from implied-equality deduction
* 'below_outer_join': TRUE if the qual is from a JOIN/ON that is below the
* nullable side of a higher-level outer join
@@ -795,7 +798,9 @@ make_outerjoininfo(PlannerInfo *root,
* all and only those special joins that are syntactically below this qual.
*/
static void
-distribute_qual_to_rels(PlannerInfo *root, Node *clause,
+distribute_qual_to_rels(PlannerInfo *root,
+ Node *clause,
+ Node *jtnode,
bool is_deduced,
bool below_outer_join,
JoinType jointype,
@@ -809,6 +814,7 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
bool pseudoconstant = false;
bool maybe_equivalence;
bool maybe_outer_join;
+ bool barrier_touched;
Relids nullable_relids;
RestrictInfo *restrictinfo;
@@ -1024,6 +1030,10 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
pseudoconstant,
relids,
nullable_relids);
+ /*
+ * Check variable references across security barrier
+ */
+ barrier_touched = check_security_barrier((Expr *)restrictinfo, jtnode);
/*
* If it's a join clause (either naturally, or because delayed by
@@ -1089,7 +1099,8 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
{
if (maybe_equivalence)
{
- if (process_equivalence(root, restrictinfo, below_outer_join))
+ if (!barrier_touched &&
+ process_equivalence(root, restrictinfo, below_outer_join))
return;
/* EC rejected it, so set left_ec/right_ec the hard way ... */
initialize_mergeclause_eclasses(root, restrictinfo);
@@ -1425,7 +1436,7 @@ process_implied_equality(PlannerInfo *root,
/*
* Push the new clause into all the appropriate restrictinfo lists.
*/
- distribute_qual_to_rels(root, (Node *) clause,
+ distribute_qual_to_rels(root, (Node *) clause, NULL,
true, below_outer_join, JOIN_INNER,
qualscope, NULL, NULL);
}
@@ -1481,6 +1492,132 @@ build_implied_join_equality(Oid opno,
return restrictinfo;
}
+/*
+ * walk_security_barrier
+ *
+ * is a helper routine of check_security_barrier
+ */
+static Relids
+walk_security_barrier(Node *jtnode, bool top_level,
+ Relids *relids, bool *touched)
+{
+ if (IsA(jtnode, RangeTblRef))
+ {
+ int varno = ((RangeTblRef *) jtnode)->rtindex;
+
+ return bms_make_singleton(varno);
+ }
+ else if (IsA(jtnode, FromExpr))
+ {
+ FromExpr *f = (FromExpr *)jtnode;
+ ListCell *l;
+ Relids temp;
+ Relids results = NULL;
+
+ foreach(l, f->fromlist)
+ {
+ temp = walk_security_barrier(lfirst(l), false, relids, touched);
+
+ results = bms_join(results, temp);
+ }
+
+ /*
+ * If this walker routine goes across security barrier view,
+ * we check whether the supplied relids references relations
+ * across security barrier. If it referenced them, we expand
+ * relids into whole of the security barrier to prevent
+ * unexpected push-down of the qualifier.
+ */
+ if (f->security_barrier && !top_level)
+ {
+ if (bms_overlap(*relids, results))
+ {
+ *relids = bms_union(*relids, results);
+ *touched = true;
+ }
+ }
+ return results;
+ }
+ else if (IsA(jtnode, JoinExpr))
+ {
+ JoinExpr *j = (JoinExpr *)jtnode;
+ Relids relids_r;
+ Relids relids_l;
+
+ relids_r = walk_security_barrier(j->rarg, false, relids, touched);
+ relids_l = walk_security_barrier(j->larg, false, relids, touched);
+
+ return bms_join(relids_r, relids_l);
+ }
+ elog(ERROR, "unexpected node type: %d", (int) nodeTag(jtnode));
+ return NULL; /* for compiler quiet */
+}
+
+/*
+ * check_security_barrier
+ *
+ * It checks whether the supplied RestrictInfo tried to reference
+ * relations from outside of security barrier; excepr for the clause
+ * that does not contain any leakable functions.
+ * If it touches any relations inside of security barriers from outside
+ * of them, Relids of RestrictInfo shall be expanded and returns true.
+ * It enables to prevent unexpected pushing-down of qualifiers that
+ * potentially cause security breakage.
+ */
+static bool
+check_security_barrier(Expr *restrictinfo, Node *jtnode)
+{
+ bool result = false;
+
+ if (!jtnode)
+ return false;
+
+ if (IsA(restrictinfo, RestrictInfo))
+ {
+ RestrictInfo *rinfo = (RestrictInfo *)restrictinfo;
+
+ if (!contain_leakable_functions((Node *)rinfo->clause))
+ return false;
+
+ if (rinfo->orclause &&
+ check_security_barrier((Expr *)rinfo->orclause, jtnode))
+ result = true;
+
+ if (rinfo->left_relids)
+ walk_security_barrier(jtnode, true,
+ &rinfo->left_relids, &result);
+ if (rinfo->right_relids)
+ walk_security_barrier(jtnode, true,
+ &rinfo->right_relids, &result);
+ if (rinfo->clause_relids)
+ walk_security_barrier(jtnode, true,
+ &rinfo->clause_relids, &result);
+ if (rinfo->required_relids)
+ walk_security_barrier(jtnode, true,
+ &rinfo->required_relids, &result);
+ }
+ else if (IsA(restrictinfo, BoolExpr))
+ {
+ BoolExpr *b = (BoolExpr *)restrictinfo;
+
+ if (check_security_barrier((RestrictInfo *)b->args, jtnode))
+ result = true;
+ }
+ else if (IsA(restrictinfo, List))
+ {
+ ListCell *l;
+
+ foreach (l, (List *)restrictinfo)
+ {
+ if (check_security_barrier(lfirst(l), jtnode))
+ result = true;
+ }
+ }
+ else
+ elog(ERROR, "unexpected node tag (%d)", (int)nodeTag(restrictinfo));
+
+ return result;
+}
/*****************************************************************************
*
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 63a52f2..1d53bc6 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -705,6 +705,13 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
pull_up_subqueries(subroot, (Node *) subquery->jointree, NULL, NULL);
/*
+ * If and when the sub-query was originally defined as a view with
+ * "security_barrier" option, we need to mark this FromExpr as a
+ * security barrier to prevent unexpected distribution of qualifiers.
+ */
+ ((FromExpr *)subquery->jointree)->security_barrier = rte->security_barrier;
+
+ /*
* Now we must recheck whether the subquery is still simple enough to pull
* up. If not, abandon processing it.
*
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 91c8429..db5e3aa 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -92,6 +92,7 @@ static bool contain_subplans_walker(Node *node, void *context);
static bool contain_mutable_functions_walker(Node *node, void *context);
static bool contain_volatile_functions_walker(Node *node, void *context);
static bool contain_nonstrict_functions_walker(Node *node, void *context);
+static bool contain_leakable_functions_walker(Node *node, void *context);
static Relids find_nonnullable_rels_walker(Node *node, bool top_level);
static List *find_nonnullable_vars_walker(Node *node, bool top_level);
static bool is_strict_saop(ScalarArrayOpExpr *expr, bool falseOK);
@@ -1129,6 +1130,125 @@ contain_nonstrict_functions_walker(Node *node, void *context)
context);
}
+/*****************************************************************************
+ * Check clauses for leakable functions
+ *****************************************************************************/
+
+/*
+ * contain_leakable_functions
+ * Recursively search for leakable functions within a clause.
+ *
+ * Returns true if any function call with side-effect is found.
+ * ie, some type-input/output handler will raise an error when given
+ * argument does not have a valid format.
+ *
+ * When people uses views for row-level security purpose, given qualifiers
+ * come from outside of the view should not be pushed down into the views
+ * if they have side-effect, because contents of tuples to be filtered out
+ * may be leaked via side-effectable functions within the qualifiers.
+ *
+ * The idea here is that the planner restrains a part of optimization when
+ * the qualifiers contains leakable functions.
+ * This routine checks whether the given clause contains leakable functions,
+ * or not. If we return false, then the clause is clean.
+ */
+bool
+contain_leakable_functions(Node *clause)
+{
+ return contain_leakable_functions_walker(clause, NULL);
+}
+
+static bool
+contain_leakable_functions_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, FuncExpr))
+ {
+ FuncExpr *expr = (FuncExpr *) node;
+
+ if (get_func_leakable(expr->funcid))
+ return true;
+ }
+ else if (IsA(node, OpExpr))
+ {
+ OpExpr *expr = (OpExpr *) node;
+
+ set_opfuncid(expr);
+ if (get_func_leakable(expr->opfuncid))
+ return true;
+ }
+ else if (IsA(node, DistinctExpr))
+ {
+ DistinctExpr *expr = (DistinctExpr *) node;
+
+ set_opfuncid((OpExpr *) expr);
+ if (get_func_leakable(expr->opfuncid))
+ return true;
+ }
+ else if (IsA(node, ScalarArrayOpExpr))
+ {
+ ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
+
+ set_sa_opfuncid(expr);
+ if (get_func_leakable(expr->opfuncid))
+ return true;
+ }
+ else if (IsA(node, CoerceViaIO))
+ {
+ CoerceViaIO *expr = (CoerceViaIO *) node;
+ Oid funcid;
+ Oid ioparam;
+ bool varlena;
+
+ getTypeInputInfo(exprType((Node *)expr->arg), &funcid, &ioparam);
+ if (get_func_leakable(funcid))
+ return true;
+
+ getTypeOutputInfo(expr->resulttype, &funcid, &varlena);
+ if (get_func_leakable(funcid))
+ return true;
+ }
+ else if (IsA(node, ArrayCoerceExpr))
+ {
+ ArrayCoerceExpr *expr = (ArrayCoerceExpr *) node;
+ Oid funcid;
+ Oid ioparam;
+ bool varlena;
+
+ getTypeInputInfo(exprType((Node *)expr->arg), &funcid, &ioparam);
+ if (get_func_leakable(funcid))
+ return true;
+ getTypeOutputInfo(expr->resulttype, &funcid, &varlena);
+ if (get_func_leakable(funcid))
+ return true;
+ }
+ else if (IsA(node, NullIfExpr))
+ {
+ NullIfExpr *expr = (NullIfExpr *) node;
+
+ set_opfuncid((OpExpr *) expr); /* rely on struct equivalence */
+ if (get_func_leakable(expr->opfuncid))
+ return true;
+ }
+ else if (IsA(node, RowCompareExpr))
+ {
+ /* RowCompare probably can't have volatile ops, but check anyway */
+ RowCompareExpr *rcexpr = (RowCompareExpr *) node;
+ ListCell *opid;
+
+ foreach(opid, rcexpr->opnos)
+ {
+ Oid funcid = get_opcode(lfirst_oid(opid));
+
+ if (get_func_leakable(funcid))
+ return true;
+ }
+ }
+ return expression_tree_walker(node, contain_leakable_functions_walker,
+ context);
+}
/*
* find_nonnullable_rels
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 326f1ee..e2bdcce 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1534,6 +1534,25 @@ func_volatile(Oid funcid)
}
/*
+ * get_func_unleakable
+ * Given procedure id, return the function's unleakable field.
+ */
+bool
+get_func_leakable(Oid funcid)
+{
+ HeapTuple tp;
+ bool result;
+
+ tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for function %u", funcid);
+
+ result = ((Form_pg_proc) GETSTRUCT(tp))->proleaky;
+ ReleaseSysCache(tp);
+ return result;
+}
+
+/*
* get_func_cost
* Given procedure id, return the function's procost field.
*/
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index b626386..274495f 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1265,6 +1265,7 @@ typedef struct FromExpr
NodeTag type;
List *fromlist; /* List of join subtrees */
Node *quals; /* qualifiers on join, if any */
+ bool security_barrier; /* Come from security-barrier view? */
} FromExpr;
#endif /* PRIMNODES_H */
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 4cef7fa..7dc3657 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -61,6 +61,7 @@ extern bool contain_subplans(Node *clause);
extern bool contain_mutable_functions(Node *clause);
extern bool contain_volatile_functions(Node *clause);
extern bool contain_nonstrict_functions(Node *clause);
+extern bool contain_leakable_functions(Node *clause);
extern Relids find_nonnullable_rels(Node *clause);
extern List *find_nonnullable_vars(Node *clause);
extern List *find_forced_null_vars(Node *clause);
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index f4490ad..b3a8e14 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -93,6 +93,7 @@ extern Oid get_func_signature(Oid funcid, Oid **argtypes, int *nargs);
extern bool get_func_retset(Oid funcid);
extern bool func_strict(Oid funcid);
extern char func_volatile(Oid funcid);
+extern bool get_func_leakable(Oid funcid);
extern float4 get_func_cost(Oid funcid);
extern float4 get_func_rows(Oid funcid);
extern Oid get_relname_relid(const char *relname, Oid relnamespace);
diff --git a/src/test/regress/expected/select_views.out b/src/test/regress/expected/select_views.out
index d1179d2..54c5d3a 100644
--- a/src/test/regress/expected/select_views.out
+++ b/src/test/regress/expected/select_views.out
@@ -1269,6 +1269,38 @@ CREATE VIEW your_credit_secure WITH (security_barrier) AS
SELECT * FROM credit_cards WHERE name = getpgusername();
GRANT SELECT ON your_credit_normal TO public;
GRANT SELECT ON your_credit_secure TO public;
+CREATE TABLE employee (
+ eid int primary key,
+ ename text,
+ etitle text
+);
+NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "employee_pkey" for table "employee"
+CREATE TABLE salary (
+ eid int references employee(eid),
+ salary int,
+ ymd date
+);
+CREATE INDEX salary_ymd on salary (ymd);
+INSERT INTO employee (eid, ename, etitle)
+ VALUES (100, 'alice', 'staff'),
+ (110, 'bob', 'manager'),
+ (120, 'eve', 'chief');
+INSERT INTO salary (eid, salary, ymd)
+ VALUES (100, 2000, '2011-06-01'),
+ (100, 2025, '2011-07-01'),
+ (110, 2500, '2011-06-01'),
+ (110, 2400, '2011-07-01'),
+ (120, 2200, '2011-07-01');
+CREATE VIEW my_salary_normal AS
+ SELECT ename,etitle,salary,ymd
+ FROM employee e JOIN salary s ON s.eid = s.eid
+ WHERE ename = getpgusername();
+CREATE VIEW my_salary_secure WITH (security_barrier) AS
+ SELECT ename,etitle,salary,ymd
+ FROM employee e JOIN salary s ON e.eid = s.eid
+ WHERE ename = getpgusername();
+GRANT SELECT ON my_salary_normal TO public;
+GRANT SELECT ON my_salary_secure TO public;
-- run leaky view
SET SESSION AUTHORIZATION alice;
SELECT * FROM your_credit_normal WHERE f_leak(number,expired);
@@ -1301,6 +1333,78 @@ EXPLAIN (COSTS OFF) SELECT * FROM your_credit_secure WHERE f_leak(number,expired
Filter: ((name = (getpgusername())::text) AND f_leak(number, expired))
(2 rows)
+SELECT * FROM my_salary_normal
+ WHERE f_leak(ymd::text,salary::text);
+NOTICE: 06-01-2011 => 2000
+NOTICE: 07-01-2011 => 2025
+NOTICE: 06-01-2011 => 2500
+NOTICE: 07-01-2011 => 2400
+NOTICE: 07-01-2011 => 2200
+ ename | etitle | salary | ymd
+-------+--------+--------+------------
+ alice | staff | 2000 | 06-01-2011
+ alice | staff | 2025 | 07-01-2011
+ alice | staff | 2500 | 06-01-2011
+ alice | staff | 2400 | 07-01-2011
+ alice | staff | 2200 | 07-01-2011
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM my_salary_normal
+ WHERE f_leak(ymd::text,salary::text);
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Nested Loop
+ -> Seq Scan on employee e
+ Filter: (ename = (getpgusername())::text)
+ -> Materialize
+ -> Seq Scan on salary s
+ Filter: ((eid = eid) AND f_leak((ymd)::text, (salary)::text))
+(6 rows)
+
+SELECT * FROM my_salary_secure
+ WHERE f_leak(ymd::text,salary::text);
+NOTICE: 06-01-2011 => 2000
+NOTICE: 07-01-2011 => 2025
+ ename | etitle | salary | ymd
+-------+--------+--------+------------
+ alice | staff | 2000 | 06-01-2011
+ alice | staff | 2025 | 07-01-2011
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM my_salary_secure
+ WHERE f_leak(ymd::text,salary::text);
+ QUERY PLAN
+---------------------------------------------------------
+ Hash Join
+ Hash Cond: (s.eid = e.eid)
+ Join Filter: f_leak((s.ymd)::text, (s.salary)::text)
+ -> Seq Scan on salary s
+ -> Hash
+ -> Seq Scan on employee e
+ Filter: (ename = (getpgusername())::text)
+(7 rows)
+
+SELECT * FROM my_salary_secure
+ WHERE f_leak(ymd::text,salary::text) AND ymd = '2011-06-01';
+NOTICE: 06-01-2011 => 2000
+ ename | etitle | salary | ymd
+-------+--------+--------+------------
+ alice | staff | 2000 | 06-01-2011
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM my_salary_secure
+ WHERE f_leak(ymd::text,salary::text) AND ymd = '2011-06-01';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (s.eid = e.eid)
+ Join Filter: ((s.ymd = '06-01-2011'::date) AND f_leak((s.ymd)::text, (s.salary)::text))
+ -> Seq Scan on salary s
+ -> Hash
+ -> Seq Scan on employee e
+ Filter: (ename = (getpgusername())::text)
+(7 rows)
+
\c -
-- cleanups
DROP ROLE IF EXISTS alice;
@@ -1310,3 +1414,8 @@ DROP TABLE IF EXISTS credit_cards CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to view your_credit_normal
drop cascades to view your_credit_secure
+DROP TABLE IF EXISTS employee CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to view my_salary_normal
+drop cascades to constraint salary_eid_fkey on table salary
+drop cascades to view my_salary_secure
diff --git a/src/test/regress/sql/select_views.sql b/src/test/regress/sql/select_views.sql
index dc49601..42fb2db 100644
--- a/src/test/regress/sql/select_views.sql
+++ b/src/test/regress/sql/select_views.sql
@@ -33,6 +33,41 @@ CREATE VIEW your_credit_secure WITH (security_barrier) AS
GRANT SELECT ON your_credit_normal TO public;
GRANT SELECT ON your_credit_secure TO public;
+
+CREATE TABLE employee (
+ eid int primary key,
+ ename text,
+ etitle text
+);
+CREATE TABLE salary (
+ eid int references employee(eid),
+ salary int,
+ ymd date
+);
+CREATE INDEX salary_ymd on salary (ymd);
+
+INSERT INTO employee (eid, ename, etitle)
+ VALUES (100, 'alice', 'staff'),
+ (110, 'bob', 'manager'),
+ (120, 'eve', 'chief');
+INSERT INTO salary (eid, salary, ymd)
+ VALUES (100, 2000, '2011-06-01'),
+ (100, 2025, '2011-07-01'),
+ (110, 2500, '2011-06-01'),
+ (110, 2400, '2011-07-01'),
+ (120, 2200, '2011-07-01');
+CREATE VIEW my_salary_normal AS
+ SELECT ename,etitle,salary,ymd
+ FROM employee e JOIN salary s ON s.eid = s.eid
+ WHERE ename = getpgusername();
+CREATE VIEW my_salary_secure WITH (security_barrier) AS
+ SELECT ename,etitle,salary,ymd
+ FROM employee e JOIN salary s ON e.eid = s.eid
+ WHERE ename = getpgusername();
+
+GRANT SELECT ON my_salary_normal TO public;
+GRANT SELECT ON my_salary_secure TO public;
+
-- run leaky view
SET SESSION AUTHORIZATION alice;
@@ -42,8 +77,24 @@ EXPLAIN (COSTS OFF) SELECT * FROM your_credit_normal WHERE f_leak(number,expired
SELECT * FROM your_credit_secure WHERE f_leak(number,expired);
EXPLAIN (COSTS OFF) SELECT * FROM your_credit_secure WHERE f_leak(number,expired);
+SELECT * FROM my_salary_normal
+ WHERE f_leak(ymd::text,salary::text);
+EXPLAIN (COSTS OFF) SELECT * FROM my_salary_normal
+ WHERE f_leak(ymd::text,salary::text);
+
+SELECT * FROM my_salary_secure
+ WHERE f_leak(ymd::text,salary::text);
+EXPLAIN (COSTS OFF) SELECT * FROM my_salary_secure
+ WHERE f_leak(ymd::text,salary::text);
+
+SELECT * FROM my_salary_secure
+ WHERE f_leak(ymd::text,salary::text) AND ymd = '2011-06-01';
+EXPLAIN (COSTS OFF) SELECT * FROM my_salary_secure
+ WHERE f_leak(ymd::text,salary::text) AND ymd = '2011-06-01';
+
\c -
-- cleanups
DROP ROLE IF EXISTS alice;
DROP FUNCTION IF EXISTS f_leak(text);
DROP TABLE IF EXISTS credit_cards CASCADE;
+DROP TABLE IF EXISTS employee CASCADE;