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

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

Because such case will end up with an error, I'm afraid the additional code
may not work much.

Regards;

----------
Koichi Suzuki

2013/5/9 Nikhil Sontakke <ni...@st...>

>
>
> Hmmm... This is not a simple issue to fix.   Maybe we should document that
>> the access privilege to the view must be the same as those of the base
>> tables.
>>
>> Any other inputs to this?
>>
>>
> Or in XC, we should add code to check that the grants to the view should
> also be the same as underlying referenced tables.
>
> Regards,
> Nikhils
>
>
>
>> ----------
>> Koichi Suzuki
>>
>>
>> 2013/5/9 Abbas Butt <abb...@en...>
>>
>>>  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>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Learn Graph Databases - Download FREE O'Reilly Book
>>> "Graph Databases" is the definitive new guide to graph databases and
>>> their applications. This 200-page book is written by three acclaimed
>>> leaders in the field. The early access version is available now.
>>> Download your free book today! http://p.sf.net/sfu/neotech_d2d_may
>>>
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>>
>>
>>
>> ------------------------------------------------------------------------------
>> Learn Graph Databases - Download FREE O'Reilly Book
>> "Graph Databases" is the definitive new guide to graph databases and
>> their applications. This 200-page book is written by three acclaimed
>> leaders in the field. The early access version is available now.
>> Download your free book today! http://p.sf.net/sfu/neotech_d2d_may
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>
>
> --
> StormDB - http://www.stormdb.com
> The Database Cloud
>