Heikki Linnakangas wrote:
Brendan O'Shea wrote:
There appears to be a bug in the way that permissions are determined for
views that contain "UNION ALL" in their definition.
There is a simple test case to reproduce the bug.
1) As a superuser create the following objects:
CREATE ROLE test_perm LOGIN PASSWORD 'test_perm';
CREATE OR REPLACE VIEW public.simple_select AS SELECT 1;
CREATE OR REPLACE VIEW public.union_all AS SELECT 1 UNION ALL SELECT 2;
2) Now log in as the test_perm user and run the following SQL:
select * from public.simple_select;
select * from public.union_all;
The first SQL statement correctly produces an error, but the second
statement will return results with no error, it should instead generate a
permission error.
Hmm, looks like pull_up_subqueries somehow loses the range table entry
referring the original view. It's reproducible on PG version 8.2 and
higher, 8.1 seems to work. I'll dig deeper tomorrow, unless someone else
beats me to it.
Yep, pull_up_simple_union_all neglects to copy those range table
references that are not directly referenced in any of the UNION ALL
subqueries.
Attached is a patch for this. I have to go to sleep now, but will commit
this tomorrow unless someone comes up with a better idea. One thing that
I'm not totally happy about is that I'm using list_union, which uses
O(n^2) algorithm.
Thanks for the report!
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/optimizer/prep/prepjointree.c
===================================================================
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/optimizer/prep/prepjointree.c,v
retrieving revision 1.44
diff -c -r1.44 prepjointree.c
*** src/backend/optimizer/prep/prepjointree.c 4 Oct 2006 00:29:54 -0000 1.44
--- src/backend/optimizer/prep/prepjointree.c 11 Aug 2008 19:54:30 -0000
***************
*** 487,492 ****
--- 487,500 ----
pull_up_union_leaf_queries(subquery->setOperations, root, varno, subquery);
/*
+ * pull_up_union_leaf_queries copied those range table entries that were
+ * referenced from one of the UNION ALL subqueries, but we need to make
+ * sure that all non-referenced ones are copied as well. They are needed
+ * for permission checks during executor startup (ExecCheckRTPerms).
+ */
+ root->parse->rtable = list_union(root->parse->rtable, subquery->rtable);
+
+ /*
* Mark the parent as an append relation.
*/
rte->inh = true;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
