| blob | fd5654df35ebb5f7e43b8021bb6d11df02797691 |
1 --
2 -- Test of Row-level security feature
3 --
4 -- Clean up in case a prior regression run failed
5 -- Suppress NOTICE messages when users/groups don't exist
6 SET client_min_messages TO 'warning';
7 DROP USER IF EXISTS regress_rls_alice;
8 DROP USER IF EXISTS regress_rls_bob;
9 DROP USER IF EXISTS regress_rls_carol;
10 DROP USER IF EXISTS regress_rls_dave;
11 DROP USER IF EXISTS regress_rls_exempt_user;
12 DROP ROLE IF EXISTS regress_rls_group1;
13 DROP ROLE IF EXISTS regress_rls_group2;
14 DROP SCHEMA IF EXISTS regress_rls_schema CASCADE;
15 RESET client_min_messages;
16 -- initial setup
17 CREATE USER regress_rls_alice NOLOGIN;
18 CREATE USER regress_rls_bob NOLOGIN;
19 CREATE USER regress_rls_carol NOLOGIN;
20 CREATE USER regress_rls_dave NOLOGIN;
21 CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
22 CREATE ROLE regress_rls_group1 NOLOGIN;
23 CREATE ROLE regress_rls_group2 NOLOGIN;
24 GRANT regress_rls_group1 TO regress_rls_bob;
25 GRANT regress_rls_group2 TO regress_rls_carol;
26 CREATE SCHEMA regress_rls_schema;
27 GRANT ALL ON SCHEMA regress_rls_schema to public;
28 SET search_path = regress_rls_schema;
29 -- setup of malicious function
30 CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
31 COST 0.0000001 LANGUAGE plpgsql
32 AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
33 GRANT EXECUTE ON FUNCTION f_leak(text) TO public;
34 -- BASIC Row-Level Security Scenario
35 SET SESSION AUTHORIZATION regress_rls_alice;
36 CREATE TABLE uaccount (
37 pguser name primary key,
38 seclv int
39 );
40 GRANT SELECT ON uaccount TO public;
41 INSERT INTO uaccount VALUES
42 ('regress_rls_alice', 99),
43 ('regress_rls_bob', 1),
44 ('regress_rls_carol', 2),
45 ('regress_rls_dave', 3);
46 CREATE TABLE category (
47 cid int primary key,
48 cname text
49 );
50 GRANT ALL ON category TO public;
51 INSERT INTO category VALUES
52 (11, 'novel'),
53 (22, 'science fiction'),
54 (33, 'technology'),
55 (44, 'manga');
56 CREATE TABLE document (
57 did int primary key,
58 cid int references category(cid),
59 dlevel int not null,
60 dauthor name,
61 dtitle text
62 );
63 GRANT ALL ON document TO public;
64 INSERT INTO document VALUES
65 ( 1, 11, 1, 'regress_rls_bob', 'my first novel'),
66 ( 2, 11, 2, 'regress_rls_bob', 'my second novel'),
67 ( 3, 22, 2, 'regress_rls_bob', 'my science fiction'),
68 ( 4, 44, 1, 'regress_rls_bob', 'my first manga'),
69 ( 5, 44, 2, 'regress_rls_bob', 'my second manga'),
70 ( 6, 22, 1, 'regress_rls_carol', 'great science fiction'),
71 ( 7, 33, 2, 'regress_rls_carol', 'great technology book'),
72 ( 8, 44, 1, 'regress_rls_carol', 'great manga'),
73 ( 9, 22, 1, 'regress_rls_dave', 'awesome science fiction'),
74 (10, 33, 2, 'regress_rls_dave', 'awesome technology book');
75 ALTER TABLE document ENABLE ROW LEVEL SECURITY;
76 -- user's security level must be higher than or equal to document's
77 CREATE POLICY p1 ON document AS PERMISSIVE
78 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
79 -- try to create a policy of bogus type
80 CREATE POLICY p1 ON document AS UGLY
81 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
82 ERROR: unrecognized row security option "ugly"
83 LINE 1: CREATE POLICY p1 ON document AS UGLY
84 ^
85 HINT: Only PERMISSIVE or RESTRICTIVE policies are supported currently.
86 -- but Dave isn't allowed to anything at cid 50 or above
87 -- this is to make sure that we sort the policies by name first
88 -- when applying WITH CHECK, a later INSERT by Dave should fail due
89 -- to p1r first
90 CREATE POLICY p2r ON document AS RESTRICTIVE TO regress_rls_dave
91 USING (cid <> 44 AND cid < 50);
92 -- and Dave isn't allowed to see manga documents
93 CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
94 USING (cid <> 44);
95 \dp
96 Access privileges
97 Schema | Name | Type | Access privileges | Column privileges | Policies
98 --------------------+----------+-------+----------------------------------------------+-------------------+--------------------------------------------
99 regress_rls_schema | category | table | regress_rls_alice=arwdDxtm/regress_rls_alice+| |
100 | | | =arwdDxtm/regress_rls_alice | |
101 regress_rls_schema | document | table | regress_rls_alice=arwdDxtm/regress_rls_alice+| | p1: +
102 | | | =arwdDxtm/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv +
103 | | | | | FROM uaccount +
104 | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+
105 | | | | | p2r (RESTRICTIVE): +
106 | | | | | (u): ((cid <> 44) AND (cid < 50)) +
107 | | | | | to: regress_rls_dave +
108 | | | | | p1r (RESTRICTIVE): +
109 | | | | | (u): (cid <> 44) +
110 | | | | | to: regress_rls_dave
111 regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxtm/regress_rls_alice+| |
112 | | | =r/regress_rls_alice | |
113 (3 rows)
115 \d document
116 Table "regress_rls_schema.document"
117 Column | Type | Collation | Nullable | Default
118 ---------+---------+-----------+----------+---------
119 did | integer | | not null |
120 cid | integer | | |
121 dlevel | integer | | not null |
122 dauthor | name | | |
123 dtitle | text | | |
124 Indexes:
125 "document_pkey" PRIMARY KEY, btree (did)
126 Foreign-key constraints:
127 "document_cid_fkey" FOREIGN KEY (cid) REFERENCES category(cid)
128 Policies:
129 POLICY "p1"
130 USING ((dlevel <= ( SELECT uaccount.seclv
131 FROM uaccount
132 WHERE (uaccount.pguser = CURRENT_USER))))
133 POLICY "p1r" AS RESTRICTIVE
134 TO regress_rls_dave
135 USING ((cid <> 44))
136 POLICY "p2r" AS RESTRICTIVE
137 TO regress_rls_dave
138 USING (((cid <> 44) AND (cid < 50)))
140 SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname;
141 schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
142 --------------------+-----------+------------+-------------+--------------------+-----+--------------------------------------------+------------
143 regress_rls_schema | document | p1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
144 | | | | | | FROM uaccount +|
145 | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
146 regress_rls_schema | document | p1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 44) |
147 regress_rls_schema | document | p2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 44) AND (cid < 50)) |
148 (3 rows)
150 -- viewpoint from regress_rls_bob
151 SET SESSION AUTHORIZATION regress_rls_bob;
152 SET row_security TO ON;
153 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
154 NOTICE: f_leak => my first novel
155 NOTICE: f_leak => my first manga
156 NOTICE: f_leak => great science fiction
157 NOTICE: f_leak => great manga
158 NOTICE: f_leak => awesome science fiction
159 did | cid | dlevel | dauthor | dtitle
160 -----+-----+--------+-------------------+-------------------------
161 1 | 11 | 1 | regress_rls_bob | my first novel
162 4 | 44 | 1 | regress_rls_bob | my first manga
163 6 | 22 | 1 | regress_rls_carol | great science fiction
164 8 | 44 | 1 | regress_rls_carol | great manga
165 9 | 22 | 1 | regress_rls_dave | awesome science fiction
166 (5 rows)
168 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
169 NOTICE: f_leak => my first novel
170 NOTICE: f_leak => my first manga
171 NOTICE: f_leak => great science fiction
172 NOTICE: f_leak => great manga
173 NOTICE: f_leak => awesome science fiction
174 cid | did | dlevel | dauthor | dtitle | cname
175 -----+-----+--------+-------------------+-------------------------+-----------------
176 11 | 1 | 1 | regress_rls_bob | my first novel | novel
177 44 | 4 | 1 | regress_rls_bob | my first manga | manga
178 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
179 44 | 8 | 1 | regress_rls_carol | great manga | manga
180 22 | 9 | 1 | regress_rls_dave | awesome science fiction | science fiction
181 (5 rows)
183 -- try a sampled version
184 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
185 WHERE f_leak(dtitle) ORDER BY did;
186 NOTICE: f_leak => my first manga
187 NOTICE: f_leak => great science fiction
188 NOTICE: f_leak => great manga
189 NOTICE: f_leak => awesome science fiction
190 did | cid | dlevel | dauthor | dtitle
191 -----+-----+--------+-------------------+-------------------------
192 4 | 44 | 1 | regress_rls_bob | my first manga
193 6 | 22 | 1 | regress_rls_carol | great science fiction
194 8 | 44 | 1 | regress_rls_carol | great manga
195 9 | 22 | 1 | regress_rls_dave | awesome science fiction
196 (4 rows)
198 -- viewpoint from regress_rls_carol
199 SET SESSION AUTHORIZATION regress_rls_carol;
200 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
201 NOTICE: f_leak => my first novel
202 NOTICE: f_leak => my second novel
203 NOTICE: f_leak => my science fiction
204 NOTICE: f_leak => my first manga
205 NOTICE: f_leak => my second manga
206 NOTICE: f_leak => great science fiction
207 NOTICE: f_leak => great technology book
208 NOTICE: f_leak => great manga
209 NOTICE: f_leak => awesome science fiction
210 NOTICE: f_leak => awesome technology book
211 did | cid | dlevel | dauthor | dtitle
212 -----+-----+--------+-------------------+-------------------------
213 1 | 11 | 1 | regress_rls_bob | my first novel
214 2 | 11 | 2 | regress_rls_bob | my second novel
215 3 | 22 | 2 | regress_rls_bob | my science fiction
216 4 | 44 | 1 | regress_rls_bob | my first manga
217 5 | 44 | 2 | regress_rls_bob | my second manga
218 6 | 22 | 1 | regress_rls_carol | great science fiction
219 7 | 33 | 2 | regress_rls_carol | great technology book
220 8 | 44 | 1 | regress_rls_carol | great manga
221 9 | 22 | 1 | regress_rls_dave | awesome science fiction
222 10 | 33 | 2 | regress_rls_dave | awesome technology book
223 (10 rows)
225 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
226 NOTICE: f_leak => my first novel
227 NOTICE: f_leak => my second novel
228 NOTICE: f_leak => my science fiction
229 NOTICE: f_leak => my first manga
230 NOTICE: f_leak => my second manga
231 NOTICE: f_leak => great science fiction
232 NOTICE: f_leak => great technology book
233 NOTICE: f_leak => great manga
234 NOTICE: f_leak => awesome science fiction
235 NOTICE: f_leak => awesome technology book
236 cid | did | dlevel | dauthor | dtitle | cname
237 -----+-----+--------+-------------------+-------------------------+-----------------
238 11 | 1 | 1 | regress_rls_bob | my first novel | novel
239 11 | 2 | 2 | regress_rls_bob | my second novel | novel
240 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction
241 44 | 4 | 1 | regress_rls_bob | my first manga | manga
242 44 | 5 | 2 | regress_rls_bob | my second manga | manga
243 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
244 33 | 7 | 2 | regress_rls_carol | great technology book | technology
245 44 | 8 | 1 | regress_rls_carol | great manga | manga
246 22 | 9 | 1 | regress_rls_dave | awesome science fiction | science fiction
247 33 | 10 | 2 | regress_rls_dave | awesome technology book | technology
248 (10 rows)
250 -- try a sampled version
251 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
252 WHERE f_leak(dtitle) ORDER BY did;
253 NOTICE: f_leak => my first manga
254 NOTICE: f_leak => my second manga
255 NOTICE: f_leak => great science fiction
256 NOTICE: f_leak => great manga
257 NOTICE: f_leak => awesome science fiction
258 did | cid | dlevel | dauthor | dtitle
259 -----+-----+--------+-------------------+-------------------------
260 4 | 44 | 1 | regress_rls_bob | my first manga
261 5 | 44 | 2 | regress_rls_bob | my second manga
262 6 | 22 | 1 | regress_rls_carol | great science fiction
263 8 | 44 | 1 | regress_rls_carol | great manga
264 9 | 22 | 1 | regress_rls_dave | awesome science fiction
265 (5 rows)
267 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
268 QUERY PLAN
269 --------------------------------------------------------------
270 Seq Scan on document
271 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
272 InitPlan 1
273 -> Index Scan using uaccount_pkey on uaccount
274 Index Cond: (pguser = CURRENT_USER)
275 (5 rows)
277 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
278 QUERY PLAN
279 --------------------------------------------------------------------------
280 Hash Join
281 Hash Cond: (category.cid = document.cid)
282 InitPlan 1
283 -> Index Scan using uaccount_pkey on uaccount
284 Index Cond: (pguser = CURRENT_USER)
285 -> Seq Scan on category
286 -> Hash
287 -> Seq Scan on document
288 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
289 (9 rows)
291 -- viewpoint from regress_rls_dave
292 SET SESSION AUTHORIZATION regress_rls_dave;
293 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
294 NOTICE: f_leak => my first novel
295 NOTICE: f_leak => my second novel
296 NOTICE: f_leak => my science fiction
297 NOTICE: f_leak => great science fiction
298 NOTICE: f_leak => great technology book
299 NOTICE: f_leak => awesome science fiction
300 NOTICE: f_leak => awesome technology book
301 did | cid | dlevel | dauthor | dtitle
302 -----+-----+--------+-------------------+-------------------------
303 1 | 11 | 1 | regress_rls_bob | my first novel
304 2 | 11 | 2 | regress_rls_bob | my second novel
305 3 | 22 | 2 | regress_rls_bob | my science fiction
306 6 | 22 | 1 | regress_rls_carol | great science fiction
307 7 | 33 | 2 | regress_rls_carol | great technology book
308 9 | 22 | 1 | regress_rls_dave | awesome science fiction
309 10 | 33 | 2 | regress_rls_dave | awesome technology book
310 (7 rows)
312 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
313 NOTICE: f_leak => my first novel
314 NOTICE: f_leak => my second novel
315 NOTICE: f_leak => my science fiction
316 NOTICE: f_leak => great science fiction
317 NOTICE: f_leak => great technology book
318 NOTICE: f_leak => awesome science fiction
319 NOTICE: f_leak => awesome technology book
320 cid | did | dlevel | dauthor | dtitle | cname
321 -----+-----+--------+-------------------+-------------------------+-----------------
322 11 | 1 | 1 | regress_rls_bob | my first novel | novel
323 11 | 2 | 2 | regress_rls_bob | my second novel | novel
324 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction
325 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
326 33 | 7 | 2 | regress_rls_carol | great technology book | technology
327 22 | 9 | 1 | regress_rls_dave | awesome science fiction | science fiction
328 33 | 10 | 2 | regress_rls_dave | awesome technology book | technology
329 (7 rows)
331 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
332 QUERY PLAN
333 -------------------------------------------------------------------------------------------------------------
334 Seq Scan on document
335 Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
336 InitPlan 1
337 -> Index Scan using uaccount_pkey on uaccount
338 Index Cond: (pguser = CURRENT_USER)
339 (5 rows)
341 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
342 QUERY PLAN
343 -------------------------------------------------------------------------------------------------------------------------
344 Hash Join
345 Hash Cond: (category.cid = document.cid)
346 InitPlan 1
347 -> Index Scan using uaccount_pkey on uaccount
348 Index Cond: (pguser = CURRENT_USER)
349 -> Seq Scan on category
350 -> Hash
351 -> Seq Scan on document
352 Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
353 (9 rows)
355 -- 44 would technically fail for both p2r and p1r, but we should get an error
356 -- back from p1r for this because it sorts first
357 INSERT INTO document VALUES (100, 44, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail
358 ERROR: new row violates row-level security policy "p1r" for table "document"
359 -- Just to see a p2r error
360 INSERT INTO document VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail
361 ERROR: new row violates row-level security policy "p2r" for table "document"
362 -- only owner can change policies
363 ALTER POLICY p1 ON document USING (true); --fail
364 ERROR: must be owner of table document
365 DROP POLICY p1 ON document; --fail
366 ERROR: must be owner of relation document
367 SET SESSION AUTHORIZATION regress_rls_alice;
368 ALTER POLICY p1 ON document USING (dauthor = current_user);
369 -- viewpoint from regress_rls_bob again
370 SET SESSION AUTHORIZATION regress_rls_bob;
371 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
372 NOTICE: f_leak => my first novel
373 NOTICE: f_leak => my second novel
374 NOTICE: f_leak => my science fiction
375 NOTICE: f_leak => my first manga
376 NOTICE: f_leak => my second manga
377 did | cid | dlevel | dauthor | dtitle
378 -----+-----+--------+-----------------+--------------------
379 1 | 11 | 1 | regress_rls_bob | my first novel
380 2 | 11 | 2 | regress_rls_bob | my second novel
381 3 | 22 | 2 | regress_rls_bob | my science fiction
382 4 | 44 | 1 | regress_rls_bob | my first manga
383 5 | 44 | 2 | regress_rls_bob | my second manga
384 (5 rows)
386 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
387 NOTICE: f_leak => my first novel
388 NOTICE: f_leak => my second novel
389 NOTICE: f_leak => my science fiction
390 NOTICE: f_leak => my first manga
391 NOTICE: f_leak => my second manga
392 cid | did | dlevel | dauthor | dtitle | cname
393 -----+-----+--------+-----------------+--------------------+-----------------
394 11 | 1 | 1 | regress_rls_bob | my first novel | novel
395 11 | 2 | 2 | regress_rls_bob | my second novel | novel
396 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction
397 44 | 4 | 1 | regress_rls_bob | my first manga | manga
398 44 | 5 | 2 | regress_rls_bob | my second manga | manga
399 (5 rows)
401 -- viewpoint from rls_regres_carol again
402 SET SESSION AUTHORIZATION regress_rls_carol;
403 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
404 NOTICE: f_leak => great science fiction
405 NOTICE: f_leak => great technology book
406 NOTICE: f_leak => great manga
407 did | cid | dlevel | dauthor | dtitle
408 -----+-----+--------+-------------------+-----------------------
409 6 | 22 | 1 | regress_rls_carol | great science fiction
410 7 | 33 | 2 | regress_rls_carol | great technology book
411 8 | 44 | 1 | regress_rls_carol | great manga
412 (3 rows)
414 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
415 NOTICE: f_leak => great science fiction
416 NOTICE: f_leak => great technology book
417 NOTICE: f_leak => great manga
418 cid | did | dlevel | dauthor | dtitle | cname
419 -----+-----+--------+-------------------+-----------------------+-----------------
420 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
421 33 | 7 | 2 | regress_rls_carol | great technology book | technology
422 44 | 8 | 1 | regress_rls_carol | great manga | manga
423 (3 rows)
425 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
426 QUERY PLAN
427 ---------------------------------------------------------
428 Seq Scan on document
429 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
430 (2 rows)
432 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
433 QUERY PLAN
434 ---------------------------------------------------------------
435 Nested Loop
436 -> Seq Scan on document
437 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
438 -> Index Scan using category_pkey on category
439 Index Cond: (cid = document.cid)
440 (5 rows)
442 -- interaction of FK/PK constraints
443 SET SESSION AUTHORIZATION regress_rls_alice;
444 CREATE POLICY p2 ON category
445 USING (CASE WHEN current_user = 'regress_rls_bob' THEN cid IN (11, 33)
446 WHEN current_user = 'regress_rls_carol' THEN cid IN (22, 44)
447 ELSE false END);
448 ALTER TABLE category ENABLE ROW LEVEL SECURITY;
449 -- cannot delete PK referenced by invisible FK
450 SET SESSION AUTHORIZATION regress_rls_bob;
451 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid;
452 did | cid | dlevel | dauthor | dtitle | cid | cname
453 -----+-----+--------+-----------------+--------------------+-----+------------
454 1 | 11 | 1 | regress_rls_bob | my first novel | 11 | novel
455 2 | 11 | 2 | regress_rls_bob | my second novel | 11 | novel
456 3 | 22 | 2 | regress_rls_bob | my science fiction | |
457 4 | 44 | 1 | regress_rls_bob | my first manga | |
458 5 | 44 | 2 | regress_rls_bob | my second manga | |
459 | | | | | 33 | technology
460 (6 rows)
462 DELETE FROM category WHERE cid = 33; -- fails with FK violation
463 ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"
464 DETAIL: Key is still referenced from table "document".
465 -- can insert FK referencing invisible PK
466 SET SESSION AUTHORIZATION regress_rls_carol;
467 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid;
468 did | cid | dlevel | dauthor | dtitle | cid | cname
469 -----+-----+--------+-------------------+-----------------------+-----+-----------------
470 6 | 22 | 1 | regress_rls_carol | great science fiction | 22 | science fiction
471 7 | 33 | 2 | regress_rls_carol | great technology book | |
472 8 | 44 | 1 | regress_rls_carol | great manga | 44 | manga
473 (3 rows)
475 INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge');
476 -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
477 SET SESSION AUTHORIZATION regress_rls_bob;
478 INSERT INTO document VALUES (8, 44, 1, 'regress_rls_bob', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
479 ERROR: duplicate key value violates unique constraint "document_pkey"
480 SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
481 did | cid | dlevel | dauthor | dtitle
482 -----+-----+--------+---------+--------
483 (0 rows)
485 -- RLS policies are checked before constraints
486 INSERT INTO document VALUES (8, 44, 1, 'regress_rls_carol', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation
487 ERROR: new row violates row-level security policy for table "document"
488 UPDATE document SET did = 8, dauthor = 'regress_rls_carol' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation
489 ERROR: new row violates row-level security policy for table "document"
490 -- database superuser does bypass RLS policy when enabled
491 RESET SESSION AUTHORIZATION;
492 SET row_security TO ON;
493 SELECT * FROM document;
494 did | cid | dlevel | dauthor | dtitle
495 -----+-----+--------+-------------------+-------------------------
496 1 | 11 | 1 | regress_rls_bob | my first novel
497 2 | 11 | 2 | regress_rls_bob | my second novel
498 3 | 22 | 2 | regress_rls_bob | my science fiction
499 4 | 44 | 1 | regress_rls_bob | my first manga
500 5 | 44 | 2 | regress_rls_bob | my second manga
501 6 | 22 | 1 | regress_rls_carol | great science fiction
502 7 | 33 | 2 | regress_rls_carol | great technology book
503 8 | 44 | 1 | regress_rls_carol | great manga
504 9 | 22 | 1 | regress_rls_dave | awesome science fiction
505 10 | 33 | 2 | regress_rls_dave | awesome technology book
506 11 | 33 | 1 | regress_rls_carol | hoge
507 (11 rows)
509 SELECT * FROM category;
510 cid | cname
511 -----+-----------------
512 11 | novel
513 22 | science fiction
514 33 | technology
515 44 | manga
516 (4 rows)
518 -- database superuser does bypass RLS policy when disabled
519 RESET SESSION AUTHORIZATION;
520 SET row_security TO OFF;
521 SELECT * FROM document;
522 did | cid | dlevel | dauthor | dtitle
523 -----+-----+--------+-------------------+-------------------------
524 1 | 11 | 1 | regress_rls_bob | my first novel
525 2 | 11 | 2 | regress_rls_bob | my second novel
526 3 | 22 | 2 | regress_rls_bob | my science fiction
527 4 | 44 | 1 | regress_rls_bob | my first manga
528 5 | 44 | 2 | regress_rls_bob | my second manga
529 6 | 22 | 1 | regress_rls_carol | great science fiction
530 7 | 33 | 2 | regress_rls_carol | great technology book
531 8 | 44 | 1 | regress_rls_carol | great manga
532 9 | 22 | 1 | regress_rls_dave | awesome science fiction
533 10 | 33 | 2 | regress_rls_dave | awesome technology book
534 11 | 33 | 1 | regress_rls_carol | hoge
535 (11 rows)
537 SELECT * FROM category;
538 cid | cname
539 -----+-----------------
540 11 | novel
541 22 | science fiction
542 33 | technology
543 44 | manga
544 (4 rows)
546 -- database non-superuser with bypass privilege can bypass RLS policy when disabled
547 SET SESSION AUTHORIZATION regress_rls_exempt_user;
548 SET row_security TO OFF;
549 SELECT * FROM document;
550 did | cid | dlevel | dauthor | dtitle
551 -----+-----+--------+-------------------+-------------------------
552 1 | 11 | 1 | regress_rls_bob | my first novel
553 2 | 11 | 2 | regress_rls_bob | my second novel
554 3 | 22 | 2 | regress_rls_bob | my science fiction
555 4 | 44 | 1 | regress_rls_bob | my first manga
556 5 | 44 | 2 | regress_rls_bob | my second manga
557 6 | 22 | 1 | regress_rls_carol | great science fiction
558 7 | 33 | 2 | regress_rls_carol | great technology book
559 8 | 44 | 1 | regress_rls_carol | great manga
560 9 | 22 | 1 | regress_rls_dave | awesome science fiction
561 10 | 33 | 2 | regress_rls_dave | awesome technology book
562 11 | 33 | 1 | regress_rls_carol | hoge
563 (11 rows)
565 SELECT * FROM category;
566 cid | cname
567 -----+-----------------
568 11 | novel
569 22 | science fiction
570 33 | technology
571 44 | manga
572 (4 rows)
574 -- RLS policy does not apply to table owner when RLS enabled.
575 SET SESSION AUTHORIZATION regress_rls_alice;
576 SET row_security TO ON;
577 SELECT * FROM document;
578 did | cid | dlevel | dauthor | dtitle
579 -----+-----+--------+-------------------+-------------------------
580 1 | 11 | 1 | regress_rls_bob | my first novel
581 2 | 11 | 2 | regress_rls_bob | my second novel
582 3 | 22 | 2 | regress_rls_bob | my science fiction
583 4 | 44 | 1 | regress_rls_bob | my first manga
584 5 | 44 | 2 | regress_rls_bob | my second manga
585 6 | 22 | 1 | regress_rls_carol | great science fiction
586 7 | 33 | 2 | regress_rls_carol | great technology book
587 8 | 44 | 1 | regress_rls_carol | great manga
588 9 | 22 | 1 | regress_rls_dave | awesome science fiction
589 10 | 33 | 2 | regress_rls_dave | awesome technology book
590 11 | 33 | 1 | regress_rls_carol | hoge
591 (11 rows)
593 SELECT * FROM category;
594 cid | cname
595 -----+-----------------
596 11 | novel
597 22 | science fiction
598 33 | technology
599 44 | manga
600 (4 rows)
602 -- RLS policy does not apply to table owner when RLS disabled.
603 SET SESSION AUTHORIZATION regress_rls_alice;
604 SET row_security TO OFF;
605 SELECT * FROM document;
606 did | cid | dlevel | dauthor | dtitle
607 -----+-----+--------+-------------------+-------------------------
608 1 | 11 | 1 | regress_rls_bob | my first novel
609 2 | 11 | 2 | regress_rls_bob | my second novel
610 3 | 22 | 2 | regress_rls_bob | my science fiction
611 4 | 44 | 1 | regress_rls_bob | my first manga
612 5 | 44 | 2 | regress_rls_bob | my second manga
613 6 | 22 | 1 | regress_rls_carol | great science fiction
614 7 | 33 | 2 | regress_rls_carol | great technology book
615 8 | 44 | 1 | regress_rls_carol | great manga
616 9 | 22 | 1 | regress_rls_dave | awesome science fiction
617 10 | 33 | 2 | regress_rls_dave | awesome technology book
618 11 | 33 | 1 | regress_rls_carol | hoge
619 (11 rows)
621 SELECT * FROM category;
622 cid | cname
623 -----+-----------------
624 11 | novel
625 22 | science fiction
626 33 | technology
627 44 | manga
628 (4 rows)
630 --
631 -- Table inheritance and RLS policy
632 --
633 SET SESSION AUTHORIZATION regress_rls_alice;
634 SET row_security TO ON;
635 CREATE TABLE t1 (id int not null primary key, a int, junk1 text, b text);
636 ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor
637 GRANT ALL ON t1 TO public;
638 COPY t1 FROM stdin WITH ;
639 CREATE TABLE t2 (c float) INHERITS (t1);
640 GRANT ALL ON t2 TO public;
641 COPY t2 FROM stdin;
642 CREATE TABLE t3 (id int not null primary key, c text, b text, a int);
643 ALTER TABLE t3 INHERIT t1;
644 GRANT ALL ON t3 TO public;
645 COPY t3(id, a,b,c) FROM stdin;
646 CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number
647 CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number
648 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
649 ALTER TABLE t2 ENABLE ROW LEVEL SECURITY;
650 SET SESSION AUTHORIZATION regress_rls_bob;
651 SELECT * FROM t1;
652 id | a | b
653 -----+---+-----
654 102 | 2 | bbb
655 104 | 4 | dad
656 202 | 2 | bcd
657 204 | 4 | def
658 302 | 2 | yyy
659 (5 rows)
661 EXPLAIN (COSTS OFF) SELECT * FROM t1;
662 QUERY PLAN
663 -------------------------------
664 Append
665 -> Seq Scan on t1 t1_1
666 Filter: ((a % 2) = 0)
667 -> Seq Scan on t2 t1_2
668 Filter: ((a % 2) = 0)
669 -> Seq Scan on t3 t1_3
670 Filter: ((a % 2) = 0)
671 (7 rows)
673 SELECT * FROM t1 WHERE f_leak(b);
674 NOTICE: f_leak => bbb
675 NOTICE: f_leak => dad
676 NOTICE: f_leak => bcd
677 NOTICE: f_leak => def
678 NOTICE: f_leak => yyy
679 id | a | b
680 -----+---+-----
681 102 | 2 | bbb
682 104 | 4 | dad
683 202 | 2 | bcd
684 204 | 4 | def
685 302 | 2 | yyy
686 (5 rows)
688 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
689 QUERY PLAN
690 -----------------------------------------------
691 Append
692 -> Seq Scan on t1 t1_1
693 Filter: (((a % 2) = 0) AND f_leak(b))
694 -> Seq Scan on t2 t1_2
695 Filter: (((a % 2) = 0) AND f_leak(b))
696 -> Seq Scan on t3 t1_3
697 Filter: (((a % 2) = 0) AND f_leak(b))
698 (7 rows)
700 -- reference to system column
701 SELECT tableoid::regclass, * FROM t1;
702 tableoid | id | a | b
703 ----------+-----+---+-----
704 t1 | 102 | 2 | bbb
705 t1 | 104 | 4 | dad
706 t2 | 202 | 2 | bcd
707 t2 | 204 | 4 | def
708 t3 | 302 | 2 | yyy
709 (5 rows)
711 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
712 QUERY PLAN
713 -------------------------------
714 Append
715 -> Seq Scan on t1 t1_1
716 Filter: ((a % 2) = 0)
717 -> Seq Scan on t2 t1_2
718 Filter: ((a % 2) = 0)
719 -> Seq Scan on t3 t1_3
720 Filter: ((a % 2) = 0)
721 (7 rows)
723 -- reference to whole-row reference
724 SELECT *, t1 FROM t1;
725 id | a | b | t1
726 -----+---+-----+-------------
727 102 | 2 | bbb | (102,2,bbb)
728 104 | 4 | dad | (104,4,dad)
729 202 | 2 | bcd | (202,2,bcd)
730 204 | 4 | def | (204,4,def)
731 302 | 2 | yyy | (302,2,yyy)
732 (5 rows)
734 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
735 QUERY PLAN
736 -------------------------------
737 Append
738 -> Seq Scan on t1 t1_1
739 Filter: ((a % 2) = 0)
740 -> Seq Scan on t2 t1_2
741 Filter: ((a % 2) = 0)
742 -> Seq Scan on t3 t1_3
743 Filter: ((a % 2) = 0)
744 (7 rows)
746 -- for share/update lock
747 SELECT * FROM t1 FOR SHARE;
748 id | a | b
749 -----+---+-----
750 102 | 2 | bbb
751 104 | 4 | dad
752 202 | 2 | bcd
753 204 | 4 | def
754 302 | 2 | yyy
755 (5 rows)
757 EXPLAIN (COSTS OFF) SELECT * FROM t1 FOR SHARE;
758 QUERY PLAN
759 -------------------------------------
760 LockRows
761 -> Append
762 -> Seq Scan on t1 t1_1
763 Filter: ((a % 2) = 0)
764 -> Seq Scan on t2 t1_2
765 Filter: ((a % 2) = 0)
766 -> Seq Scan on t3 t1_3
767 Filter: ((a % 2) = 0)
768 (8 rows)
770 SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
771 NOTICE: f_leak => bbb
772 NOTICE: f_leak => dad
773 NOTICE: f_leak => bcd
774 NOTICE: f_leak => def
775 NOTICE: f_leak => yyy
776 id | a | b
777 -----+---+-----
778 102 | 2 | bbb
779 104 | 4 | dad
780 202 | 2 | bcd
781 204 | 4 | def
782 302 | 2 | yyy
783 (5 rows)
785 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
786 QUERY PLAN
787 -----------------------------------------------------
788 LockRows
789 -> Append
790 -> Seq Scan on t1 t1_1
791 Filter: (((a % 2) = 0) AND f_leak(b))
792 -> Seq Scan on t2 t1_2
793 Filter: (((a % 2) = 0) AND f_leak(b))
794 -> Seq Scan on t3 t1_3
795 Filter: (((a % 2) = 0) AND f_leak(b))
796 (8 rows)
798 -- union all query
799 SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3;
800 a | b | tableoid
801 ---+-----+----------
802 1 | abc | t2
803 3 | cde | t2
804 1 | xxx | t3
805 2 | yyy | t3
806 3 | zzz | t3
807 (5 rows)
809 EXPLAIN (COSTS OFF) SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3;
810 QUERY PLAN
811 -------------------------------
812 Append
813 -> Seq Scan on t2
814 Filter: ((a % 2) = 1)
815 -> Seq Scan on t3
816 (4 rows)
818 -- superuser is allowed to bypass RLS checks
819 RESET SESSION AUTHORIZATION;
820 SET row_security TO OFF;
821 SELECT * FROM t1 WHERE f_leak(b);
822 NOTICE: f_leak => aba
823 NOTICE: f_leak => bbb
824 NOTICE: f_leak => ccc
825 NOTICE: f_leak => dad
826 NOTICE: f_leak => abc
827 NOTICE: f_leak => bcd
828 NOTICE: f_leak => cde
829 NOTICE: f_leak => def
830 NOTICE: f_leak => xxx
831 NOTICE: f_leak => yyy
832 NOTICE: f_leak => zzz
833 id | a | b
834 -----+---+-----
835 101 | 1 | aba
836 102 | 2 | bbb
837 103 | 3 | ccc
838 104 | 4 | dad
839 201 | 1 | abc
840 202 | 2 | bcd
841 203 | 3 | cde
842 204 | 4 | def
843 301 | 1 | xxx
844 302 | 2 | yyy
845 303 | 3 | zzz
846 (11 rows)
848 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
849 QUERY PLAN
850 ---------------------------
851 Append
852 -> Seq Scan on t1 t1_1
853 Filter: f_leak(b)
854 -> Seq Scan on t2 t1_2
855 Filter: f_leak(b)
856 -> Seq Scan on t3 t1_3
857 Filter: f_leak(b)
858 (7 rows)
860 -- non-superuser with bypass privilege can bypass RLS policy when disabled
861 SET SESSION AUTHORIZATION regress_rls_exempt_user;
862 SET row_security TO OFF;
863 SELECT * FROM t1 WHERE f_leak(b);
864 NOTICE: f_leak => aba
865 NOTICE: f_leak => bbb
866 NOTICE: f_leak => ccc
867 NOTICE: f_leak => dad
868 NOTICE: f_leak => abc
869 NOTICE: f_leak => bcd
870 NOTICE: f_leak => cde
871 NOTICE: f_leak => def
872 NOTICE: f_leak => xxx
873 NOTICE: f_leak => yyy
874 NOTICE: f_leak => zzz
875 id | a | b
876 -----+---+-----
877 101 | 1 | aba
878 102 | 2 | bbb
879 103 | 3 | ccc
880 104 | 4 | dad
881 201 | 1 | abc
882 202 | 2 | bcd
883 203 | 3 | cde
884 204 | 4 | def
885 301 | 1 | xxx
886 302 | 2 | yyy
887 303 | 3 | zzz
888 (11 rows)
890 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
891 QUERY PLAN
892 ---------------------------
893 Append
894 -> Seq Scan on t1 t1_1
895 Filter: f_leak(b)
896 -> Seq Scan on t2 t1_2
897 Filter: f_leak(b)
898 -> Seq Scan on t3 t1_3
899 Filter: f_leak(b)
900 (7 rows)
902 --
903 -- Partitioned Tables
904 --
905 SET SESSION AUTHORIZATION regress_rls_alice;
906 CREATE TABLE part_document (
907 did int,
908 cid int,
909 dlevel int not null,
910 dauthor name,
911 dtitle text
912 ) PARTITION BY RANGE (cid);
913 GRANT ALL ON part_document TO public;
914 -- Create partitions for document categories
915 CREATE TABLE part_document_fiction PARTITION OF part_document FOR VALUES FROM (11) to (12);
916 CREATE TABLE part_document_satire PARTITION OF part_document FOR VALUES FROM (55) to (56);
917 CREATE TABLE part_document_nonfiction PARTITION OF part_document FOR VALUES FROM (99) to (100);
918 GRANT ALL ON part_document_fiction TO public;
919 GRANT ALL ON part_document_satire TO public;
920 GRANT ALL ON part_document_nonfiction TO public;
921 INSERT INTO part_document VALUES
922 ( 1, 11, 1, 'regress_rls_bob', 'my first novel'),
923 ( 2, 11, 2, 'regress_rls_bob', 'my second novel'),
924 ( 3, 99, 2, 'regress_rls_bob', 'my science textbook'),
925 ( 4, 55, 1, 'regress_rls_bob', 'my first satire'),
926 ( 5, 99, 2, 'regress_rls_bob', 'my history book'),
927 ( 6, 11, 1, 'regress_rls_carol', 'great science fiction'),
928 ( 7, 99, 2, 'regress_rls_carol', 'great technology book'),
929 ( 8, 55, 2, 'regress_rls_carol', 'great satire'),
930 ( 9, 11, 1, 'regress_rls_dave', 'awesome science fiction'),
931 (10, 99, 2, 'regress_rls_dave', 'awesome technology book');
932 ALTER TABLE part_document ENABLE ROW LEVEL SECURITY;
933 -- Create policy on parent
934 -- user's security level must be higher than or equal to document's
935 CREATE POLICY pp1 ON part_document AS PERMISSIVE
936 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
937 -- Dave is only allowed to see cid < 55
938 CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
939 USING (cid < 55);
940 \d+ part_document
941 Partitioned table "regress_rls_schema.part_document"
942 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
943 ---------+---------+-----------+----------+---------+----------+--------------+-------------
944 did | integer | | | | plain | |
945 cid | integer | | | | plain | |
946 dlevel | integer | | not null | | plain | |
947 dauthor | name | | | | plain | |
948 dtitle | text | | | | extended | |
949 Partition key: RANGE (cid)
950 Policies:
951 POLICY "pp1"
952 USING ((dlevel <= ( SELECT uaccount.seclv
953 FROM uaccount
954 WHERE (uaccount.pguser = CURRENT_USER))))
955 POLICY "pp1r" AS RESTRICTIVE
956 TO regress_rls_dave
957 USING ((cid < 55))
958 Not-null constraints:
959 "part_document_dlevel_not_null" NOT NULL "dlevel"
960 Partitions: part_document_fiction FOR VALUES FROM (11) TO (12),
961 part_document_nonfiction FOR VALUES FROM (99) TO (100),
962 part_document_satire FOR VALUES FROM (55) TO (56)
964 SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename like '%part_document%' ORDER BY policyname;
965 schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
966 --------------------+---------------+------------+-------------+--------------------+-----+--------------------------------------------+------------
967 regress_rls_schema | part_document | pp1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
968 | | | | | | FROM uaccount +|
969 | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
970 regress_rls_schema | part_document | pp1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid < 55) |
971 (2 rows)
973 -- viewpoint from regress_rls_bob
974 SET SESSION AUTHORIZATION regress_rls_bob;
975 SET row_security TO ON;
976 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
977 NOTICE: f_leak => my first novel
978 NOTICE: f_leak => great science fiction
979 NOTICE: f_leak => awesome science fiction
980 NOTICE: f_leak => my first satire
981 did | cid | dlevel | dauthor | dtitle
982 -----+-----+--------+-------------------+-------------------------
983 1 | 11 | 1 | regress_rls_bob | my first novel
984 4 | 55 | 1 | regress_rls_bob | my first satire
985 6 | 11 | 1 | regress_rls_carol | great science fiction
986 9 | 11 | 1 | regress_rls_dave | awesome science fiction
987 (4 rows)
989 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
990 QUERY PLAN
991 --------------------------------------------------------------------
992 Append
993 InitPlan 1
994 -> Index Scan using uaccount_pkey on uaccount
995 Index Cond: (pguser = CURRENT_USER)
996 -> Seq Scan on part_document_fiction part_document_1
997 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
998 -> Seq Scan on part_document_satire part_document_2
999 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1000 -> Seq Scan on part_document_nonfiction part_document_3
1001 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1002 (10 rows)
1004 -- viewpoint from regress_rls_carol
1005 SET SESSION AUTHORIZATION regress_rls_carol;
1006 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1007 NOTICE: f_leak => my first novel
1008 NOTICE: f_leak => my second novel
1009 NOTICE: f_leak => great science fiction
1010 NOTICE: f_leak => awesome science fiction
1011 NOTICE: f_leak => my first satire
1012 NOTICE: f_leak => great satire
1013 NOTICE: f_leak => my science textbook
1014 NOTICE: f_leak => my history book
1015 NOTICE: f_leak => great technology book
1016 NOTICE: f_leak => awesome technology book
1017 did | cid | dlevel | dauthor | dtitle
1018 -----+-----+--------+-------------------+-------------------------
1019 1 | 11 | 1 | regress_rls_bob | my first novel
1020 2 | 11 | 2 | regress_rls_bob | my second novel
1021 3 | 99 | 2 | regress_rls_bob | my science textbook
1022 4 | 55 | 1 | regress_rls_bob | my first satire
1023 5 | 99 | 2 | regress_rls_bob | my history book
1024 6 | 11 | 1 | regress_rls_carol | great science fiction
1025 7 | 99 | 2 | regress_rls_carol | great technology book
1026 8 | 55 | 2 | regress_rls_carol | great satire
1027 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1028 10 | 99 | 2 | regress_rls_dave | awesome technology book
1029 (10 rows)
1031 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1032 QUERY PLAN
1033 --------------------------------------------------------------------
1034 Append
1035 InitPlan 1
1036 -> Index Scan using uaccount_pkey on uaccount
1037 Index Cond: (pguser = CURRENT_USER)
1038 -> Seq Scan on part_document_fiction part_document_1
1039 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1040 -> Seq Scan on part_document_satire part_document_2
1041 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1042 -> Seq Scan on part_document_nonfiction part_document_3
1043 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1044 (10 rows)
1046 -- viewpoint from regress_rls_dave
1047 SET SESSION AUTHORIZATION regress_rls_dave;
1048 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1049 NOTICE: f_leak => my first novel
1050 NOTICE: f_leak => my second novel
1051 NOTICE: f_leak => great science fiction
1052 NOTICE: f_leak => awesome science fiction
1053 did | cid | dlevel | dauthor | dtitle
1054 -----+-----+--------+-------------------+-------------------------
1055 1 | 11 | 1 | regress_rls_bob | my first novel
1056 2 | 11 | 2 | regress_rls_bob | my second novel
1057 6 | 11 | 1 | regress_rls_carol | great science fiction
1058 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1059 (4 rows)
1061 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1062 QUERY PLAN
1063 -----------------------------------------------------------------------------
1064 Seq Scan on part_document_fiction part_document
1065 Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1066 InitPlan 1
1067 -> Index Scan using uaccount_pkey on uaccount
1068 Index Cond: (pguser = CURRENT_USER)
1069 (5 rows)
1071 -- pp1 ERROR
1072 INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail
1073 ERROR: new row violates row-level security policy for table "part_document"
1074 -- pp1r ERROR
1075 INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail
1076 ERROR: new row violates row-level security policy "pp1r" for table "part_document"
1077 -- Show that RLS policy does not apply for direct inserts to children
1078 -- This should fail with RLS POLICY pp1r violation.
1079 INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
1080 ERROR: new row violates row-level security policy "pp1r" for table "part_document"
1081 -- But this should succeed.
1082 INSERT INTO part_document_satire VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- success
1083 -- We still cannot see the row using the parent
1084 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1085 NOTICE: f_leak => my first novel
1086 NOTICE: f_leak => my second novel
1087 NOTICE: f_leak => great science fiction
1088 NOTICE: f_leak => awesome science fiction
1089 did | cid | dlevel | dauthor | dtitle
1090 -----+-----+--------+-------------------+-------------------------
1091 1 | 11 | 1 | regress_rls_bob | my first novel
1092 2 | 11 | 2 | regress_rls_bob | my second novel
1093 6 | 11 | 1 | regress_rls_carol | great science fiction
1094 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1095 (4 rows)
1097 -- But we can if we look directly
1098 SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
1099 NOTICE: f_leak => my first satire
1100 NOTICE: f_leak => great satire
1101 NOTICE: f_leak => testing RLS with partitions
1102 did | cid | dlevel | dauthor | dtitle
1103 -----+-----+--------+-------------------+-----------------------------
1104 4 | 55 | 1 | regress_rls_bob | my first satire
1105 8 | 55 | 2 | regress_rls_carol | great satire
1106 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1107 (3 rows)
1109 -- Turn on RLS and create policy on child to show RLS is checked before constraints
1110 SET SESSION AUTHORIZATION regress_rls_alice;
1111 ALTER TABLE part_document_satire ENABLE ROW LEVEL SECURITY;
1112 CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE
1113 USING (cid < 55);
1114 -- This should fail with RLS violation now.
1115 SET SESSION AUTHORIZATION regress_rls_dave;
1116 INSERT INTO part_document_satire VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
1117 ERROR: new row violates row-level security policy for table "part_document_satire"
1118 -- And now we cannot see directly into the partition either, due to RLS
1119 SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
1120 did | cid | dlevel | dauthor | dtitle
1121 -----+-----+--------+---------+--------
1122 (0 rows)
1124 -- The parent looks same as before
1125 -- viewpoint from regress_rls_dave
1126 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1127 NOTICE: f_leak => my first novel
1128 NOTICE: f_leak => my second novel
1129 NOTICE: f_leak => great science fiction
1130 NOTICE: f_leak => awesome science fiction
1131 did | cid | dlevel | dauthor | dtitle
1132 -----+-----+--------+-------------------+-------------------------
1133 1 | 11 | 1 | regress_rls_bob | my first novel
1134 2 | 11 | 2 | regress_rls_bob | my second novel
1135 6 | 11 | 1 | regress_rls_carol | great science fiction
1136 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1137 (4 rows)
1139 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1140 QUERY PLAN
1141 -----------------------------------------------------------------------------
1142 Seq Scan on part_document_fiction part_document
1143 Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1144 InitPlan 1
1145 -> Index Scan using uaccount_pkey on uaccount
1146 Index Cond: (pguser = CURRENT_USER)
1147 (5 rows)
1149 -- viewpoint from regress_rls_carol
1150 SET SESSION AUTHORIZATION regress_rls_carol;
1151 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1152 NOTICE: f_leak => my first novel
1153 NOTICE: f_leak => my second novel
1154 NOTICE: f_leak => great science fiction
1155 NOTICE: f_leak => awesome science fiction
1156 NOTICE: f_leak => my first satire
1157 NOTICE: f_leak => great satire
1158 NOTICE: f_leak => testing RLS with partitions
1159 NOTICE: f_leak => my science textbook
1160 NOTICE: f_leak => my history book
1161 NOTICE: f_leak => great technology book
1162 NOTICE: f_leak => awesome technology book
1163 did | cid | dlevel | dauthor | dtitle
1164 -----+-----+--------+-------------------+-----------------------------
1165 1 | 11 | 1 | regress_rls_bob | my first novel
1166 2 | 11 | 2 | regress_rls_bob | my second novel
1167 3 | 99 | 2 | regress_rls_bob | my science textbook
1168 4 | 55 | 1 | regress_rls_bob | my first satire
1169 5 | 99 | 2 | regress_rls_bob | my history book
1170 6 | 11 | 1 | regress_rls_carol | great science fiction
1171 7 | 99 | 2 | regress_rls_carol | great technology book
1172 8 | 55 | 2 | regress_rls_carol | great satire
1173 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1174 10 | 99 | 2 | regress_rls_dave | awesome technology book
1175 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1176 (11 rows)
1178 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1179 QUERY PLAN
1180 --------------------------------------------------------------------
1181 Append
1182 InitPlan 1
1183 -> Index Scan using uaccount_pkey on uaccount
1184 Index Cond: (pguser = CURRENT_USER)
1185 -> Seq Scan on part_document_fiction part_document_1
1186 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1187 -> Seq Scan on part_document_satire part_document_2
1188 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1189 -> Seq Scan on part_document_nonfiction part_document_3
1190 Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
1191 (10 rows)
1193 -- only owner can change policies
1194 ALTER POLICY pp1 ON part_document USING (true); --fail
1195 ERROR: must be owner of table part_document
1196 DROP POLICY pp1 ON part_document; --fail
1197 ERROR: must be owner of relation part_document
1198 SET SESSION AUTHORIZATION regress_rls_alice;
1199 ALTER POLICY pp1 ON part_document USING (dauthor = current_user);
1200 -- viewpoint from regress_rls_bob again
1201 SET SESSION AUTHORIZATION regress_rls_bob;
1202 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1203 NOTICE: f_leak => my first novel
1204 NOTICE: f_leak => my second novel
1205 NOTICE: f_leak => my first satire
1206 NOTICE: f_leak => my science textbook
1207 NOTICE: f_leak => my history book
1208 did | cid | dlevel | dauthor | dtitle
1209 -----+-----+--------+-----------------+---------------------
1210 1 | 11 | 1 | regress_rls_bob | my first novel
1211 2 | 11 | 2 | regress_rls_bob | my second novel
1212 3 | 99 | 2 | regress_rls_bob | my science textbook
1213 4 | 55 | 1 | regress_rls_bob | my first satire
1214 5 | 99 | 2 | regress_rls_bob | my history book
1215 (5 rows)
1217 -- viewpoint from rls_regres_carol again
1218 SET SESSION AUTHORIZATION regress_rls_carol;
1219 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1220 NOTICE: f_leak => great science fiction
1221 NOTICE: f_leak => great satire
1222 NOTICE: f_leak => great technology book
1223 did | cid | dlevel | dauthor | dtitle
1224 -----+-----+--------+-------------------+-----------------------
1225 6 | 11 | 1 | regress_rls_carol | great science fiction
1226 7 | 99 | 2 | regress_rls_carol | great technology book
1227 8 | 55 | 2 | regress_rls_carol | great satire
1228 (3 rows)
1230 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1231 QUERY PLAN
1232 ---------------------------------------------------------------
1233 Append
1234 -> Seq Scan on part_document_fiction part_document_1
1235 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
1236 -> Seq Scan on part_document_satire part_document_2
1237 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
1238 -> Seq Scan on part_document_nonfiction part_document_3
1239 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
1240 (7 rows)
1242 -- database superuser does bypass RLS policy when enabled
1243 RESET SESSION AUTHORIZATION;
1244 SET row_security TO ON;
1245 SELECT * FROM part_document ORDER BY did;
1246 did | cid | dlevel | dauthor | dtitle
1247 -----+-----+--------+-------------------+-----------------------------
1248 1 | 11 | 1 | regress_rls_bob | my first novel
1249 2 | 11 | 2 | regress_rls_bob | my second novel
1250 3 | 99 | 2 | regress_rls_bob | my science textbook
1251 4 | 55 | 1 | regress_rls_bob | my first satire
1252 5 | 99 | 2 | regress_rls_bob | my history book
1253 6 | 11 | 1 | regress_rls_carol | great science fiction
1254 7 | 99 | 2 | regress_rls_carol | great technology book
1255 8 | 55 | 2 | regress_rls_carol | great satire
1256 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1257 10 | 99 | 2 | regress_rls_dave | awesome technology book
1258 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1259 (11 rows)
1261 SELECT * FROM part_document_satire ORDER by did;
1262 did | cid | dlevel | dauthor | dtitle
1263 -----+-----+--------+-------------------+-----------------------------
1264 4 | 55 | 1 | regress_rls_bob | my first satire
1265 8 | 55 | 2 | regress_rls_carol | great satire
1266 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1267 (3 rows)
1269 -- database non-superuser with bypass privilege can bypass RLS policy when disabled
1270 SET SESSION AUTHORIZATION regress_rls_exempt_user;
1271 SET row_security TO OFF;
1272 SELECT * FROM part_document ORDER BY did;
1273 did | cid | dlevel | dauthor | dtitle
1274 -----+-----+--------+-------------------+-----------------------------
1275 1 | 11 | 1 | regress_rls_bob | my first novel
1276 2 | 11 | 2 | regress_rls_bob | my second novel
1277 3 | 99 | 2 | regress_rls_bob | my science textbook
1278 4 | 55 | 1 | regress_rls_bob | my first satire
1279 5 | 99 | 2 | regress_rls_bob | my history book
1280 6 | 11 | 1 | regress_rls_carol | great science fiction
1281 7 | 99 | 2 | regress_rls_carol | great technology book
1282 8 | 55 | 2 | regress_rls_carol | great satire
1283 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1284 10 | 99 | 2 | regress_rls_dave | awesome technology book
1285 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1286 (11 rows)
1288 SELECT * FROM part_document_satire ORDER by did;
1289 did | cid | dlevel | dauthor | dtitle
1290 -----+-----+--------+-------------------+-----------------------------
1291 4 | 55 | 1 | regress_rls_bob | my first satire
1292 8 | 55 | 2 | regress_rls_carol | great satire
1293 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1294 (3 rows)
1296 -- RLS policy does not apply to table owner when RLS enabled.
1297 SET SESSION AUTHORIZATION regress_rls_alice;
1298 SET row_security TO ON;
1299 SELECT * FROM part_document ORDER by did;
1300 did | cid | dlevel | dauthor | dtitle
1301 -----+-----+--------+-------------------+-----------------------------
1302 1 | 11 | 1 | regress_rls_bob | my first novel
1303 2 | 11 | 2 | regress_rls_bob | my second novel
1304 3 | 99 | 2 | regress_rls_bob | my science textbook
1305 4 | 55 | 1 | regress_rls_bob | my first satire
1306 5 | 99 | 2 | regress_rls_bob | my history book
1307 6 | 11 | 1 | regress_rls_carol | great science fiction
1308 7 | 99 | 2 | regress_rls_carol | great technology book
1309 8 | 55 | 2 | regress_rls_carol | great satire
1310 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1311 10 | 99 | 2 | regress_rls_dave | awesome technology book
1312 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1313 (11 rows)
1315 SELECT * FROM part_document_satire ORDER by did;
1316 did | cid | dlevel | dauthor | dtitle
1317 -----+-----+--------+-------------------+-----------------------------
1318 4 | 55 | 1 | regress_rls_bob | my first satire
1319 8 | 55 | 2 | regress_rls_carol | great satire
1320 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1321 (3 rows)
1323 -- When RLS disabled, other users get ERROR.
1324 SET SESSION AUTHORIZATION regress_rls_dave;
1325 SET row_security TO OFF;
1326 SELECT * FROM part_document ORDER by did;
1327 ERROR: query would be affected by row-level security policy for table "part_document"
1328 SELECT * FROM part_document_satire ORDER by did;
1329 ERROR: query would be affected by row-level security policy for table "part_document_satire"
1330 -- Check behavior with a policy that uses a SubPlan not an InitPlan.
1331 SET SESSION AUTHORIZATION regress_rls_alice;
1332 SET row_security TO ON;
1333 CREATE POLICY pp3 ON part_document AS RESTRICTIVE
1334 USING ((SELECT dlevel <= seclv FROM uaccount WHERE pguser = current_user));
1335 SET SESSION AUTHORIZATION regress_rls_carol;
1336 INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_carol', 'testing pp3'); -- fail
1337 ERROR: new row violates row-level security policy "pp3" for table "part_document"
1338 ----- Dependencies -----
1339 SET SESSION AUTHORIZATION regress_rls_alice;
1340 SET row_security TO ON;
1341 CREATE TABLE dependee (x integer, y integer);
1342 CREATE TABLE dependent (x integer, y integer);
1343 CREATE POLICY d1 ON dependent FOR ALL
1344 TO PUBLIC
1345 USING (x = (SELECT d.x FROM dependee d WHERE d.y = y));
1346 DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row security qual?
1347 ERROR: cannot drop table dependee because other objects depend on it
1348 DETAIL: policy d1 on table dependent depends on table dependee
1349 HINT: Use DROP ... CASCADE to drop the dependent objects too.
1350 DROP TABLE dependee CASCADE;
1351 NOTICE: drop cascades to policy d1 on table dependent
1352 EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified
1353 QUERY PLAN
1354 -----------------------
1355 Seq Scan on dependent
1356 (1 row)
1358 ----- RECURSION ----
1359 --
1360 -- Simple recursion
1361 --
1362 SET SESSION AUTHORIZATION regress_rls_alice;
1363 CREATE TABLE rec1 (x integer, y integer);
1364 CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y));
1365 ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;
1366 SET SESSION AUTHORIZATION regress_rls_bob;
1367 SELECT * FROM rec1; -- fail, direct recursion
1368 ERROR: infinite recursion detected in policy for relation "rec1"
1369 --
1370 -- Mutual recursion
1371 --
1372 SET SESSION AUTHORIZATION regress_rls_alice;
1373 CREATE TABLE rec2 (a integer, b integer);
1374 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y));
1375 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b));
1376 ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;
1377 SET SESSION AUTHORIZATION regress_rls_bob;
1378 SELECT * FROM rec1; -- fail, mutual recursion
1379 ERROR: infinite recursion detected in policy for relation "rec1"
1380 --
1381 -- Mutual recursion via views
1382 --
1383 SET SESSION AUTHORIZATION regress_rls_bob;
1384 CREATE VIEW rec1v AS SELECT * FROM rec1;
1385 CREATE VIEW rec2v AS SELECT * FROM rec2;
1386 SET SESSION AUTHORIZATION regress_rls_alice;
1387 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
1388 ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
1389 SET SESSION AUTHORIZATION regress_rls_bob;
1390 SELECT * FROM rec1; -- fail, mutual recursion via views
1391 ERROR: infinite recursion detected in policy for relation "rec1"
1392 --
1393 -- Mutual recursion via .s.b views
1394 --
1395 SET SESSION AUTHORIZATION regress_rls_bob;
1396 DROP VIEW rec1v, rec2v CASCADE;
1397 NOTICE: drop cascades to 2 other objects
1398 DETAIL: drop cascades to policy r1 on table rec1
1399 drop cascades to policy r2 on table rec2
1400 CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
1401 CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
1402 SET SESSION AUTHORIZATION regress_rls_alice;
1403 CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
1404 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
1405 SET SESSION AUTHORIZATION regress_rls_bob;
1406 SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
1407 ERROR: infinite recursion detected in policy for relation "rec1"
1408 --
1409 -- recursive RLS and VIEWs in policy
1410 --
1411 SET SESSION AUTHORIZATION regress_rls_alice;
1412 CREATE TABLE s1 (a int, b text);
1413 INSERT INTO s1 (SELECT x, public.fipshash(x::text) FROM generate_series(-10,10) x);
1414 CREATE TABLE s2 (x int, y text);
1415 INSERT INTO s2 (SELECT x, public.fipshash(x::text) FROM generate_series(-6,6) x);
1416 GRANT SELECT ON s1, s2 TO regress_rls_bob;
1417 CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
1418 CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
1419 CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1));
1420 ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;
1421 ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;
1422 SET SESSION AUTHORIZATION regress_rls_bob;
1423 CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
1424 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
1425 ERROR: infinite recursion detected in policy for relation "s1"
1426 INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion)
1427 ERROR: infinite recursion detected in policy for relation "s1"
1428 SET SESSION AUTHORIZATION regress_rls_alice;
1429 DROP POLICY p3 on s1;
1430 ALTER POLICY p2 ON s2 USING (x % 2 = 0);
1431 SET SESSION AUTHORIZATION regress_rls_bob;
1432 SELECT * FROM s1 WHERE f_leak(b); -- OK
1433 NOTICE: f_leak => 03b26944890929ff751653acb2f2af79
1434 a | b
1435 ----+----------------------------------
1436 -6 | 03b26944890929ff751653acb2f2af79
1437 (1 row)
1439 EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
1440 QUERY PLAN
1441 ---------------------------------------------------------------
1442 Seq Scan on s1
1443 Filter: ((ANY (a = (hashed SubPlan 1).col1)) AND f_leak(b))
1444 SubPlan 1
1445 -> Seq Scan on s2
1446 Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text))
1447 (5 rows)
1449 SET SESSION AUTHORIZATION regress_rls_alice;
1450 ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy
1451 SET SESSION AUTHORIZATION regress_rls_bob;
1452 SELECT * FROM s1 WHERE f_leak(b); -- OK
1453 NOTICE: f_leak => 03b26944890929ff751653acb2f2af79
1454 a | b
1455 ----+----------------------------------
1456 -6 | 03b26944890929ff751653acb2f2af79
1457 (1 row)
1459 EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
1460 QUERY PLAN
1461 ---------------------------------------------------------------
1462 Seq Scan on s1
1463 Filter: ((ANY (a = (hashed SubPlan 1).col1)) AND f_leak(b))
1464 SubPlan 1
1465 -> Seq Scan on s2
1466 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
1467 (5 rows)
1469 SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
1470 xx | x | y
1471 ----+----+----------------------------------
1472 -4 | -4 | e5e0093f285a4fb94c3fcc2ad7fd04ed
1473 (1 row)
1475 EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
1476 QUERY PLAN
1477 -------------------------------------------------------------------------
1478 Seq Scan on s2
1479 Filter: (((x % 2) = 0) AND (y ~~ '%28%'::text))
1480 SubPlan 2
1481 -> Limit
1482 -> Seq Scan on s1
1483 Filter: (ANY (a = (hashed SubPlan 1).col1))
1484 SubPlan 1
1485 -> Seq Scan on s2 s2_1
1486 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
1487 (9 rows)
1489 SET SESSION AUTHORIZATION regress_rls_alice;
1490 ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%'));
1491 SET SESSION AUTHORIZATION regress_rls_bob;
1492 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
1493 ERROR: infinite recursion detected in policy for relation "s1"
1494 -- prepared statement with regress_rls_alice privilege
1495 PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
1496 EXECUTE p1(2);
1497 id | a | b
1498 -----+---+-----
1499 102 | 2 | bbb
1500 202 | 2 | bcd
1501 302 | 2 | yyy
1502 (3 rows)
1504 EXPLAIN (COSTS OFF) EXECUTE p1(2);
1505 QUERY PLAN
1506 ----------------------------------------------
1507 Append
1508 -> Seq Scan on t1 t1_1
1509 Filter: ((a <= 2) AND ((a % 2) = 0))
1510 -> Seq Scan on t2 t1_2
1511 Filter: ((a <= 2) AND ((a % 2) = 0))
1512 -> Seq Scan on t3 t1_3
1513 Filter: ((a <= 2) AND ((a % 2) = 0))
1514 (7 rows)
1516 -- superuser is allowed to bypass RLS checks
1517 RESET SESSION AUTHORIZATION;
1518 SET row_security TO OFF;
1519 SELECT * FROM t1 WHERE f_leak(b);
1520 NOTICE: f_leak => aba
1521 NOTICE: f_leak => bbb
1522 NOTICE: f_leak => ccc
1523 NOTICE: f_leak => dad
1524 NOTICE: f_leak => abc
1525 NOTICE: f_leak => bcd
1526 NOTICE: f_leak => cde
1527 NOTICE: f_leak => def
1528 NOTICE: f_leak => xxx
1529 NOTICE: f_leak => yyy
1530 NOTICE: f_leak => zzz
1531 id | a | b
1532 -----+---+-----
1533 101 | 1 | aba
1534 102 | 2 | bbb
1535 103 | 3 | ccc
1536 104 | 4 | dad
1537 201 | 1 | abc
1538 202 | 2 | bcd
1539 203 | 3 | cde
1540 204 | 4 | def
1541 301 | 1 | xxx
1542 302 | 2 | yyy
1543 303 | 3 | zzz
1544 (11 rows)
1546 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
1547 QUERY PLAN
1548 ---------------------------
1549 Append
1550 -> Seq Scan on t1 t1_1
1551 Filter: f_leak(b)
1552 -> Seq Scan on t2 t1_2
1553 Filter: f_leak(b)
1554 -> Seq Scan on t3 t1_3
1555 Filter: f_leak(b)
1556 (7 rows)
1558 -- plan cache should be invalidated
1559 EXECUTE p1(2);
1560 id | a | b
1561 -----+---+-----
1562 101 | 1 | aba
1563 102 | 2 | bbb
1564 201 | 1 | abc
1565 202 | 2 | bcd
1566 301 | 1 | xxx
1567 302 | 2 | yyy
1568 (6 rows)
1570 EXPLAIN (COSTS OFF) EXECUTE p1(2);
1571 QUERY PLAN
1572 ---------------------------
1573 Append
1574 -> Seq Scan on t1 t1_1
1575 Filter: (a <= 2)
1576 -> Seq Scan on t2 t1_2
1577 Filter: (a <= 2)
1578 -> Seq Scan on t3 t1_3
1579 Filter: (a <= 2)
1580 (7 rows)
1582 PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1;
1583 EXECUTE p2(2);
1584 id | a | b
1585 -----+---+-----
1586 102 | 2 | bbb
1587 202 | 2 | bcd
1588 302 | 2 | yyy
1589 (3 rows)
1591 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1592 QUERY PLAN
1593 ---------------------------
1594 Append
1595 -> Seq Scan on t1 t1_1
1596 Filter: (a = 2)
1597 -> Seq Scan on t2 t1_2
1598 Filter: (a = 2)
1599 -> Seq Scan on t3 t1_3
1600 Filter: (a = 2)
1601 (7 rows)
1603 -- also, case when privilege switch from superuser
1604 SET SESSION AUTHORIZATION regress_rls_bob;
1605 SET row_security TO ON;
1606 EXECUTE p2(2);
1607 id | a | b
1608 -----+---+-----
1609 102 | 2 | bbb
1610 202 | 2 | bcd
1611 302 | 2 | yyy
1612 (3 rows)
1614 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1615 QUERY PLAN
1616 ---------------------------------------------
1617 Append
1618 -> Seq Scan on t1 t1_1
1619 Filter: ((a = 2) AND ((a % 2) = 0))
1620 -> Seq Scan on t2 t1_2
1621 Filter: ((a = 2) AND ((a % 2) = 0))
1622 -> Seq Scan on t3 t1_3
1623 Filter: ((a = 2) AND ((a % 2) = 0))
1624 (7 rows)
1626 --
1627 -- UPDATE / DELETE and Row-level security
1628 --
1629 SET SESSION AUTHORIZATION regress_rls_bob;
1630 EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
1631 QUERY PLAN
1632 -----------------------------------------------------------
1633 Update on t1
1634 Update on t1 t1_1
1635 Update on t2 t1_2
1636 Update on t3 t1_3
1637 -> Result
1638 -> Append
1639 -> Seq Scan on t1 t1_1
1640 Filter: (((a % 2) = 0) AND f_leak(b))
1641 -> Seq Scan on t2 t1_2
1642 Filter: (((a % 2) = 0) AND f_leak(b))
1643 -> Seq Scan on t3 t1_3
1644 Filter: (((a % 2) = 0) AND f_leak(b))
1645 (12 rows)
1647 UPDATE t1 SET b = b || b WHERE f_leak(b);
1648 NOTICE: f_leak => bbb
1649 NOTICE: f_leak => dad
1650 NOTICE: f_leak => bcd
1651 NOTICE: f_leak => def
1652 NOTICE: f_leak => yyy
1653 EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1654 QUERY PLAN
1655 -----------------------------------------------
1656 Update on t1
1657 -> Seq Scan on t1
1658 Filter: (((a % 2) = 0) AND f_leak(b))
1659 (3 rows)
1661 UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1662 NOTICE: f_leak => bbbbbb
1663 NOTICE: f_leak => daddad
1664 -- returning clause with system column
1665 UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1666 NOTICE: f_leak => bbbbbb_updt
1667 NOTICE: f_leak => daddad_updt
1668 tableoid | id | a | b | t1
1669 ----------+-----+---+-------------+---------------------
1670 t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt)
1671 t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt)
1672 (2 rows)
1674 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
1675 NOTICE: f_leak => bbbbbb_updt
1676 NOTICE: f_leak => daddad_updt
1677 NOTICE: f_leak => bcdbcd
1678 NOTICE: f_leak => defdef
1679 NOTICE: f_leak => yyyyyy
1680 id | a | b
1681 -----+---+-------------
1682 102 | 2 | bbbbbb_updt
1683 104 | 4 | daddad_updt
1684 202 | 2 | bcdbcd
1685 204 | 4 | defdef
1686 302 | 2 | yyyyyy
1687 (5 rows)
1689 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1690 NOTICE: f_leak => bbbbbb_updt
1691 NOTICE: f_leak => daddad_updt
1692 NOTICE: f_leak => bcdbcd
1693 NOTICE: f_leak => defdef
1694 NOTICE: f_leak => yyyyyy
1695 tableoid | id | a | b | t1
1696 ----------+-----+---+-------------+---------------------
1697 t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt)
1698 t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt)
1699 t2 | 202 | 2 | bcdbcd | (202,2,bcdbcd)
1700 t2 | 204 | 4 | defdef | (204,4,defdef)
1701 t3 | 302 | 2 | yyyyyy | (302,2,yyyyyy)
1702 (5 rows)
1704 -- updates with from clause
1705 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3
1706 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1707 QUERY PLAN
1708 -----------------------------------------------------------------
1709 Update on t2
1710 -> Nested Loop
1711 -> Seq Scan on t2
1712 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1713 -> Seq Scan on t3
1714 Filter: ((a = 2) AND f_leak(b))
1715 (6 rows)
1717 UPDATE t2 SET b=t2.b FROM t3
1718 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1719 NOTICE: f_leak => cde
1720 NOTICE: f_leak => yyyyyy
1721 EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2
1722 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1723 QUERY PLAN
1724 -----------------------------------------------------------------------
1725 Update on t1
1726 Update on t1 t1_1
1727 Update on t2 t1_2
1728 Update on t3 t1_3
1729 -> Nested Loop
1730 -> Seq Scan on t2
1731 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1732 -> Append
1733 -> Seq Scan on t1 t1_1
1734 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1735 -> Seq Scan on t2 t1_2
1736 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1737 -> Seq Scan on t3 t1_3
1738 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1739 (14 rows)
1741 UPDATE t1 SET b=t1.b FROM t2
1742 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1743 NOTICE: f_leak => cde
1744 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1
1745 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1746 QUERY PLAN
1747 -----------------------------------------------------------------------
1748 Update on t2
1749 -> Nested Loop
1750 -> Seq Scan on t2
1751 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1752 -> Append
1753 -> Seq Scan on t1 t1_1
1754 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1755 -> Seq Scan on t2 t1_2
1756 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1757 -> Seq Scan on t3 t1_3
1758 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1759 (11 rows)
1761 UPDATE t2 SET b=t2.b FROM t1
1762 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1763 NOTICE: f_leak => cde
1764 -- updates with from clause self join
1765 EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1766 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1767 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1768 QUERY PLAN
1769 -----------------------------------------------------------------
1770 Update on t2 t2_1
1771 -> Nested Loop
1772 Join Filter: (t2_1.b = t2_2.b)
1773 -> Seq Scan on t2 t2_1
1774 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1775 -> Seq Scan on t2 t2_2
1776 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1777 (7 rows)
1779 UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1780 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1781 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1782 NOTICE: f_leak => cde
1783 NOTICE: f_leak => cde
1784 id | a | b | c | id | a | b | c | t2_1 | t2_2
1785 -----+---+-----+-----+-----+---+-----+-----+-----------------+-----------------
1786 203 | 3 | cde | 3.3 | 203 | 3 | cde | 3.3 | (203,3,cde,3.3) | (203,3,cde,3.3)
1787 (1 row)
1789 EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1790 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1791 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1792 QUERY PLAN
1793 -----------------------------------------------------------------------------
1794 Update on t1 t1_1
1795 Update on t1 t1_1_1
1796 Update on t2 t1_1_2
1797 Update on t3 t1_1_3
1798 -> Nested Loop
1799 Join Filter: (t1_1.b = t1_2.b)
1800 -> Append
1801 -> Seq Scan on t1 t1_1_1
1802 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1803 -> Seq Scan on t2 t1_1_2
1804 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1805 -> Seq Scan on t3 t1_1_3
1806 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1807 -> Materialize
1808 -> Append
1809 -> Seq Scan on t1 t1_2_1
1810 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1811 -> Seq Scan on t2 t1_2_2
1812 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1813 -> Seq Scan on t3 t1_2_3
1814 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1815 (21 rows)
1817 UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1818 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1819 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1820 NOTICE: f_leak => daddad_updt
1821 NOTICE: f_leak => daddad_updt
1822 NOTICE: f_leak => defdef
1823 NOTICE: f_leak => defdef
1824 id | a | b | id | a | b | t1_1 | t1_2
1825 -----+---+-------------+-----+---+-------------+---------------------+---------------------
1826 104 | 4 | daddad_updt | 104 | 4 | daddad_updt | (104,4,daddad_updt) | (104,4,daddad_updt)
1827 204 | 4 | defdef | 204 | 4 | defdef | (204,4,defdef) | (204,4,defdef)
1828 (2 rows)
1830 RESET SESSION AUTHORIZATION;
1831 SET row_security TO OFF;
1832 SELECT * FROM t1 ORDER BY a,b;
1833 id | a | b
1834 -----+---+-------------
1835 101 | 1 | aba
1836 201 | 1 | abc
1837 301 | 1 | xxx
1838 102 | 2 | bbbbbb_updt
1839 202 | 2 | bcdbcd
1840 302 | 2 | yyyyyy
1841 103 | 3 | ccc
1842 203 | 3 | cde
1843 303 | 3 | zzz
1844 104 | 4 | daddad_updt
1845 204 | 4 | defdef
1846 (11 rows)
1848 SET SESSION AUTHORIZATION regress_rls_bob;
1849 SET row_security TO ON;
1850 EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b);
1851 QUERY PLAN
1852 -----------------------------------------------
1853 Delete on t1
1854 -> Seq Scan on t1
1855 Filter: (((a % 2) = 0) AND f_leak(b))
1856 (3 rows)
1858 EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
1859 QUERY PLAN
1860 -----------------------------------------------------
1861 Delete on t1
1862 Delete on t1 t1_1
1863 Delete on t2 t1_2
1864 Delete on t3 t1_3
1865 -> Append
1866 -> Seq Scan on t1 t1_1
1867 Filter: (((a % 2) = 0) AND f_leak(b))
1868 -> Seq Scan on t2 t1_2
1869 Filter: (((a % 2) = 0) AND f_leak(b))
1870 -> Seq Scan on t3 t1_3
1871 Filter: (((a % 2) = 0) AND f_leak(b))
1872 (11 rows)
1874 DELETE FROM only t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1875 NOTICE: f_leak => bbbbbb_updt
1876 NOTICE: f_leak => daddad_updt
1877 tableoid | id | a | b | t1
1878 ----------+-----+---+-------------+---------------------
1879 t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt)
1880 t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt)
1881 (2 rows)
1883 DELETE FROM t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1884 NOTICE: f_leak => bcdbcd
1885 NOTICE: f_leak => defdef
1886 NOTICE: f_leak => yyyyyy
1887 tableoid | id | a | b | t1
1888 ----------+-----+---+--------+----------------
1889 t2 | 202 | 2 | bcdbcd | (202,2,bcdbcd)
1890 t2 | 204 | 4 | defdef | (204,4,defdef)
1891 t3 | 302 | 2 | yyyyyy | (302,2,yyyyyy)
1892 (3 rows)
1894 --
1895 -- S.b. view on top of Row-level security
1896 --
1897 SET SESSION AUTHORIZATION regress_rls_alice;
1898 CREATE TABLE b1 (a int, b text);
1899 INSERT INTO b1 (SELECT x, public.fipshash(x::text) FROM generate_series(-10,10) x);
1900 CREATE POLICY p1 ON b1 USING (a % 2 = 0);
1901 ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
1902 GRANT ALL ON b1 TO regress_rls_bob;
1903 SET SESSION AUTHORIZATION regress_rls_bob;
1904 CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
1905 GRANT ALL ON bv1 TO regress_rls_carol;
1906 SET SESSION AUTHORIZATION regress_rls_carol;
1907 EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
1908 QUERY PLAN
1909 ---------------------------------------------
1910 Subquery Scan on bv1
1911 Filter: f_leak(bv1.b)
1912 -> Seq Scan on b1
1913 Filter: ((a > 0) AND ((a % 2) = 0))
1914 (4 rows)
1916 SELECT * FROM bv1 WHERE f_leak(b);
1917 NOTICE: f_leak => d4735e3a265e16eee03f59718b9b5d03
1918 NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
1919 NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
1920 NOTICE: f_leak => 2c624232cdd221771294dfbb310aca00
1921 NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
1922 a | b
1923 ----+----------------------------------
1924 2 | d4735e3a265e16eee03f59718b9b5d03
1925 4 | 4b227777d4dd1fc61c6f884f48641d02
1926 6 | e7f6c011776e8db7cd330b54174fd76f
1927 8 | 2c624232cdd221771294dfbb310aca00
1928 10 | 4a44dc15364204a80fe80e9039455cc1
1929 (5 rows)
1931 INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO
1932 ERROR: new row violates row-level security policy for table "b1"
1933 INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check
1934 ERROR: new row violates row-level security policy for table "b1"
1935 INSERT INTO bv1 VALUES (12, 'xxx'); -- ok
1936 EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1937 QUERY PLAN
1938 -----------------------------------------------------------------------
1939 Update on b1
1940 -> Seq Scan on b1
1941 Filter: ((a > 0) AND (a = 4) AND ((a % 2) = 0) AND f_leak(b))
1942 (3 rows)
1944 UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1945 NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
1946 EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1947 QUERY PLAN
1948 -----------------------------------------------------------------------
1949 Delete on b1
1950 -> Seq Scan on b1
1951 Filter: ((a > 0) AND (a = 6) AND ((a % 2) = 0) AND f_leak(b))
1952 (3 rows)
1954 DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1955 NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
1956 SET SESSION AUTHORIZATION regress_rls_alice;
1957 SELECT * FROM b1;
1958 a | b
1959 -----+----------------------------------
1960 -10 | c171d4ec282b23db89a99880cd624e9b
1961 -9 | d5c534fde62beb89c745a59952c8efed
1962 -8 | e91592205d3881e3ea35d66973bb4898
1963 -7 | a770d3270c9dcdedf12ed9fd70444f7c
1964 -6 | 03b26944890929ff751653acb2f2af79
1965 -5 | 37aa1ccf80e481832b2db282d4d4f895
1966 -4 | e5e0093f285a4fb94c3fcc2ad7fd04ed
1967 -3 | 615bdd17c2556f82f384392ea8557f8c
1968 -2 | cf3bae39dd692048a8bf961182e6a34d
1969 -1 | 1bad6b8cf97131fceab8543e81f77571
1970 0 | 5feceb66ffc86f38d952786c6d696c79
1971 1 | 6b86b273ff34fce19d6b804eff5a3f57
1972 2 | d4735e3a265e16eee03f59718b9b5d03
1973 3 | 4e07408562bedb8b60ce05c1decfe3ad
1974 5 | ef2d127de37b942baad06145e54b0c61
1975 7 | 7902699be42c8a8e46fbbb4501726517
1976 8 | 2c624232cdd221771294dfbb310aca00
1977 9 | 19581e27de7ced00ff1ce50b2047e7a5
1978 10 | 4a44dc15364204a80fe80e9039455cc1
1979 12 | xxx
1980 4 | yyy
1981 (21 rows)
1983 --
1984 -- INSERT ... ON CONFLICT DO UPDATE and Row-level security
1985 --
1986 SET SESSION AUTHORIZATION regress_rls_alice;
1987 DROP POLICY p1 ON document;
1988 DROP POLICY p1r ON document;
1989 CREATE POLICY p1 ON document FOR SELECT USING (true);
1990 CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
1991 CREATE POLICY p3 ON document FOR UPDATE
1992 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
1993 WITH CHECK (dauthor = current_user);
1994 SET SESSION AUTHORIZATION regress_rls_bob;
1995 -- Exists...
1996 SELECT * FROM document WHERE did = 2;
1997 did | cid | dlevel | dauthor | dtitle
1998 -----+-----+--------+-----------------+-----------------
1999 2 | 11 | 2 | regress_rls_bob | my second novel
2000 (1 row)
2002 -- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
2003 -- alternative UPDATE path happens to be taken):
2004 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel')
2005 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor;
2006 ERROR: new row violates row-level security policy for table "document"
2007 -- Violates USING qual for UPDATE policy p3.
2008 --
2009 -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
2010 -- updated is not a "novel"/cid 11 (row is not leaked, even though we have
2011 -- SELECT privileges sufficient to see the row in this instance):
2012 INSERT INTO document VALUES (33, 22, 1, 'regress_rls_bob', 'okay science fiction'); -- preparation for next statement
2013 INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi') -- takes UPDATE path
2014 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
2015 ERROR: new row violates row-level security policy (USING expression) for table "document"
2016 -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
2017 -- not violated):
2018 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
2019 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2020 did | cid | dlevel | dauthor | dtitle
2021 -----+-----+--------+-----------------+----------------
2022 2 | 11 | 2 | regress_rls_bob | my first novel
2023 (1 row)
2025 -- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated):
2026 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
2027 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
2028 did | cid | dlevel | dauthor | dtitle
2029 -----+-----+--------+-----------------+-----------------------
2030 78 | 11 | 1 | regress_rls_bob | some technology novel
2031 (1 row)
2033 -- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the
2034 -- case in respect of *existing* tuple):
2035 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
2036 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
2037 did | cid | dlevel | dauthor | dtitle
2038 -----+-----+--------+-----------------+-----------------------
2039 78 | 33 | 1 | regress_rls_bob | some technology novel
2040 (1 row)
2042 -- Same query a third time, but now fails due to existing tuple finally not
2043 -- passing quals:
2044 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
2045 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
2046 ERROR: new row violates row-level security policy (USING expression) for table "document"
2047 -- Don't fail just because INSERT doesn't satisfy WITH CHECK option that
2048 -- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE
2049 -- path *isn't* taken, and so UPDATE-related policy does not apply:
2050 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
2051 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2052 did | cid | dlevel | dauthor | dtitle
2053 -----+-----+--------+-----------------+----------------------------------
2054 79 | 33 | 1 | regress_rls_bob | technology book, can only insert
2055 (1 row)
2057 -- But this time, the same statement fails, because the UPDATE path is taken,
2058 -- and updating the row just inserted falls afoul of security barrier qual
2059 -- (enforced as WCO) -- what we might have updated target tuple to is
2060 -- irrelevant, in fact.
2061 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
2062 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2063 ERROR: new row violates row-level security policy (USING expression) for table "document"
2064 -- Test default USING qual enforced as WCO
2065 SET SESSION AUTHORIZATION regress_rls_alice;
2066 DROP POLICY p1 ON document;
2067 DROP POLICY p2 ON document;
2068 DROP POLICY p3 ON document;
2069 CREATE POLICY p3_with_default ON document FOR UPDATE
2070 USING (cid = (SELECT cid from category WHERE cname = 'novel'));
2071 SET SESSION AUTHORIZATION regress_rls_bob;
2072 -- Just because WCO-style enforcement of USING quals occurs with
2073 -- existing/target tuple does not mean that the implementation can be allowed
2074 -- to fail to also enforce this qual against the final tuple appended to
2075 -- relation (since in the absence of an explicit WCO, this is also interpreted
2076 -- as an UPDATE/ALL WCO in general).
2077 --
2078 -- UPDATE path is taken here (fails due to existing tuple). Note that this is
2079 -- not reported as a "USING expression", because it's an RLS UPDATE check that originated as
2080 -- a USING qual for the purposes of RLS in general, as opposed to an explicit
2081 -- USING qual that is ordinarily a security barrier. We leave it up to the
2082 -- UPDATE to make this fail:
2083 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
2084 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2085 ERROR: new row violates row-level security policy for table "document"
2086 -- UPDATE path is taken here. Existing tuple passes, since its cid
2087 -- corresponds to "novel", but default USING qual is enforced against
2088 -- post-UPDATE tuple too (as always when updating with a policy that lacks an
2089 -- explicit WCO), and so this fails:
2090 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'my first novel')
2091 ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *;
2092 ERROR: new row violates row-level security policy for table "document"
2093 SET SESSION AUTHORIZATION regress_rls_alice;
2094 DROP POLICY p3_with_default ON document;
2095 --
2096 -- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE
2097 -- tests)
2098 --
2099 CREATE POLICY p3_with_all ON document FOR ALL
2100 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
2101 WITH CHECK (dauthor = current_user);
2102 SET SESSION AUTHORIZATION regress_rls_bob;
2103 -- Fails, since ALL WCO is enforced in insert path:
2104 INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel')
2105 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33;
2106 ERROR: new row violates row-level security policy for table "document"
2107 -- Fails, since ALL policy USING qual is enforced (existing, target tuple is in
2108 -- violation, since it has the "manga" cid):
2109 INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
2110 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
2111 ERROR: new row violates row-level security policy (USING expression) for table "document"
2112 -- Fails, since ALL WCO are enforced:
2113 INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
2114 ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol';
2115 ERROR: new row violates row-level security policy for table "document"
2116 --
2117 -- MERGE
2118 --
2119 RESET SESSION AUTHORIZATION;
2120 DROP POLICY p3_with_all ON document;
2121 ALTER TABLE document ADD COLUMN dnotes text DEFAULT '';
2122 -- all documents are readable
2123 CREATE POLICY p1 ON document FOR SELECT USING (true);
2124 -- one may insert documents only authored by them
2125 CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
2126 -- one may only update documents in 'novel' category and new dlevel must be > 0
2127 CREATE POLICY p3 ON document FOR UPDATE
2128 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
2129 WITH CHECK (dlevel > 0);
2130 -- one may only delete documents in 'manga' category
2131 CREATE POLICY p4 ON document FOR DELETE
2132 USING (cid = (SELECT cid from category WHERE cname = 'manga'));
2133 SELECT * FROM document;
2134 did | cid | dlevel | dauthor | dtitle | dnotes
2135 -----+-----+--------+-------------------+----------------------------------+--------
2136 1 | 11 | 1 | regress_rls_bob | my first novel |
2137 3 | 22 | 2 | regress_rls_bob | my science fiction |
2138 4 | 44 | 1 | regress_rls_bob | my first manga |
2139 5 | 44 | 2 | regress_rls_bob | my second manga |
2140 6 | 22 | 1 | regress_rls_carol | great science fiction |
2141 7 | 33 | 2 | regress_rls_carol | great technology book |
2142 8 | 44 | 1 | regress_rls_carol | great manga |
2143 9 | 22 | 1 | regress_rls_dave | awesome science fiction |
2144 10 | 33 | 2 | regress_rls_dave | awesome technology book |
2145 11 | 33 | 1 | regress_rls_carol | hoge |
2146 33 | 22 | 1 | regress_rls_bob | okay science fiction |
2147 2 | 11 | 2 | regress_rls_bob | my first novel |
2148 78 | 33 | 1 | regress_rls_bob | some technology novel |
2149 79 | 33 | 1 | regress_rls_bob | technology book, can only insert |
2150 (14 rows)
2152 SET SESSION AUTHORIZATION regress_rls_bob;
2153 -- Fails, since update violates WITH CHECK qual on dlevel
2154 MERGE INTO document d
2155 USING (SELECT 1 as sdid) s
2156 ON did = s.sdid
2157 WHEN MATCHED THEN
2158 UPDATE SET dnotes = dnotes || ' notes added by merge1 ', dlevel = 0;
2159 ERROR: new row violates row-level security policy for table "document"
2160 -- Should be OK since USING and WITH CHECK quals pass
2161 MERGE INTO document d
2162 USING (SELECT 1 as sdid) s
2163 ON did = s.sdid
2164 WHEN MATCHED THEN
2165 UPDATE SET dnotes = dnotes || ' notes added by merge2 ';
2166 -- Even when dlevel is updated explicitly, but to the existing value
2167 MERGE INTO document d
2168 USING (SELECT 1 as sdid) s
2169 ON did = s.sdid
2170 WHEN MATCHED THEN
2171 UPDATE SET dnotes = dnotes || ' notes added by merge3 ', dlevel = 1;
2172 -- There is a MATCH for did = 3, but UPDATE's USING qual does not allow
2173 -- updating an item in category 'science fiction'
2174 MERGE INTO document d
2175 USING (SELECT 3 as sdid) s
2176 ON did = s.sdid
2177 WHEN MATCHED THEN
2178 UPDATE SET dnotes = dnotes || ' notes added by merge ';
2179 ERROR: target row violates row-level security policy (USING expression) for table "document"
2180 -- The same thing with DELETE action, but fails again because no permissions
2181 -- to delete items in 'science fiction' category that did 3 belongs to.
2182 MERGE INTO document d
2183 USING (SELECT 3 as sdid) s
2184 ON did = s.sdid
2185 WHEN MATCHED THEN
2186 DELETE;
2187 ERROR: target row violates row-level security policy (USING expression) for table "document"
2188 -- Document with did 4 belongs to 'manga' category which is allowed for
2189 -- deletion. But this fails because the UPDATE action is matched first and
2190 -- UPDATE policy does not allow updation in the category.
2191 MERGE INTO document d
2192 USING (SELECT 4 as sdid) s
2193 ON did = s.sdid
2194 WHEN MATCHED AND dnotes = '' THEN
2195 UPDATE SET dnotes = dnotes || ' notes added by merge '
2196 WHEN MATCHED THEN
2197 DELETE;
2198 ERROR: target row violates row-level security policy (USING expression) for table "document"
2199 -- UPDATE action is not matched this time because of the WHEN qual.
2200 -- DELETE still fails because role regress_rls_bob does not have SELECT
2201 -- privileges on 'manga' category row in the category table.
2202 MERGE INTO document d
2203 USING (SELECT 4 as sdid) s
2204 ON did = s.sdid
2205 WHEN MATCHED AND dnotes <> '' THEN
2206 UPDATE SET dnotes = dnotes || ' notes added by merge '
2207 WHEN MATCHED THEN
2208 DELETE;
2209 ERROR: target row violates row-level security policy (USING expression) for table "document"
2210 -- OK if DELETE is replaced with DO NOTHING
2211 MERGE INTO document d
2212 USING (SELECT 4 as sdid) s
2213 ON did = s.sdid
2214 WHEN MATCHED AND dnotes <> '' THEN
2215 UPDATE SET dnotes = dnotes || ' notes added by merge '
2216 WHEN MATCHED THEN
2217 DO NOTHING;
2218 SELECT * FROM document WHERE did = 4;
2219 did | cid | dlevel | dauthor | dtitle | dnotes
2220 -----+-----+--------+-----------------+----------------+--------
2221 4 | 44 | 1 | regress_rls_bob | my first manga |
2222 (1 row)
2224 -- Switch to regress_rls_carol role and try the DELETE again. It should succeed
2225 -- this time
2226 RESET SESSION AUTHORIZATION;
2227 SET SESSION AUTHORIZATION regress_rls_carol;
2228 MERGE INTO document d
2229 USING (SELECT 4 as sdid) s
2230 ON did = s.sdid
2231 WHEN MATCHED AND dnotes <> '' THEN
2232 UPDATE SET dnotes = dnotes || ' notes added by merge '
2233 WHEN MATCHED THEN
2234 DELETE;
2235 -- Switch back to regress_rls_bob role
2236 RESET SESSION AUTHORIZATION;
2237 SET SESSION AUTHORIZATION regress_rls_bob;
2238 -- Try INSERT action. This fails because we are trying to insert
2239 -- dauthor = regress_rls_dave and INSERT's WITH CHECK does not allow
2240 -- that
2241 MERGE INTO document d
2242 USING (SELECT 12 as sdid) s
2243 ON did = s.sdid
2244 WHEN MATCHED THEN
2245 DELETE
2246 WHEN NOT MATCHED THEN
2247 INSERT VALUES (12, 11, 1, 'regress_rls_dave', 'another novel');
2248 ERROR: new row violates row-level security policy for table "document"
2249 -- This should be fine
2250 MERGE INTO document d
2251 USING (SELECT 12 as sdid) s
2252 ON did = s.sdid
2253 WHEN MATCHED THEN
2254 DELETE
2255 WHEN NOT MATCHED THEN
2256 INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel');
2257 -- ok
2258 MERGE INTO document d
2259 USING (SELECT 1 as sdid) s
2260 ON did = s.sdid
2261 WHEN MATCHED THEN
2262 UPDATE SET dnotes = dnotes || ' notes added by merge4 '
2263 WHEN NOT MATCHED THEN
2264 INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel');
2265 -- drop and create a new SELECT policy which prevents us from reading
2266 -- any document except with category 'novel'
2267 RESET SESSION AUTHORIZATION;
2268 DROP POLICY p1 ON document;
2269 CREATE POLICY p1 ON document FOR SELECT
2270 USING (cid = (SELECT cid from category WHERE cname = 'novel'));
2271 SET SESSION AUTHORIZATION regress_rls_bob;
2272 -- MERGE can no longer see the matching row and hence attempts the
2273 -- NOT MATCHED action, which results in unique key violation
2274 MERGE INTO document d
2275 USING (SELECT 7 as sdid) s
2276 ON did = s.sdid
2277 WHEN MATCHED THEN
2278 UPDATE SET dnotes = dnotes || ' notes added by merge5 '
2279 WHEN NOT MATCHED THEN
2280 INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel');
2281 ERROR: duplicate key value violates unique constraint "document_pkey"
2282 -- UPDATE action fails if new row is not visible
2283 MERGE INTO document d
2284 USING (SELECT 1 as sdid) s
2285 ON did = s.sdid
2286 WHEN MATCHED THEN
2287 UPDATE SET dnotes = dnotes || ' notes added by merge6 ',
2288 cid = (SELECT cid from category WHERE cname = 'technology');
2289 ERROR: new row violates row-level security policy for table "document"
2290 -- but OK if new row is visible
2291 MERGE INTO document d
2292 USING (SELECT 1 as sdid) s
2293 ON did = s.sdid
2294 WHEN MATCHED THEN
2295 UPDATE SET dnotes = dnotes || ' notes added by merge7 ',
2296 cid = (SELECT cid from category WHERE cname = 'novel');
2297 -- OK to insert a new row that is not visible
2298 MERGE INTO document d
2299 USING (SELECT 13 as sdid) s
2300 ON did = s.sdid
2301 WHEN MATCHED THEN
2302 UPDATE SET dnotes = dnotes || ' notes added by merge8 '
2303 WHEN NOT MATCHED THEN
2304 INSERT VALUES (13, 44, 1, 'regress_rls_bob', 'new manga');
2305 SELECT * FROM document WHERE did = 13;
2306 did | cid | dlevel | dauthor | dtitle | dnotes
2307 -----+-----+--------+---------+--------+--------
2308 (0 rows)
2310 -- but not OK if RETURNING is used
2311 MERGE INTO document d
2312 USING (SELECT 14 as sdid) s
2313 ON did = s.sdid
2314 WHEN MATCHED THEN
2315 UPDATE SET dnotes = dnotes || ' notes added by merge9 '
2316 WHEN NOT MATCHED THEN
2317 INSERT VALUES (14, 44, 1, 'regress_rls_bob', 'new manga')
2318 RETURNING *;
2319 ERROR: new row violates row-level security policy for table "document"
2320 -- but OK if new row is visible
2321 MERGE INTO document d
2322 USING (SELECT 14 as sdid) s
2323 ON did = s.sdid
2324 WHEN MATCHED THEN
2325 UPDATE SET dnotes = dnotes || ' notes added by merge10 '
2326 WHEN NOT MATCHED THEN
2327 INSERT VALUES (14, 11, 1, 'regress_rls_bob', 'new novel')
2328 RETURNING *;
2329 sdid | did | cid | dlevel | dauthor | dtitle | dnotes
2330 ------+-----+-----+--------+-----------------+-----------+--------
2331 14 | 14 | 11 | 1 | regress_rls_bob | new novel |
2332 (1 row)
2334 RESET SESSION AUTHORIZATION;
2335 -- drop the restrictive SELECT policy so that we can look at the
2336 -- final state of the table
2337 DROP POLICY p1 ON document;
2338 -- Just check everything went per plan
2339 SELECT * FROM document;
2340 did | cid | dlevel | dauthor | dtitle | dnotes
2341 -----+-----+--------+-------------------+----------------------------------+----------------------------------------------------------------------------------------------
2342 3 | 22 | 2 | regress_rls_bob | my science fiction |
2343 5 | 44 | 2 | regress_rls_bob | my second manga |
2344 6 | 22 | 1 | regress_rls_carol | great science fiction |
2345 7 | 33 | 2 | regress_rls_carol | great technology book |
2346 8 | 44 | 1 | regress_rls_carol | great manga |
2347 9 | 22 | 1 | regress_rls_dave | awesome science fiction |
2348 10 | 33 | 2 | regress_rls_dave | awesome technology book |
2349 11 | 33 | 1 | regress_rls_carol | hoge |
2350 33 | 22 | 1 | regress_rls_bob | okay science fiction |
2351 2 | 11 | 2 | regress_rls_bob | my first novel |
2352 78 | 33 | 1 | regress_rls_bob | some technology novel |
2353 79 | 33 | 1 | regress_rls_bob | technology book, can only insert |
2354 12 | 11 | 1 | regress_rls_bob | another novel |
2355 1 | 11 | 1 | regress_rls_bob | my first novel | notes added by merge2 notes added by merge3 notes added by merge4 notes added by merge7
2356 13 | 44 | 1 | regress_rls_bob | new manga |
2357 14 | 11 | 1 | regress_rls_bob | new novel |
2358 (16 rows)
2360 --
2361 -- ROLE/GROUP
2362 --
2363 SET SESSION AUTHORIZATION regress_rls_alice;
2364 CREATE TABLE z1 (a int, b text);
2365 CREATE TABLE z2 (a int, b text);
2366 GRANT SELECT ON z1,z2 TO regress_rls_group1, regress_rls_group2,
2367 regress_rls_bob, regress_rls_carol;
2368 INSERT INTO z1 VALUES
2369 (1, 'aba'),
2370 (2, 'bbb'),
2371 (3, 'ccc'),
2372 (4, 'dad');
2373 CREATE POLICY p1 ON z1 TO regress_rls_group1 USING (a % 2 = 0);
2374 CREATE POLICY p2 ON z1 TO regress_rls_group2 USING (a % 2 = 1);
2375 ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
2376 SET SESSION AUTHORIZATION regress_rls_bob;
2377 SELECT * FROM z1 WHERE f_leak(b);
2378 NOTICE: f_leak => bbb
2379 NOTICE: f_leak => dad
2380 a | b
2381 ---+-----
2382 2 | bbb
2383 4 | dad
2384 (2 rows)
2386 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2387 QUERY PLAN
2388 -----------------------------------------
2389 Seq Scan on z1
2390 Filter: (((a % 2) = 0) AND f_leak(b))
2391 (2 rows)
2393 PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b);
2394 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2395 QUERY PLAN
2396 -----------------------------------------
2397 Seq Scan on z1
2398 Filter: (((a % 2) = 0) AND f_leak(b))
2399 (2 rows)
2401 PREPARE plancache_test2 AS WITH q AS MATERIALIZED (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
2402 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2403 QUERY PLAN
2404 -------------------------------------------------
2405 Nested Loop
2406 CTE q
2407 -> Seq Scan on z1
2408 Filter: (((a % 2) = 0) AND f_leak(b))
2409 -> CTE Scan on q
2410 -> Materialize
2411 -> Seq Scan on z2
2412 (7 rows)
2414 PREPARE plancache_test3 AS WITH q AS MATERIALIZED (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
2415 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2416 QUERY PLAN
2417 -----------------------------------------------------
2418 Nested Loop
2419 CTE q
2420 -> Seq Scan on z2
2421 -> CTE Scan on q
2422 -> Materialize
2423 -> Seq Scan on z1
2424 Filter: (((a % 2) = 0) AND f_leak(b))
2425 (7 rows)
2427 SET ROLE regress_rls_group1;
2428 SELECT * FROM z1 WHERE f_leak(b);
2429 NOTICE: f_leak => bbb
2430 NOTICE: f_leak => dad
2431 a | b
2432 ---+-----
2433 2 | bbb
2434 4 | dad
2435 (2 rows)
2437 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2438 QUERY PLAN
2439 -----------------------------------------
2440 Seq Scan on z1
2441 Filter: (((a % 2) = 0) AND f_leak(b))
2442 (2 rows)
2444 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2445 QUERY PLAN
2446 -----------------------------------------
2447 Seq Scan on z1
2448 Filter: (((a % 2) = 0) AND f_leak(b))
2449 (2 rows)
2451 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2452 QUERY PLAN
2453 -------------------------------------------------
2454 Nested Loop
2455 CTE q
2456 -> Seq Scan on z1
2457 Filter: (((a % 2) = 0) AND f_leak(b))
2458 -> CTE Scan on q
2459 -> Materialize
2460 -> Seq Scan on z2
2461 (7 rows)
2463 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2464 QUERY PLAN
2465 -----------------------------------------------------
2466 Nested Loop
2467 CTE q
2468 -> Seq Scan on z2
2469 -> CTE Scan on q
2470 -> Materialize
2471 -> Seq Scan on z1
2472 Filter: (((a % 2) = 0) AND f_leak(b))
2473 (7 rows)
2475 SET SESSION AUTHORIZATION regress_rls_carol;
2476 SELECT * FROM z1 WHERE f_leak(b);
2477 NOTICE: f_leak => aba
2478 NOTICE: f_leak => ccc
2479 a | b
2480 ---+-----
2481 1 | aba
2482 3 | ccc
2483 (2 rows)
2485 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2486 QUERY PLAN
2487 -----------------------------------------
2488 Seq Scan on z1
2489 Filter: (((a % 2) = 1) AND f_leak(b))
2490 (2 rows)
2492 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2493 QUERY PLAN
2494 -----------------------------------------
2495 Seq Scan on z1
2496 Filter: (((a % 2) = 1) AND f_leak(b))
2497 (2 rows)
2499 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2500 QUERY PLAN
2501 -------------------------------------------------
2502 Nested Loop
2503 CTE q
2504 -> Seq Scan on z1
2505 Filter: (((a % 2) = 1) AND f_leak(b))
2506 -> CTE Scan on q
2507 -> Materialize
2508 -> Seq Scan on z2
2509 (7 rows)
2511 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2512 QUERY PLAN
2513 -----------------------------------------------------
2514 Nested Loop
2515 CTE q
2516 -> Seq Scan on z2
2517 -> CTE Scan on q
2518 -> Materialize
2519 -> Seq Scan on z1
2520 Filter: (((a % 2) = 1) AND f_leak(b))
2521 (7 rows)
2523 SET ROLE regress_rls_group2;
2524 SELECT * FROM z1 WHERE f_leak(b);
2525 NOTICE: f_leak => aba
2526 NOTICE: f_leak => ccc
2527 a | b
2528 ---+-----
2529 1 | aba
2530 3 | ccc
2531 (2 rows)
2533 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2534 QUERY PLAN
2535 -----------------------------------------
2536 Seq Scan on z1
2537 Filter: (((a % 2) = 1) AND f_leak(b))
2538 (2 rows)
2540 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2541 QUERY PLAN
2542 -----------------------------------------
2543 Seq Scan on z1
2544 Filter: (((a % 2) = 1) AND f_leak(b))
2545 (2 rows)
2547 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2548 QUERY PLAN
2549 -------------------------------------------------
2550 Nested Loop
2551 CTE q
2552 -> Seq Scan on z1
2553 Filter: (((a % 2) = 1) AND f_leak(b))
2554 -> CTE Scan on q
2555 -> Materialize
2556 -> Seq Scan on z2
2557 (7 rows)
2559 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2560 QUERY PLAN
2561 -----------------------------------------------------
2562 Nested Loop
2563 CTE q
2564 -> Seq Scan on z2
2565 -> CTE Scan on q
2566 -> Materialize
2567 -> Seq Scan on z1
2568 Filter: (((a % 2) = 1) AND f_leak(b))
2569 (7 rows)
2571 --
2572 -- Views should follow policy for view owner.
2573 --
2574 -- View and Table owner are the same.
2575 SET SESSION AUTHORIZATION regress_rls_alice;
2576 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
2577 GRANT SELECT ON rls_view TO regress_rls_bob;
2578 -- Query as role that is not owner of view or table. Should return all records.
2579 SET SESSION AUTHORIZATION regress_rls_bob;
2580 SELECT * FROM rls_view;
2581 NOTICE: f_leak => aba
2582 NOTICE: f_leak => bbb
2583 NOTICE: f_leak => ccc
2584 NOTICE: f_leak => dad
2585 a | b
2586 ---+-----
2587 1 | aba
2588 2 | bbb
2589 3 | ccc
2590 4 | dad
2591 (4 rows)
2593 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2594 QUERY PLAN
2595 ---------------------
2596 Seq Scan on z1
2597 Filter: f_leak(b)
2598 (2 rows)
2600 -- Query as view/table owner. Should return all records.
2601 SET SESSION AUTHORIZATION regress_rls_alice;
2602 SELECT * FROM rls_view;
2603 NOTICE: f_leak => aba
2604 NOTICE: f_leak => bbb
2605 NOTICE: f_leak => ccc
2606 NOTICE: f_leak => dad
2607 a | b
2608 ---+-----
2609 1 | aba
2610 2 | bbb
2611 3 | ccc
2612 4 | dad
2613 (4 rows)
2615 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2616 QUERY PLAN
2617 ---------------------
2618 Seq Scan on z1
2619 Filter: f_leak(b)
2620 (2 rows)
2622 DROP VIEW rls_view;
2623 -- View and Table owners are different.
2624 SET SESSION AUTHORIZATION regress_rls_bob;
2625 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
2626 GRANT SELECT ON rls_view TO regress_rls_alice;
2627 -- Query as role that is not owner of view but is owner of table.
2628 -- Should return records based on view owner policies.
2629 SET SESSION AUTHORIZATION regress_rls_alice;
2630 SELECT * FROM rls_view;
2631 NOTICE: f_leak => bbb
2632 NOTICE: f_leak => dad
2633 a | b
2634 ---+-----
2635 2 | bbb
2636 4 | dad
2637 (2 rows)
2639 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2640 QUERY PLAN
2641 -----------------------------------------
2642 Seq Scan on z1
2643 Filter: (((a % 2) = 0) AND f_leak(b))
2644 (2 rows)
2646 -- Query as role that is not owner of table but is owner of view.
2647 -- Should return records based on view owner policies.
2648 SET SESSION AUTHORIZATION regress_rls_bob;
2649 SELECT * FROM rls_view;
2650 NOTICE: f_leak => bbb
2651 NOTICE: f_leak => dad
2652 a | b
2653 ---+-----
2654 2 | bbb
2655 4 | dad
2656 (2 rows)
2658 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2659 QUERY PLAN
2660 -----------------------------------------
2661 Seq Scan on z1
2662 Filter: (((a % 2) = 0) AND f_leak(b))
2663 (2 rows)
2665 -- Query as role that is not the owner of the table or view without permissions.
2666 SET SESSION AUTHORIZATION regress_rls_carol;
2667 SELECT * FROM rls_view; --fail - permission denied.
2668 ERROR: permission denied for view rls_view
2669 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
2670 ERROR: permission denied for view rls_view
2671 -- Query as role that is not the owner of the table or view with permissions.
2672 SET SESSION AUTHORIZATION regress_rls_bob;
2673 GRANT SELECT ON rls_view TO regress_rls_carol;
2674 SET SESSION AUTHORIZATION regress_rls_carol;
2675 SELECT * FROM rls_view;
2676 NOTICE: f_leak => bbb
2677 NOTICE: f_leak => dad
2678 a | b
2679 ---+-----
2680 2 | bbb
2681 4 | dad
2682 (2 rows)
2684 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2685 QUERY PLAN
2686 -----------------------------------------
2687 Seq Scan on z1
2688 Filter: (((a % 2) = 0) AND f_leak(b))
2689 (2 rows)
2691 -- Policy requiring access to another table.
2692 SET SESSION AUTHORIZATION regress_rls_alice;
2693 CREATE TABLE z1_blacklist (a int);
2694 INSERT INTO z1_blacklist VALUES (3), (4);
2695 CREATE POLICY p3 ON z1 AS RESTRICTIVE USING (a NOT IN (SELECT a FROM z1_blacklist));
2696 -- Query as role that is not owner of table but is owner of view without permissions.
2697 SET SESSION AUTHORIZATION regress_rls_bob;
2698 SELECT * FROM rls_view; --fail - permission denied.
2699 ERROR: permission denied for table z1_blacklist
2700 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
2701 ERROR: permission denied for table z1_blacklist
2702 -- Query as role that is not the owner of the table or view without permissions.
2703 SET SESSION AUTHORIZATION regress_rls_carol;
2704 SELECT * FROM rls_view; --fail - permission denied.
2705 ERROR: permission denied for table z1_blacklist
2706 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
2707 ERROR: permission denied for table z1_blacklist
2708 -- Query as role that is not owner of table but is owner of view with permissions.
2709 SET SESSION AUTHORIZATION regress_rls_alice;
2710 GRANT SELECT ON z1_blacklist TO regress_rls_bob;
2711 SET SESSION AUTHORIZATION regress_rls_bob;
2712 SELECT * FROM rls_view;
2713 NOTICE: f_leak => bbb
2714 a | b
2715 ---+-----
2716 2 | bbb
2717 (1 row)
2719 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2720 QUERY PLAN
2721 ---------------------------------------------------------------------------------------
2722 Seq Scan on z1
2723 Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b))
2724 SubPlan 1
2725 -> Seq Scan on z1_blacklist
2726 (4 rows)
2728 -- Query as role that is not the owner of the table or view with permissions.
2729 SET SESSION AUTHORIZATION regress_rls_carol;
2730 SELECT * FROM rls_view;
2731 NOTICE: f_leak => bbb
2732 a | b
2733 ---+-----
2734 2 | bbb
2735 (1 row)
2737 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2738 QUERY PLAN
2739 ---------------------------------------------------------------------------------------
2740 Seq Scan on z1
2741 Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b))
2742 SubPlan 1
2743 -> Seq Scan on z1_blacklist
2744 (4 rows)
2746 SET SESSION AUTHORIZATION regress_rls_alice;
2747 REVOKE SELECT ON z1_blacklist FROM regress_rls_bob;
2748 DROP POLICY p3 ON z1;
2749 SET SESSION AUTHORIZATION regress_rls_bob;
2750 DROP VIEW rls_view;
2751 --
2752 -- Security invoker views should follow policy for current user.
2753 --
2754 -- View and table owner are the same.
2755 SET SESSION AUTHORIZATION regress_rls_alice;
2756 CREATE VIEW rls_view WITH (security_invoker) AS
2757 SELECT * FROM z1 WHERE f_leak(b);
2758 GRANT SELECT ON rls_view TO regress_rls_bob;
2759 GRANT SELECT ON rls_view TO regress_rls_carol;
2760 -- Query as table owner. Should return all records.
2761 SELECT * FROM rls_view;
2762 NOTICE: f_leak => aba
2763 NOTICE: f_leak => bbb
2764 NOTICE: f_leak => ccc
2765 NOTICE: f_leak => dad
2766 a | b
2767 ---+-----
2768 1 | aba
2769 2 | bbb
2770 3 | ccc
2771 4 | dad
2772 (4 rows)
2774 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2775 QUERY PLAN
2776 ---------------------
2777 Seq Scan on z1
2778 Filter: f_leak(b)
2779 (2 rows)
2781 -- Queries as other users.
2782 -- Should return records based on current user's policies.
2783 SET SESSION AUTHORIZATION regress_rls_bob;
2784 SELECT * FROM rls_view;
2785 NOTICE: f_leak => bbb
2786 NOTICE: f_leak => dad
2787 a | b
2788 ---+-----
2789 2 | bbb
2790 4 | dad
2791 (2 rows)
2793 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2794 QUERY PLAN
2795 -----------------------------------------
2796 Seq Scan on z1
2797 Filter: (((a % 2) = 0) AND f_leak(b))
2798 (2 rows)
2800 SET SESSION AUTHORIZATION regress_rls_carol;
2801 SELECT * FROM rls_view;
2802 NOTICE: f_leak => aba
2803 NOTICE: f_leak => ccc
2804 a | b
2805 ---+-----
2806 1 | aba
2807 3 | ccc
2808 (2 rows)
2810 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2811 QUERY PLAN
2812 -----------------------------------------
2813 Seq Scan on z1
2814 Filter: (((a % 2) = 1) AND f_leak(b))
2815 (2 rows)
2817 -- View and table owners are different.
2818 SET SESSION AUTHORIZATION regress_rls_alice;
2819 DROP VIEW rls_view;
2820 SET SESSION AUTHORIZATION regress_rls_bob;
2821 CREATE VIEW rls_view WITH (security_invoker) AS
2822 SELECT * FROM z1 WHERE f_leak(b);
2823 GRANT SELECT ON rls_view TO regress_rls_alice;
2824 GRANT SELECT ON rls_view TO regress_rls_carol;
2825 -- Query as table owner. Should return all records.
2826 SET SESSION AUTHORIZATION regress_rls_alice;
2827 SELECT * FROM rls_view;
2828 NOTICE: f_leak => aba
2829 NOTICE: f_leak => bbb
2830 NOTICE: f_leak => ccc
2831 NOTICE: f_leak => dad
2832 a | b
2833 ---+-----
2834 1 | aba
2835 2 | bbb
2836 3 | ccc
2837 4 | dad
2838 (4 rows)
2840 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2841 QUERY PLAN
2842 ---------------------
2843 Seq Scan on z1
2844 Filter: f_leak(b)
2845 (2 rows)
2847 -- Queries as other users.
2848 -- Should return records based on current user's policies.
2849 SET SESSION AUTHORIZATION regress_rls_bob;
2850 SELECT * FROM rls_view;
2851 NOTICE: f_leak => bbb
2852 NOTICE: f_leak => dad
2853 a | b
2854 ---+-----
2855 2 | bbb
2856 4 | dad
2857 (2 rows)
2859 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2860 QUERY PLAN
2861 -----------------------------------------
2862 Seq Scan on z1
2863 Filter: (((a % 2) = 0) AND f_leak(b))
2864 (2 rows)
2866 SET SESSION AUTHORIZATION regress_rls_carol;
2867 SELECT * FROM rls_view;
2868 NOTICE: f_leak => aba
2869 NOTICE: f_leak => ccc
2870 a | b
2871 ---+-----
2872 1 | aba
2873 3 | ccc
2874 (2 rows)
2876 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2877 QUERY PLAN
2878 -----------------------------------------
2879 Seq Scan on z1
2880 Filter: (((a % 2) = 1) AND f_leak(b))
2881 (2 rows)
2883 -- Policy requiring access to another table.
2884 SET SESSION AUTHORIZATION regress_rls_alice;
2885 CREATE POLICY p3 ON z1 AS RESTRICTIVE USING (a NOT IN (SELECT a FROM z1_blacklist));
2886 -- Query as role that is not owner of table but is owner of view without permissions.
2887 SET SESSION AUTHORIZATION regress_rls_bob;
2888 SELECT * FROM rls_view; --fail - permission denied.
2889 ERROR: permission denied for table z1_blacklist
2890 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
2891 ERROR: permission denied for table z1_blacklist
2892 -- Query as role that is not the owner of the table or view without permissions.
2893 SET SESSION AUTHORIZATION regress_rls_carol;
2894 SELECT * FROM rls_view; --fail - permission denied.
2895 ERROR: permission denied for table z1_blacklist
2896 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
2897 ERROR: permission denied for table z1_blacklist
2898 -- Query as role that is not owner of table but is owner of view with permissions.
2899 SET SESSION AUTHORIZATION regress_rls_alice;
2900 GRANT SELECT ON z1_blacklist TO regress_rls_bob;
2901 SET SESSION AUTHORIZATION regress_rls_bob;
2902 SELECT * FROM rls_view;
2903 NOTICE: f_leak => bbb
2904 a | b
2905 ---+-----
2906 2 | bbb
2907 (1 row)
2909 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2910 QUERY PLAN
2911 ---------------------------------------------------------------------------------------
2912 Seq Scan on z1
2913 Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b))
2914 SubPlan 1
2915 -> Seq Scan on z1_blacklist
2916 (4 rows)
2918 -- Query as role that is not the owner of the table or view without permissions.
2919 SET SESSION AUTHORIZATION regress_rls_carol;
2920 SELECT * FROM rls_view; --fail - permission denied.
2921 ERROR: permission denied for table z1_blacklist
2922 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
2923 ERROR: permission denied for table z1_blacklist
2924 -- Query as role that is not the owner of the table or view with permissions.
2925 SET SESSION AUTHORIZATION regress_rls_alice;
2926 GRANT SELECT ON z1_blacklist TO regress_rls_carol;
2927 SET SESSION AUTHORIZATION regress_rls_carol;
2928 SELECT * FROM rls_view;
2929 NOTICE: f_leak => aba
2930 a | b
2931 ---+-----
2932 1 | aba
2933 (1 row)
2935 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2936 QUERY PLAN
2937 ---------------------------------------------------------------------------------------
2938 Seq Scan on z1
2939 Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 1) AND f_leak(b))
2940 SubPlan 1
2941 -> Seq Scan on z1_blacklist
2942 (4 rows)
2944 SET SESSION AUTHORIZATION regress_rls_bob;
2945 DROP VIEW rls_view;
2946 --
2947 -- Command specific
2948 --
2949 SET SESSION AUTHORIZATION regress_rls_alice;
2950 CREATE TABLE x1 (a int, b text, c text);
2951 GRANT ALL ON x1 TO PUBLIC;
2952 INSERT INTO x1 VALUES
2953 (1, 'abc', 'regress_rls_bob'),
2954 (2, 'bcd', 'regress_rls_bob'),
2955 (3, 'cde', 'regress_rls_carol'),
2956 (4, 'def', 'regress_rls_carol'),
2957 (5, 'efg', 'regress_rls_bob'),
2958 (6, 'fgh', 'regress_rls_bob'),
2959 (7, 'fgh', 'regress_rls_carol'),
2960 (8, 'fgh', 'regress_rls_carol');
2961 CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
2962 CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
2963 CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1);
2964 CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0);
2965 CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
2966 ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
2967 SET SESSION AUTHORIZATION regress_rls_bob;
2968 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
2969 NOTICE: f_leak => abc
2970 NOTICE: f_leak => bcd
2971 NOTICE: f_leak => def
2972 NOTICE: f_leak => efg
2973 NOTICE: f_leak => fgh
2974 NOTICE: f_leak => fgh
2975 a | b | c
2976 ---+-----+-------------------
2977 1 | abc | regress_rls_bob
2978 2 | bcd | regress_rls_bob
2979 4 | def | regress_rls_carol
2980 5 | efg | regress_rls_bob
2981 6 | fgh | regress_rls_bob
2982 8 | fgh | regress_rls_carol
2983 (6 rows)
2985 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
2986 NOTICE: f_leak => abc
2987 NOTICE: f_leak => bcd
2988 NOTICE: f_leak => def
2989 NOTICE: f_leak => efg
2990 NOTICE: f_leak => fgh
2991 NOTICE: f_leak => fgh
2992 a | b | c
2993 ---+----------+-------------------
2994 1 | abc_updt | regress_rls_bob
2995 2 | bcd_updt | regress_rls_bob
2996 4 | def_updt | regress_rls_carol
2997 5 | efg_updt | regress_rls_bob
2998 6 | fgh_updt | regress_rls_bob
2999 8 | fgh_updt | regress_rls_carol
3000 (6 rows)
3002 SET SESSION AUTHORIZATION regress_rls_carol;
3003 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
3004 NOTICE: f_leak => cde
3005 NOTICE: f_leak => fgh
3006 NOTICE: f_leak => bcd_updt
3007 NOTICE: f_leak => def_updt
3008 NOTICE: f_leak => fgh_updt
3009 NOTICE: f_leak => fgh_updt
3010 a | b | c
3011 ---+----------+-------------------
3012 2 | bcd_updt | regress_rls_bob
3013 3 | cde | regress_rls_carol
3014 4 | def_updt | regress_rls_carol
3015 6 | fgh_updt | regress_rls_bob
3016 7 | fgh | regress_rls_carol
3017 8 | fgh_updt | regress_rls_carol
3018 (6 rows)
3020 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
3021 NOTICE: f_leak => cde
3022 NOTICE: f_leak => fgh
3023 NOTICE: f_leak => bcd_updt
3024 NOTICE: f_leak => def_updt
3025 NOTICE: f_leak => fgh_updt
3026 NOTICE: f_leak => fgh_updt
3027 a | b | c
3028 ---+---------------+-------------------
3029 3 | cde_updt | regress_rls_carol
3030 7 | fgh_updt | regress_rls_carol
3031 2 | bcd_updt_updt | regress_rls_bob
3032 4 | def_updt_updt | regress_rls_carol
3033 6 | fgh_updt_updt | regress_rls_bob
3034 8 | fgh_updt_updt | regress_rls_carol
3035 (6 rows)
3037 DELETE FROM x1 WHERE f_leak(b) RETURNING *;
3038 NOTICE: f_leak => cde_updt
3039 NOTICE: f_leak => fgh_updt
3040 NOTICE: f_leak => bcd_updt_updt
3041 NOTICE: f_leak => def_updt_updt
3042 NOTICE: f_leak => fgh_updt_updt
3043 NOTICE: f_leak => fgh_updt_updt
3044 a | b | c
3045 ---+---------------+-------------------
3046 3 | cde_updt | regress_rls_carol
3047 7 | fgh_updt | regress_rls_carol
3048 2 | bcd_updt_updt | regress_rls_bob
3049 4 | def_updt_updt | regress_rls_carol
3050 6 | fgh_updt_updt | regress_rls_bob
3051 8 | fgh_updt_updt | regress_rls_carol
3052 (6 rows)
3054 --
3055 -- Duplicate Policy Names
3056 --
3057 SET SESSION AUTHORIZATION regress_rls_alice;
3058 CREATE TABLE y1 (a int, b text);
3059 CREATE TABLE y2 (a int, b text);
3060 GRANT ALL ON y1, y2 TO regress_rls_bob;
3061 CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
3062 CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
3063 CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
3064 ERROR: policy "p1" for table "y1" already exists
3065 CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK
3066 ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;
3067 ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
3068 --
3069 -- Expression structure with SBV
3070 --
3071 -- Create view as table owner. RLS should NOT be applied.
3072 SET SESSION AUTHORIZATION regress_rls_alice;
3073 CREATE VIEW rls_sbv WITH (security_barrier) AS
3074 SELECT * FROM y1 WHERE f_leak(b);
3075 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
3076 QUERY PLAN
3077 -----------------------------------
3078 Seq Scan on y1
3079 Filter: (f_leak(b) AND (a = 1))
3080 (2 rows)
3082 DROP VIEW rls_sbv;
3083 -- Create view as role that does not own table. RLS should be applied.
3084 SET SESSION AUTHORIZATION regress_rls_bob;
3085 CREATE VIEW rls_sbv WITH (security_barrier) AS
3086 SELECT * FROM y1 WHERE f_leak(b);
3087 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
3088 QUERY PLAN
3089 ------------------------------------------------------------------
3090 Seq Scan on y1
3091 Filter: ((a = 1) AND ((a > 2) OR ((a % 2) = 0)) AND f_leak(b))
3092 (2 rows)
3094 DROP VIEW rls_sbv;
3095 --
3096 -- Expression structure
3097 --
3098 SET SESSION AUTHORIZATION regress_rls_alice;
3099 INSERT INTO y2 (SELECT x, public.fipshash(x::text) FROM generate_series(0,20) x);
3100 CREATE POLICY p2 ON y2 USING (a % 3 = 0);
3101 CREATE POLICY p3 ON y2 USING (a % 4 = 0);
3102 SET SESSION AUTHORIZATION regress_rls_bob;
3103 SELECT * FROM y2 WHERE f_leak(b);
3104 NOTICE: f_leak => 5feceb66ffc86f38d952786c6d696c79
3105 NOTICE: f_leak => d4735e3a265e16eee03f59718b9b5d03
3106 NOTICE: f_leak => 4e07408562bedb8b60ce05c1decfe3ad
3107 NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
3108 NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
3109 NOTICE: f_leak => 2c624232cdd221771294dfbb310aca00
3110 NOTICE: f_leak => 19581e27de7ced00ff1ce50b2047e7a5
3111 NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
3112 NOTICE: f_leak => 6b51d431df5d7f141cbececcf79edf3d
3113 NOTICE: f_leak => 8527a891e224136950ff32ca212b45bc
3114 NOTICE: f_leak => e629fa6598d732768f7c726b4b621285
3115 NOTICE: f_leak => b17ef6d19c7a5b1ee83b907c595526dc
3116 NOTICE: f_leak => 4ec9599fc203d176a301536c2e091a19
3117 NOTICE: f_leak => f5ca38f748a1d6eaf726b8a42fb575c3
3118 a | b
3119 ----+----------------------------------
3120 0 | 5feceb66ffc86f38d952786c6d696c79
3121 2 | d4735e3a265e16eee03f59718b9b5d03
3122 3 | 4e07408562bedb8b60ce05c1decfe3ad
3123 4 | 4b227777d4dd1fc61c6f884f48641d02
3124 6 | e7f6c011776e8db7cd330b54174fd76f
3125 8 | 2c624232cdd221771294dfbb310aca00
3126 9 | 19581e27de7ced00ff1ce50b2047e7a5
3127 10 | 4a44dc15364204a80fe80e9039455cc1
3128 12 | 6b51d431df5d7f141cbececcf79edf3d
3129 14 | 8527a891e224136950ff32ca212b45bc
3130 15 | e629fa6598d732768f7c726b4b621285
3131 16 | b17ef6d19c7a5b1ee83b907c595526dc
3132 18 | 4ec9599fc203d176a301536c2e091a19
3133 20 | f5ca38f748a1d6eaf726b8a42fb575c3
3134 (14 rows)
3136 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
3137 QUERY PLAN
3138 -----------------------------------------------------------------------------
3139 Seq Scan on y2
3140 Filter: ((((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)) AND f_leak(b))
3141 (2 rows)
3143 --
3144 -- Qual push-down of leaky functions, when not referring to table
3145 --
3146 SELECT * FROM y2 WHERE f_leak('abc');
3147 NOTICE: f_leak => abc
3148 NOTICE: f_leak => abc
3149 NOTICE: f_leak => abc
3150 NOTICE: f_leak => abc
3151 NOTICE: f_leak => abc
3152 NOTICE: f_leak => abc
3153 NOTICE: f_leak => abc
3154 NOTICE: f_leak => abc
3155 NOTICE: f_leak => abc
3156 NOTICE: f_leak => abc
3157 NOTICE: f_leak => abc
3158 NOTICE: f_leak => abc
3159 NOTICE: f_leak => abc
3160 NOTICE: f_leak => abc
3161 NOTICE: f_leak => abc
3162 NOTICE: f_leak => abc
3163 NOTICE: f_leak => abc
3164 NOTICE: f_leak => abc
3165 NOTICE: f_leak => abc
3166 NOTICE: f_leak => abc
3167 NOTICE: f_leak => abc
3168 a | b
3169 ----+----------------------------------
3170 0 | 5feceb66ffc86f38d952786c6d696c79
3171 2 | d4735e3a265e16eee03f59718b9b5d03
3172 3 | 4e07408562bedb8b60ce05c1decfe3ad
3173 4 | 4b227777d4dd1fc61c6f884f48641d02
3174 6 | e7f6c011776e8db7cd330b54174fd76f
3175 8 | 2c624232cdd221771294dfbb310aca00
3176 9 | 19581e27de7ced00ff1ce50b2047e7a5
3177 10 | 4a44dc15364204a80fe80e9039455cc1
3178 12 | 6b51d431df5d7f141cbececcf79edf3d
3179 14 | 8527a891e224136950ff32ca212b45bc
3180 15 | e629fa6598d732768f7c726b4b621285
3181 16 | b17ef6d19c7a5b1ee83b907c595526dc
3182 18 | 4ec9599fc203d176a301536c2e091a19
3183 20 | f5ca38f748a1d6eaf726b8a42fb575c3
3184 (14 rows)
3186 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc');
3187 QUERY PLAN
3188 ---------------------------------------------------------------------------------------
3189 Seq Scan on y2
3190 Filter: (f_leak('abc'::text) AND (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)))
3191 (2 rows)
3193 CREATE TABLE test_qual_pushdown (
3194 abc text
3195 );
3196 INSERT INTO test_qual_pushdown VALUES ('abc'),('def');
3197 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
3198 NOTICE: f_leak => abc
3199 NOTICE: f_leak => def
3200 a | b | abc
3201 ---+---+-----
3202 (0 rows)
3204 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
3205 QUERY PLAN
3206 -------------------------------------------------------------------------
3207 Hash Join
3208 Hash Cond: (test_qual_pushdown.abc = y2.b)
3209 -> Seq Scan on test_qual_pushdown
3210 Filter: f_leak(abc)
3211 -> Hash
3212 -> Seq Scan on y2
3213 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
3214 (7 rows)
3216 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
3217 NOTICE: f_leak => 5feceb66ffc86f38d952786c6d696c79
3218 NOTICE: f_leak => d4735e3a265e16eee03f59718b9b5d03
3219 NOTICE: f_leak => 4e07408562bedb8b60ce05c1decfe3ad
3220 NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
3221 NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
3222 NOTICE: f_leak => 2c624232cdd221771294dfbb310aca00
3223 NOTICE: f_leak => 19581e27de7ced00ff1ce50b2047e7a5
3224 NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
3225 NOTICE: f_leak => 6b51d431df5d7f141cbececcf79edf3d
3226 NOTICE: f_leak => 8527a891e224136950ff32ca212b45bc
3227 NOTICE: f_leak => e629fa6598d732768f7c726b4b621285
3228 NOTICE: f_leak => b17ef6d19c7a5b1ee83b907c595526dc
3229 NOTICE: f_leak => 4ec9599fc203d176a301536c2e091a19
3230 NOTICE: f_leak => f5ca38f748a1d6eaf726b8a42fb575c3
3231 a | b | abc
3232 ---+---+-----
3233 (0 rows)
3235 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
3236 QUERY PLAN
3237 -----------------------------------------------------------------------------------------
3238 Hash Join
3239 Hash Cond: (test_qual_pushdown.abc = y2.b)
3240 -> Seq Scan on test_qual_pushdown
3241 -> Hash
3242 -> Seq Scan on y2
3243 Filter: ((((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)) AND f_leak(b))
3244 (6 rows)
3246 DROP TABLE test_qual_pushdown;
3247 --
3248 -- Plancache invalidate on user change.
3249 --
3250 RESET SESSION AUTHORIZATION;
3251 DROP TABLE t1 CASCADE;
3252 NOTICE: drop cascades to 2 other objects
3253 DETAIL: drop cascades to table t2
3254 drop cascades to table t3
3255 CREATE TABLE t1 (a integer);
3256 GRANT SELECT ON t1 TO regress_rls_bob, regress_rls_carol;
3257 CREATE POLICY p1 ON t1 TO regress_rls_bob USING ((a % 2) = 0);
3258 CREATE POLICY p2 ON t1 TO regress_rls_carol USING ((a % 4) = 0);
3259 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
3260 -- Prepare as regress_rls_bob
3261 SET ROLE regress_rls_bob;
3262 PREPARE role_inval AS SELECT * FROM t1;
3263 -- Check plan
3264 EXPLAIN (COSTS OFF) EXECUTE role_inval;
3265 QUERY PLAN
3266 -------------------------
3267 Seq Scan on t1
3268 Filter: ((a % 2) = 0)
3269 (2 rows)
3271 -- Change to regress_rls_carol
3272 SET ROLE regress_rls_carol;
3273 -- Check plan- should be different
3274 EXPLAIN (COSTS OFF) EXECUTE role_inval;
3275 QUERY PLAN
3276 -------------------------
3277 Seq Scan on t1
3278 Filter: ((a % 4) = 0)
3279 (2 rows)
3281 -- Change back to regress_rls_bob
3282 SET ROLE regress_rls_bob;
3283 -- Check plan- should be back to original
3284 EXPLAIN (COSTS OFF) EXECUTE role_inval;
3285 QUERY PLAN
3286 -------------------------
3287 Seq Scan on t1
3288 Filter: ((a % 2) = 0)
3289 (2 rows)
3291 --
3292 -- CTE and RLS
3293 --
3294 RESET SESSION AUTHORIZATION;
3295 DROP TABLE t1 CASCADE;
3296 CREATE TABLE t1 (a integer, b text);
3297 CREATE POLICY p1 ON t1 USING (a % 2 = 0);
3298 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
3299 GRANT ALL ON t1 TO regress_rls_bob;
3300 INSERT INTO t1 (SELECT x, public.fipshash(x::text) FROM generate_series(0,20) x);
3301 SET SESSION AUTHORIZATION regress_rls_bob;
3302 WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
3303 NOTICE: f_leak => 5feceb66ffc86f38d952786c6d696c79
3304 NOTICE: f_leak => d4735e3a265e16eee03f59718b9b5d03
3305 NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
3306 NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
3307 NOTICE: f_leak => 2c624232cdd221771294dfbb310aca00
3308 NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
3309 NOTICE: f_leak => 6b51d431df5d7f141cbececcf79edf3d
3310 NOTICE: f_leak => 8527a891e224136950ff32ca212b45bc
3311 NOTICE: f_leak => b17ef6d19c7a5b1ee83b907c595526dc
3312 NOTICE: f_leak => 4ec9599fc203d176a301536c2e091a19
3313 NOTICE: f_leak => f5ca38f748a1d6eaf726b8a42fb575c3
3314 a | b
3315 ----+----------------------------------
3316 0 | 5feceb66ffc86f38d952786c6d696c79
3317 2 | d4735e3a265e16eee03f59718b9b5d03
3318 4 | 4b227777d4dd1fc61c6f884f48641d02
3319 6 | e7f6c011776e8db7cd330b54174fd76f
3320 8 | 2c624232cdd221771294dfbb310aca00
3321 10 | 4a44dc15364204a80fe80e9039455cc1
3322 12 | 6b51d431df5d7f141cbececcf79edf3d
3323 14 | 8527a891e224136950ff32ca212b45bc
3324 16 | b17ef6d19c7a5b1ee83b907c595526dc
3325 18 | 4ec9599fc203d176a301536c2e091a19
3326 20 | f5ca38f748a1d6eaf726b8a42fb575c3
3327 (11 rows)
3329 EXPLAIN (COSTS OFF)
3330 WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
3331 QUERY PLAN
3332 -------------------------------------------------
3333 CTE Scan on cte1
3334 CTE cte1
3335 -> Seq Scan on t1
3336 Filter: (((a % 2) = 0) AND f_leak(b))
3337 (4 rows)
3339 WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
3340 ERROR: new row violates row-level security policy for table "t1"
3341 WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
3342 a | b
3343 ----+----------------------------------
3344 0 | 5feceb66ffc86f38d952786c6d696c79
3345 2 | d4735e3a265e16eee03f59718b9b5d03
3346 4 | 4b227777d4dd1fc61c6f884f48641d02
3347 6 | e7f6c011776e8db7cd330b54174fd76f
3348 8 | 2c624232cdd221771294dfbb310aca00
3349 10 | 4a44dc15364204a80fe80e9039455cc1
3350 12 | 6b51d431df5d7f141cbececcf79edf3d
3351 14 | 8527a891e224136950ff32ca212b45bc
3352 16 | b17ef6d19c7a5b1ee83b907c595526dc
3353 18 | 4ec9599fc203d176a301536c2e091a19
3354 20 | f5ca38f748a1d6eaf726b8a42fb575c3
3355 (11 rows)
3357 WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail
3358 ERROR: new row violates row-level security policy for table "t1"
3359 WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok
3360 a | b
3361 ----+---------
3362 20 | Success
3363 (1 row)
3365 --
3366 -- Rename Policy
3367 --
3368 RESET SESSION AUTHORIZATION;
3369 ALTER POLICY p1 ON t1 RENAME TO p1; --fail
3370 ERROR: policy "p1" for table "t1" already exists
3371 SELECT polname, relname
3372 FROM pg_policy pol
3373 JOIN pg_class pc ON (pc.oid = pol.polrelid)
3374 WHERE relname = 't1';
3375 polname | relname
3376 ---------+---------
3377 p1 | t1
3378 (1 row)
3380 ALTER POLICY p1 ON t1 RENAME TO p2; --ok
3381 SELECT polname, relname
3382 FROM pg_policy pol
3383 JOIN pg_class pc ON (pc.oid = pol.polrelid)
3384 WHERE relname = 't1';
3385 polname | relname
3386 ---------+---------
3387 p2 | t1
3388 (1 row)
3390 --
3391 -- Check INSERT SELECT
3392 --
3393 SET SESSION AUTHORIZATION regress_rls_bob;
3394 CREATE TABLE t2 (a integer, b text);
3395 INSERT INTO t2 (SELECT * FROM t1);
3396 EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
3397 QUERY PLAN
3398 -------------------------------
3399 Insert on t2
3400 -> Seq Scan on t1
3401 Filter: ((a % 2) = 0)
3402 (3 rows)
3404 SELECT * FROM t2;
3405 a | b
3406 ----+----------------------------------
3407 0 | 5feceb66ffc86f38d952786c6d696c79
3408 2 | d4735e3a265e16eee03f59718b9b5d03
3409 4 | 4b227777d4dd1fc61c6f884f48641d02
3410 6 | e7f6c011776e8db7cd330b54174fd76f
3411 8 | 2c624232cdd221771294dfbb310aca00
3412 10 | 4a44dc15364204a80fe80e9039455cc1
3413 12 | 6b51d431df5d7f141cbececcf79edf3d
3414 14 | 8527a891e224136950ff32ca212b45bc
3415 16 | b17ef6d19c7a5b1ee83b907c595526dc
3416 18 | 4ec9599fc203d176a301536c2e091a19
3417 20 | f5ca38f748a1d6eaf726b8a42fb575c3
3418 20 | Success
3419 (12 rows)
3421 EXPLAIN (COSTS OFF) SELECT * FROM t2;
3422 QUERY PLAN
3423 ----------------
3424 Seq Scan on t2
3425 (1 row)
3427 CREATE TABLE t3 AS SELECT * FROM t1;
3428 SELECT * FROM t3;
3429 a | b
3430 ----+----------------------------------
3431 0 | 5feceb66ffc86f38d952786c6d696c79
3432 2 | d4735e3a265e16eee03f59718b9b5d03
3433 4 | 4b227777d4dd1fc61c6f884f48641d02
3434 6 | e7f6c011776e8db7cd330b54174fd76f
3435 8 | 2c624232cdd221771294dfbb310aca00
3436 10 | 4a44dc15364204a80fe80e9039455cc1
3437 12 | 6b51d431df5d7f141cbececcf79edf3d
3438 14 | 8527a891e224136950ff32ca212b45bc
3439 16 | b17ef6d19c7a5b1ee83b907c595526dc
3440 18 | 4ec9599fc203d176a301536c2e091a19
3441 20 | f5ca38f748a1d6eaf726b8a42fb575c3
3442 20 | Success
3443 (12 rows)
3445 SELECT * INTO t4 FROM t1;
3446 SELECT * FROM t4;
3447 a | b
3448 ----+----------------------------------
3449 0 | 5feceb66ffc86f38d952786c6d696c79
3450 2 | d4735e3a265e16eee03f59718b9b5d03
3451 4 | 4b227777d4dd1fc61c6f884f48641d02
3452 6 | e7f6c011776e8db7cd330b54174fd76f
3453 8 | 2c624232cdd221771294dfbb310aca00
3454 10 | 4a44dc15364204a80fe80e9039455cc1
3455 12 | 6b51d431df5d7f141cbececcf79edf3d
3456 14 | 8527a891e224136950ff32ca212b45bc
3457 16 | b17ef6d19c7a5b1ee83b907c595526dc
3458 18 | 4ec9599fc203d176a301536c2e091a19
3459 20 | f5ca38f748a1d6eaf726b8a42fb575c3
3460 20 | Success
3461 (12 rows)
3463 --
3464 -- RLS with JOIN
3465 --
3466 SET SESSION AUTHORIZATION regress_rls_alice;
3467 CREATE TABLE blog (id integer, author text, post text);
3468 CREATE TABLE comment (blog_id integer, message text);
3469 GRANT ALL ON blog, comment TO regress_rls_bob;
3470 CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
3471 ALTER TABLE blog ENABLE ROW LEVEL SECURITY;
3472 INSERT INTO blog VALUES
3473 (1, 'alice', 'blog #1'),
3474 (2, 'bob', 'blog #1'),
3475 (3, 'alice', 'blog #2'),
3476 (4, 'alice', 'blog #3'),
3477 (5, 'john', 'blog #1');
3478 INSERT INTO comment VALUES
3479 (1, 'cool blog'),
3480 (1, 'fun blog'),
3481 (3, 'crazy blog'),
3482 (5, 'what?'),
3483 (4, 'insane!'),
3484 (2, 'who did it?');
3485 SET SESSION AUTHORIZATION regress_rls_bob;
3486 -- Check RLS JOIN with Non-RLS.
3487 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
3488 id | author | message
3489 ----+--------+-------------
3490 4 | alice | insane!
3491 2 | bob | who did it?
3492 (2 rows)
3494 -- Check Non-RLS JOIN with RLS.
3495 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
3496 id | author | message
3497 ----+--------+-------------
3498 4 | alice | insane!
3499 2 | bob | who did it?
3500 (2 rows)
3502 SET SESSION AUTHORIZATION regress_rls_alice;
3503 CREATE POLICY comment_1 ON comment USING (blog_id < 4);
3504 ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
3505 SET SESSION AUTHORIZATION regress_rls_bob;
3506 -- Check RLS JOIN RLS
3507 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
3508 id | author | message
3509 ----+--------+-------------
3510 2 | bob | who did it?
3511 (1 row)
3513 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
3514 id | author | message
3515 ----+--------+-------------
3516 2 | bob | who did it?
3517 (1 row)
3519 SET SESSION AUTHORIZATION regress_rls_alice;
3520 DROP TABLE blog, comment;
3521 --
3522 -- Default Deny Policy
3523 --
3524 RESET SESSION AUTHORIZATION;
3525 DROP POLICY p2 ON t1;
3526 ALTER TABLE t1 OWNER TO regress_rls_alice;
3527 -- Check that default deny does not apply to superuser.
3528 RESET SESSION AUTHORIZATION;
3529 SELECT * FROM t1;
3530 a | b
3531 ----+----------------------------------
3532 1 | 6b86b273ff34fce19d6b804eff5a3f57
3533 3 | 4e07408562bedb8b60ce05c1decfe3ad
3534 5 | ef2d127de37b942baad06145e54b0c61
3535 7 | 7902699be42c8a8e46fbbb4501726517
3536 9 | 19581e27de7ced00ff1ce50b2047e7a5
3537 11 | 4fc82b26aecb47d2868c4efbe3581732
3538 13 | 3fdba35f04dc8c462986c992bcf87554
3539 15 | e629fa6598d732768f7c726b4b621285
3540 17 | 4523540f1504cd17100c4835e85b7eef
3541 19 | 9400f1b21cb527d7fa3d3eabba93557a
3542 0 | 5feceb66ffc86f38d952786c6d696c79
3543 2 | d4735e3a265e16eee03f59718b9b5d03
3544 4 | 4b227777d4dd1fc61c6f884f48641d02
3545 6 | e7f6c011776e8db7cd330b54174fd76f
3546 8 | 2c624232cdd221771294dfbb310aca00
3547 10 | 4a44dc15364204a80fe80e9039455cc1
3548 12 | 6b51d431df5d7f141cbececcf79edf3d
3549 14 | 8527a891e224136950ff32ca212b45bc
3550 16 | b17ef6d19c7a5b1ee83b907c595526dc
3551 18 | 4ec9599fc203d176a301536c2e091a19
3552 20 | f5ca38f748a1d6eaf726b8a42fb575c3
3553 20 | Success
3554 (22 rows)
3556 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3557 QUERY PLAN
3558 ----------------
3559 Seq Scan on t1
3560 (1 row)
3562 -- Check that default deny does not apply to table owner.
3563 SET SESSION AUTHORIZATION regress_rls_alice;
3564 SELECT * FROM t1;
3565 a | b
3566 ----+----------------------------------
3567 1 | 6b86b273ff34fce19d6b804eff5a3f57
3568 3 | 4e07408562bedb8b60ce05c1decfe3ad
3569 5 | ef2d127de37b942baad06145e54b0c61
3570 7 | 7902699be42c8a8e46fbbb4501726517
3571 9 | 19581e27de7ced00ff1ce50b2047e7a5
3572 11 | 4fc82b26aecb47d2868c4efbe3581732
3573 13 | 3fdba35f04dc8c462986c992bcf87554
3574 15 | e629fa6598d732768f7c726b4b621285
3575 17 | 4523540f1504cd17100c4835e85b7eef
3576 19 | 9400f1b21cb527d7fa3d3eabba93557a
3577 0 | 5feceb66ffc86f38d952786c6d696c79
3578 2 | d4735e3a265e16eee03f59718b9b5d03
3579 4 | 4b227777d4dd1fc61c6f884f48641d02
3580 6 | e7f6c011776e8db7cd330b54174fd76f
3581 8 | 2c624232cdd221771294dfbb310aca00
3582 10 | 4a44dc15364204a80fe80e9039455cc1
3583 12 | 6b51d431df5d7f141cbececcf79edf3d
3584 14 | 8527a891e224136950ff32ca212b45bc
3585 16 | b17ef6d19c7a5b1ee83b907c595526dc
3586 18 | 4ec9599fc203d176a301536c2e091a19
3587 20 | f5ca38f748a1d6eaf726b8a42fb575c3
3588 20 | Success
3589 (22 rows)
3591 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3592 QUERY PLAN
3593 ----------------
3594 Seq Scan on t1
3595 (1 row)
3597 -- Check that default deny applies to non-owner/non-superuser when RLS on.
3598 SET SESSION AUTHORIZATION regress_rls_bob;
3599 SET row_security TO ON;
3600 SELECT * FROM t1;
3601 a | b
3602 ---+---
3603 (0 rows)
3605 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3606 QUERY PLAN
3607 --------------------------
3608 Result
3609 One-Time Filter: false
3610 (2 rows)
3612 SET SESSION AUTHORIZATION regress_rls_bob;
3613 SELECT * FROM t1;
3614 a | b
3615 ---+---
3616 (0 rows)
3618 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3619 QUERY PLAN
3620 --------------------------
3621 Result
3622 One-Time Filter: false
3623 (2 rows)
3625 --
3626 -- COPY TO/FROM
3627 --
3628 RESET SESSION AUTHORIZATION;
3629 DROP TABLE copy_t CASCADE;
3630 ERROR: table "copy_t" does not exist
3631 CREATE TABLE copy_t (a integer, b text);
3632 CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
3633 ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
3634 GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user;
3635 INSERT INTO copy_t (SELECT x, public.fipshash(x::text) FROM generate_series(0,10) x);
3636 -- Check COPY TO as Superuser/owner.
3637 RESET SESSION AUTHORIZATION;
3638 SET row_security TO OFF;
3639 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
3640 0,5feceb66ffc86f38d952786c6d696c79
3641 1,6b86b273ff34fce19d6b804eff5a3f57
3642 2,d4735e3a265e16eee03f59718b9b5d03
3643 3,4e07408562bedb8b60ce05c1decfe3ad
3644 4,4b227777d4dd1fc61c6f884f48641d02
3645 5,ef2d127de37b942baad06145e54b0c61
3646 6,e7f6c011776e8db7cd330b54174fd76f
3647 7,7902699be42c8a8e46fbbb4501726517
3648 8,2c624232cdd221771294dfbb310aca00
3649 9,19581e27de7ced00ff1ce50b2047e7a5
3650 10,4a44dc15364204a80fe80e9039455cc1
3651 SET row_security TO ON;
3652 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
3653 0,5feceb66ffc86f38d952786c6d696c79
3654 1,6b86b273ff34fce19d6b804eff5a3f57
3655 2,d4735e3a265e16eee03f59718b9b5d03
3656 3,4e07408562bedb8b60ce05c1decfe3ad
3657 4,4b227777d4dd1fc61c6f884f48641d02
3658 5,ef2d127de37b942baad06145e54b0c61
3659 6,e7f6c011776e8db7cd330b54174fd76f
3660 7,7902699be42c8a8e46fbbb4501726517
3661 8,2c624232cdd221771294dfbb310aca00
3662 9,19581e27de7ced00ff1ce50b2047e7a5
3663 10,4a44dc15364204a80fe80e9039455cc1
3664 -- Check COPY TO as user with permissions.
3665 SET SESSION AUTHORIZATION regress_rls_bob;
3666 SET row_security TO OFF;
3667 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3668 ERROR: query would be affected by row-level security policy for table "copy_t"
3669 SET row_security TO ON;
3670 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
3671 0,5feceb66ffc86f38d952786c6d696c79
3672 2,d4735e3a265e16eee03f59718b9b5d03
3673 4,4b227777d4dd1fc61c6f884f48641d02
3674 6,e7f6c011776e8db7cd330b54174fd76f
3675 8,2c624232cdd221771294dfbb310aca00
3676 10,4a44dc15364204a80fe80e9039455cc1
3677 -- Check COPY TO as user with permissions and BYPASSRLS
3678 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3679 SET row_security TO OFF;
3680 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
3681 0,5feceb66ffc86f38d952786c6d696c79
3682 1,6b86b273ff34fce19d6b804eff5a3f57
3683 2,d4735e3a265e16eee03f59718b9b5d03
3684 3,4e07408562bedb8b60ce05c1decfe3ad
3685 4,4b227777d4dd1fc61c6f884f48641d02
3686 5,ef2d127de37b942baad06145e54b0c61
3687 6,e7f6c011776e8db7cd330b54174fd76f
3688 7,7902699be42c8a8e46fbbb4501726517
3689 8,2c624232cdd221771294dfbb310aca00
3690 9,19581e27de7ced00ff1ce50b2047e7a5
3691 10,4a44dc15364204a80fe80e9039455cc1
3692 SET row_security TO ON;
3693 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
3694 0,5feceb66ffc86f38d952786c6d696c79
3695 1,6b86b273ff34fce19d6b804eff5a3f57
3696 2,d4735e3a265e16eee03f59718b9b5d03
3697 3,4e07408562bedb8b60ce05c1decfe3ad
3698 4,4b227777d4dd1fc61c6f884f48641d02
3699 5,ef2d127de37b942baad06145e54b0c61
3700 6,e7f6c011776e8db7cd330b54174fd76f
3701 7,7902699be42c8a8e46fbbb4501726517
3702 8,2c624232cdd221771294dfbb310aca00
3703 9,19581e27de7ced00ff1ce50b2047e7a5
3704 10,4a44dc15364204a80fe80e9039455cc1
3705 -- Check COPY TO as user without permissions. SET row_security TO OFF;
3706 SET SESSION AUTHORIZATION regress_rls_carol;
3707 SET row_security TO OFF;
3708 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3709 ERROR: query would be affected by row-level security policy for table "copy_t"
3710 SET row_security TO ON;
3711 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
3712 ERROR: permission denied for table copy_t
3713 -- Check COPY relation TO; keep it just one row to avoid reordering issues
3714 RESET SESSION AUTHORIZATION;
3715 SET row_security TO ON;
3716 CREATE TABLE copy_rel_to (a integer, b text);
3717 CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0);
3718 ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
3719 GRANT ALL ON copy_rel_to TO regress_rls_bob, regress_rls_exempt_user;
3720 INSERT INTO copy_rel_to VALUES (1, public.fipshash('1'));
3721 -- Check COPY TO as Superuser/owner.
3722 RESET SESSION AUTHORIZATION;
3723 SET row_security TO OFF;
3724 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3725 1,6b86b273ff34fce19d6b804eff5a3f57
3726 SET row_security TO ON;
3727 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3728 1,6b86b273ff34fce19d6b804eff5a3f57
3729 -- Check COPY TO as user with permissions.
3730 SET SESSION AUTHORIZATION regress_rls_bob;
3731 SET row_security TO OFF;
3732 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3733 ERROR: query would be affected by row-level security policy for table "copy_rel_to"
3734 SET row_security TO ON;
3735 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3736 -- Check COPY TO as user with permissions and BYPASSRLS
3737 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3738 SET row_security TO OFF;
3739 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3740 1,6b86b273ff34fce19d6b804eff5a3f57
3741 SET row_security TO ON;
3742 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3743 1,6b86b273ff34fce19d6b804eff5a3f57
3744 -- Check COPY TO as user without permissions. SET row_security TO OFF;
3745 SET SESSION AUTHORIZATION regress_rls_carol;
3746 SET row_security TO OFF;
3747 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3748 ERROR: permission denied for table copy_rel_to
3749 SET row_security TO ON;
3750 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3751 ERROR: permission denied for table copy_rel_to
3752 -- Check behavior with a child table.
3753 RESET SESSION AUTHORIZATION;
3754 SET row_security TO ON;
3755 CREATE TABLE copy_rel_to_child () INHERITS (copy_rel_to);
3756 INSERT INTO copy_rel_to_child VALUES (1, 'one'), (2, 'two');
3757 -- Check COPY TO as Superuser/owner.
3758 RESET SESSION AUTHORIZATION;
3759 SET row_security TO OFF;
3760 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3761 1,6b86b273ff34fce19d6b804eff5a3f57
3762 SET row_security TO ON;
3763 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3764 1,6b86b273ff34fce19d6b804eff5a3f57
3765 -- Check COPY TO as user with permissions.
3766 SET SESSION AUTHORIZATION regress_rls_bob;
3767 SET row_security TO OFF;
3768 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3769 ERROR: query would be affected by row-level security policy for table "copy_rel_to"
3770 SET row_security TO ON;
3771 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3772 -- Check COPY TO as user with permissions and BYPASSRLS
3773 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3774 SET row_security TO OFF;
3775 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3776 1,6b86b273ff34fce19d6b804eff5a3f57
3777 SET row_security TO ON;
3778 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3779 1,6b86b273ff34fce19d6b804eff5a3f57
3780 -- Check COPY TO as user without permissions. SET row_security TO OFF;
3781 SET SESSION AUTHORIZATION regress_rls_carol;
3782 SET row_security TO OFF;
3783 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3784 ERROR: permission denied for table copy_rel_to
3785 SET row_security TO ON;
3786 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3787 ERROR: permission denied for table copy_rel_to
3788 -- Check COPY FROM as Superuser/owner.
3789 RESET SESSION AUTHORIZATION;
3790 SET row_security TO OFF;
3791 COPY copy_t FROM STDIN; --ok
3792 SET row_security TO ON;
3793 COPY copy_t FROM STDIN; --ok
3794 -- Check COPY FROM as user with permissions.
3795 SET SESSION AUTHORIZATION regress_rls_bob;
3796 SET row_security TO OFF;
3797 COPY copy_t FROM STDIN; --fail - would be affected by RLS.
3798 ERROR: query would be affected by row-level security policy for table "copy_t"
3799 SET row_security TO ON;
3800 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
3801 ERROR: COPY FROM not supported with row-level security
3802 HINT: Use INSERT statements instead.
3803 -- Check COPY FROM as user with permissions and BYPASSRLS
3804 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3805 SET row_security TO ON;
3806 COPY copy_t FROM STDIN; --ok
3807 -- Check COPY FROM as user without permissions.
3808 SET SESSION AUTHORIZATION regress_rls_carol;
3809 SET row_security TO OFF;
3810 COPY copy_t FROM STDIN; --fail - permission denied.
3811 ERROR: permission denied for table copy_t
3812 SET row_security TO ON;
3813 COPY copy_t FROM STDIN; --fail - permission denied.
3814 ERROR: permission denied for table copy_t
3815 RESET SESSION AUTHORIZATION;
3816 DROP TABLE copy_t;
3817 DROP TABLE copy_rel_to CASCADE;
3818 NOTICE: drop cascades to table copy_rel_to_child
3819 -- Check WHERE CURRENT OF
3820 SET SESSION AUTHORIZATION regress_rls_alice;
3821 CREATE TABLE current_check (currentid int, payload text, rlsuser text);
3822 GRANT ALL ON current_check TO PUBLIC;
3823 INSERT INTO current_check VALUES
3824 (1, 'abc', 'regress_rls_bob'),
3825 (2, 'bcd', 'regress_rls_bob'),
3826 (3, 'cde', 'regress_rls_bob'),
3827 (4, 'def', 'regress_rls_bob');
3828 CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
3829 CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
3830 CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user);
3831 ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
3832 SET SESSION AUTHORIZATION regress_rls_bob;
3833 -- Can SELECT even rows
3834 SELECT * FROM current_check;
3835 currentid | payload | rlsuser
3836 -----------+---------+-----------------
3837 2 | bcd | regress_rls_bob
3838 4 | def | regress_rls_bob
3839 (2 rows)
3841 -- Cannot UPDATE row 2
3842 UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *;
3843 currentid | payload | rlsuser
3844 -----------+---------+---------
3845 (0 rows)
3847 BEGIN;
3848 DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check;
3849 -- Returns rows that can be seen according to SELECT policy, like plain SELECT
3850 -- above (even rows)
3851 FETCH ABSOLUTE 1 FROM current_check_cursor;
3852 currentid | payload | rlsuser
3853 -----------+---------+-----------------
3854 2 | bcd | regress_rls_bob
3855 (1 row)
3857 -- Still cannot UPDATE row 2 through cursor
3858 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
3859 currentid | payload | rlsuser
3860 -----------+---------+---------
3861 (0 rows)
3863 -- Can update row 4 through cursor, which is the next visible row
3864 FETCH RELATIVE 1 FROM current_check_cursor;
3865 currentid | payload | rlsuser
3866 -----------+---------+-----------------
3867 4 | def | regress_rls_bob
3868 (1 row)
3870 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
3871 currentid | payload | rlsuser
3872 -----------+---------+-----------------
3873 4 | def_new | regress_rls_bob
3874 (1 row)
3876 SELECT * FROM current_check;
3877 currentid | payload | rlsuser
3878 -----------+---------+-----------------
3879 2 | bcd | regress_rls_bob
3880 4 | def_new | regress_rls_bob
3881 (2 rows)
3883 -- Plan should be a subquery TID scan
3884 EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor;
3885 QUERY PLAN
3886 -------------------------------------------------------------
3887 Update on current_check
3888 -> Tid Scan on current_check
3889 TID Cond: CURRENT OF current_check_cursor
3890 Filter: ((currentid = 4) AND ((currentid % 2) = 0))
3891 (4 rows)
3893 -- Similarly can only delete row 4
3894 FETCH ABSOLUTE 1 FROM current_check_cursor;
3895 currentid | payload | rlsuser
3896 -----------+---------+-----------------
3897 2 | bcd | regress_rls_bob
3898 (1 row)
3900 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
3901 currentid | payload | rlsuser
3902 -----------+---------+---------
3903 (0 rows)
3905 FETCH RELATIVE 1 FROM current_check_cursor;
3906 currentid | payload | rlsuser
3907 -----------+---------+-----------------
3908 4 | def | regress_rls_bob
3909 (1 row)
3911 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
3912 currentid | payload | rlsuser
3913 -----------+---------+-----------------
3914 4 | def_new | regress_rls_bob
3915 (1 row)
3917 SELECT * FROM current_check;
3918 currentid | payload | rlsuser
3919 -----------+---------+-----------------
3920 2 | bcd | regress_rls_bob
3921 (1 row)
3923 COMMIT;
3924 -- Check that RLS filters that are tidquals don't override WHERE CURRENT OF
3925 BEGIN;
3926 CREATE TABLE current_check_2 (a int, b text);
3927 INSERT INTO current_check_2 VALUES (1, 'Apple');
3928 ALTER TABLE current_check_2 ENABLE ROW LEVEL SECURITY;
3929 ALTER TABLE current_check_2 FORCE ROW LEVEL SECURITY;
3930 -- policy must accept ctid = (InvalidBlockNumber,0) since updates check it
3931 -- before assigning a ctid to the new row
3932 CREATE POLICY p1 ON current_check_2 AS PERMISSIVE
3933 USING (ctid IN ('(0,1)', '(0,2)', '(4294967295,0)'));
3934 SELECT ctid, * FROM current_check_2;
3935 ctid | a | b
3936 -------+---+-------
3937 (0,1) | 1 | Apple
3938 (1 row)
3940 DECLARE current_check_cursor CURSOR FOR SELECT * FROM current_check_2;
3941 FETCH FROM current_check_cursor;
3942 a | b
3943 ---+-------
3944 1 | Apple
3945 (1 row)
3947 EXPLAIN (COSTS OFF)
3948 UPDATE current_check_2 SET b = 'Manzana' WHERE CURRENT OF current_check_cursor;
3949 QUERY PLAN
3950 ----------------------------------------------------------------------------
3951 Update on current_check_2
3952 -> Tid Scan on current_check_2
3953 TID Cond: CURRENT OF current_check_cursor
3954 Filter: (ctid = ANY ('{"(0,1)","(0,2)","(4294967295,0)"}'::tid[]))
3955 (4 rows)
3957 UPDATE current_check_2 SET b = 'Manzana' WHERE CURRENT OF current_check_cursor;
3958 SELECT ctid, * FROM current_check_2;
3959 ctid | a | b
3960 -------+---+---------
3961 (0,2) | 1 | Manzana
3962 (1 row)
3964 ROLLBACK;
3965 --
3966 -- check pg_stats view filtering
3967 --
3968 SET row_security TO ON;
3969 SET SESSION AUTHORIZATION regress_rls_alice;
3970 ANALYZE current_check;
3971 -- Stats visible
3972 SELECT row_security_active('current_check');
3973 row_security_active
3974 ---------------------
3975 f
3976 (1 row)
3978 SELECT attname, most_common_vals FROM pg_stats
3979 WHERE tablename = 'current_check'
3980 ORDER BY 1;
3981 attname | most_common_vals
3982 -----------+-------------------
3983 currentid |
3984 payload |
3985 rlsuser | {regress_rls_bob}
3986 (3 rows)
3988 SET SESSION AUTHORIZATION regress_rls_bob;
3989 -- Stats not visible
3990 SELECT row_security_active('current_check');
3991 row_security_active
3992 ---------------------
3993 t
3994 (1 row)
3996 SELECT attname, most_common_vals FROM pg_stats
3997 WHERE tablename = 'current_check'
3998 ORDER BY 1;
3999 attname | most_common_vals
4000 ---------+------------------
4001 (0 rows)
4003 --
4004 -- Collation support
4005 --
4006 BEGIN;
4007 CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
4008 CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
4009 ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
4010 GRANT SELECT ON coll_t TO regress_rls_alice;
4011 SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
4012 inputcollid
4013 ------------------
4014 inputcollid 950
4015 (1 row)
4017 SET SESSION AUTHORIZATION regress_rls_alice;
4018 SELECT * FROM coll_t;
4019 c
4020 -----
4021 bar
4022 (1 row)
4024 ROLLBACK;
4025 --
4026 -- Shared Object Dependencies
4027 --
4028 RESET SESSION AUTHORIZATION;
4029 BEGIN;
4030 CREATE ROLE regress_rls_eve;
4031 CREATE ROLE regress_rls_frank;
4032 CREATE TABLE tbl1 (c) AS VALUES ('bar'::text);
4033 GRANT SELECT ON TABLE tbl1 TO regress_rls_eve;
4034 CREATE POLICY P ON tbl1 TO regress_rls_eve, regress_rls_frank USING (true);
4035 SELECT refclassid::regclass, deptype
4036 FROM pg_depend
4037 WHERE classid = 'pg_policy'::regclass
4038 AND refobjid = 'tbl1'::regclass;
4039 refclassid | deptype
4040 ------------+---------
4041 pg_class | a
4042 (1 row)
4044 SELECT refclassid::regclass, deptype
4045 FROM pg_shdepend
4046 WHERE classid = 'pg_policy'::regclass
4047 AND refobjid IN ('regress_rls_eve'::regrole, 'regress_rls_frank'::regrole);
4048 refclassid | deptype
4049 ------------+---------
4050 pg_authid | r
4051 pg_authid | r
4052 (2 rows)
4054 SAVEPOINT q;
4055 DROP ROLE regress_rls_eve; --fails due to dependency on POLICY p
4056 ERROR: role "regress_rls_eve" cannot be dropped because some objects depend on it
4057 DETAIL: privileges for table tbl1
4058 target of policy p on table tbl1
4059 ROLLBACK TO q;
4060 ALTER POLICY p ON tbl1 TO regress_rls_frank USING (true);
4061 SAVEPOINT q;
4062 DROP ROLE regress_rls_eve; --fails due to dependency on GRANT SELECT
4063 ERROR: role "regress_rls_eve" cannot be dropped because some objects depend on it
4064 DETAIL: privileges for table tbl1
4065 ROLLBACK TO q;
4066 REVOKE ALL ON TABLE tbl1 FROM regress_rls_eve;
4067 SAVEPOINT q;
4068 DROP ROLE regress_rls_eve; --succeeds
4069 ROLLBACK TO q;
4070 SAVEPOINT q;
4071 DROP ROLE regress_rls_frank; --fails due to dependency on POLICY p
4072 ERROR: role "regress_rls_frank" cannot be dropped because some objects depend on it
4073 DETAIL: target of policy p on table tbl1
4074 ROLLBACK TO q;
4075 DROP POLICY p ON tbl1;
4076 SAVEPOINT q;
4077 DROP ROLE regress_rls_frank; -- succeeds
4078 ROLLBACK TO q;
4079 ROLLBACK; -- cleanup
4080 --
4081 -- Policy expression handling
4082 --
4083 BEGIN;
4084 CREATE TABLE t (c) AS VALUES ('bar'::text);
4085 CREATE POLICY p ON t USING (max(c)); -- fails: aggregate functions are not allowed in policy expressions
4086 ERROR: aggregate functions are not allowed in policy expressions
4087 ROLLBACK;
4088 --
4089 -- Non-target relations are only subject to SELECT policies
4090 --
4091 SET SESSION AUTHORIZATION regress_rls_alice;
4092 CREATE TABLE r1 (a int);
4093 CREATE TABLE r2 (a int);
4094 INSERT INTO r1 VALUES (10), (20);
4095 INSERT INTO r2 VALUES (10), (20);
4096 GRANT ALL ON r1, r2 TO regress_rls_bob;
4097 CREATE POLICY p1 ON r1 USING (true);
4098 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
4099 CREATE POLICY p1 ON r2 FOR SELECT USING (true);
4100 CREATE POLICY p2 ON r2 FOR INSERT WITH CHECK (false);
4101 CREATE POLICY p3 ON r2 FOR UPDATE USING (false);
4102 CREATE POLICY p4 ON r2 FOR DELETE USING (false);
4103 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
4104 SET SESSION AUTHORIZATION regress_rls_bob;
4105 SELECT * FROM r1;
4106 a
4107 ----
4108 10
4109 20
4110 (2 rows)
4112 SELECT * FROM r2;
4113 a
4114 ----
4115 10
4116 20
4117 (2 rows)
4119 -- r2 is read-only
4120 INSERT INTO r2 VALUES (2); -- Not allowed
4121 ERROR: new row violates row-level security policy for table "r2"
4122 UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing
4123 a
4124 ---
4125 (0 rows)
4127 DELETE FROM r2 RETURNING *; -- Deletes nothing
4128 a
4129 ---
4130 (0 rows)
4132 -- r2 can be used as a non-target relation in DML
4133 INSERT INTO r1 SELECT a + 1 FROM r2 RETURNING *; -- OK
4134 a
4135 ----
4136 11
4137 21
4138 (2 rows)
4140 UPDATE r1 SET a = r2.a + 2 FROM r2 WHERE r1.a = r2.a RETURNING *; -- OK
4141 a | a
4142 ----+----
4143 12 | 10
4144 22 | 20
4145 (2 rows)
4147 DELETE FROM r1 USING r2 WHERE r1.a = r2.a + 2 RETURNING *; -- OK
4148 a | a
4149 ----+----
4150 12 | 10
4151 22 | 20
4152 (2 rows)
4154 SELECT * FROM r1;
4155 a
4156 ----
4157 11
4158 21
4159 (2 rows)
4161 SELECT * FROM r2;
4162 a
4163 ----
4164 10
4165 20
4166 (2 rows)
4168 SET SESSION AUTHORIZATION regress_rls_alice;
4169 DROP TABLE r1;
4170 DROP TABLE r2;
4171 --
4172 -- FORCE ROW LEVEL SECURITY applies RLS to owners too
4173 --
4174 SET SESSION AUTHORIZATION regress_rls_alice;
4175 SET row_security = on;
4176 CREATE TABLE r1 (a int);
4177 INSERT INTO r1 VALUES (10), (20);
4178 CREATE POLICY p1 ON r1 USING (false);
4179 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
4180 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
4181 -- No error, but no rows
4182 TABLE r1;
4183 a
4184 ---
4185 (0 rows)
4187 -- RLS error
4188 INSERT INTO r1 VALUES (1);
4189 ERROR: new row violates row-level security policy for table "r1"
4190 -- No error (unable to see any rows to update)
4191 UPDATE r1 SET a = 1;
4192 TABLE r1;
4193 a
4194 ---
4195 (0 rows)
4197 -- No error (unable to see any rows to delete)
4198 DELETE FROM r1;
4199 TABLE r1;
4200 a
4201 ---
4202 (0 rows)
4204 SET row_security = off;
4205 -- these all fail, would be affected by RLS
4206 TABLE r1;
4207 ERROR: query would be affected by row-level security policy for table "r1"
4208 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
4209 UPDATE r1 SET a = 1;
4210 ERROR: query would be affected by row-level security policy for table "r1"
4211 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
4212 DELETE FROM r1;
4213 ERROR: query would be affected by row-level security policy for table "r1"
4214 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
4215 DROP TABLE r1;
4216 --
4217 -- FORCE ROW LEVEL SECURITY does not break RI
4218 --
4219 SET SESSION AUTHORIZATION regress_rls_alice;
4220 SET row_security = on;
4221 CREATE TABLE r1 (a int PRIMARY KEY);
4222 CREATE TABLE r2 (a int REFERENCES r1);
4223 INSERT INTO r1 VALUES (10), (20);
4224 INSERT INTO r2 VALUES (10), (20);
4225 -- Create policies on r2 which prevent the
4226 -- owner from seeing any rows, but RI should
4227 -- still see them.
4228 CREATE POLICY p1 ON r2 USING (false);
4229 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
4230 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
4231 -- Errors due to rows in r2
4232 DELETE FROM r1;
4233 ERROR: update or delete on table "r1" violates foreign key constraint "r2_a_fkey" on table "r2"
4234 DETAIL: Key (a)=(10) is still referenced from table "r2".
4235 -- Reset r2 to no-RLS
4236 DROP POLICY p1 ON r2;
4237 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
4238 ALTER TABLE r2 DISABLE ROW LEVEL SECURITY;
4239 -- clean out r2 for INSERT test below
4240 DELETE FROM r2;
4241 -- Change r1 to not allow rows to be seen
4242 CREATE POLICY p1 ON r1 USING (false);
4243 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
4244 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
4245 -- No rows seen
4246 TABLE r1;
4247 a
4248 ---
4249 (0 rows)
4251 -- No error, RI still sees that row exists in r1
4252 INSERT INTO r2 VALUES (10);
4253 DROP TABLE r2;
4254 DROP TABLE r1;
4255 -- Ensure cascaded DELETE works
4256 CREATE TABLE r1 (a int PRIMARY KEY);
4257 CREATE TABLE r2 (a int REFERENCES r1 ON DELETE CASCADE);
4258 INSERT INTO r1 VALUES (10), (20);
4259 INSERT INTO r2 VALUES (10), (20);
4260 -- Create policies on r2 which prevent the
4261 -- owner from seeing any rows, but RI should
4262 -- still see them.
4263 CREATE POLICY p1 ON r2 USING (false);
4264 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
4265 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
4266 -- Deletes all records from both
4267 DELETE FROM r1;
4268 -- Remove FORCE from r2
4269 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
4270 -- As owner, we now bypass RLS
4271 -- verify no rows in r2 now
4272 TABLE r2;
4273 a
4274 ---
4275 (0 rows)
4277 DROP TABLE r2;
4278 DROP TABLE r1;
4279 -- Ensure cascaded UPDATE works
4280 CREATE TABLE r1 (a int PRIMARY KEY);
4281 CREATE TABLE r2 (a int REFERENCES r1 ON UPDATE CASCADE);
4282 INSERT INTO r1 VALUES (10), (20);
4283 INSERT INTO r2 VALUES (10), (20);
4284 -- Create policies on r2 which prevent the
4285 -- owner from seeing any rows, but RI should
4286 -- still see them.
4287 CREATE POLICY p1 ON r2 USING (false);
4288 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
4289 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
4290 -- Updates records in both
4291 UPDATE r1 SET a = a+5;
4292 -- Remove FORCE from r2
4293 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
4294 -- As owner, we now bypass RLS
4295 -- verify records in r2 updated
4296 TABLE r2;
4297 a
4298 ----
4299 15
4300 25
4301 (2 rows)
4303 DROP TABLE r2;
4304 DROP TABLE r1;
4305 --
4306 -- Test INSERT+RETURNING applies SELECT policies as
4307 -- WithCheckOptions (meaning an error is thrown)
4308 --
4309 SET SESSION AUTHORIZATION regress_rls_alice;
4310 SET row_security = on;
4311 CREATE TABLE r1 (a int);
4312 CREATE POLICY p1 ON r1 FOR SELECT USING (false);
4313 CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true);
4314 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
4315 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
4316 -- Works fine
4317 INSERT INTO r1 VALUES (10), (20);
4318 -- No error, but no rows
4319 TABLE r1;
4320 a
4321 ---
4322 (0 rows)
4324 SET row_security = off;
4325 -- fail, would be affected by RLS
4326 TABLE r1;
4327 ERROR: query would be affected by row-level security policy for table "r1"
4328 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
4329 SET row_security = on;
4330 -- Error
4331 INSERT INTO r1 VALUES (10), (20) RETURNING *;
4332 ERROR: new row violates row-level security policy for table "r1"
4333 DROP TABLE r1;
4334 --
4335 -- Test UPDATE+RETURNING applies SELECT policies as
4336 -- WithCheckOptions (meaning an error is thrown)
4337 --
4338 SET SESSION AUTHORIZATION regress_rls_alice;
4339 SET row_security = on;
4340 CREATE TABLE r1 (a int PRIMARY KEY);
4341 CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
4342 CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true);
4343 CREATE POLICY p3 ON r1 FOR INSERT WITH CHECK (true);
4344 INSERT INTO r1 VALUES (10);
4345 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
4346 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
4347 -- Works fine
4348 UPDATE r1 SET a = 30;
4349 -- Show updated rows
4350 ALTER TABLE r1 NO FORCE ROW LEVEL SECURITY;
4351 TABLE r1;
4352 a
4353 ----
4354 30
4355 (1 row)
4357 -- reset value in r1 for test with RETURNING
4358 UPDATE r1 SET a = 10;
4359 -- Verify row reset
4360 TABLE r1;
4361 a
4362 ----
4363 10
4364 (1 row)
4366 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
4367 -- Error
4368 UPDATE r1 SET a = 30 RETURNING *;
4369 ERROR: new row violates row-level security policy for table "r1"
4370 -- UPDATE path of INSERT ... ON CONFLICT DO UPDATE should also error out
4371 INSERT INTO r1 VALUES (10)
4372 ON CONFLICT (a) DO UPDATE SET a = 30 RETURNING *;
4373 ERROR: new row violates row-level security policy for table "r1"
4374 -- Should still error out without RETURNING (use of arbiter always requires
4375 -- SELECT permissions)
4376 INSERT INTO r1 VALUES (10)
4377 ON CONFLICT (a) DO UPDATE SET a = 30;
4378 ERROR: new row violates row-level security policy for table "r1"
4379 INSERT INTO r1 VALUES (10)
4380 ON CONFLICT ON CONSTRAINT r1_pkey DO UPDATE SET a = 30;
4381 ERROR: new row violates row-level security policy for table "r1"
4382 DROP TABLE r1;
4383 -- Check dependency handling
4384 RESET SESSION AUTHORIZATION;
4385 CREATE TABLE dep1 (c1 int);
4386 CREATE TABLE dep2 (c1 int);
4387 CREATE POLICY dep_p1 ON dep1 TO regress_rls_bob USING (c1 > (select max(dep2.c1) from dep2));
4388 ALTER POLICY dep_p1 ON dep1 TO regress_rls_bob,regress_rls_carol;
4389 -- Should return one
4390 SELECT count(*) = 1 FROM pg_depend
4391 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
4392 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
4393 ?column?
4394 ----------
4395 t
4396 (1 row)
4398 ALTER POLICY dep_p1 ON dep1 USING (true);
4399 -- Should return one
4400 SELECT count(*) = 1 FROM pg_shdepend
4401 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
4402 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_bob');
4403 ?column?
4404 ----------
4405 t
4406 (1 row)
4408 -- Should return one
4409 SELECT count(*) = 1 FROM pg_shdepend
4410 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
4411 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_carol');
4412 ?column?
4413 ----------
4414 t
4415 (1 row)
4417 -- Should return zero
4418 SELECT count(*) = 0 FROM pg_depend
4419 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
4420 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
4421 ?column?
4422 ----------
4423 t
4424 (1 row)
4426 -- DROP OWNED BY testing
4427 RESET SESSION AUTHORIZATION;
4428 CREATE ROLE regress_rls_dob_role1;
4429 CREATE ROLE regress_rls_dob_role2;
4430 CREATE TABLE dob_t1 (c1 int);
4431 CREATE TABLE dob_t2 (c1 int) PARTITION BY RANGE (c1);
4432 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1 USING (true);
4433 DROP OWNED BY regress_rls_dob_role1;
4434 DROP POLICY p1 ON dob_t1; -- should fail, already gone
4435 ERROR: policy "p1" for table "dob_t1" does not exist
4436 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
4437 DROP OWNED BY regress_rls_dob_role1;
4438 DROP POLICY p1 ON dob_t1; -- should succeed
4439 -- same cases with duplicate polroles entries
4440 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role1 USING (true);
4441 DROP OWNED BY regress_rls_dob_role1;
4442 DROP POLICY p1 ON dob_t1; -- should fail, already gone
4443 ERROR: policy "p1" for table "dob_t1" does not exist
4444 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
4445 DROP OWNED BY regress_rls_dob_role1;
4446 DROP POLICY p1 ON dob_t1; -- should succeed
4447 -- partitioned target
4448 CREATE POLICY p1 ON dob_t2 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
4449 DROP OWNED BY regress_rls_dob_role1;
4450 DROP POLICY p1 ON dob_t2; -- should succeed
4451 DROP USER regress_rls_dob_role1;
4452 DROP USER regress_rls_dob_role2;
4453 -- Bug #15708: view + table with RLS should check policies as view owner
4454 CREATE TABLE ref_tbl (a int);
4455 INSERT INTO ref_tbl VALUES (1);
4456 CREATE TABLE rls_tbl (a int);
4457 INSERT INTO rls_tbl VALUES (10);
4458 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
4459 CREATE POLICY p1 ON rls_tbl USING (EXISTS (SELECT 1 FROM ref_tbl));
4460 GRANT SELECT ON ref_tbl TO regress_rls_bob;
4461 GRANT SELECT ON rls_tbl TO regress_rls_bob;
4462 CREATE VIEW rls_view AS SELECT * FROM rls_tbl;
4463 ALTER VIEW rls_view OWNER TO regress_rls_bob;
4464 GRANT SELECT ON rls_view TO regress_rls_alice;
4465 SET SESSION AUTHORIZATION regress_rls_alice;
4466 SELECT * FROM ref_tbl; -- Permission denied
4467 ERROR: permission denied for table ref_tbl
4468 SELECT * FROM rls_tbl; -- Permission denied
4469 ERROR: permission denied for table rls_tbl
4470 SELECT * FROM rls_view; -- OK
4471 a
4472 ----
4473 10
4474 (1 row)
4476 RESET SESSION AUTHORIZATION;
4477 DROP VIEW rls_view;
4478 DROP TABLE rls_tbl;
4479 DROP TABLE ref_tbl;
4480 -- Leaky operator test
4481 CREATE TABLE rls_tbl (a int);
4482 INSERT INTO rls_tbl SELECT x/10 FROM generate_series(1, 100) x;
4483 ANALYZE rls_tbl;
4484 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
4485 GRANT SELECT ON rls_tbl TO regress_rls_alice;
4486 SET SESSION AUTHORIZATION regress_rls_alice;
4487 CREATE FUNCTION op_leak(int, int) RETURNS bool
4488 AS 'BEGIN RAISE NOTICE ''op_leak => %, %'', $1, $2; RETURN $1 < $2; END'
4489 LANGUAGE plpgsql;
4490 CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int,
4491 restrict = scalarltsel);
4492 SELECT * FROM rls_tbl WHERE a <<< 1000;
4493 a
4494 ---
4495 (0 rows)
4497 EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900;
4498 QUERY PLAN
4499 --------------------------
4500 Result
4501 One-Time Filter: false
4502 (2 rows)
4504 DROP OPERATOR <<< (int, int);
4505 DROP FUNCTION op_leak(int, int);
4506 RESET SESSION AUTHORIZATION;
4507 DROP TABLE rls_tbl;
4508 -- Bug #16006: whole-row Vars in a policy don't play nice with sub-selects
4509 SET SESSION AUTHORIZATION regress_rls_alice;
4510 CREATE TABLE rls_tbl (a int, b int, c int);
4511 CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1));
4512 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
4513 ALTER TABLE rls_tbl FORCE ROW LEVEL SECURITY;
4514 INSERT INTO rls_tbl SELECT 10, 20, 30;
4515 EXPLAIN (VERBOSE, COSTS OFF)
4516 INSERT INTO rls_tbl
4517 SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss;
4518 QUERY PLAN
4519 --------------------------------------------------------------------
4520 Insert on regress_rls_schema.rls_tbl
4521 -> Subquery Scan on ss
4522 Output: ss.b, ss.c, NULL::integer
4523 -> Sort
4524 Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a
4525 Sort Key: rls_tbl_1.a
4526 -> Seq Scan on regress_rls_schema.rls_tbl rls_tbl_1
4527 Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a
4528 Filter: (rls_tbl_1.* >= '(1,1,1)'::record)
4529 (9 rows)
4531 INSERT INTO rls_tbl
4532 SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss;
4533 SELECT * FROM rls_tbl;
4534 a | b | c
4535 ----+----+----
4536 10 | 20 | 30
4537 20 | 30 |
4538 (2 rows)
4540 DROP TABLE rls_tbl;
4541 RESET SESSION AUTHORIZATION;
4542 -- CVE-2023-2455: inlining an SRF may introduce an RLS dependency
4543 create table rls_t (c text);
4544 insert into rls_t values ('invisible to bob');
4545 alter table rls_t enable row level security;
4546 grant select on rls_t to regress_rls_alice, regress_rls_bob;
4547 create policy p1 on rls_t for select to regress_rls_alice using (true);
4548 create policy p2 on rls_t for select to regress_rls_bob using (false);
4549 create function rls_f () returns setof rls_t
4550 stable language sql
4551 as $$ select * from rls_t $$;
4552 prepare q as select current_user, * from rls_f();
4553 set role regress_rls_alice;
4554 execute q;
4555 current_user | c
4556 -------------------+------------------
4557 regress_rls_alice | invisible to bob
4558 (1 row)
4560 set role regress_rls_bob;
4561 execute q;
4562 current_user | c
4563 --------------+---
4564 (0 rows)
4566 -- make sure RLS dependencies in CTEs are handled
4567 reset role;
4568 create or replace function rls_f() returns setof rls_t
4569 stable language sql
4570 as $$ with cte as (select * from rls_t) select * from cte $$;
4571 prepare r as select current_user, * from rls_f();
4572 set role regress_rls_alice;
4573 execute r;
4574 current_user | c
4575 -------------------+------------------
4576 regress_rls_alice | invisible to bob
4577 (1 row)
4579 set role regress_rls_bob;
4580 execute r;
4581 current_user | c
4582 --------------+---
4583 (0 rows)
4585 -- make sure RLS dependencies in subqueries are handled
4586 reset role;
4587 create or replace function rls_f() returns setof rls_t
4588 stable language sql
4589 as $$ select * from (select * from rls_t) _ $$;
4590 prepare s as select current_user, * from rls_f();
4591 set role regress_rls_alice;
4592 execute s;
4593 current_user | c
4594 -------------------+------------------
4595 regress_rls_alice | invisible to bob
4596 (1 row)
4598 set role regress_rls_bob;
4599 execute s;
4600 current_user | c
4601 --------------+---
4602 (0 rows)
4604 -- make sure RLS dependencies in sublinks are handled
4605 reset role;
4606 create or replace function rls_f() returns setof rls_t
4607 stable language sql
4608 as $$ select exists(select * from rls_t)::text $$;
4609 prepare t as select current_user, * from rls_f();
4610 set role regress_rls_alice;
4611 execute t;
4612 current_user | c
4613 -------------------+------
4614 regress_rls_alice | true
4615 (1 row)
4617 set role regress_rls_bob;
4618 execute t;
4619 current_user | c
4620 -----------------+-------
4621 regress_rls_bob | false
4622 (1 row)
4624 -- make sure RLS dependencies are handled when coercion projections are inserted
4625 reset role;
4626 create or replace function rls_f() returns setof rls_t
4627 stable language sql
4628 as $$ select * from (select array_agg(c) as cs from rls_t) _ group by cs $$;
4629 prepare u as select current_user, * from rls_f();
4630 set role regress_rls_alice;
4631 execute u;
4632 current_user | c
4633 -------------------+----------------------
4634 regress_rls_alice | {"invisible to bob"}
4635 (1 row)
4637 set role regress_rls_bob;
4638 execute u;
4639 current_user | c
4640 -----------------+---
4641 regress_rls_bob |
4642 (1 row)
4644 -- make sure RLS dependencies in security invoker views are handled
4645 reset role;
4646 create view rls_v with (security_invoker) as select * from rls_t;
4647 grant select on rls_v to regress_rls_alice, regress_rls_bob;
4648 create or replace function rls_f() returns setof rls_t
4649 stable language sql
4650 as $$ select * from rls_v $$;
4651 prepare v as select current_user, * from rls_f();
4652 set role regress_rls_alice;
4653 execute v;
4654 current_user | c
4655 -------------------+------------------
4656 regress_rls_alice | invisible to bob
4657 (1 row)
4659 set role regress_rls_bob;
4660 execute v;
4661 current_user | c
4662 --------------+---
4663 (0 rows)
4665 RESET ROLE;
4666 DROP FUNCTION rls_f();
4667 DROP VIEW rls_v;
4668 DROP TABLE rls_t;
4669 --
4670 -- Clean up objects
4671 --
4672 RESET SESSION AUTHORIZATION;
4673 DROP SCHEMA regress_rls_schema CASCADE;
4674 NOTICE: drop cascades to 30 other objects
4675 DETAIL: drop cascades to function f_leak(text)
4676 drop cascades to table uaccount
4677 drop cascades to table category
4678 drop cascades to table document
4679 drop cascades to table part_document
4680 drop cascades to table dependent
4681 drop cascades to table rec1
4682 drop cascades to table rec2
4683 drop cascades to view rec1v
4684 drop cascades to view rec2v
4685 drop cascades to table s1
4686 drop cascades to table s2
4687 drop cascades to view v2
4688 drop cascades to table b1
4689 drop cascades to view bv1
4690 drop cascades to table z1
4691 drop cascades to table z2
4692 drop cascades to table z1_blacklist
4693 drop cascades to table x1
4694 drop cascades to table y1
4695 drop cascades to table y2
4696 drop cascades to table t1
4697 drop cascades to table t2
4698 drop cascades to table t3
4699 drop cascades to table t4
4700 drop cascades to table current_check
4701 drop cascades to table dep1
4702 drop cascades to table dep2
4703 drop cascades to table dob_t1
4704 drop cascades to table dob_t2
4705 DROP USER regress_rls_alice;
4706 DROP USER regress_rls_bob;
4707 DROP USER regress_rls_carol;
4708 DROP USER regress_rls_dave;
4709 DROP USER regress_rls_exempt_user;
4710 DROP ROLE regress_rls_group1;
4711 DROP ROLE regress_rls_group2;
4712 -- Arrange to have a few policies left over, for testing
4713 -- pg_dump/pg_restore
4714 CREATE SCHEMA regress_rls_schema;
4715 CREATE TABLE rls_tbl (c1 int);
4716 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
4717 CREATE POLICY p1 ON rls_tbl USING (c1 > 5);
4718 CREATE POLICY p2 ON rls_tbl FOR SELECT USING (c1 <= 3);
4719 CREATE POLICY p3 ON rls_tbl FOR UPDATE USING (c1 <= 3) WITH CHECK (c1 > 5);
4720 CREATE POLICY p4 ON rls_tbl FOR DELETE USING (c1 <= 3);
4721 CREATE TABLE rls_tbl_force (c1 int);
4722 ALTER TABLE rls_tbl_force ENABLE ROW LEVEL SECURITY;
4723 ALTER TABLE rls_tbl_force FORCE ROW LEVEL SECURITY;
4724 CREATE POLICY p1 ON rls_tbl_force USING (c1 = 5) WITH CHECK (c1 < 5);
4725 CREATE POLICY p2 ON rls_tbl_force FOR SELECT USING (c1 = 8);
4726 CREATE POLICY p3 ON rls_tbl_force FOR UPDATE USING (c1 = 8) WITH CHECK (c1 >= 5);
4727 CREATE POLICY p4 ON rls_tbl_force FOR DELETE USING (c1 = 8);