Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit504f0c5

Browse files
committed
Regression tests for security_barrier views.
KaiGai Kohei
1 parent4b496a3 commit504f0c5

File tree

3 files changed

+636
-0
lines changed

3 files changed

+636
-0
lines changed

‎src/test/regress/expected/select_views.out

Lines changed: 255 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1247,3 +1247,258 @@ SELECT * FROM toyemp WHERE name = 'sharon';
12471247
sharon | 25 | (15,12) | 12000
12481248
(1 row)
12491249

1250+
--
1251+
-- Test for Leaky view scenario
1252+
--
1253+
CREATE USER alice;
1254+
CREATE FUNCTION f_leak (text)
1255+
RETURNS bool LANGUAGE 'plpgsql' COST 0.0000001
1256+
AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
1257+
CREATE TABLE customer (
1258+
cid int primary key,
1259+
name text not null,
1260+
tel text,
1261+
passwdtext
1262+
);
1263+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer"
1264+
CREATE TABLE credit_card (
1265+
cid int references customer(cid),
1266+
cnum text,
1267+
climit int
1268+
);
1269+
CREATE TABLE credit_usage (
1270+
cid int references customer(cid),
1271+
ymd date,
1272+
usage int
1273+
);
1274+
INSERT INTO customer
1275+
VALUES (101, 'alice', '+81-12-3456-7890', 'passwd123'),
1276+
(102, 'bob', '+01-234-567-8901', 'beafsteak'),
1277+
(103, 'eve', '+49-8765-43210', 'hamburger');
1278+
INSERT INTO credit_card
1279+
VALUES (101, '1111-2222-3333-4444', 4000),
1280+
(102, '5555-6666-7777-8888', 3000),
1281+
(103, '9801-2345-6789-0123', 2000);
1282+
INSERT INTO credit_usage
1283+
VALUES (101, '2011-09-15', 120),
1284+
(101, '2011-10-05', 90),
1285+
(101, '2011-10-18', 110),
1286+
(101, '2011-10-21', 200),
1287+
(101, '2011-11-10', 80),
1288+
(102, '2011-09-22', 300),
1289+
(102, '2011-10-12', 120),
1290+
(102, '2011-10-28', 200),
1291+
(103, '2011-10-15', 480);
1292+
CREATE VIEW my_property_normal AS
1293+
SELECT * FROM customer WHERE name = current_user;
1294+
CREATE VIEW my_property_secure WITH (security_barrier) AS
1295+
SELECT * FROM customer WHERE name = current_user;
1296+
CREATE VIEW my_credit_card_normal AS
1297+
SELECT * FROM customer l NATURAL JOIN credit_card r
1298+
WHERE l.name = current_user;
1299+
CREATE VIEW my_credit_card_secure WITH (security_barrier) AS
1300+
SELECT * FROM customer l NATURAL JOIN credit_card r
1301+
WHERE l.name = current_user;
1302+
CREATE VIEW my_credit_card_usage_normal AS
1303+
SELECT * FROM my_credit_card_secure l NATURAL JOIN credit_usage r;
1304+
CREATE VIEW my_credit_card_usage_secure WITH (security_barrier) AS
1305+
SELECT * FROM my_credit_card_secure l NATURAL JOIN credit_usage r;
1306+
GRANT SELECT ON my_property_normal TO public;
1307+
GRANT SELECT ON my_property_secure TO public;
1308+
GRANT SELECT ON my_credit_card_normal TO public;
1309+
GRANT SELECT ON my_credit_card_secure TO public;
1310+
GRANT SELECT ON my_credit_card_usage_normal TO public;
1311+
GRANT SELECT ON my_credit_card_usage_secure TO public;
1312+
--
1313+
-- Run leaky view scenarios
1314+
--
1315+
SET SESSION AUTHORIZATION alice;
1316+
--
1317+
-- scenario: if a qualifier with tiny-cost is given, it shall be launched
1318+
-- prior to the security policy of the view.
1319+
--
1320+
SELECT * FROM my_property_normal WHERE f_leak(passwd);
1321+
NOTICE: f_leak => passwd123
1322+
NOTICE: f_leak => beafsteak
1323+
NOTICE: f_leak => hamburger
1324+
cid | name | tel | passwd
1325+
-----+-------+------------------+-----------
1326+
101 | alice | +81-12-3456-7890 | passwd123
1327+
(1 row)
1328+
1329+
EXPLAIN (COSTS OFF) SELECT * FROM my_property_normal WHERE f_leak(passwd);
1330+
QUERY PLAN
1331+
------------------------------------------------------------------
1332+
Seq Scan on customer
1333+
Filter: (f_leak(passwd) AND (name = ("current_user"())::text))
1334+
(2 rows)
1335+
1336+
SELECT * FROM my_property_secure WHERE f_leak(passwd);
1337+
NOTICE: f_leak => passwd123
1338+
cid | name | tel | passwd
1339+
-----+-------+------------------+-----------
1340+
101 | alice | +81-12-3456-7890 | passwd123
1341+
(1 row)
1342+
1343+
EXPLAIN (COSTS OFF) SELECT * FROM my_property_secure WHERE f_leak(passwd);
1344+
QUERY PLAN
1345+
---------------------------------------------------
1346+
Subquery Scan on my_property_secure
1347+
Filter: f_leak(my_property_secure.passwd)
1348+
-> Seq Scan on customer
1349+
Filter: (name = ("current_user"())::text)
1350+
(4 rows)
1351+
1352+
--
1353+
-- scenario: if a qualifier references only one-side of a particular join-
1354+
-- tree, it shall be distributed to the most deep scan plan as
1355+
-- possible as we can.
1356+
--
1357+
SELECT * FROM my_credit_card_normal WHERE f_leak(cnum);
1358+
NOTICE: f_leak => 1111-2222-3333-4444
1359+
NOTICE: f_leak => 5555-6666-7777-8888
1360+
NOTICE: f_leak => 9801-2345-6789-0123
1361+
cid | name | tel | passwd | cnum | climit
1362+
-----+-------+------------------+-----------+---------------------+--------
1363+
101 | alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000
1364+
(1 row)
1365+
1366+
EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_normal WHERE f_leak(cnum);
1367+
QUERY PLAN
1368+
---------------------------------------------------------
1369+
Hash Join
1370+
Hash Cond: (r.cid = l.cid)
1371+
-> Seq Scan on credit_card r
1372+
Filter: f_leak(cnum)
1373+
-> Hash
1374+
-> Seq Scan on customer l
1375+
Filter: (name = ("current_user"())::text)
1376+
(7 rows)
1377+
1378+
SELECT * FROM my_credit_card_secure WHERE f_leak(cnum);
1379+
NOTICE: f_leak => 1111-2222-3333-4444
1380+
cid | name | tel | passwd | cnum | climit
1381+
-----+-------+------------------+-----------+---------------------+--------
1382+
101 | alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000
1383+
(1 row)
1384+
1385+
EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_secure WHERE f_leak(cnum);
1386+
QUERY PLAN
1387+
---------------------------------------------------------------
1388+
Subquery Scan on my_credit_card_secure
1389+
Filter: f_leak(my_credit_card_secure.cnum)
1390+
-> Hash Join
1391+
Hash Cond: (r.cid = l.cid)
1392+
-> Seq Scan on credit_card r
1393+
-> Hash
1394+
-> Seq Scan on customer l
1395+
Filter: (name = ("current_user"())::text)
1396+
(8 rows)
1397+
1398+
--
1399+
-- scenario: an external qualifier can be pushed-down by in-front-of the
1400+
-- views with "security_barrier" attribute
1401+
--
1402+
SELECT * FROM my_credit_card_usage_normal
1403+
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
1404+
NOTICE: f_leak => 1111-2222-3333-4444
1405+
cid | name | tel | passwd | cnum | climit | ymd | usage
1406+
-----+-------+------------------+-----------+---------------------+--------+------------+-------
1407+
101 | alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-05-2011 | 90
1408+
101 | alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-18-2011 | 110
1409+
101 | alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-21-2011 | 200
1410+
(3 rows)
1411+
1412+
EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_normal
1413+
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
1414+
QUERY PLAN
1415+
------------------------------------------------------------------------------
1416+
Nested Loop
1417+
Join Filter: (l.cid = r.cid)
1418+
-> Seq Scan on credit_usage r
1419+
Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date))
1420+
-> Materialize
1421+
-> Subquery Scan on l
1422+
Filter: f_leak(l.cnum)
1423+
-> Hash Join
1424+
Hash Cond: (r.cid = l.cid)
1425+
-> Seq Scan on credit_card r
1426+
-> Hash
1427+
-> Seq Scan on customer l
1428+
Filter: (name = ("current_user"())::text)
1429+
(13 rows)
1430+
1431+
SELECT * FROM my_credit_card_usage_secure
1432+
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
1433+
NOTICE: f_leak => 1111-2222-3333-4444
1434+
NOTICE: f_leak => 1111-2222-3333-4444
1435+
NOTICE: f_leak => 1111-2222-3333-4444
1436+
NOTICE: f_leak => 1111-2222-3333-4444
1437+
NOTICE: f_leak => 1111-2222-3333-4444
1438+
cid | name | tel | passwd | cnum | climit | ymd | usage
1439+
-----+-------+------------------+-----------+---------------------+--------+------------+-------
1440+
101 | alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-05-2011 | 90
1441+
101 | alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-18-2011 | 110
1442+
101 | alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-21-2011 | 200
1443+
(3 rows)
1444+
1445+
EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_secure
1446+
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
1447+
QUERY PLAN
1448+
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1449+
Subquery Scan on my_credit_card_usage_secure
1450+
Filter: (f_leak(my_credit_card_usage_secure.cnum) AND (my_credit_card_usage_secure.ymd >= '10-01-2011'::date) AND (my_credit_card_usage_secure.ymd < '11-01-2011'::date))
1451+
-> Hash Join
1452+
Hash Cond: (r.cid = l.cid)
1453+
-> Seq Scan on credit_usage r
1454+
-> Hash
1455+
-> Hash Join
1456+
Hash Cond: (r.cid = l.cid)
1457+
-> Seq Scan on credit_card r
1458+
-> Hash
1459+
-> Seq Scan on customer l
1460+
Filter: (name = ("current_user"())::text)
1461+
(12 rows)
1462+
1463+
--
1464+
-- Test for the case when security_barrier gets changed between rewriter
1465+
-- and planner stage.
1466+
--
1467+
PREPARE p1 AS SELECT * FROM my_property_normal WHERE f_leak(passwd);
1468+
PREPARE p2 AS SELECT * FROM my_property_secure WHERE f_leak(passwd);
1469+
EXECUTE p1;
1470+
NOTICE: f_leak => passwd123
1471+
NOTICE: f_leak => beafsteak
1472+
NOTICE: f_leak => hamburger
1473+
cid | name | tel | passwd
1474+
-----+-------+------------------+-----------
1475+
101 | alice | +81-12-3456-7890 | passwd123
1476+
(1 row)
1477+
1478+
EXECUTE p2;
1479+
NOTICE: f_leak => passwd123
1480+
cid | name | tel | passwd
1481+
-----+-------+------------------+-----------
1482+
101 | alice | +81-12-3456-7890 | passwd123
1483+
(1 row)
1484+
1485+
RESET SESSION AUTHORIZATION;
1486+
ALTER VIEW my_property_normal SET (security_barrier=true);
1487+
ALTER VIEW my_property_secure SET (security_barrier=false);
1488+
SET SESSION AUTHORIZATION alice;
1489+
EXECUTE p1;-- To be perform as a view with security-barrier
1490+
NOTICE: f_leak => passwd123
1491+
cid | name | tel | passwd
1492+
-----+-------+------------------+-----------
1493+
101 | alice | +81-12-3456-7890 | passwd123
1494+
(1 row)
1495+
1496+
EXECUTE p2;-- To be perform as a view without security-barrier
1497+
NOTICE: f_leak => passwd123
1498+
NOTICE: f_leak => beafsteak
1499+
NOTICE: f_leak => hamburger
1500+
cid | name | tel | passwd
1501+
-----+-------+------------------+-----------
1502+
101 | alice | +81-12-3456-7890 | passwd123
1503+
(1 row)
1504+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp