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