1-- 2-- SELECT_VIEWS 3-- test the views defined in CREATE_VIEWS 4-- 5 6SELECT * FROM street; 7 8SELECT name, #thepath FROM iexit ORDER BY 1, 2; 9 10SELECT * FROM toyemp WHERE name = 'sharon'; 11 12-- 13-- Test for Leaky view scenario 14-- 15CREATE ROLE regress_alice; 16 17CREATE FUNCTION f_leak (text) 18 RETURNS bool LANGUAGE 'plpgsql' COST 0.0000001 19 AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END'; 20 21CREATE TABLE customer ( 22 cid int primary key, 23 name text not null, 24 tel text, 25 passwd text 26); 27 28CREATE TABLE credit_card ( 29 cid int references customer(cid), 30 cnum text, 31 climit int 32); 33 34CREATE TABLE credit_usage ( 35 cid int references customer(cid), 36 ymd date, 37 usage int 38); 39 40INSERT INTO customer 41 VALUES (101, 'regress_alice', '+81-12-3456-7890', 'passwd123'), 42 (102, 'regress_bob', '+01-234-567-8901', 'beafsteak'), 43 (103, 'regress_eve', '+49-8765-43210', 'hamburger'); 44INSERT INTO credit_card 45 VALUES (101, '1111-2222-3333-4444', 4000), 46 (102, '5555-6666-7777-8888', 3000), 47 (103, '9801-2345-6789-0123', 2000); 48INSERT INTO credit_usage 49 VALUES (101, '2011-09-15', 120), 50 (101, '2011-10-05', 90), 51 (101, '2011-10-18', 110), 52 (101, '2011-10-21', 200), 53 (101, '2011-11-10', 80), 54 (102, '2011-09-22', 300), 55 (102, '2011-10-12', 120), 56 (102, '2011-10-28', 200), 57 (103, '2011-10-15', 480); 58 59CREATE VIEW my_property_normal AS 60 SELECT * FROM customer WHERE name = current_user; 61CREATE VIEW my_property_secure WITH (security_barrier) AS 62 SELECT * FROM customer WHERE name = current_user; 63 64CREATE VIEW my_credit_card_normal AS 65 SELECT * FROM customer l NATURAL JOIN credit_card r 66 WHERE l.name = current_user; 67CREATE VIEW my_credit_card_secure WITH (security_barrier) AS 68 SELECT * FROM customer l NATURAL JOIN credit_card r 69 WHERE l.name = current_user; 70 71CREATE VIEW my_credit_card_usage_normal AS 72 SELECT * FROM my_credit_card_secure l NATURAL JOIN credit_usage r; 73CREATE VIEW my_credit_card_usage_secure WITH (security_barrier) AS 74 SELECT * FROM my_credit_card_secure l NATURAL JOIN credit_usage r; 75 76GRANT SELECT ON my_property_normal TO public; 77GRANT SELECT ON my_property_secure TO public; 78GRANT SELECT ON my_credit_card_normal TO public; 79GRANT SELECT ON my_credit_card_secure TO public; 80GRANT SELECT ON my_credit_card_usage_normal TO public; 81GRANT SELECT ON my_credit_card_usage_secure TO public; 82 83-- 84-- Run leaky view scenarios 85-- 86SET SESSION AUTHORIZATION regress_alice; 87 88-- 89-- scenario: if a qualifier with tiny-cost is given, it shall be launched 90-- prior to the security policy of the view. 91-- 92SELECT * FROM my_property_normal WHERE f_leak(passwd); 93EXPLAIN (COSTS OFF) SELECT * FROM my_property_normal WHERE f_leak(passwd); 94 95SELECT * FROM my_property_secure WHERE f_leak(passwd); 96EXPLAIN (COSTS OFF) SELECT * FROM my_property_secure WHERE f_leak(passwd); 97 98-- 99-- scenario: qualifiers can be pushed down if they contain leaky functions, 100-- provided they aren't passed data from inside the view. 101-- 102SELECT * FROM my_property_normal v 103 WHERE f_leak('passwd') AND f_leak(passwd); 104EXPLAIN (COSTS OFF) SELECT * FROM my_property_normal v 105 WHERE f_leak('passwd') AND f_leak(passwd); 106 107SELECT * FROM my_property_secure v 108 WHERE f_leak('passwd') AND f_leak(passwd); 109EXPLAIN (COSTS OFF) SELECT * FROM my_property_secure v 110 WHERE f_leak('passwd') AND f_leak(passwd); 111 112-- 113-- scenario: if a qualifier references only one-side of a particular join- 114-- tree, it shall be distributed to the most deep scan plan as 115-- possible as we can. 116-- 117SELECT * FROM my_credit_card_normal WHERE f_leak(cnum); 118EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_normal WHERE f_leak(cnum); 119 120SELECT * FROM my_credit_card_secure WHERE f_leak(cnum); 121EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_secure WHERE f_leak(cnum); 122 123-- 124-- scenario: an external qualifier can be pushed-down by in-front-of the 125-- views with "security_barrier" attribute, except for operators 126-- implemented with leakproof functions. 127-- 128SELECT * FROM my_credit_card_usage_normal 129 WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01'; 130EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_normal 131 WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01'; 132 133SELECT * FROM my_credit_card_usage_secure 134 WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01'; 135EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_secure 136 WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01'; 137 138-- 139-- Test for the case when security_barrier gets changed between rewriter 140-- and planner stage. 141-- 142PREPARE p1 AS SELECT * FROM my_property_normal WHERE f_leak(passwd); 143PREPARE p2 AS SELECT * FROM my_property_secure WHERE f_leak(passwd); 144EXECUTE p1; 145EXECUTE p2; 146RESET SESSION AUTHORIZATION; 147ALTER VIEW my_property_normal SET (security_barrier=true); 148ALTER VIEW my_property_secure SET (security_barrier=false); 149SET SESSION AUTHORIZATION regress_alice; 150EXECUTE p1; -- To be perform as a view with security-barrier 151EXECUTE p2; -- To be perform as a view without security-barrier 152 153-- Cleanup. 154RESET SESSION AUTHORIZATION; 155DROP ROLE regress_alice; 156