Skip to content

Commit f9bb9c0

Browse files
sfrostsfrost
committed
Add regression tests for INSERT/UPDATE+RETURNING
This adds regressions tests which are specific to INSERT+RETURNING and UPDATE+RETURNING to ensure that the SELECT policies are added as WithCheckOptions (and should therefore throw an error when the policy is violated). Per suggestion from Andres. Back-patch to 9.5 as the prior commit was.
1 parent 7bed97d commit f9bb9c0

File tree

2 files changed

+132
-0
lines changed

2 files changed

+132
-0
lines changed

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

+69
Original file line numberDiff line numberDiff line change
@@ -3158,6 +3158,75 @@ TABLE r2;
31583158
DROP TABLE r2;
31593159
DROP TABLE r1;
31603160
--
3161+
-- Test INSERT+RETURNING applies SELECT policies as
3162+
-- WithCheckOptions (meaning an error is thrown)
3163+
--
3164+
SET SESSION AUTHORIZATION rls_regress_user0;
3165+
SET row_security = on;
3166+
CREATE TABLE r1 (a int);
3167+
CREATE POLICY p1 ON r1 FOR SELECT USING (false);
3168+
CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true);
3169+
ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3170+
ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3171+
-- Works fine
3172+
INSERT INTO r1 VALUES (10), (20);
3173+
-- No error, but no rows
3174+
TABLE r1;
3175+
a
3176+
---
3177+
(0 rows)
3178+
3179+
SET row_security = off;
3180+
-- Rows shown now
3181+
TABLE r1;
3182+
a
3183+
----
3184+
10
3185+
20
3186+
(2 rows)
3187+
3188+
SET row_security = on;
3189+
-- Error
3190+
INSERT INTO r1 VALUES (10), (20) RETURNING *;
3191+
ERROR: new row violates row level security policy for "r1"
3192+
DROP TABLE r1;
3193+
--
3194+
-- Test UPDATE+RETURNING applies SELECT policies as
3195+
-- WithCheckOptions (meaning an error is thrown)
3196+
--
3197+
SET SESSION AUTHORIZATION rls_regress_user0;
3198+
SET row_security = on;
3199+
CREATE TABLE r1 (a int);
3200+
CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
3201+
CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true);
3202+
INSERT INTO r1 VALUES (10);
3203+
ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3204+
ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3205+
-- Works fine
3206+
UPDATE r1 SET a = 30;
3207+
-- Show updated rows
3208+
SET row_security = off;
3209+
TABLE r1;
3210+
a
3211+
----
3212+
30
3213+
(1 row)
3214+
3215+
-- reset value in r1 for test with RETURNING
3216+
UPDATE r1 SET a = 10;
3217+
-- Verify row reset
3218+
TABLE r1;
3219+
a
3220+
----
3221+
10
3222+
(1 row)
3223+
3224+
SET row_security = on;
3225+
-- Error
3226+
UPDATE r1 SET a = 30 RETURNING *;
3227+
ERROR: new row violates row level security policy for "r1"
3228+
DROP TABLE r1;
3229+
--
31613230
-- Clean up objects
31623231
--
31633232
RESET SESSION AUTHORIZATION;

‎src/test/regress/sql/rowsecurity.sql

+63
Original file line numberDiff line numberDiff line change
@@ -1423,6 +1423,69 @@ TABLE r2;
14231423
DROP TABLE r2;
14241424
DROP TABLE r1;
14251425

1426+
--
1427+
-- Test INSERT+RETURNING applies SELECT policies as
1428+
-- WithCheckOptions (meaning an error is thrown)
1429+
--
1430+
SET SESSION AUTHORIZATION rls_regress_user0;
1431+
SET row_security = on;
1432+
CREATE TABLE r1 (a int);
1433+
1434+
CREATE POLICY p1 ON r1 FOR SELECT USING (false);
1435+
CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true);
1436+
ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1437+
ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1438+
1439+
-- Works fine
1440+
INSERT INTO r1 VALUES (10), (20);
1441+
1442+
-- No error, but no rows
1443+
TABLE r1;
1444+
1445+
SET row_security = off;
1446+
-- Rows shown now
1447+
TABLE r1;
1448+
1449+
SET row_security = on;
1450+
1451+
-- Error
1452+
INSERT INTO r1 VALUES (10), (20) RETURNING *;
1453+
1454+
DROP TABLE r1;
1455+
1456+
--
1457+
-- Test UPDATE+RETURNING applies SELECT policies as
1458+
-- WithCheckOptions (meaning an error is thrown)
1459+
--
1460+
SET SESSION AUTHORIZATION rls_regress_user0;
1461+
SET row_security = on;
1462+
CREATE TABLE r1 (a int);
1463+
1464+
CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
1465+
CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true);
1466+
INSERT INTO r1 VALUES (10);
1467+
ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1468+
ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1469+
1470+
-- Works fine
1471+
UPDATE r1 SET a = 30;
1472+
1473+
-- Show updated rows
1474+
SET row_security = off;
1475+
TABLE r1;
1476+
-- reset value in r1 for test with RETURNING
1477+
UPDATE r1 SET a = 10;
1478+
1479+
-- Verify row reset
1480+
TABLE r1;
1481+
1482+
SET row_security = on;
1483+
1484+
-- Error
1485+
UPDATE r1 SET a = 30 RETURNING *;
1486+
1487+
DROP TABLE r1;
1488+
14261489
--
14271490
-- Clean up objects
14281491
--

0 commit comments

Comments
 (0)