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

Commitbe400cd

Browse files
committed
Add regression tests for INSERT/UPDATE+RETURNING
This adds regressions tests which are specific to INSERT+RETURNING andUPDATE+RETURNING to ensure that the SELECT policies are added asWithCheckOptions (and should therefore throw an error when the policy isviolated).Per suggestion from Andres.Back-patch to 9.5 as the prior commit was.
1 parent5976097 commitbe400cd

File tree

2 files changed

+132
-0
lines changed

2 files changed

+132
-0
lines changed

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

Lines changed: 69 additions & 0 deletions
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

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1423,6 +1423,69 @@ TABLE r2;
14231423
DROPTABLE r2;
14241424
DROPTABLE 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+
CREATETABLEr1 (aint);
1433+
1434+
CREATE POLICY p1ON r1 FORSELECT USING (false);
1435+
CREATE POLICY p2ON r1 FOR INSERT WITHCHECK (true);
1436+
ALTERTABLE r1 ENABLE ROW LEVEL SECURITY;
1437+
ALTERTABLE r1 FORCE ROW LEVEL SECURITY;
1438+
1439+
-- Works fine
1440+
INSERT INTO r1VALUES (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 r1VALUES (10), (20) RETURNING*;
1453+
1454+
DROPTABLE 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+
CREATETABLEr1 (aint);
1463+
1464+
CREATE POLICY p1ON r1 FORSELECT USING (a<20);
1465+
CREATE POLICY p2ON r1 FORUPDATE USING (a<20) WITHCHECK (true);
1466+
INSERT INTO r1VALUES (10);
1467+
ALTERTABLE r1 ENABLE ROW LEVEL SECURITY;
1468+
ALTERTABLE r1 FORCE ROW LEVEL SECURITY;
1469+
1470+
-- Works fine
1471+
UPDATE r1SET 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 r1SET a=10;
1478+
1479+
-- Verify row reset
1480+
TABLE r1;
1481+
1482+
SET row_security=on;
1483+
1484+
-- Error
1485+
UPDATE r1SET a=30 RETURNING*;
1486+
1487+
DROPTABLE r1;
1488+
14261489
--
14271490
-- Clean up objects
14281491
--

0 commit comments

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