#325 Failure of test select_views, problem with leaky scenarios

[Michael Paquier]

bug found during merge with 9.2.
select_views has a set of new tests for leaky scenarios.

For more or less all the queries involved there, it is not possible to get permissions to the relation.
Here is the example of 1 problem.
+ SELECT * FROM my_credit_card_usage_normal
+ WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
+ ERROR: permission denied for relation customer

[Michael Paquier]

The origin of the problem is as follows: a GRANT on a view is only sent to Coordinators as the view is just created on them. This can be easily reproduced with the following queries.
create table aa (a int, b int);
create view aa_v as select a,b from aa;
GRANT SELECT ON aa_v TO public;
CREATE ROLE foo;
SET SESSION AUTHORIZATION foo;
SELECT * from aa_v; --error on permission

-- Clean up
SET SESSION AUTHORIZATION DEFAULT;
DROP TABLE aa CASCADE;

[Koichi Suzuki]

Q4

[Koichi Suzuki]

This fails because the pooler uses only one user at the time. This is architectural bug and very complicated to fix.

[Abbas Butt]

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.