Skip site navigation (1) Skip section navigation (2)

Re: BUG #4350: 'select' acess given to views containing "union all" even though user has no grants

From: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
To: "Brendan O'Shea" <boshea(at)akamai(dot)com>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #4350: 'select' acess given to views containing "union all" even though user has no grants
Date: 2008-08-11 20:07:50
Message-ID: 48A09C16.6020803@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Attachment: fix-union-all-view-perm-1.patch
Description: text/x-diff (1014 bytes)

In response to

Responses

pgsql-bugs by date

Next:From: Lawrence CohanDate: 2008-08-11 21:28:22
Subject: BUG #4351: Full text search performance
Previous:From: Heikki LinnakangasDate: 2008-08-11 19:19:38
Subject: Re: BUG #4350: 'select' acess given to views containing "union all" even though user has no grants

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group