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?

----------
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
>
>