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

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

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