Re: [Postgres-xc-developers] Fix for test case select_views

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Consider this test cases, which has been taken form select_views.sql and
simplified to pin point the issue.

CREATE ROLE regress_alice;
CREATE TABLE customer(cid int primary key, name text not null, tel text,
passwd text);

INSERT INTO customer VALUES
 (101, 'regress_alice', '+81-12-3456-7890', 'passwd123'),
 (102, 'regress_bob',   '+01-234-567-8901', 'beafsteak');

CREATE VIEW my_property_normal AS SELECT * FROM customer;

GRANT SELECT ON my_property_normal TO public;

SET SESSION AUTHORIZATION regress_alice;

On PG
=====

test=> EXPLAIN VERBOSE SELECT * FROM my_property_normal;
                              QUERY PLAN
----------------------------------------------------------------------
 Seq Scan on public.customer  (cost=0.00..16.20 rows=620 width=100)
   Output: customer.cid, customer.name, customer.tel, customer.passwd
(2 rows)

test=>
test=> SELECT * FROM my_property_normal;
 cid |     name      |       tel        |  passwd
-----+---------------+------------------+-----------
 101 | regress_alice | +81-12-3456-7890 | passwd123
 102 | regress_bob   | +01-234-567-8901 | beafsteak
(2 rows)

On XC
=====

test=> EXPLAIN VERBOSE SELECT * FROM my_property_normal;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: my_property_normal.cid, my_property_normal.name,
my_property_normal.tel, my_property_normal.passwd
   Node/s: data_node_1, data_node_2, data_node_3, data_node_4
   Remote query: SELECT cid, name, tel, passwd FROM (SELECT customer.cid,
customer.name, customer.tel, customer.passwd FROM customer)
my_property_normal
(4 rows)

test=>
test=> SELECT * FROM my_property_normal;
ERROR:  permission denied for relation customer

Please refer to the documentation at
http://www.postgresql.org/docs/9.2/static/rules-privileges.html
It says
"*Relations that are used due to rules get checked against the privileges
of the rule owner, not the user invoking the rule. This means that a user
only needs the required privileges for the tables/views that he names
explicitly in his queries.*"

It is because of this fact that the query
SELECT * FROM my_property_normal
works fine when issued on PG.
In XC on the other hand, since views are not even created on datanodes,
they (datanodes) are not even aware that the query is actually a select
form a view, let alone enforcing the above statement in documentation.

What strategy are we going to fix this problem?
Are we going to fix it or do some changes in documentation?
Regards
Abbas

On Tue, May 7, 2013 at 11:38 AM, Abbas Butt <abb...@en...>wrote:

> ok, sure.
>
>
> On Tue, May 7, 2013 at 11:37 AM, Ashutosh Bapat <
> ash...@en...> wrote:
>
>> BTW, fix the bug in separate patch and fix expected outputs in separate
>> patch.
>>
>>
>> On Tue, May 7, 2013 at 11:54 AM, Ashutosh Bapat <
>> ash...@en...> wrote:
>>
>>> Can you please fix the bug then?
>>>
>>>
>>> On Tue, May 7, 2013 at 10:25 AM, Abbas Butt <abb...@en...
>>> > wrote:
>>>
>>>>
>>>>
>>>> On Tue, Apr 30, 2013 at 4:57 PM, Ashutosh Bapat <
>>>> ash...@en...> wrote:
>>>>
>>>>> Hi Abbas,
>>>>> I see that the EXPLAIN commands are changed to include the XC specific
>>>>> options. Since the original test is using capital letters everywhere, it's
>>>>> better to maintain that style.
>>>>>
>>>>
>>>> Done.
>>>>
>>>>
>>>>>
>>>>> Please make sure that the changes you have done (explain output)
>>>>> reflect in all the alt. expected output files.
>>>>>
>>>>
>>>> Done.
>>>>
>>>>
>>>>>  Can you please see if we can eliminate the alternate expected output
>>>>> files as well? What are the reasons we have to maintain those?
>>>>>
>>>>
>>>> select_views.out and select_view_1.out are maintained by vanilla PG. We
>>>> have added select_views_2.out and It can be removed, however I noticed that
>>>> the test case that was added (Leaky View Scenario) is failing and needs a
>>>> fix.
>>>>
>>>>
>>>>>
>>>>>
>>>>> On Wed, Apr 24, 2013 at 10:59 PM, Abbas Butt <
>>>>> abb...@en...> wrote:
>>>>>
>>>>>> Hi,
>>>>>> The test was failing because some more tests are added in sql file.
>>>>>>
>>>>>> --
>>>>>> *Abbas*
>>>>>>  Architect
>>>>>>
>>>>>> Ph: 92.334.5100153
>>>>>>  Skype ID: gabbasb
>>>>>> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>>>>>> *
>>>>>> Follow us on Twitter*
>>>>>> @EnterpriseDB
>>>>>>
>>>>>> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> Try New Relic Now & We'll Send You this Cool Shirt
>>>>>> New Relic is the only SaaS-based application performance monitoring
>>>>>> service
>>>>>> that delivers powerful full stack analytics. Optimize and monitor your
>>>>>> browser, app, & servers with just a few lines of code. Try New Relic
>>>>>> and get this awesome Nerd Life shirt!
>>>>>> http://p.sf.net/sfu/newrelic_d2d_apr
>>>>>> _______________________________________________
>>>>>> Postgres-xc-developers mailing list
>>>>>> Pos...@li...
>>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Best Wishes,
>>>>> Ashutosh Bapat
>>>>> EntepriseDB Corporation
>>>>> The Postgres Database Company
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> --
>>>> *Abbas*
>>>>  Architect
>>>>
>>>> Ph: 92.334.5100153
>>>>  Skype ID: gabbasb
>>>> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>>>> *
>>>> Follow us on Twitter*
>>>> @EnterpriseDB
>>>>
>>>> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>>>>
>>>
>>>
>>>
>>> --
>>> Best Wishes,
>>> Ashutosh Bapat
>>> EntepriseDB Corporation
>>> The Postgres Database Company
>>>
>>
>>
>>
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EntepriseDB Corporation
>> The Postgres Database Company
>>
>
>
>
> --
> --
> *Abbas*
>  Architect
>
> Ph: 92.334.5100153
>  Skype ID: gabbasb
> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
> *
> Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>

-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co
<http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
*
Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars,
whitepapers<http://www.enterprisedb.com/resources-community>and
more<http://www.enterprisedb.com/resources-community>