| blob | 89397e41f017faa3d54e8f922898809b786994d0 |
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=arwdDxt/regress_rls_alice+| |
100 | | | =arwdDxt/regress_rls_alice | |
101 regress_rls_schema | document | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p1: +
102 | | | =arwdDxt/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=arwdDxt/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 <= $0) AND f_leak(dtitle))
272 InitPlan 1 (returns $0)
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 (returns $0)
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 <= $0) 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 <= $0) AND f_leak(dtitle))
336 InitPlan 1 (returns $0)
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 (returns $0)
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 <= $0) 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 Partitions: part_document_fiction FOR VALUES FROM (11) TO (12),
959 part_document_nonfiction FOR VALUES FROM (99) TO (100),
960 part_document_satire FOR VALUES FROM (55) TO (56)
962 SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename like '%part_document%' ORDER BY policyname;
963 schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
964 --------------------+---------------+------------+-------------+--------------------+-----+--------------------------------------------+------------
965 regress_rls_schema | part_document | pp1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
966 | | | | | | FROM uaccount +|
967 | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
968 regress_rls_schema | part_document | pp1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid < 55) |
969 (2 rows)
971 -- viewpoint from regress_rls_bob
972 SET SESSION AUTHORIZATION regress_rls_bob;
973 SET row_security TO ON;
974 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
975 NOTICE: f_leak => my first novel
976 NOTICE: f_leak => great science fiction
977 NOTICE: f_leak => awesome science fiction
978 NOTICE: f_leak => my first satire
979 did | cid | dlevel | dauthor | dtitle
980 -----+-----+--------+-------------------+-------------------------
981 1 | 11 | 1 | regress_rls_bob | my first novel
982 4 | 55 | 1 | regress_rls_bob | my first satire
983 6 | 11 | 1 | regress_rls_carol | great science fiction
984 9 | 11 | 1 | regress_rls_dave | awesome science fiction
985 (4 rows)
987 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
988 QUERY PLAN
989 ------------------------------------------------------------
990 Append
991 InitPlan 1 (returns $0)
992 -> Index Scan using uaccount_pkey on uaccount
993 Index Cond: (pguser = CURRENT_USER)
994 -> Seq Scan on part_document_fiction part_document_1
995 Filter: ((dlevel <= $0) AND f_leak(dtitle))
996 -> Seq Scan on part_document_satire part_document_2
997 Filter: ((dlevel <= $0) AND f_leak(dtitle))
998 -> Seq Scan on part_document_nonfiction part_document_3
999 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1000 (10 rows)
1002 -- viewpoint from regress_rls_carol
1003 SET SESSION AUTHORIZATION regress_rls_carol;
1004 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1005 NOTICE: f_leak => my first novel
1006 NOTICE: f_leak => my second novel
1007 NOTICE: f_leak => great science fiction
1008 NOTICE: f_leak => awesome science fiction
1009 NOTICE: f_leak => my first satire
1010 NOTICE: f_leak => great satire
1011 NOTICE: f_leak => my science textbook
1012 NOTICE: f_leak => my history book
1013 NOTICE: f_leak => great technology book
1014 NOTICE: f_leak => awesome technology book
1015 did | cid | dlevel | dauthor | dtitle
1016 -----+-----+--------+-------------------+-------------------------
1017 1 | 11 | 1 | regress_rls_bob | my first novel
1018 2 | 11 | 2 | regress_rls_bob | my second novel
1019 3 | 99 | 2 | regress_rls_bob | my science textbook
1020 4 | 55 | 1 | regress_rls_bob | my first satire
1021 5 | 99 | 2 | regress_rls_bob | my history book
1022 6 | 11 | 1 | regress_rls_carol | great science fiction
1023 7 | 99 | 2 | regress_rls_carol | great technology book
1024 8 | 55 | 2 | regress_rls_carol | great satire
1025 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1026 10 | 99 | 2 | regress_rls_dave | awesome technology book
1027 (10 rows)
1029 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1030 QUERY PLAN
1031 ------------------------------------------------------------
1032 Append
1033 InitPlan 1 (returns $0)
1034 -> Index Scan using uaccount_pkey on uaccount
1035 Index Cond: (pguser = CURRENT_USER)
1036 -> Seq Scan on part_document_fiction part_document_1
1037 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1038 -> Seq Scan on part_document_satire part_document_2
1039 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1040 -> Seq Scan on part_document_nonfiction part_document_3
1041 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1042 (10 rows)
1044 -- viewpoint from regress_rls_dave
1045 SET SESSION AUTHORIZATION regress_rls_dave;
1046 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1047 NOTICE: f_leak => my first novel
1048 NOTICE: f_leak => my second novel
1049 NOTICE: f_leak => great science fiction
1050 NOTICE: f_leak => awesome science fiction
1051 did | cid | dlevel | dauthor | dtitle
1052 -----+-----+--------+-------------------+-------------------------
1053 1 | 11 | 1 | regress_rls_bob | my first novel
1054 2 | 11 | 2 | regress_rls_bob | my second novel
1055 6 | 11 | 1 | regress_rls_carol | great science fiction
1056 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1057 (4 rows)
1059 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1060 QUERY PLAN
1061 --------------------------------------------------------------
1062 Seq Scan on part_document_fiction part_document
1063 Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
1064 InitPlan 1 (returns $0)
1065 -> Index Scan using uaccount_pkey on uaccount
1066 Index Cond: (pguser = CURRENT_USER)
1067 (5 rows)
1069 -- pp1 ERROR
1070 INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail
1071 ERROR: new row violates row-level security policy for table "part_document"
1072 -- pp1r ERROR
1073 INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail
1074 ERROR: new row violates row-level security policy "pp1r" for table "part_document"
1075 -- Show that RLS policy does not apply for direct inserts to children
1076 -- This should fail with RLS POLICY pp1r violation.
1077 INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
1078 ERROR: new row violates row-level security policy "pp1r" for table "part_document"
1079 -- But this should succeed.
1080 INSERT INTO part_document_satire VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- success
1081 -- We still cannot see the row using the parent
1082 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1083 NOTICE: f_leak => my first novel
1084 NOTICE: f_leak => my second novel
1085 NOTICE: f_leak => great science fiction
1086 NOTICE: f_leak => awesome science fiction
1087 did | cid | dlevel | dauthor | dtitle
1088 -----+-----+--------+-------------------+-------------------------
1089 1 | 11 | 1 | regress_rls_bob | my first novel
1090 2 | 11 | 2 | regress_rls_bob | my second novel
1091 6 | 11 | 1 | regress_rls_carol | great science fiction
1092 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1093 (4 rows)
1095 -- But we can if we look directly
1096 SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
1097 NOTICE: f_leak => my first satire
1098 NOTICE: f_leak => great satire
1099 NOTICE: f_leak => testing RLS with partitions
1100 did | cid | dlevel | dauthor | dtitle
1101 -----+-----+--------+-------------------+-----------------------------
1102 4 | 55 | 1 | regress_rls_bob | my first satire
1103 8 | 55 | 2 | regress_rls_carol | great satire
1104 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1105 (3 rows)
1107 -- Turn on RLS and create policy on child to show RLS is checked before constraints
1108 SET SESSION AUTHORIZATION regress_rls_alice;
1109 ALTER TABLE part_document_satire ENABLE ROW LEVEL SECURITY;
1110 CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE
1111 USING (cid < 55);
1112 -- This should fail with RLS violation now.
1113 SET SESSION AUTHORIZATION regress_rls_dave;
1114 INSERT INTO part_document_satire VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
1115 ERROR: new row violates row-level security policy for table "part_document_satire"
1116 -- And now we cannot see directly into the partition either, due to RLS
1117 SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
1118 did | cid | dlevel | dauthor | dtitle
1119 -----+-----+--------+---------+--------
1120 (0 rows)
1122 -- The parent looks same as before
1123 -- viewpoint from regress_rls_dave
1124 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1125 NOTICE: f_leak => my first novel
1126 NOTICE: f_leak => my second novel
1127 NOTICE: f_leak => great science fiction
1128 NOTICE: f_leak => awesome science fiction
1129 did | cid | dlevel | dauthor | dtitle
1130 -----+-----+--------+-------------------+-------------------------
1131 1 | 11 | 1 | regress_rls_bob | my first novel
1132 2 | 11 | 2 | regress_rls_bob | my second novel
1133 6 | 11 | 1 | regress_rls_carol | great science fiction
1134 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1135 (4 rows)
1137 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1138 QUERY PLAN
1139 --------------------------------------------------------------
1140 Seq Scan on part_document_fiction part_document
1141 Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
1142 InitPlan 1 (returns $0)
1143 -> Index Scan using uaccount_pkey on uaccount
1144 Index Cond: (pguser = CURRENT_USER)
1145 (5 rows)
1147 -- viewpoint from regress_rls_carol
1148 SET SESSION AUTHORIZATION regress_rls_carol;
1149 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1150 NOTICE: f_leak => my first novel
1151 NOTICE: f_leak => my second novel
1152 NOTICE: f_leak => great science fiction
1153 NOTICE: f_leak => awesome science fiction
1154 NOTICE: f_leak => my first satire
1155 NOTICE: f_leak => great satire
1156 NOTICE: f_leak => testing RLS with partitions
1157 NOTICE: f_leak => my science textbook
1158 NOTICE: f_leak => my history book
1159 NOTICE: f_leak => great technology book
1160 NOTICE: f_leak => awesome technology book
1161 did | cid | dlevel | dauthor | dtitle
1162 -----+-----+--------+-------------------+-----------------------------
1163 1 | 11 | 1 | regress_rls_bob | my first novel
1164 2 | 11 | 2 | regress_rls_bob | my second novel
1165 3 | 99 | 2 | regress_rls_bob | my science textbook
1166 4 | 55 | 1 | regress_rls_bob | my first satire
1167 5 | 99 | 2 | regress_rls_bob | my history book
1168 6 | 11 | 1 | regress_rls_carol | great science fiction
1169 7 | 99 | 2 | regress_rls_carol | great technology book
1170 8 | 55 | 2 | regress_rls_carol | great satire
1171 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1172 10 | 99 | 2 | regress_rls_dave | awesome technology book
1173 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1174 (11 rows)
1176 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1177 QUERY PLAN
1178 ------------------------------------------------------------
1179 Append
1180 InitPlan 1 (returns $0)
1181 -> Index Scan using uaccount_pkey on uaccount
1182 Index Cond: (pguser = CURRENT_USER)
1183 -> Seq Scan on part_document_fiction part_document_1
1184 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1185 -> Seq Scan on part_document_satire part_document_2
1186 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1187 -> Seq Scan on part_document_nonfiction part_document_3
1188 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1189 (10 rows)
1191 -- only owner can change policies
1192 ALTER POLICY pp1 ON part_document USING (true); --fail
1193 ERROR: must be owner of table part_document
1194 DROP POLICY pp1 ON part_document; --fail
1195 ERROR: must be owner of relation part_document
1196 SET SESSION AUTHORIZATION regress_rls_alice;
1197 ALTER POLICY pp1 ON part_document USING (dauthor = current_user);
1198 -- viewpoint from regress_rls_bob again
1199 SET SESSION AUTHORIZATION regress_rls_bob;
1200 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1201 NOTICE: f_leak => my first novel
1202 NOTICE: f_leak => my second novel
1203 NOTICE: f_leak => my first satire
1204 NOTICE: f_leak => my science textbook
1205 NOTICE: f_leak => my history book
1206 did | cid | dlevel | dauthor | dtitle
1207 -----+-----+--------+-----------------+---------------------
1208 1 | 11 | 1 | regress_rls_bob | my first novel
1209 2 | 11 | 2 | regress_rls_bob | my second novel
1210 3 | 99 | 2 | regress_rls_bob | my science textbook
1211 4 | 55 | 1 | regress_rls_bob | my first satire
1212 5 | 99 | 2 | regress_rls_bob | my history book
1213 (5 rows)
1215 -- viewpoint from rls_regres_carol again
1216 SET SESSION AUTHORIZATION regress_rls_carol;
1217 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1218 NOTICE: f_leak => great science fiction
1219 NOTICE: f_leak => great satire
1220 NOTICE: f_leak => great technology book
1221 did | cid | dlevel | dauthor | dtitle
1222 -----+-----+--------+-------------------+-----------------------
1223 6 | 11 | 1 | regress_rls_carol | great science fiction
1224 7 | 99 | 2 | regress_rls_carol | great technology book
1225 8 | 55 | 2 | regress_rls_carol | great satire
1226 (3 rows)
1228 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1229 QUERY PLAN
1230 ---------------------------------------------------------------
1231 Append
1232 -> Seq Scan on part_document_fiction part_document_1
1233 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
1234 -> Seq Scan on part_document_satire part_document_2
1235 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
1236 -> Seq Scan on part_document_nonfiction part_document_3
1237 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
1238 (7 rows)
1240 -- database superuser does bypass RLS policy when enabled
1241 RESET SESSION AUTHORIZATION;
1242 SET row_security TO ON;
1243 SELECT * FROM part_document ORDER BY did;
1244 did | cid | dlevel | dauthor | dtitle
1245 -----+-----+--------+-------------------+-----------------------------
1246 1 | 11 | 1 | regress_rls_bob | my first novel
1247 2 | 11 | 2 | regress_rls_bob | my second novel
1248 3 | 99 | 2 | regress_rls_bob | my science textbook
1249 4 | 55 | 1 | regress_rls_bob | my first satire
1250 5 | 99 | 2 | regress_rls_bob | my history book
1251 6 | 11 | 1 | regress_rls_carol | great science fiction
1252 7 | 99 | 2 | regress_rls_carol | great technology book
1253 8 | 55 | 2 | regress_rls_carol | great satire
1254 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1255 10 | 99 | 2 | regress_rls_dave | awesome technology book
1256 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1257 (11 rows)
1259 SELECT * FROM part_document_satire ORDER by did;
1260 did | cid | dlevel | dauthor | dtitle
1261 -----+-----+--------+-------------------+-----------------------------
1262 4 | 55 | 1 | regress_rls_bob | my first satire
1263 8 | 55 | 2 | regress_rls_carol | great satire
1264 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1265 (3 rows)
1267 -- database non-superuser with bypass privilege can bypass RLS policy when disabled
1268 SET SESSION AUTHORIZATION regress_rls_exempt_user;
1269 SET row_security TO OFF;
1270 SELECT * FROM part_document ORDER BY did;
1271 did | cid | dlevel | dauthor | dtitle
1272 -----+-----+--------+-------------------+-----------------------------
1273 1 | 11 | 1 | regress_rls_bob | my first novel
1274 2 | 11 | 2 | regress_rls_bob | my second novel
1275 3 | 99 | 2 | regress_rls_bob | my science textbook
1276 4 | 55 | 1 | regress_rls_bob | my first satire
1277 5 | 99 | 2 | regress_rls_bob | my history book
1278 6 | 11 | 1 | regress_rls_carol | great science fiction
1279 7 | 99 | 2 | regress_rls_carol | great technology book
1280 8 | 55 | 2 | regress_rls_carol | great satire
1281 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1282 10 | 99 | 2 | regress_rls_dave | awesome technology book
1283 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1284 (11 rows)
1286 SELECT * FROM part_document_satire ORDER by did;
1287 did | cid | dlevel | dauthor | dtitle
1288 -----+-----+--------+-------------------+-----------------------------
1289 4 | 55 | 1 | regress_rls_bob | my first satire
1290 8 | 55 | 2 | regress_rls_carol | great satire
1291 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1292 (3 rows)
1294 -- RLS policy does not apply to table owner when RLS enabled.
1295 SET SESSION AUTHORIZATION regress_rls_alice;
1296 SET row_security TO ON;
1297 SELECT * FROM part_document ORDER by did;
1298 did | cid | dlevel | dauthor | dtitle
1299 -----+-----+--------+-------------------+-----------------------------
1300 1 | 11 | 1 | regress_rls_bob | my first novel
1301 2 | 11 | 2 | regress_rls_bob | my second novel
1302 3 | 99 | 2 | regress_rls_bob | my science textbook
1303 4 | 55 | 1 | regress_rls_bob | my first satire
1304 5 | 99 | 2 | regress_rls_bob | my history book
1305 6 | 11 | 1 | regress_rls_carol | great science fiction
1306 7 | 99 | 2 | regress_rls_carol | great technology book
1307 8 | 55 | 2 | regress_rls_carol | great satire
1308 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1309 10 | 99 | 2 | regress_rls_dave | awesome technology book
1310 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1311 (11 rows)
1313 SELECT * FROM part_document_satire ORDER by did;
1314 did | cid | dlevel | dauthor | dtitle
1315 -----+-----+--------+-------------------+-----------------------------
1316 4 | 55 | 1 | regress_rls_bob | my first satire
1317 8 | 55 | 2 | regress_rls_carol | great satire
1318 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1319 (3 rows)
1321 -- When RLS disabled, other users get ERROR.
1322 SET SESSION AUTHORIZATION regress_rls_dave;
1323 SET row_security TO OFF;
1324 SELECT * FROM part_document ORDER by did;
1325 ERROR: query would be affected by row-level security policy for table "part_document"
1326 SELECT * FROM part_document_satire ORDER by did;
1327 ERROR: query would be affected by row-level security policy for table "part_document_satire"
1328 -- Check behavior with a policy that uses a SubPlan not an InitPlan.
1329 SET SESSION AUTHORIZATION regress_rls_alice;
1330 SET row_security TO ON;
1331 CREATE POLICY pp3 ON part_document AS RESTRICTIVE
1332 USING ((SELECT dlevel <= seclv FROM uaccount WHERE pguser = current_user));
1333 SET SESSION AUTHORIZATION regress_rls_carol;
1334 INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_carol', 'testing pp3'); -- fail
1335 ERROR: new row violates row-level security policy "pp3" for table "part_document"
1336 ----- Dependencies -----
1337 SET SESSION AUTHORIZATION regress_rls_alice;
1338 SET row_security TO ON;
1339 CREATE TABLE dependee (x integer, y integer);
1340 CREATE TABLE dependent (x integer, y integer);
1341 CREATE POLICY d1 ON dependent FOR ALL
1342 TO PUBLIC
1343 USING (x = (SELECT d.x FROM dependee d WHERE d.y = y));
1344 DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row security qual?
1345 ERROR: cannot drop table dependee because other objects depend on it
1346 DETAIL: policy d1 on table dependent depends on table dependee
1347 HINT: Use DROP ... CASCADE to drop the dependent objects too.
1348 DROP TABLE dependee CASCADE;
1349 NOTICE: drop cascades to policy d1 on table dependent
1350 EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified
1351 QUERY PLAN
1352 -----------------------
1353 Seq Scan on dependent
1354 (1 row)
1356 ----- RECURSION ----
1357 --
1358 -- Simple recursion
1359 --
1360 SET SESSION AUTHORIZATION regress_rls_alice;
1361 CREATE TABLE rec1 (x integer, y integer);
1362 CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y));
1363 ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;
1364 SET SESSION AUTHORIZATION regress_rls_bob;
1365 SELECT * FROM rec1; -- fail, direct recursion
1366 ERROR: infinite recursion detected in policy for relation "rec1"
1367 --
1368 -- Mutual recursion
1369 --
1370 SET SESSION AUTHORIZATION regress_rls_alice;
1371 CREATE TABLE rec2 (a integer, b integer);
1372 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y));
1373 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b));
1374 ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;
1375 SET SESSION AUTHORIZATION regress_rls_bob;
1376 SELECT * FROM rec1; -- fail, mutual recursion
1377 ERROR: infinite recursion detected in policy for relation "rec1"
1378 --
1379 -- Mutual recursion via views
1380 --
1381 SET SESSION AUTHORIZATION regress_rls_bob;
1382 CREATE VIEW rec1v AS SELECT * FROM rec1;
1383 CREATE VIEW rec2v AS SELECT * FROM rec2;
1384 SET SESSION AUTHORIZATION regress_rls_alice;
1385 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
1386 ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
1387 SET SESSION AUTHORIZATION regress_rls_bob;
1388 SELECT * FROM rec1; -- fail, mutual recursion via views
1389 ERROR: infinite recursion detected in policy for relation "rec1"
1390 --
1391 -- Mutual recursion via .s.b views
1392 --
1393 SET SESSION AUTHORIZATION regress_rls_bob;
1394 DROP VIEW rec1v, rec2v CASCADE;
1395 NOTICE: drop cascades to 2 other objects
1396 DETAIL: drop cascades to policy r1 on table rec1
1397 drop cascades to policy r2 on table rec2
1398 CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
1399 CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
1400 SET SESSION AUTHORIZATION regress_rls_alice;
1401 CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
1402 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
1403 SET SESSION AUTHORIZATION regress_rls_bob;
1404 SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
1405 ERROR: infinite recursion detected in policy for relation "rec1"
1406 --
1407 -- recursive RLS and VIEWs in policy
1408 --
1409 SET SESSION AUTHORIZATION regress_rls_alice;
1410 CREATE TABLE s1 (a int, b text);
1411 INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
1412 CREATE TABLE s2 (x int, y text);
1413 INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
1414 GRANT SELECT ON s1, s2 TO regress_rls_bob;
1415 CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
1416 CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
1417 CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1));
1418 ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;
1419 ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;
1420 SET SESSION AUTHORIZATION regress_rls_bob;
1421 CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
1422 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
1423 ERROR: infinite recursion detected in policy for relation "s1"
1424 INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion)
1425 ERROR: infinite recursion detected in policy for relation "s1"
1426 SET SESSION AUTHORIZATION regress_rls_alice;
1427 DROP POLICY p3 on s1;
1428 ALTER POLICY p2 ON s2 USING (x % 2 = 0);
1429 SET SESSION AUTHORIZATION regress_rls_bob;
1430 SELECT * FROM s1 WHERE f_leak(b); -- OK
1431 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
1432 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1433 a | b
1434 ---+----------------------------------
1435 2 | c81e728d9d4c2f636f067f89cc14862c
1436 4 | a87ff679a2f3e71d9181a67b7542122c
1437 (2 rows)
1439 EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
1440 QUERY PLAN
1441 -----------------------------------------------------------
1442 Seq Scan on s1
1443 Filter: ((hashed SubPlan 1) 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 => 0267aaf632e87a63288a08331f22c7c3
1454 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1455 a | b
1456 ----+----------------------------------
1457 -4 | 0267aaf632e87a63288a08331f22c7c3
1458 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1459 (2 rows)
1461 EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
1462 QUERY PLAN
1463 -----------------------------------------------------------
1464 Seq Scan on s1
1465 Filter: ((hashed SubPlan 1) AND f_leak(b))
1466 SubPlan 1
1467 -> Seq Scan on s2
1468 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
1469 (5 rows)
1471 SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
1472 xx | x | y
1473 ----+----+----------------------------------
1474 -6 | -6 | 596a3d04481816330f07e4f97510c28f
1475 -4 | -4 | 0267aaf632e87a63288a08331f22c7c3
1476 2 | 2 | c81e728d9d4c2f636f067f89cc14862c
1477 (3 rows)
1479 EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
1480 QUERY PLAN
1481 -------------------------------------------------------------------------
1482 Seq Scan on s2
1483 Filter: (((x % 2) = 0) AND (y ~~ '%28%'::text))
1484 SubPlan 2
1485 -> Limit
1486 -> Seq Scan on s1
1487 Filter: (hashed SubPlan 1)
1488 SubPlan 1
1489 -> Seq Scan on s2 s2_1
1490 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
1491 (9 rows)
1493 SET SESSION AUTHORIZATION regress_rls_alice;
1494 ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%'));
1495 SET SESSION AUTHORIZATION regress_rls_bob;
1496 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
1497 ERROR: infinite recursion detected in policy for relation "s1"
1498 -- prepared statement with regress_rls_alice privilege
1499 PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
1500 EXECUTE p1(2);
1501 id | a | b
1502 -----+---+-----
1503 102 | 2 | bbb
1504 202 | 2 | bcd
1505 302 | 2 | yyy
1506 (3 rows)
1508 EXPLAIN (COSTS OFF) EXECUTE p1(2);
1509 QUERY PLAN
1510 ----------------------------------------------
1511 Append
1512 -> Seq Scan on t1 t1_1
1513 Filter: ((a <= 2) AND ((a % 2) = 0))
1514 -> Seq Scan on t2 t1_2
1515 Filter: ((a <= 2) AND ((a % 2) = 0))
1516 -> Seq Scan on t3 t1_3
1517 Filter: ((a <= 2) AND ((a % 2) = 0))
1518 (7 rows)
1520 -- superuser is allowed to bypass RLS checks
1521 RESET SESSION AUTHORIZATION;
1522 SET row_security TO OFF;
1523 SELECT * FROM t1 WHERE f_leak(b);
1524 NOTICE: f_leak => aba
1525 NOTICE: f_leak => bbb
1526 NOTICE: f_leak => ccc
1527 NOTICE: f_leak => dad
1528 NOTICE: f_leak => abc
1529 NOTICE: f_leak => bcd
1530 NOTICE: f_leak => cde
1531 NOTICE: f_leak => def
1532 NOTICE: f_leak => xxx
1533 NOTICE: f_leak => yyy
1534 NOTICE: f_leak => zzz
1535 id | a | b
1536 -----+---+-----
1537 101 | 1 | aba
1538 102 | 2 | bbb
1539 103 | 3 | ccc
1540 104 | 4 | dad
1541 201 | 1 | abc
1542 202 | 2 | bcd
1543 203 | 3 | cde
1544 204 | 4 | def
1545 301 | 1 | xxx
1546 302 | 2 | yyy
1547 303 | 3 | zzz
1548 (11 rows)
1550 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
1551 QUERY PLAN
1552 ---------------------------
1553 Append
1554 -> Seq Scan on t1 t1_1
1555 Filter: f_leak(b)
1556 -> Seq Scan on t2 t1_2
1557 Filter: f_leak(b)
1558 -> Seq Scan on t3 t1_3
1559 Filter: f_leak(b)
1560 (7 rows)
1562 -- plan cache should be invalidated
1563 EXECUTE p1(2);
1564 id | a | b
1565 -----+---+-----
1566 101 | 1 | aba
1567 102 | 2 | bbb
1568 201 | 1 | abc
1569 202 | 2 | bcd
1570 301 | 1 | xxx
1571 302 | 2 | yyy
1572 (6 rows)
1574 EXPLAIN (COSTS OFF) EXECUTE p1(2);
1575 QUERY PLAN
1576 ---------------------------
1577 Append
1578 -> Seq Scan on t1 t1_1
1579 Filter: (a <= 2)
1580 -> Seq Scan on t2 t1_2
1581 Filter: (a <= 2)
1582 -> Seq Scan on t3 t1_3
1583 Filter: (a <= 2)
1584 (7 rows)
1586 PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1;
1587 EXECUTE p2(2);
1588 id | a | b
1589 -----+---+-----
1590 102 | 2 | bbb
1591 202 | 2 | bcd
1592 302 | 2 | yyy
1593 (3 rows)
1595 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1596 QUERY PLAN
1597 ---------------------------
1598 Append
1599 -> Seq Scan on t1 t1_1
1600 Filter: (a = 2)
1601 -> Seq Scan on t2 t1_2
1602 Filter: (a = 2)
1603 -> Seq Scan on t3 t1_3
1604 Filter: (a = 2)
1605 (7 rows)
1607 -- also, case when privilege switch from superuser
1608 SET SESSION AUTHORIZATION regress_rls_bob;
1609 SET row_security TO ON;
1610 EXECUTE p2(2);
1611 id | a | b
1612 -----+---+-----
1613 102 | 2 | bbb
1614 202 | 2 | bcd
1615 302 | 2 | yyy
1616 (3 rows)
1618 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1619 QUERY PLAN
1620 ---------------------------------------------
1621 Append
1622 -> Seq Scan on t1 t1_1
1623 Filter: ((a = 2) AND ((a % 2) = 0))
1624 -> Seq Scan on t2 t1_2
1625 Filter: ((a = 2) AND ((a % 2) = 0))
1626 -> Seq Scan on t3 t1_3
1627 Filter: ((a = 2) AND ((a % 2) = 0))
1628 (7 rows)
1630 --
1631 -- UPDATE / DELETE and Row-level security
1632 --
1633 SET SESSION AUTHORIZATION regress_rls_bob;
1634 EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
1635 QUERY PLAN
1636 -----------------------------------------------------------
1637 Update on t1
1638 Update on t1 t1_1
1639 Update on t2 t1_2
1640 Update on t3 t1_3
1641 -> Result
1642 -> Append
1643 -> Seq Scan on t1 t1_1
1644 Filter: (((a % 2) = 0) AND f_leak(b))
1645 -> Seq Scan on t2 t1_2
1646 Filter: (((a % 2) = 0) AND f_leak(b))
1647 -> Seq Scan on t3 t1_3
1648 Filter: (((a % 2) = 0) AND f_leak(b))
1649 (12 rows)
1651 UPDATE t1 SET b = b || b WHERE f_leak(b);
1652 NOTICE: f_leak => bbb
1653 NOTICE: f_leak => dad
1654 NOTICE: f_leak => bcd
1655 NOTICE: f_leak => def
1656 NOTICE: f_leak => yyy
1657 EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1658 QUERY PLAN
1659 -----------------------------------------------
1660 Update on t1
1661 -> Seq Scan on t1
1662 Filter: (((a % 2) = 0) AND f_leak(b))
1663 (3 rows)
1665 UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1666 NOTICE: f_leak => bbbbbb
1667 NOTICE: f_leak => daddad
1668 -- returning clause with system column
1669 UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1670 NOTICE: f_leak => bbbbbb_updt
1671 NOTICE: f_leak => daddad_updt
1672 tableoid | id | a | b | t1
1673 ----------+-----+---+-------------+---------------------
1674 t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt)
1675 t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt)
1676 (2 rows)
1678 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
1679 NOTICE: f_leak => bbbbbb_updt
1680 NOTICE: f_leak => daddad_updt
1681 NOTICE: f_leak => bcdbcd
1682 NOTICE: f_leak => defdef
1683 NOTICE: f_leak => yyyyyy
1684 id | a | b
1685 -----+---+-------------
1686 102 | 2 | bbbbbb_updt
1687 104 | 4 | daddad_updt
1688 202 | 2 | bcdbcd
1689 204 | 4 | defdef
1690 302 | 2 | yyyyyy
1691 (5 rows)
1693 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1694 NOTICE: f_leak => bbbbbb_updt
1695 NOTICE: f_leak => daddad_updt
1696 NOTICE: f_leak => bcdbcd
1697 NOTICE: f_leak => defdef
1698 NOTICE: f_leak => yyyyyy
1699 tableoid | id | a | b | t1
1700 ----------+-----+---+-------------+---------------------
1701 t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt)
1702 t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt)
1703 t2 | 202 | 2 | bcdbcd | (202,2,bcdbcd)
1704 t2 | 204 | 4 | defdef | (204,4,defdef)
1705 t3 | 302 | 2 | yyyyyy | (302,2,yyyyyy)
1706 (5 rows)
1708 -- updates with from clause
1709 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3
1710 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1711 QUERY PLAN
1712 -----------------------------------------------------------------
1713 Update on t2
1714 -> Nested Loop
1715 -> Seq Scan on t2
1716 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1717 -> Seq Scan on t3
1718 Filter: ((a = 2) AND f_leak(b))
1719 (6 rows)
1721 UPDATE t2 SET b=t2.b FROM t3
1722 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1723 NOTICE: f_leak => cde
1724 NOTICE: f_leak => yyyyyy
1725 EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2
1726 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1727 QUERY PLAN
1728 -----------------------------------------------------------------------
1729 Update on t1
1730 Update on t1 t1_1
1731 Update on t2 t1_2
1732 Update on t3 t1_3
1733 -> Nested Loop
1734 -> Seq Scan on t2
1735 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1736 -> Append
1737 -> Seq Scan on t1 t1_1
1738 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1739 -> Seq Scan on t2 t1_2
1740 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1741 -> Seq Scan on t3 t1_3
1742 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1743 (14 rows)
1745 UPDATE t1 SET b=t1.b FROM t2
1746 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1747 NOTICE: f_leak => cde
1748 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1
1749 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1750 QUERY PLAN
1751 -----------------------------------------------------------------------
1752 Update on t2
1753 -> Nested Loop
1754 -> Seq Scan on t2
1755 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1756 -> Append
1757 -> Seq Scan on t1 t1_1
1758 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1759 -> Seq Scan on t2 t1_2
1760 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1761 -> Seq Scan on t3 t1_3
1762 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1763 (11 rows)
1765 UPDATE t2 SET b=t2.b FROM t1
1766 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1767 NOTICE: f_leak => cde
1768 -- updates with from clause self join
1769 EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1770 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1771 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1772 QUERY PLAN
1773 -----------------------------------------------------------------
1774 Update on t2 t2_1
1775 -> Nested Loop
1776 Join Filter: (t2_1.b = t2_2.b)
1777 -> Seq Scan on t2 t2_1
1778 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1779 -> Seq Scan on t2 t2_2
1780 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1781 (7 rows)
1783 UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1784 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1785 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1786 NOTICE: f_leak => cde
1787 NOTICE: f_leak => cde
1788 id | a | b | c | id | a | b | c | t2_1 | t2_2
1789 -----+---+-----+-----+-----+---+-----+-----+-----------------+-----------------
1790 203 | 3 | cde | 3.3 | 203 | 3 | cde | 3.3 | (203,3,cde,3.3) | (203,3,cde,3.3)
1791 (1 row)
1793 EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1794 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1795 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1796 QUERY PLAN
1797 -----------------------------------------------------------------------------
1798 Update on t1 t1_1
1799 Update on t1 t1_1_1
1800 Update on t2 t1_1_2
1801 Update on t3 t1_1_3
1802 -> Nested Loop
1803 Join Filter: (t1_1.b = t1_2.b)
1804 -> Append
1805 -> Seq Scan on t1 t1_1_1
1806 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1807 -> Seq Scan on t2 t1_1_2
1808 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1809 -> Seq Scan on t3 t1_1_3
1810 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1811 -> Materialize
1812 -> Append
1813 -> Seq Scan on t1 t1_2_1
1814 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1815 -> Seq Scan on t2 t1_2_2
1816 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1817 -> Seq Scan on t3 t1_2_3
1818 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1819 (21 rows)
1821 UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1822 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1823 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1824 NOTICE: f_leak => daddad_updt
1825 NOTICE: f_leak => daddad_updt
1826 NOTICE: f_leak => defdef
1827 NOTICE: f_leak => defdef
1828 id | a | b | id | a | b | t1_1 | t1_2
1829 -----+---+-------------+-----+---+-------------+---------------------+---------------------
1830 104 | 4 | daddad_updt | 104 | 4 | daddad_updt | (104,4,daddad_updt) | (104,4,daddad_updt)
1831 204 | 4 | defdef | 204 | 4 | defdef | (204,4,defdef) | (204,4,defdef)
1832 (2 rows)
1834 RESET SESSION AUTHORIZATION;
1835 SET row_security TO OFF;
1836 SELECT * FROM t1 ORDER BY a,b;
1837 id | a | b
1838 -----+---+-------------
1839 101 | 1 | aba
1840 201 | 1 | abc
1841 301 | 1 | xxx
1842 102 | 2 | bbbbbb_updt
1843 202 | 2 | bcdbcd
1844 302 | 2 | yyyyyy
1845 103 | 3 | ccc
1846 203 | 3 | cde
1847 303 | 3 | zzz
1848 104 | 4 | daddad_updt
1849 204 | 4 | defdef
1850 (11 rows)
1852 SET SESSION AUTHORIZATION regress_rls_bob;
1853 SET row_security TO ON;
1854 EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b);
1855 QUERY PLAN
1856 -----------------------------------------------
1857 Delete on t1
1858 -> Seq Scan on t1
1859 Filter: (((a % 2) = 0) AND f_leak(b))
1860 (3 rows)
1862 EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
1863 QUERY PLAN
1864 -----------------------------------------------------
1865 Delete on t1
1866 Delete on t1 t1_1
1867 Delete on t2 t1_2
1868 Delete on t3 t1_3
1869 -> Append
1870 -> Seq Scan on t1 t1_1
1871 Filter: (((a % 2) = 0) AND f_leak(b))
1872 -> Seq Scan on t2 t1_2
1873 Filter: (((a % 2) = 0) AND f_leak(b))
1874 -> Seq Scan on t3 t1_3
1875 Filter: (((a % 2) = 0) AND f_leak(b))
1876 (11 rows)
1878 DELETE FROM only t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1879 NOTICE: f_leak => bbbbbb_updt
1880 NOTICE: f_leak => daddad_updt
1881 tableoid | id | a | b | t1
1882 ----------+-----+---+-------------+---------------------
1883 t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt)
1884 t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt)
1885 (2 rows)
1887 DELETE FROM t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1888 NOTICE: f_leak => bcdbcd
1889 NOTICE: f_leak => defdef
1890 NOTICE: f_leak => yyyyyy
1891 tableoid | id | a | b | t1
1892 ----------+-----+---+--------+----------------
1893 t2 | 202 | 2 | bcdbcd | (202,2,bcdbcd)
1894 t2 | 204 | 4 | defdef | (204,4,defdef)
1895 t3 | 302 | 2 | yyyyyy | (302,2,yyyyyy)
1896 (3 rows)
1898 --
1899 -- S.b. view on top of Row-level security
1900 --
1901 SET SESSION AUTHORIZATION regress_rls_alice;
1902 CREATE TABLE b1 (a int, b text);
1903 INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
1904 CREATE POLICY p1 ON b1 USING (a % 2 = 0);
1905 ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
1906 GRANT ALL ON b1 TO regress_rls_bob;
1907 SET SESSION AUTHORIZATION regress_rls_bob;
1908 CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
1909 GRANT ALL ON bv1 TO regress_rls_carol;
1910 SET SESSION AUTHORIZATION regress_rls_carol;
1911 EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
1912 QUERY PLAN
1913 ---------------------------------------------
1914 Subquery Scan on bv1
1915 Filter: f_leak(bv1.b)
1916 -> Seq Scan on b1
1917 Filter: ((a > 0) AND ((a % 2) = 0))
1918 (4 rows)
1920 SELECT * FROM bv1 WHERE f_leak(b);
1921 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
1922 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1923 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1924 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
1925 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
1926 a | b
1927 ----+----------------------------------
1928 2 | c81e728d9d4c2f636f067f89cc14862c
1929 4 | a87ff679a2f3e71d9181a67b7542122c
1930 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1931 8 | c9f0f895fb98ab9159f51fd0297e236d
1932 10 | d3d9446802a44259755d38e6d163e820
1933 (5 rows)
1935 INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO
1936 ERROR: new row violates row-level security policy for table "b1"
1937 INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check
1938 ERROR: new row violates row-level security policy for table "b1"
1939 INSERT INTO bv1 VALUES (12, 'xxx'); -- ok
1940 EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1941 QUERY PLAN
1942 -----------------------------------------------------------------------
1943 Update on b1
1944 -> Seq Scan on b1
1945 Filter: ((a > 0) AND (a = 4) AND ((a % 2) = 0) AND f_leak(b))
1946 (3 rows)
1948 UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1949 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1950 EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1951 QUERY PLAN
1952 -----------------------------------------------------------------------
1953 Delete on b1
1954 -> Seq Scan on b1
1955 Filter: ((a > 0) AND (a = 6) AND ((a % 2) = 0) AND f_leak(b))
1956 (3 rows)
1958 DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1959 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1960 SET SESSION AUTHORIZATION regress_rls_alice;
1961 SELECT * FROM b1;
1962 a | b
1963 -----+----------------------------------
1964 -10 | 1b0fd9efa5279c4203b7c70233f86dbf
1965 -9 | 252e691406782824eec43d7eadc3d256
1966 -8 | a8d2ec85eaf98407310b72eb73dda247
1967 -7 | 74687a12d3915d3c4d83f1af7b3683d5
1968 -6 | 596a3d04481816330f07e4f97510c28f
1969 -5 | 47c1b025fa18ea96c33fbb6718688c0f
1970 -4 | 0267aaf632e87a63288a08331f22c7c3
1971 -3 | b3149ecea4628efd23d2f86e5a723472
1972 -2 | 5d7b9adcbe1c629ec722529dd12e5129
1973 -1 | 6bb61e3b7bce0931da574d19d1d82c88
1974 0 | cfcd208495d565ef66e7dff9f98764da
1975 1 | c4ca4238a0b923820dcc509a6f75849b
1976 2 | c81e728d9d4c2f636f067f89cc14862c
1977 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
1978 5 | e4da3b7fbbce2345d7772b0674a318d5
1979 7 | 8f14e45fceea167a5a36dedd4bea2543
1980 8 | c9f0f895fb98ab9159f51fd0297e236d
1981 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
1982 10 | d3d9446802a44259755d38e6d163e820
1983 12 | xxx
1984 4 | yyy
1985 (21 rows)
1987 --
1988 -- INSERT ... ON CONFLICT DO UPDATE and Row-level security
1989 --
1990 SET SESSION AUTHORIZATION regress_rls_alice;
1991 DROP POLICY p1 ON document;
1992 DROP POLICY p1r ON document;
1993 CREATE POLICY p1 ON document FOR SELECT USING (true);
1994 CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
1995 CREATE POLICY p3 ON document FOR UPDATE
1996 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
1997 WITH CHECK (dauthor = current_user);
1998 SET SESSION AUTHORIZATION regress_rls_bob;
1999 -- Exists...
2000 SELECT * FROM document WHERE did = 2;
2001 did | cid | dlevel | dauthor | dtitle
2002 -----+-----+--------+-----------------+-----------------
2003 2 | 11 | 2 | regress_rls_bob | my second novel
2004 (1 row)
2006 -- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
2007 -- alternative UPDATE path happens to be taken):
2008 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel')
2009 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor;
2010 ERROR: new row violates row-level security policy for table "document"
2011 -- Violates USING qual for UPDATE policy p3.
2012 --
2013 -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
2014 -- updated is not a "novel"/cid 11 (row is not leaked, even though we have
2015 -- SELECT privileges sufficient to see the row in this instance):
2016 INSERT INTO document VALUES (33, 22, 1, 'regress_rls_bob', 'okay science fiction'); -- preparation for next statement
2017 INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi') -- takes UPDATE path
2018 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
2019 ERROR: new row violates row-level security policy (USING expression) for table "document"
2020 -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
2021 -- not violated):
2022 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
2023 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2024 did | cid | dlevel | dauthor | dtitle
2025 -----+-----+--------+-----------------+----------------
2026 2 | 11 | 2 | regress_rls_bob | my first novel
2027 (1 row)
2029 -- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated):
2030 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
2031 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
2032 did | cid | dlevel | dauthor | dtitle
2033 -----+-----+--------+-----------------+-----------------------
2034 78 | 11 | 1 | regress_rls_bob | some technology novel
2035 (1 row)
2037 -- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the
2038 -- case in respect of *existing* tuple):
2039 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
2040 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
2041 did | cid | dlevel | dauthor | dtitle
2042 -----+-----+--------+-----------------+-----------------------
2043 78 | 33 | 1 | regress_rls_bob | some technology novel
2044 (1 row)
2046 -- Same query a third time, but now fails due to existing tuple finally not
2047 -- passing quals:
2048 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
2049 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
2050 ERROR: new row violates row-level security policy (USING expression) for table "document"
2051 -- Don't fail just because INSERT doesn't satisfy WITH CHECK option that
2052 -- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE
2053 -- path *isn't* taken, and so UPDATE-related policy does not apply:
2054 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
2055 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2056 did | cid | dlevel | dauthor | dtitle
2057 -----+-----+--------+-----------------+----------------------------------
2058 79 | 33 | 1 | regress_rls_bob | technology book, can only insert
2059 (1 row)
2061 -- But this time, the same statement fails, because the UPDATE path is taken,
2062 -- and updating the row just inserted falls afoul of security barrier qual
2063 -- (enforced as WCO) -- what we might have updated target tuple to is
2064 -- irrelevant, in fact.
2065 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
2066 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2067 ERROR: new row violates row-level security policy (USING expression) for table "document"
2068 -- Test default USING qual enforced as WCO
2069 SET SESSION AUTHORIZATION regress_rls_alice;
2070 DROP POLICY p1 ON document;
2071 DROP POLICY p2 ON document;
2072 DROP POLICY p3 ON document;
2073 CREATE POLICY p3_with_default ON document FOR UPDATE
2074 USING (cid = (SELECT cid from category WHERE cname = 'novel'));
2075 SET SESSION AUTHORIZATION regress_rls_bob;
2076 -- Just because WCO-style enforcement of USING quals occurs with
2077 -- existing/target tuple does not mean that the implementation can be allowed
2078 -- to fail to also enforce this qual against the final tuple appended to
2079 -- relation (since in the absence of an explicit WCO, this is also interpreted
2080 -- as an UPDATE/ALL WCO in general).
2081 --
2082 -- UPDATE path is taken here (fails due to existing tuple). Note that this is
2083 -- not reported as a "USING expression", because it's an RLS UPDATE check that originated as
2084 -- a USING qual for the purposes of RLS in general, as opposed to an explicit
2085 -- USING qual that is ordinarily a security barrier. We leave it up to the
2086 -- UPDATE to make this fail:
2087 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
2088 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2089 ERROR: new row violates row-level security policy for table "document"
2090 -- UPDATE path is taken here. Existing tuple passes, since its cid
2091 -- corresponds to "novel", but default USING qual is enforced against
2092 -- post-UPDATE tuple too (as always when updating with a policy that lacks an
2093 -- explicit WCO), and so this fails:
2094 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'my first novel')
2095 ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *;
2096 ERROR: new row violates row-level security policy for table "document"
2097 SET SESSION AUTHORIZATION regress_rls_alice;
2098 DROP POLICY p3_with_default ON document;
2099 --
2100 -- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE
2101 -- tests)
2102 --
2103 CREATE POLICY p3_with_all ON document FOR ALL
2104 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
2105 WITH CHECK (dauthor = current_user);
2106 SET SESSION AUTHORIZATION regress_rls_bob;
2107 -- Fails, since ALL WCO is enforced in insert path:
2108 INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel')
2109 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33;
2110 ERROR: new row violates row-level security policy for table "document"
2111 -- Fails, since ALL policy USING qual is enforced (existing, target tuple is in
2112 -- violation, since it has the "manga" cid):
2113 INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
2114 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
2115 ERROR: new row violates row-level security policy (USING expression) for table "document"
2116 -- Fails, since ALL WCO are enforced:
2117 INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
2118 ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol';
2119 ERROR: new row violates row-level security policy for table "document"
2120 --
2121 -- ROLE/GROUP
2122 --
2123 SET SESSION AUTHORIZATION regress_rls_alice;
2124 CREATE TABLE z1 (a int, b text);
2125 CREATE TABLE z2 (a int, b text);
2126 GRANT SELECT ON z1,z2 TO regress_rls_group1, regress_rls_group2,
2127 regress_rls_bob, regress_rls_carol;
2128 INSERT INTO z1 VALUES
2129 (1, 'aba'),
2130 (2, 'bbb'),
2131 (3, 'ccc'),
2132 (4, 'dad');
2133 CREATE POLICY p1 ON z1 TO regress_rls_group1 USING (a % 2 = 0);
2134 CREATE POLICY p2 ON z1 TO regress_rls_group2 USING (a % 2 = 1);
2135 ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
2136 SET SESSION AUTHORIZATION regress_rls_bob;
2137 SELECT * FROM z1 WHERE f_leak(b);
2138 NOTICE: f_leak => bbb
2139 NOTICE: f_leak => dad
2140 a | b
2141 ---+-----
2142 2 | bbb
2143 4 | dad
2144 (2 rows)
2146 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2147 QUERY PLAN
2148 -----------------------------------------
2149 Seq Scan on z1
2150 Filter: (((a % 2) = 0) AND f_leak(b))
2151 (2 rows)
2153 PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b);
2154 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2155 QUERY PLAN
2156 -----------------------------------------
2157 Seq Scan on z1
2158 Filter: (((a % 2) = 0) AND f_leak(b))
2159 (2 rows)
2161 PREPARE plancache_test2 AS WITH q AS MATERIALIZED (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
2162 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2163 QUERY PLAN
2164 -------------------------------------------------
2165 Nested Loop
2166 CTE q
2167 -> Seq Scan on z1
2168 Filter: (((a % 2) = 0) AND f_leak(b))
2169 -> CTE Scan on q
2170 -> Materialize
2171 -> Seq Scan on z2
2172 (7 rows)
2174 PREPARE plancache_test3 AS WITH q AS MATERIALIZED (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
2175 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2176 QUERY PLAN
2177 -----------------------------------------------------
2178 Nested Loop
2179 CTE q
2180 -> Seq Scan on z2
2181 -> CTE Scan on q
2182 -> Materialize
2183 -> Seq Scan on z1
2184 Filter: (((a % 2) = 0) AND f_leak(b))
2185 (7 rows)
2187 SET ROLE regress_rls_group1;
2188 SELECT * FROM z1 WHERE f_leak(b);
2189 NOTICE: f_leak => bbb
2190 NOTICE: f_leak => dad
2191 a | b
2192 ---+-----
2193 2 | bbb
2194 4 | dad
2195 (2 rows)
2197 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2198 QUERY PLAN
2199 -----------------------------------------
2200 Seq Scan on z1
2201 Filter: (((a % 2) = 0) AND f_leak(b))
2202 (2 rows)
2204 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2205 QUERY PLAN
2206 -----------------------------------------
2207 Seq Scan on z1
2208 Filter: (((a % 2) = 0) AND f_leak(b))
2209 (2 rows)
2211 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2212 QUERY PLAN
2213 -------------------------------------------------
2214 Nested Loop
2215 CTE q
2216 -> Seq Scan on z1
2217 Filter: (((a % 2) = 0) AND f_leak(b))
2218 -> CTE Scan on q
2219 -> Materialize
2220 -> Seq Scan on z2
2221 (7 rows)
2223 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2224 QUERY PLAN
2225 -----------------------------------------------------
2226 Nested Loop
2227 CTE q
2228 -> Seq Scan on z2
2229 -> CTE Scan on q
2230 -> Materialize
2231 -> Seq Scan on z1
2232 Filter: (((a % 2) = 0) AND f_leak(b))
2233 (7 rows)
2235 SET SESSION AUTHORIZATION regress_rls_carol;
2236 SELECT * FROM z1 WHERE f_leak(b);
2237 NOTICE: f_leak => aba
2238 NOTICE: f_leak => ccc
2239 a | b
2240 ---+-----
2241 1 | aba
2242 3 | ccc
2243 (2 rows)
2245 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2246 QUERY PLAN
2247 -----------------------------------------
2248 Seq Scan on z1
2249 Filter: (((a % 2) = 1) AND f_leak(b))
2250 (2 rows)
2252 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2253 QUERY PLAN
2254 -----------------------------------------
2255 Seq Scan on z1
2256 Filter: (((a % 2) = 1) AND f_leak(b))
2257 (2 rows)
2259 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2260 QUERY PLAN
2261 -------------------------------------------------
2262 Nested Loop
2263 CTE q
2264 -> Seq Scan on z1
2265 Filter: (((a % 2) = 1) AND f_leak(b))
2266 -> CTE Scan on q
2267 -> Materialize
2268 -> Seq Scan on z2
2269 (7 rows)
2271 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2272 QUERY PLAN
2273 -----------------------------------------------------
2274 Nested Loop
2275 CTE q
2276 -> Seq Scan on z2
2277 -> CTE Scan on q
2278 -> Materialize
2279 -> Seq Scan on z1
2280 Filter: (((a % 2) = 1) AND f_leak(b))
2281 (7 rows)
2283 SET ROLE regress_rls_group2;
2284 SELECT * FROM z1 WHERE f_leak(b);
2285 NOTICE: f_leak => aba
2286 NOTICE: f_leak => ccc
2287 a | b
2288 ---+-----
2289 1 | aba
2290 3 | ccc
2291 (2 rows)
2293 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2294 QUERY PLAN
2295 -----------------------------------------
2296 Seq Scan on z1
2297 Filter: (((a % 2) = 1) AND f_leak(b))
2298 (2 rows)
2300 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2301 QUERY PLAN
2302 -----------------------------------------
2303 Seq Scan on z1
2304 Filter: (((a % 2) = 1) AND f_leak(b))
2305 (2 rows)
2307 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2308 QUERY PLAN
2309 -------------------------------------------------
2310 Nested Loop
2311 CTE q
2312 -> Seq Scan on z1
2313 Filter: (((a % 2) = 1) AND f_leak(b))
2314 -> CTE Scan on q
2315 -> Materialize
2316 -> Seq Scan on z2
2317 (7 rows)
2319 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2320 QUERY PLAN
2321 -----------------------------------------------------
2322 Nested Loop
2323 CTE q
2324 -> Seq Scan on z2
2325 -> CTE Scan on q
2326 -> Materialize
2327 -> Seq Scan on z1
2328 Filter: (((a % 2) = 1) AND f_leak(b))
2329 (7 rows)
2331 --
2332 -- Views should follow policy for view owner.
2333 --
2334 -- View and Table owner are the same.
2335 SET SESSION AUTHORIZATION regress_rls_alice;
2336 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
2337 GRANT SELECT ON rls_view TO regress_rls_bob;
2338 -- Query as role that is not owner of view or table. Should return all records.
2339 SET SESSION AUTHORIZATION regress_rls_bob;
2340 SELECT * FROM rls_view;
2341 NOTICE: f_leak => aba
2342 NOTICE: f_leak => bbb
2343 NOTICE: f_leak => ccc
2344 NOTICE: f_leak => dad
2345 a | b
2346 ---+-----
2347 1 | aba
2348 2 | bbb
2349 3 | ccc
2350 4 | dad
2351 (4 rows)
2353 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2354 QUERY PLAN
2355 ---------------------
2356 Seq Scan on z1
2357 Filter: f_leak(b)
2358 (2 rows)
2360 -- Query as view/table owner. Should return all records.
2361 SET SESSION AUTHORIZATION regress_rls_alice;
2362 SELECT * FROM rls_view;
2363 NOTICE: f_leak => aba
2364 NOTICE: f_leak => bbb
2365 NOTICE: f_leak => ccc
2366 NOTICE: f_leak => dad
2367 a | b
2368 ---+-----
2369 1 | aba
2370 2 | bbb
2371 3 | ccc
2372 4 | dad
2373 (4 rows)
2375 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2376 QUERY PLAN
2377 ---------------------
2378 Seq Scan on z1
2379 Filter: f_leak(b)
2380 (2 rows)
2382 DROP VIEW rls_view;
2383 -- View and Table owners are different.
2384 SET SESSION AUTHORIZATION regress_rls_bob;
2385 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
2386 GRANT SELECT ON rls_view TO regress_rls_alice;
2387 -- Query as role that is not owner of view but is owner of table.
2388 -- Should return records based on view owner policies.
2389 SET SESSION AUTHORIZATION regress_rls_alice;
2390 SELECT * FROM rls_view;
2391 NOTICE: f_leak => bbb
2392 NOTICE: f_leak => dad
2393 a | b
2394 ---+-----
2395 2 | bbb
2396 4 | dad
2397 (2 rows)
2399 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2400 QUERY PLAN
2401 -----------------------------------------
2402 Seq Scan on z1
2403 Filter: (((a % 2) = 0) AND f_leak(b))
2404 (2 rows)
2406 -- Query as role that is not owner of table but is owner of view.
2407 -- Should return records based on view owner policies.
2408 SET SESSION AUTHORIZATION regress_rls_bob;
2409 SELECT * FROM rls_view;
2410 NOTICE: f_leak => bbb
2411 NOTICE: f_leak => dad
2412 a | b
2413 ---+-----
2414 2 | bbb
2415 4 | dad
2416 (2 rows)
2418 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2419 QUERY PLAN
2420 -----------------------------------------
2421 Seq Scan on z1
2422 Filter: (((a % 2) = 0) AND f_leak(b))
2423 (2 rows)
2425 -- Query as role that is not the owner of the table or view without permissions.
2426 SET SESSION AUTHORIZATION regress_rls_carol;
2427 SELECT * FROM rls_view; --fail - permission denied.
2428 ERROR: permission denied for view rls_view
2429 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
2430 ERROR: permission denied for view rls_view
2431 -- Query as role that is not the owner of the table or view with permissions.
2432 SET SESSION AUTHORIZATION regress_rls_bob;
2433 GRANT SELECT ON rls_view TO regress_rls_carol;
2434 SELECT * FROM rls_view;
2435 NOTICE: f_leak => bbb
2436 NOTICE: f_leak => dad
2437 a | b
2438 ---+-----
2439 2 | bbb
2440 4 | dad
2441 (2 rows)
2443 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2444 QUERY PLAN
2445 -----------------------------------------
2446 Seq Scan on z1
2447 Filter: (((a % 2) = 0) AND f_leak(b))
2448 (2 rows)
2450 SET SESSION AUTHORIZATION regress_rls_bob;
2451 DROP VIEW rls_view;
2452 --
2453 -- Command specific
2454 --
2455 SET SESSION AUTHORIZATION regress_rls_alice;
2456 CREATE TABLE x1 (a int, b text, c text);
2457 GRANT ALL ON x1 TO PUBLIC;
2458 INSERT INTO x1 VALUES
2459 (1, 'abc', 'regress_rls_bob'),
2460 (2, 'bcd', 'regress_rls_bob'),
2461 (3, 'cde', 'regress_rls_carol'),
2462 (4, 'def', 'regress_rls_carol'),
2463 (5, 'efg', 'regress_rls_bob'),
2464 (6, 'fgh', 'regress_rls_bob'),
2465 (7, 'fgh', 'regress_rls_carol'),
2466 (8, 'fgh', 'regress_rls_carol');
2467 CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
2468 CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
2469 CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1);
2470 CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0);
2471 CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
2472 ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
2473 SET SESSION AUTHORIZATION regress_rls_bob;
2474 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
2475 NOTICE: f_leak => abc
2476 NOTICE: f_leak => bcd
2477 NOTICE: f_leak => def
2478 NOTICE: f_leak => efg
2479 NOTICE: f_leak => fgh
2480 NOTICE: f_leak => fgh
2481 a | b | c
2482 ---+-----+-------------------
2483 1 | abc | regress_rls_bob
2484 2 | bcd | regress_rls_bob
2485 4 | def | regress_rls_carol
2486 5 | efg | regress_rls_bob
2487 6 | fgh | regress_rls_bob
2488 8 | fgh | regress_rls_carol
2489 (6 rows)
2491 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
2492 NOTICE: f_leak => abc
2493 NOTICE: f_leak => bcd
2494 NOTICE: f_leak => def
2495 NOTICE: f_leak => efg
2496 NOTICE: f_leak => fgh
2497 NOTICE: f_leak => fgh
2498 a | b | c
2499 ---+----------+-------------------
2500 1 | abc_updt | regress_rls_bob
2501 2 | bcd_updt | regress_rls_bob
2502 4 | def_updt | regress_rls_carol
2503 5 | efg_updt | regress_rls_bob
2504 6 | fgh_updt | regress_rls_bob
2505 8 | fgh_updt | regress_rls_carol
2506 (6 rows)
2508 SET SESSION AUTHORIZATION regress_rls_carol;
2509 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
2510 NOTICE: f_leak => cde
2511 NOTICE: f_leak => fgh
2512 NOTICE: f_leak => bcd_updt
2513 NOTICE: f_leak => def_updt
2514 NOTICE: f_leak => fgh_updt
2515 NOTICE: f_leak => fgh_updt
2516 a | b | c
2517 ---+----------+-------------------
2518 2 | bcd_updt | regress_rls_bob
2519 3 | cde | regress_rls_carol
2520 4 | def_updt | regress_rls_carol
2521 6 | fgh_updt | regress_rls_bob
2522 7 | fgh | regress_rls_carol
2523 8 | fgh_updt | regress_rls_carol
2524 (6 rows)
2526 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
2527 NOTICE: f_leak => cde
2528 NOTICE: f_leak => fgh
2529 NOTICE: f_leak => bcd_updt
2530 NOTICE: f_leak => def_updt
2531 NOTICE: f_leak => fgh_updt
2532 NOTICE: f_leak => fgh_updt
2533 a | b | c
2534 ---+---------------+-------------------
2535 3 | cde_updt | regress_rls_carol
2536 7 | fgh_updt | regress_rls_carol
2537 2 | bcd_updt_updt | regress_rls_bob
2538 4 | def_updt_updt | regress_rls_carol
2539 6 | fgh_updt_updt | regress_rls_bob
2540 8 | fgh_updt_updt | regress_rls_carol
2541 (6 rows)
2543 DELETE FROM x1 WHERE f_leak(b) RETURNING *;
2544 NOTICE: f_leak => cde_updt
2545 NOTICE: f_leak => fgh_updt
2546 NOTICE: f_leak => bcd_updt_updt
2547 NOTICE: f_leak => def_updt_updt
2548 NOTICE: f_leak => fgh_updt_updt
2549 NOTICE: f_leak => fgh_updt_updt
2550 a | b | c
2551 ---+---------------+-------------------
2552 3 | cde_updt | regress_rls_carol
2553 7 | fgh_updt | regress_rls_carol
2554 2 | bcd_updt_updt | regress_rls_bob
2555 4 | def_updt_updt | regress_rls_carol
2556 6 | fgh_updt_updt | regress_rls_bob
2557 8 | fgh_updt_updt | regress_rls_carol
2558 (6 rows)
2560 --
2561 -- Duplicate Policy Names
2562 --
2563 SET SESSION AUTHORIZATION regress_rls_alice;
2564 CREATE TABLE y1 (a int, b text);
2565 CREATE TABLE y2 (a int, b text);
2566 GRANT ALL ON y1, y2 TO regress_rls_bob;
2567 CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
2568 CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
2569 CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
2570 ERROR: policy "p1" for table "y1" already exists
2571 CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK
2572 ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;
2573 ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
2574 --
2575 -- Expression structure with SBV
2576 --
2577 -- Create view as table owner. RLS should NOT be applied.
2578 SET SESSION AUTHORIZATION regress_rls_alice;
2579 CREATE VIEW rls_sbv WITH (security_barrier) AS
2580 SELECT * FROM y1 WHERE f_leak(b);
2581 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
2582 QUERY PLAN
2583 -----------------------------------
2584 Seq Scan on y1
2585 Filter: (f_leak(b) AND (a = 1))
2586 (2 rows)
2588 DROP VIEW rls_sbv;
2589 -- Create view as role that does not own table. RLS should be applied.
2590 SET SESSION AUTHORIZATION regress_rls_bob;
2591 CREATE VIEW rls_sbv WITH (security_barrier) AS
2592 SELECT * FROM y1 WHERE f_leak(b);
2593 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
2594 QUERY PLAN
2595 ------------------------------------------------------------------
2596 Seq Scan on y1
2597 Filter: ((a = 1) AND ((a > 2) OR ((a % 2) = 0)) AND f_leak(b))
2598 (2 rows)
2600 DROP VIEW rls_sbv;
2601 --
2602 -- Expression structure
2603 --
2604 SET SESSION AUTHORIZATION regress_rls_alice;
2605 INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
2606 CREATE POLICY p2 ON y2 USING (a % 3 = 0);
2607 CREATE POLICY p3 ON y2 USING (a % 4 = 0);
2608 SET SESSION AUTHORIZATION regress_rls_bob;
2609 SELECT * FROM y2 WHERE f_leak(b);
2610 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2611 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2612 NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
2613 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2614 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2615 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2616 NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
2617 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2618 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2619 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2620 NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
2621 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2622 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2623 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2624 a | b
2625 ----+----------------------------------
2626 0 | cfcd208495d565ef66e7dff9f98764da
2627 2 | c81e728d9d4c2f636f067f89cc14862c
2628 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2629 4 | a87ff679a2f3e71d9181a67b7542122c
2630 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2631 8 | c9f0f895fb98ab9159f51fd0297e236d
2632 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2633 10 | d3d9446802a44259755d38e6d163e820
2634 12 | c20ad4d76fe97759aa27a0c99bff6710
2635 14 | aab3238922bcc25a6f606eb525ffdc56
2636 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2637 16 | c74d97b01eae257e44aa9d5bade97baf
2638 18 | 6f4922f45568161a8cdf4ad2299f6d23
2639 20 | 98f13708210194c475687be6106a3b84
2640 (14 rows)
2642 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
2643 QUERY PLAN
2644 -----------------------------------------------------------------------------
2645 Seq Scan on y2
2646 Filter: ((((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)) AND f_leak(b))
2647 (2 rows)
2649 --
2650 -- Qual push-down of leaky functions, when not referring to table
2651 --
2652 SELECT * FROM y2 WHERE f_leak('abc');
2653 NOTICE: f_leak => abc
2654 NOTICE: f_leak => abc
2655 NOTICE: f_leak => abc
2656 NOTICE: f_leak => abc
2657 NOTICE: f_leak => abc
2658 NOTICE: f_leak => abc
2659 NOTICE: f_leak => abc
2660 NOTICE: f_leak => abc
2661 NOTICE: f_leak => abc
2662 NOTICE: f_leak => abc
2663 NOTICE: f_leak => abc
2664 NOTICE: f_leak => abc
2665 NOTICE: f_leak => abc
2666 NOTICE: f_leak => abc
2667 NOTICE: f_leak => abc
2668 NOTICE: f_leak => abc
2669 NOTICE: f_leak => abc
2670 NOTICE: f_leak => abc
2671 NOTICE: f_leak => abc
2672 NOTICE: f_leak => abc
2673 NOTICE: f_leak => abc
2674 a | b
2675 ----+----------------------------------
2676 0 | cfcd208495d565ef66e7dff9f98764da
2677 2 | c81e728d9d4c2f636f067f89cc14862c
2678 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2679 4 | a87ff679a2f3e71d9181a67b7542122c
2680 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2681 8 | c9f0f895fb98ab9159f51fd0297e236d
2682 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2683 10 | d3d9446802a44259755d38e6d163e820
2684 12 | c20ad4d76fe97759aa27a0c99bff6710
2685 14 | aab3238922bcc25a6f606eb525ffdc56
2686 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2687 16 | c74d97b01eae257e44aa9d5bade97baf
2688 18 | 6f4922f45568161a8cdf4ad2299f6d23
2689 20 | 98f13708210194c475687be6106a3b84
2690 (14 rows)
2692 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc');
2693 QUERY PLAN
2694 ---------------------------------------------------------------------------------------
2695 Seq Scan on y2
2696 Filter: (f_leak('abc'::text) AND (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)))
2697 (2 rows)
2699 CREATE TABLE test_qual_pushdown (
2700 abc text
2701 );
2702 INSERT INTO test_qual_pushdown VALUES ('abc'),('def');
2703 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
2704 NOTICE: f_leak => abc
2705 NOTICE: f_leak => def
2706 a | b | abc
2707 ---+---+-----
2708 (0 rows)
2710 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
2711 QUERY PLAN
2712 -------------------------------------------------------------------------
2713 Hash Join
2714 Hash Cond: (test_qual_pushdown.abc = y2.b)
2715 -> Seq Scan on test_qual_pushdown
2716 Filter: f_leak(abc)
2717 -> Hash
2718 -> Seq Scan on y2
2719 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
2720 (7 rows)
2722 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
2723 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2724 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2725 NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
2726 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2727 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2728 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2729 NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
2730 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2731 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2732 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2733 NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
2734 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2735 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2736 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2737 a | b | abc
2738 ---+---+-----
2739 (0 rows)
2741 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
2742 QUERY PLAN
2743 -----------------------------------------------------------------------------------------
2744 Hash Join
2745 Hash Cond: (test_qual_pushdown.abc = y2.b)
2746 -> Seq Scan on test_qual_pushdown
2747 -> Hash
2748 -> Seq Scan on y2
2749 Filter: ((((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)) AND f_leak(b))
2750 (6 rows)
2752 DROP TABLE test_qual_pushdown;
2753 --
2754 -- Plancache invalidate on user change.
2755 --
2756 RESET SESSION AUTHORIZATION;
2757 DROP TABLE t1 CASCADE;
2758 NOTICE: drop cascades to 2 other objects
2759 DETAIL: drop cascades to table t2
2760 drop cascades to table t3
2761 CREATE TABLE t1 (a integer);
2762 GRANT SELECT ON t1 TO regress_rls_bob, regress_rls_carol;
2763 CREATE POLICY p1 ON t1 TO regress_rls_bob USING ((a % 2) = 0);
2764 CREATE POLICY p2 ON t1 TO regress_rls_carol USING ((a % 4) = 0);
2765 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
2766 -- Prepare as regress_rls_bob
2767 SET ROLE regress_rls_bob;
2768 PREPARE role_inval AS SELECT * FROM t1;
2769 -- Check plan
2770 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2771 QUERY PLAN
2772 -------------------------
2773 Seq Scan on t1
2774 Filter: ((a % 2) = 0)
2775 (2 rows)
2777 -- Change to regress_rls_carol
2778 SET ROLE regress_rls_carol;
2779 -- Check plan- should be different
2780 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2781 QUERY PLAN
2782 -------------------------
2783 Seq Scan on t1
2784 Filter: ((a % 4) = 0)
2785 (2 rows)
2787 -- Change back to regress_rls_bob
2788 SET ROLE regress_rls_bob;
2789 -- Check plan- should be back to original
2790 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2791 QUERY PLAN
2792 -------------------------
2793 Seq Scan on t1
2794 Filter: ((a % 2) = 0)
2795 (2 rows)
2797 --
2798 -- CTE and RLS
2799 --
2800 RESET SESSION AUTHORIZATION;
2801 DROP TABLE t1 CASCADE;
2802 CREATE TABLE t1 (a integer, b text);
2803 CREATE POLICY p1 ON t1 USING (a % 2 = 0);
2804 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
2805 GRANT ALL ON t1 TO regress_rls_bob;
2806 INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
2807 SET SESSION AUTHORIZATION regress_rls_bob;
2808 WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
2809 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2810 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2811 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2812 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2813 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2814 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2815 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2816 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2817 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2818 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2819 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2820 a | b
2821 ----+----------------------------------
2822 0 | cfcd208495d565ef66e7dff9f98764da
2823 2 | c81e728d9d4c2f636f067f89cc14862c
2824 4 | a87ff679a2f3e71d9181a67b7542122c
2825 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2826 8 | c9f0f895fb98ab9159f51fd0297e236d
2827 10 | d3d9446802a44259755d38e6d163e820
2828 12 | c20ad4d76fe97759aa27a0c99bff6710
2829 14 | aab3238922bcc25a6f606eb525ffdc56
2830 16 | c74d97b01eae257e44aa9d5bade97baf
2831 18 | 6f4922f45568161a8cdf4ad2299f6d23
2832 20 | 98f13708210194c475687be6106a3b84
2833 (11 rows)
2835 EXPLAIN (COSTS OFF)
2836 WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
2837 QUERY PLAN
2838 -------------------------------------------------
2839 CTE Scan on cte1
2840 CTE cte1
2841 -> Seq Scan on t1
2842 Filter: (((a % 2) = 0) AND f_leak(b))
2843 (4 rows)
2845 WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
2846 ERROR: new row violates row-level security policy for table "t1"
2847 WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
2848 a | b
2849 ----+----------------------------------
2850 0 | cfcd208495d565ef66e7dff9f98764da
2851 2 | c81e728d9d4c2f636f067f89cc14862c
2852 4 | a87ff679a2f3e71d9181a67b7542122c
2853 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2854 8 | c9f0f895fb98ab9159f51fd0297e236d
2855 10 | d3d9446802a44259755d38e6d163e820
2856 12 | c20ad4d76fe97759aa27a0c99bff6710
2857 14 | aab3238922bcc25a6f606eb525ffdc56
2858 16 | c74d97b01eae257e44aa9d5bade97baf
2859 18 | 6f4922f45568161a8cdf4ad2299f6d23
2860 20 | 98f13708210194c475687be6106a3b84
2861 (11 rows)
2863 WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail
2864 ERROR: new row violates row-level security policy for table "t1"
2865 WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok
2866 a | b
2867 ----+---------
2868 20 | Success
2869 (1 row)
2871 --
2872 -- Rename Policy
2873 --
2874 RESET SESSION AUTHORIZATION;
2875 ALTER POLICY p1 ON t1 RENAME TO p1; --fail
2876 ERROR: policy "p1" for table "t1" already exists
2877 SELECT polname, relname
2878 FROM pg_policy pol
2879 JOIN pg_class pc ON (pc.oid = pol.polrelid)
2880 WHERE relname = 't1';
2881 polname | relname
2882 ---------+---------
2883 p1 | t1
2884 (1 row)
2886 ALTER POLICY p1 ON t1 RENAME TO p2; --ok
2887 SELECT polname, relname
2888 FROM pg_policy pol
2889 JOIN pg_class pc ON (pc.oid = pol.polrelid)
2890 WHERE relname = 't1';
2891 polname | relname
2892 ---------+---------
2893 p2 | t1
2894 (1 row)
2896 --
2897 -- Check INSERT SELECT
2898 --
2899 SET SESSION AUTHORIZATION regress_rls_bob;
2900 CREATE TABLE t2 (a integer, b text);
2901 INSERT INTO t2 (SELECT * FROM t1);
2902 EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
2903 QUERY PLAN
2904 -------------------------------
2905 Insert on t2
2906 -> Seq Scan on t1
2907 Filter: ((a % 2) = 0)
2908 (3 rows)
2910 SELECT * FROM t2;
2911 a | b
2912 ----+----------------------------------
2913 0 | cfcd208495d565ef66e7dff9f98764da
2914 2 | c81e728d9d4c2f636f067f89cc14862c
2915 4 | a87ff679a2f3e71d9181a67b7542122c
2916 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2917 8 | c9f0f895fb98ab9159f51fd0297e236d
2918 10 | d3d9446802a44259755d38e6d163e820
2919 12 | c20ad4d76fe97759aa27a0c99bff6710
2920 14 | aab3238922bcc25a6f606eb525ffdc56
2921 16 | c74d97b01eae257e44aa9d5bade97baf
2922 18 | 6f4922f45568161a8cdf4ad2299f6d23
2923 20 | 98f13708210194c475687be6106a3b84
2924 20 | Success
2925 (12 rows)
2927 EXPLAIN (COSTS OFF) SELECT * FROM t2;
2928 QUERY PLAN
2929 ----------------
2930 Seq Scan on t2
2931 (1 row)
2933 CREATE TABLE t3 AS SELECT * FROM t1;
2934 SELECT * FROM t3;
2935 a | b
2936 ----+----------------------------------
2937 0 | cfcd208495d565ef66e7dff9f98764da
2938 2 | c81e728d9d4c2f636f067f89cc14862c
2939 4 | a87ff679a2f3e71d9181a67b7542122c
2940 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2941 8 | c9f0f895fb98ab9159f51fd0297e236d
2942 10 | d3d9446802a44259755d38e6d163e820
2943 12 | c20ad4d76fe97759aa27a0c99bff6710
2944 14 | aab3238922bcc25a6f606eb525ffdc56
2945 16 | c74d97b01eae257e44aa9d5bade97baf
2946 18 | 6f4922f45568161a8cdf4ad2299f6d23
2947 20 | 98f13708210194c475687be6106a3b84
2948 20 | Success
2949 (12 rows)
2951 SELECT * INTO t4 FROM t1;
2952 SELECT * FROM t4;
2953 a | b
2954 ----+----------------------------------
2955 0 | cfcd208495d565ef66e7dff9f98764da
2956 2 | c81e728d9d4c2f636f067f89cc14862c
2957 4 | a87ff679a2f3e71d9181a67b7542122c
2958 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2959 8 | c9f0f895fb98ab9159f51fd0297e236d
2960 10 | d3d9446802a44259755d38e6d163e820
2961 12 | c20ad4d76fe97759aa27a0c99bff6710
2962 14 | aab3238922bcc25a6f606eb525ffdc56
2963 16 | c74d97b01eae257e44aa9d5bade97baf
2964 18 | 6f4922f45568161a8cdf4ad2299f6d23
2965 20 | 98f13708210194c475687be6106a3b84
2966 20 | Success
2967 (12 rows)
2969 --
2970 -- RLS with JOIN
2971 --
2972 SET SESSION AUTHORIZATION regress_rls_alice;
2973 CREATE TABLE blog (id integer, author text, post text);
2974 CREATE TABLE comment (blog_id integer, message text);
2975 GRANT ALL ON blog, comment TO regress_rls_bob;
2976 CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
2977 ALTER TABLE blog ENABLE ROW LEVEL SECURITY;
2978 INSERT INTO blog VALUES
2979 (1, 'alice', 'blog #1'),
2980 (2, 'bob', 'blog #1'),
2981 (3, 'alice', 'blog #2'),
2982 (4, 'alice', 'blog #3'),
2983 (5, 'john', 'blog #1');
2984 INSERT INTO comment VALUES
2985 (1, 'cool blog'),
2986 (1, 'fun blog'),
2987 (3, 'crazy blog'),
2988 (5, 'what?'),
2989 (4, 'insane!'),
2990 (2, 'who did it?');
2991 SET SESSION AUTHORIZATION regress_rls_bob;
2992 -- Check RLS JOIN with Non-RLS.
2993 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
2994 id | author | message
2995 ----+--------+-------------
2996 4 | alice | insane!
2997 2 | bob | who did it?
2998 (2 rows)
3000 -- Check Non-RLS JOIN with RLS.
3001 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
3002 id | author | message
3003 ----+--------+-------------
3004 4 | alice | insane!
3005 2 | bob | who did it?
3006 (2 rows)
3008 SET SESSION AUTHORIZATION regress_rls_alice;
3009 CREATE POLICY comment_1 ON comment USING (blog_id < 4);
3010 ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
3011 SET SESSION AUTHORIZATION regress_rls_bob;
3012 -- Check RLS JOIN RLS
3013 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
3014 id | author | message
3015 ----+--------+-------------
3016 2 | bob | who did it?
3017 (1 row)
3019 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
3020 id | author | message
3021 ----+--------+-------------
3022 2 | bob | who did it?
3023 (1 row)
3025 SET SESSION AUTHORIZATION regress_rls_alice;
3026 DROP TABLE blog, comment;
3027 --
3028 -- Default Deny Policy
3029 --
3030 RESET SESSION AUTHORIZATION;
3031 DROP POLICY p2 ON t1;
3032 ALTER TABLE t1 OWNER TO regress_rls_alice;
3033 -- Check that default deny does not apply to superuser.
3034 RESET SESSION AUTHORIZATION;
3035 SELECT * FROM t1;
3036 a | b
3037 ----+----------------------------------
3038 1 | c4ca4238a0b923820dcc509a6f75849b
3039 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
3040 5 | e4da3b7fbbce2345d7772b0674a318d5
3041 7 | 8f14e45fceea167a5a36dedd4bea2543
3042 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
3043 11 | 6512bd43d9caa6e02c990b0a82652dca
3044 13 | c51ce410c124a10e0db5e4b97fc2af39
3045 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
3046 17 | 70efdf2ec9b086079795c442636b55fb
3047 19 | 1f0e3dad99908345f7439f8ffabdffc4
3048 0 | cfcd208495d565ef66e7dff9f98764da
3049 2 | c81e728d9d4c2f636f067f89cc14862c
3050 4 | a87ff679a2f3e71d9181a67b7542122c
3051 6 | 1679091c5a880faf6fb5e6087eb1b2dc
3052 8 | c9f0f895fb98ab9159f51fd0297e236d
3053 10 | d3d9446802a44259755d38e6d163e820
3054 12 | c20ad4d76fe97759aa27a0c99bff6710
3055 14 | aab3238922bcc25a6f606eb525ffdc56
3056 16 | c74d97b01eae257e44aa9d5bade97baf
3057 18 | 6f4922f45568161a8cdf4ad2299f6d23
3058 20 | 98f13708210194c475687be6106a3b84
3059 20 | Success
3060 (22 rows)
3062 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3063 QUERY PLAN
3064 ----------------
3065 Seq Scan on t1
3066 (1 row)
3068 -- Check that default deny does not apply to table owner.
3069 SET SESSION AUTHORIZATION regress_rls_alice;
3070 SELECT * FROM t1;
3071 a | b
3072 ----+----------------------------------
3073 1 | c4ca4238a0b923820dcc509a6f75849b
3074 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
3075 5 | e4da3b7fbbce2345d7772b0674a318d5
3076 7 | 8f14e45fceea167a5a36dedd4bea2543
3077 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
3078 11 | 6512bd43d9caa6e02c990b0a82652dca
3079 13 | c51ce410c124a10e0db5e4b97fc2af39
3080 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
3081 17 | 70efdf2ec9b086079795c442636b55fb
3082 19 | 1f0e3dad99908345f7439f8ffabdffc4
3083 0 | cfcd208495d565ef66e7dff9f98764da
3084 2 | c81e728d9d4c2f636f067f89cc14862c
3085 4 | a87ff679a2f3e71d9181a67b7542122c
3086 6 | 1679091c5a880faf6fb5e6087eb1b2dc
3087 8 | c9f0f895fb98ab9159f51fd0297e236d
3088 10 | d3d9446802a44259755d38e6d163e820
3089 12 | c20ad4d76fe97759aa27a0c99bff6710
3090 14 | aab3238922bcc25a6f606eb525ffdc56
3091 16 | c74d97b01eae257e44aa9d5bade97baf
3092 18 | 6f4922f45568161a8cdf4ad2299f6d23
3093 20 | 98f13708210194c475687be6106a3b84
3094 20 | Success
3095 (22 rows)
3097 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3098 QUERY PLAN
3099 ----------------
3100 Seq Scan on t1
3101 (1 row)
3103 -- Check that default deny applies to non-owner/non-superuser when RLS on.
3104 SET SESSION AUTHORIZATION regress_rls_bob;
3105 SET row_security TO ON;
3106 SELECT * FROM t1;
3107 a | b
3108 ---+---
3109 (0 rows)
3111 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3112 QUERY PLAN
3113 --------------------------
3114 Result
3115 One-Time Filter: false
3116 (2 rows)
3118 SET SESSION AUTHORIZATION regress_rls_bob;
3119 SELECT * FROM t1;
3120 a | b
3121 ---+---
3122 (0 rows)
3124 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3125 QUERY PLAN
3126 --------------------------
3127 Result
3128 One-Time Filter: false
3129 (2 rows)
3131 --
3132 -- COPY TO/FROM
3133 --
3134 RESET SESSION AUTHORIZATION;
3135 DROP TABLE copy_t CASCADE;
3136 ERROR: table "copy_t" does not exist
3137 CREATE TABLE copy_t (a integer, b text);
3138 CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
3139 ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
3140 GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user;
3141 INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
3142 -- Check COPY TO as Superuser/owner.
3143 RESET SESSION AUTHORIZATION;
3144 SET row_security TO OFF;
3145 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
3146 0,cfcd208495d565ef66e7dff9f98764da
3147 1,c4ca4238a0b923820dcc509a6f75849b
3148 2,c81e728d9d4c2f636f067f89cc14862c
3149 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
3150 4,a87ff679a2f3e71d9181a67b7542122c
3151 5,e4da3b7fbbce2345d7772b0674a318d5
3152 6,1679091c5a880faf6fb5e6087eb1b2dc
3153 7,8f14e45fceea167a5a36dedd4bea2543
3154 8,c9f0f895fb98ab9159f51fd0297e236d
3155 9,45c48cce2e2d7fbdea1afc51c7c6ad26
3156 10,d3d9446802a44259755d38e6d163e820
3157 SET row_security TO ON;
3158 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
3159 0,cfcd208495d565ef66e7dff9f98764da
3160 1,c4ca4238a0b923820dcc509a6f75849b
3161 2,c81e728d9d4c2f636f067f89cc14862c
3162 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
3163 4,a87ff679a2f3e71d9181a67b7542122c
3164 5,e4da3b7fbbce2345d7772b0674a318d5
3165 6,1679091c5a880faf6fb5e6087eb1b2dc
3166 7,8f14e45fceea167a5a36dedd4bea2543
3167 8,c9f0f895fb98ab9159f51fd0297e236d
3168 9,45c48cce2e2d7fbdea1afc51c7c6ad26
3169 10,d3d9446802a44259755d38e6d163e820
3170 -- Check COPY TO as user with permissions.
3171 SET SESSION AUTHORIZATION regress_rls_bob;
3172 SET row_security TO OFF;
3173 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3174 ERROR: query would be affected by row-level security policy for table "copy_t"
3175 SET row_security TO ON;
3176 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
3177 0,cfcd208495d565ef66e7dff9f98764da
3178 2,c81e728d9d4c2f636f067f89cc14862c
3179 4,a87ff679a2f3e71d9181a67b7542122c
3180 6,1679091c5a880faf6fb5e6087eb1b2dc
3181 8,c9f0f895fb98ab9159f51fd0297e236d
3182 10,d3d9446802a44259755d38e6d163e820
3183 -- Check COPY TO as user with permissions and BYPASSRLS
3184 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3185 SET row_security TO OFF;
3186 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
3187 0,cfcd208495d565ef66e7dff9f98764da
3188 1,c4ca4238a0b923820dcc509a6f75849b
3189 2,c81e728d9d4c2f636f067f89cc14862c
3190 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
3191 4,a87ff679a2f3e71d9181a67b7542122c
3192 5,e4da3b7fbbce2345d7772b0674a318d5
3193 6,1679091c5a880faf6fb5e6087eb1b2dc
3194 7,8f14e45fceea167a5a36dedd4bea2543
3195 8,c9f0f895fb98ab9159f51fd0297e236d
3196 9,45c48cce2e2d7fbdea1afc51c7c6ad26
3197 10,d3d9446802a44259755d38e6d163e820
3198 SET row_security TO ON;
3199 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
3200 0,cfcd208495d565ef66e7dff9f98764da
3201 1,c4ca4238a0b923820dcc509a6f75849b
3202 2,c81e728d9d4c2f636f067f89cc14862c
3203 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
3204 4,a87ff679a2f3e71d9181a67b7542122c
3205 5,e4da3b7fbbce2345d7772b0674a318d5
3206 6,1679091c5a880faf6fb5e6087eb1b2dc
3207 7,8f14e45fceea167a5a36dedd4bea2543
3208 8,c9f0f895fb98ab9159f51fd0297e236d
3209 9,45c48cce2e2d7fbdea1afc51c7c6ad26
3210 10,d3d9446802a44259755d38e6d163e820
3211 -- Check COPY TO as user without permissions. SET row_security TO OFF;
3212 SET SESSION AUTHORIZATION regress_rls_carol;
3213 SET row_security TO OFF;
3214 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3215 ERROR: query would be affected by row-level security policy for table "copy_t"
3216 SET row_security TO ON;
3217 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
3218 ERROR: permission denied for table copy_t
3219 -- Check COPY relation TO; keep it just one row to avoid reordering issues
3220 RESET SESSION AUTHORIZATION;
3221 SET row_security TO ON;
3222 CREATE TABLE copy_rel_to (a integer, b text);
3223 CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0);
3224 ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
3225 GRANT ALL ON copy_rel_to TO regress_rls_bob, regress_rls_exempt_user;
3226 INSERT INTO copy_rel_to VALUES (1, md5('1'));
3227 -- Check COPY TO as Superuser/owner.
3228 RESET SESSION AUTHORIZATION;
3229 SET row_security TO OFF;
3230 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3231 1,c4ca4238a0b923820dcc509a6f75849b
3232 SET row_security TO ON;
3233 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3234 1,c4ca4238a0b923820dcc509a6f75849b
3235 -- Check COPY TO as user with permissions.
3236 SET SESSION AUTHORIZATION regress_rls_bob;
3237 SET row_security TO OFF;
3238 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3239 ERROR: query would be affected by row-level security policy for table "copy_rel_to"
3240 SET row_security TO ON;
3241 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3242 -- Check COPY TO as user with permissions and BYPASSRLS
3243 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3244 SET row_security TO OFF;
3245 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3246 1,c4ca4238a0b923820dcc509a6f75849b
3247 SET row_security TO ON;
3248 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3249 1,c4ca4238a0b923820dcc509a6f75849b
3250 -- Check COPY TO as user without permissions. SET row_security TO OFF;
3251 SET SESSION AUTHORIZATION regress_rls_carol;
3252 SET row_security TO OFF;
3253 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3254 ERROR: permission denied for table copy_rel_to
3255 SET row_security TO ON;
3256 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3257 ERROR: permission denied for table copy_rel_to
3258 -- Check COPY FROM as Superuser/owner.
3259 RESET SESSION AUTHORIZATION;
3260 SET row_security TO OFF;
3261 COPY copy_t FROM STDIN; --ok
3262 SET row_security TO ON;
3263 COPY copy_t FROM STDIN; --ok
3264 -- Check COPY FROM as user with permissions.
3265 SET SESSION AUTHORIZATION regress_rls_bob;
3266 SET row_security TO OFF;
3267 COPY copy_t FROM STDIN; --fail - would be affected by RLS.
3268 ERROR: query would be affected by row-level security policy for table "copy_t"
3269 SET row_security TO ON;
3270 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
3271 ERROR: COPY FROM not supported with row-level security
3272 HINT: Use INSERT statements instead.
3273 -- Check COPY FROM as user with permissions and BYPASSRLS
3274 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3275 SET row_security TO ON;
3276 COPY copy_t FROM STDIN; --ok
3277 -- Check COPY FROM as user without permissions.
3278 SET SESSION AUTHORIZATION regress_rls_carol;
3279 SET row_security TO OFF;
3280 COPY copy_t FROM STDIN; --fail - permission denied.
3281 ERROR: permission denied for table copy_t
3282 SET row_security TO ON;
3283 COPY copy_t FROM STDIN; --fail - permission denied.
3284 ERROR: permission denied for table copy_t
3285 RESET SESSION AUTHORIZATION;
3286 DROP TABLE copy_t;
3287 DROP TABLE copy_rel_to CASCADE;
3288 -- Check WHERE CURRENT OF
3289 SET SESSION AUTHORIZATION regress_rls_alice;
3290 CREATE TABLE current_check (currentid int, payload text, rlsuser text);
3291 GRANT ALL ON current_check TO PUBLIC;
3292 INSERT INTO current_check VALUES
3293 (1, 'abc', 'regress_rls_bob'),
3294 (2, 'bcd', 'regress_rls_bob'),
3295 (3, 'cde', 'regress_rls_bob'),
3296 (4, 'def', 'regress_rls_bob');
3297 CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
3298 CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
3299 CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user);
3300 ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
3301 SET SESSION AUTHORIZATION regress_rls_bob;
3302 -- Can SELECT even rows
3303 SELECT * FROM current_check;
3304 currentid | payload | rlsuser
3305 -----------+---------+-----------------
3306 2 | bcd | regress_rls_bob
3307 4 | def | regress_rls_bob
3308 (2 rows)
3310 -- Cannot UPDATE row 2
3311 UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *;
3312 currentid | payload | rlsuser
3313 -----------+---------+---------
3314 (0 rows)
3316 BEGIN;
3317 DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check;
3318 -- Returns rows that can be seen according to SELECT policy, like plain SELECT
3319 -- above (even rows)
3320 FETCH ABSOLUTE 1 FROM current_check_cursor;
3321 currentid | payload | rlsuser
3322 -----------+---------+-----------------
3323 2 | bcd | regress_rls_bob
3324 (1 row)
3326 -- Still cannot UPDATE row 2 through cursor
3327 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
3328 currentid | payload | rlsuser
3329 -----------+---------+---------
3330 (0 rows)
3332 -- Can update row 4 through cursor, which is the next visible row
3333 FETCH RELATIVE 1 FROM current_check_cursor;
3334 currentid | payload | rlsuser
3335 -----------+---------+-----------------
3336 4 | def | regress_rls_bob
3337 (1 row)
3339 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
3340 currentid | payload | rlsuser
3341 -----------+---------+-----------------
3342 4 | def_new | regress_rls_bob
3343 (1 row)
3345 SELECT * FROM current_check;
3346 currentid | payload | rlsuser
3347 -----------+---------+-----------------
3348 2 | bcd | regress_rls_bob
3349 4 | def_new | regress_rls_bob
3350 (2 rows)
3352 -- Plan should be a subquery TID scan
3353 EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor;
3354 QUERY PLAN
3355 -------------------------------------------------------------
3356 Update on current_check
3357 -> Tid Scan on current_check
3358 TID Cond: CURRENT OF current_check_cursor
3359 Filter: ((currentid = 4) AND ((currentid % 2) = 0))
3360 (4 rows)
3362 -- Similarly can only delete row 4
3363 FETCH ABSOLUTE 1 FROM current_check_cursor;
3364 currentid | payload | rlsuser
3365 -----------+---------+-----------------
3366 2 | bcd | regress_rls_bob
3367 (1 row)
3369 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
3370 currentid | payload | rlsuser
3371 -----------+---------+---------
3372 (0 rows)
3374 FETCH RELATIVE 1 FROM current_check_cursor;
3375 currentid | payload | rlsuser
3376 -----------+---------+-----------------
3377 4 | def | regress_rls_bob
3378 (1 row)
3380 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
3381 currentid | payload | rlsuser
3382 -----------+---------+-----------------
3383 4 | def_new | regress_rls_bob
3384 (1 row)
3386 SELECT * FROM current_check;
3387 currentid | payload | rlsuser
3388 -----------+---------+-----------------
3389 2 | bcd | regress_rls_bob
3390 (1 row)
3392 COMMIT;
3393 --
3394 -- check pg_stats view filtering
3395 --
3396 SET row_security TO ON;
3397 SET SESSION AUTHORIZATION regress_rls_alice;
3398 ANALYZE current_check;
3399 -- Stats visible
3400 SELECT row_security_active('current_check');
3401 row_security_active
3402 ---------------------
3403 f
3404 (1 row)
3406 SELECT attname, most_common_vals FROM pg_stats
3407 WHERE tablename = 'current_check'
3408 ORDER BY 1;
3409 attname | most_common_vals
3410 -----------+-------------------
3411 currentid |
3412 payload |
3413 rlsuser | {regress_rls_bob}
3414 (3 rows)
3416 SET SESSION AUTHORIZATION regress_rls_bob;
3417 -- Stats not visible
3418 SELECT row_security_active('current_check');
3419 row_security_active
3420 ---------------------
3421 t
3422 (1 row)
3424 SELECT attname, most_common_vals FROM pg_stats
3425 WHERE tablename = 'current_check'
3426 ORDER BY 1;
3427 attname | most_common_vals
3428 ---------+------------------
3429 (0 rows)
3431 --
3432 -- Collation support
3433 --
3434 BEGIN;
3435 CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
3436 CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
3437 ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
3438 GRANT SELECT ON coll_t TO regress_rls_alice;
3439 SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
3440 inputcollid
3441 ------------------
3442 inputcollid 950
3443 (1 row)
3445 SET SESSION AUTHORIZATION regress_rls_alice;
3446 SELECT * FROM coll_t;
3447 c
3448 -----
3449 bar
3450 (1 row)
3452 ROLLBACK;
3453 --
3454 -- Shared Object Dependencies
3455 --
3456 RESET SESSION AUTHORIZATION;
3457 BEGIN;
3458 CREATE ROLE regress_rls_eve;
3459 CREATE ROLE regress_rls_frank;
3460 CREATE TABLE tbl1 (c) AS VALUES ('bar'::text);
3461 GRANT SELECT ON TABLE tbl1 TO regress_rls_eve;
3462 CREATE POLICY P ON tbl1 TO regress_rls_eve, regress_rls_frank USING (true);
3463 SELECT refclassid::regclass, deptype
3464 FROM pg_depend
3465 WHERE classid = 'pg_policy'::regclass
3466 AND refobjid = 'tbl1'::regclass;
3467 refclassid | deptype
3468 ------------+---------
3469 pg_class | a
3470 (1 row)
3472 SELECT refclassid::regclass, deptype
3473 FROM pg_shdepend
3474 WHERE classid = 'pg_policy'::regclass
3475 AND refobjid IN ('regress_rls_eve'::regrole, 'regress_rls_frank'::regrole);
3476 refclassid | deptype
3477 ------------+---------
3478 pg_authid | r
3479 pg_authid | r
3480 (2 rows)
3482 SAVEPOINT q;
3483 DROP ROLE regress_rls_eve; --fails due to dependency on POLICY p
3484 ERROR: role "regress_rls_eve" cannot be dropped because some objects depend on it
3485 DETAIL: privileges for table tbl1
3486 target of policy p on table tbl1
3487 ROLLBACK TO q;
3488 ALTER POLICY p ON tbl1 TO regress_rls_frank USING (true);
3489 SAVEPOINT q;
3490 DROP ROLE regress_rls_eve; --fails due to dependency on GRANT SELECT
3491 ERROR: role "regress_rls_eve" cannot be dropped because some objects depend on it
3492 DETAIL: privileges for table tbl1
3493 ROLLBACK TO q;
3494 REVOKE ALL ON TABLE tbl1 FROM regress_rls_eve;
3495 SAVEPOINT q;
3496 DROP ROLE regress_rls_eve; --succeeds
3497 ROLLBACK TO q;
3498 SAVEPOINT q;
3499 DROP ROLE regress_rls_frank; --fails due to dependency on POLICY p
3500 ERROR: role "regress_rls_frank" cannot be dropped because some objects depend on it
3501 DETAIL: target of policy p on table tbl1
3502 ROLLBACK TO q;
3503 DROP POLICY p ON tbl1;
3504 SAVEPOINT q;
3505 DROP ROLE regress_rls_frank; -- succeeds
3506 ROLLBACK TO q;
3507 ROLLBACK; -- cleanup
3508 --
3509 -- Converting table to view
3510 --
3511 BEGIN;
3512 CREATE TABLE t (c int);
3513 CREATE POLICY p ON t USING (c % 2 = 1);
3514 ALTER TABLE t ENABLE ROW LEVEL SECURITY;
3515 SAVEPOINT q;
3516 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
3517 SELECT * FROM generate_series(1,5) t0(c); -- fails due to row-level security enabled
3518 ERROR: could not convert table "t" to a view because it has row security enabled
3519 ROLLBACK TO q;
3520 ALTER TABLE t DISABLE ROW LEVEL SECURITY;
3521 SAVEPOINT q;
3522 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
3523 SELECT * FROM generate_series(1,5) t0(c); -- fails due to policy p on t
3524 ERROR: could not convert table "t" to a view because it has row security policies
3525 ROLLBACK TO q;
3526 DROP POLICY p ON t;
3527 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
3528 SELECT * FROM generate_series(1,5) t0(c); -- succeeds
3529 ROLLBACK;
3530 --
3531 -- Policy expression handling
3532 --
3533 BEGIN;
3534 CREATE TABLE t (c) AS VALUES ('bar'::text);
3535 CREATE POLICY p ON t USING (max(c)); -- fails: aggregate functions are not allowed in policy expressions
3536 ERROR: aggregate functions are not allowed in policy expressions
3537 ROLLBACK;
3538 --
3539 -- Non-target relations are only subject to SELECT policies
3540 --
3541 SET SESSION AUTHORIZATION regress_rls_alice;
3542 CREATE TABLE r1 (a int);
3543 CREATE TABLE r2 (a int);
3544 INSERT INTO r1 VALUES (10), (20);
3545 INSERT INTO r2 VALUES (10), (20);
3546 GRANT ALL ON r1, r2 TO regress_rls_bob;
3547 CREATE POLICY p1 ON r1 USING (true);
3548 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3549 CREATE POLICY p1 ON r2 FOR SELECT USING (true);
3550 CREATE POLICY p2 ON r2 FOR INSERT WITH CHECK (false);
3551 CREATE POLICY p3 ON r2 FOR UPDATE USING (false);
3552 CREATE POLICY p4 ON r2 FOR DELETE USING (false);
3553 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
3554 SET SESSION AUTHORIZATION regress_rls_bob;
3555 SELECT * FROM r1;
3556 a
3557 ----
3558 10
3559 20
3560 (2 rows)
3562 SELECT * FROM r2;
3563 a
3564 ----
3565 10
3566 20
3567 (2 rows)
3569 -- r2 is read-only
3570 INSERT INTO r2 VALUES (2); -- Not allowed
3571 ERROR: new row violates row-level security policy for table "r2"
3572 UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing
3573 a
3574 ---
3575 (0 rows)
3577 DELETE FROM r2 RETURNING *; -- Deletes nothing
3578 a
3579 ---
3580 (0 rows)
3582 -- r2 can be used as a non-target relation in DML
3583 INSERT INTO r1 SELECT a + 1 FROM r2 RETURNING *; -- OK
3584 a
3585 ----
3586 11
3587 21
3588 (2 rows)
3590 UPDATE r1 SET a = r2.a + 2 FROM r2 WHERE r1.a = r2.a RETURNING *; -- OK
3591 a | a
3592 ----+----
3593 12 | 10
3594 22 | 20
3595 (2 rows)
3597 DELETE FROM r1 USING r2 WHERE r1.a = r2.a + 2 RETURNING *; -- OK
3598 a | a
3599 ----+----
3600 12 | 10
3601 22 | 20
3602 (2 rows)
3604 SELECT * FROM r1;
3605 a
3606 ----
3607 11
3608 21
3609 (2 rows)
3611 SELECT * FROM r2;
3612 a
3613 ----
3614 10
3615 20
3616 (2 rows)
3618 SET SESSION AUTHORIZATION regress_rls_alice;
3619 DROP TABLE r1;
3620 DROP TABLE r2;
3621 --
3622 -- FORCE ROW LEVEL SECURITY applies RLS to owners too
3623 --
3624 SET SESSION AUTHORIZATION regress_rls_alice;
3625 SET row_security = on;
3626 CREATE TABLE r1 (a int);
3627 INSERT INTO r1 VALUES (10), (20);
3628 CREATE POLICY p1 ON r1 USING (false);
3629 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3630 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3631 -- No error, but no rows
3632 TABLE r1;
3633 a
3634 ---
3635 (0 rows)
3637 -- RLS error
3638 INSERT INTO r1 VALUES (1);
3639 ERROR: new row violates row-level security policy for table "r1"
3640 -- No error (unable to see any rows to update)
3641 UPDATE r1 SET a = 1;
3642 TABLE r1;
3643 a
3644 ---
3645 (0 rows)
3647 -- No error (unable to see any rows to delete)
3648 DELETE FROM r1;
3649 TABLE r1;
3650 a
3651 ---
3652 (0 rows)
3654 SET row_security = off;
3655 -- these all fail, would be affected by RLS
3656 TABLE r1;
3657 ERROR: query would be affected by row-level security policy for table "r1"
3658 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
3659 UPDATE r1 SET a = 1;
3660 ERROR: query would be affected by row-level security policy for table "r1"
3661 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
3662 DELETE FROM r1;
3663 ERROR: query would be affected by row-level security policy for table "r1"
3664 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
3665 DROP TABLE r1;
3666 --
3667 -- FORCE ROW LEVEL SECURITY does not break RI
3668 --
3669 SET SESSION AUTHORIZATION regress_rls_alice;
3670 SET row_security = on;
3671 CREATE TABLE r1 (a int PRIMARY KEY);
3672 CREATE TABLE r2 (a int REFERENCES r1);
3673 INSERT INTO r1 VALUES (10), (20);
3674 INSERT INTO r2 VALUES (10), (20);
3675 -- Create policies on r2 which prevent the
3676 -- owner from seeing any rows, but RI should
3677 -- still see them.
3678 CREATE POLICY p1 ON r2 USING (false);
3679 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
3680 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
3681 -- Errors due to rows in r2
3682 DELETE FROM r1;
3683 ERROR: update or delete on table "r1" violates foreign key constraint "r2_a_fkey" on table "r2"
3684 DETAIL: Key (a)=(10) is still referenced from table "r2".
3685 -- Reset r2 to no-RLS
3686 DROP POLICY p1 ON r2;
3687 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
3688 ALTER TABLE r2 DISABLE ROW LEVEL SECURITY;
3689 -- clean out r2 for INSERT test below
3690 DELETE FROM r2;
3691 -- Change r1 to not allow rows to be seen
3692 CREATE POLICY p1 ON r1 USING (false);
3693 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3694 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3695 -- No rows seen
3696 TABLE r1;
3697 a
3698 ---
3699 (0 rows)
3701 -- No error, RI still sees that row exists in r1
3702 INSERT INTO r2 VALUES (10);
3703 DROP TABLE r2;
3704 DROP TABLE r1;
3705 -- Ensure cascaded DELETE works
3706 CREATE TABLE r1 (a int PRIMARY KEY);
3707 CREATE TABLE r2 (a int REFERENCES r1 ON DELETE CASCADE);
3708 INSERT INTO r1 VALUES (10), (20);
3709 INSERT INTO r2 VALUES (10), (20);
3710 -- Create policies on r2 which prevent the
3711 -- owner from seeing any rows, but RI should
3712 -- still see them.
3713 CREATE POLICY p1 ON r2 USING (false);
3714 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
3715 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
3716 -- Deletes all records from both
3717 DELETE FROM r1;
3718 -- Remove FORCE from r2
3719 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
3720 -- As owner, we now bypass RLS
3721 -- verify no rows in r2 now
3722 TABLE r2;
3723 a
3724 ---
3725 (0 rows)
3727 DROP TABLE r2;
3728 DROP TABLE r1;
3729 -- Ensure cascaded UPDATE works
3730 CREATE TABLE r1 (a int PRIMARY KEY);
3731 CREATE TABLE r2 (a int REFERENCES r1 ON UPDATE CASCADE);
3732 INSERT INTO r1 VALUES (10), (20);
3733 INSERT INTO r2 VALUES (10), (20);
3734 -- Create policies on r2 which prevent the
3735 -- owner from seeing any rows, but RI should
3736 -- still see them.
3737 CREATE POLICY p1 ON r2 USING (false);
3738 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
3739 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
3740 -- Updates records in both
3741 UPDATE r1 SET a = a+5;
3742 -- Remove FORCE from r2
3743 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
3744 -- As owner, we now bypass RLS
3745 -- verify records in r2 updated
3746 TABLE r2;
3747 a
3748 ----
3749 15
3750 25
3751 (2 rows)
3753 DROP TABLE r2;
3754 DROP TABLE r1;
3755 --
3756 -- Test INSERT+RETURNING applies SELECT policies as
3757 -- WithCheckOptions (meaning an error is thrown)
3758 --
3759 SET SESSION AUTHORIZATION regress_rls_alice;
3760 SET row_security = on;
3761 CREATE TABLE r1 (a int);
3762 CREATE POLICY p1 ON r1 FOR SELECT USING (false);
3763 CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true);
3764 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3765 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3766 -- Works fine
3767 INSERT INTO r1 VALUES (10), (20);
3768 -- No error, but no rows
3769 TABLE r1;
3770 a
3771 ---
3772 (0 rows)
3774 SET row_security = off;
3775 -- fail, would be affected by RLS
3776 TABLE r1;
3777 ERROR: query would be affected by row-level security policy for table "r1"
3778 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
3779 SET row_security = on;
3780 -- Error
3781 INSERT INTO r1 VALUES (10), (20) RETURNING *;
3782 ERROR: new row violates row-level security policy for table "r1"
3783 DROP TABLE r1;
3784 --
3785 -- Test UPDATE+RETURNING applies SELECT policies as
3786 -- WithCheckOptions (meaning an error is thrown)
3787 --
3788 SET SESSION AUTHORIZATION regress_rls_alice;
3789 SET row_security = on;
3790 CREATE TABLE r1 (a int PRIMARY KEY);
3791 CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
3792 CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true);
3793 CREATE POLICY p3 ON r1 FOR INSERT WITH CHECK (true);
3794 INSERT INTO r1 VALUES (10);
3795 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3796 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3797 -- Works fine
3798 UPDATE r1 SET a = 30;
3799 -- Show updated rows
3800 ALTER TABLE r1 NO FORCE ROW LEVEL SECURITY;
3801 TABLE r1;
3802 a
3803 ----
3804 30
3805 (1 row)
3807 -- reset value in r1 for test with RETURNING
3808 UPDATE r1 SET a = 10;
3809 -- Verify row reset
3810 TABLE r1;
3811 a
3812 ----
3813 10
3814 (1 row)
3816 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3817 -- Error
3818 UPDATE r1 SET a = 30 RETURNING *;
3819 ERROR: new row violates row-level security policy for table "r1"
3820 -- UPDATE path of INSERT ... ON CONFLICT DO UPDATE should also error out
3821 INSERT INTO r1 VALUES (10)
3822 ON CONFLICT (a) DO UPDATE SET a = 30 RETURNING *;
3823 ERROR: new row violates row-level security policy for table "r1"
3824 -- Should still error out without RETURNING (use of arbiter always requires
3825 -- SELECT permissions)
3826 INSERT INTO r1 VALUES (10)
3827 ON CONFLICT (a) DO UPDATE SET a = 30;
3828 ERROR: new row violates row-level security policy for table "r1"
3829 INSERT INTO r1 VALUES (10)
3830 ON CONFLICT ON CONSTRAINT r1_pkey DO UPDATE SET a = 30;
3831 ERROR: new row violates row-level security policy for table "r1"
3832 DROP TABLE r1;
3833 -- Check dependency handling
3834 RESET SESSION AUTHORIZATION;
3835 CREATE TABLE dep1 (c1 int);
3836 CREATE TABLE dep2 (c1 int);
3837 CREATE POLICY dep_p1 ON dep1 TO regress_rls_bob USING (c1 > (select max(dep2.c1) from dep2));
3838 ALTER POLICY dep_p1 ON dep1 TO regress_rls_bob,regress_rls_carol;
3839 -- Should return one
3840 SELECT count(*) = 1 FROM pg_depend
3841 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
3842 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
3843 ?column?
3844 ----------
3845 t
3846 (1 row)
3848 ALTER POLICY dep_p1 ON dep1 USING (true);
3849 -- Should return one
3850 SELECT count(*) = 1 FROM pg_shdepend
3851 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
3852 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_bob');
3853 ?column?
3854 ----------
3855 t
3856 (1 row)
3858 -- Should return one
3859 SELECT count(*) = 1 FROM pg_shdepend
3860 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
3861 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_carol');
3862 ?column?
3863 ----------
3864 t
3865 (1 row)
3867 -- Should return zero
3868 SELECT count(*) = 0 FROM pg_depend
3869 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
3870 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
3871 ?column?
3872 ----------
3873 t
3874 (1 row)
3876 -- DROP OWNED BY testing
3877 RESET SESSION AUTHORIZATION;
3878 CREATE ROLE regress_rls_dob_role1;
3879 CREATE ROLE regress_rls_dob_role2;
3880 CREATE TABLE dob_t1 (c1 int);
3881 CREATE TABLE dob_t2 (c1 int) PARTITION BY RANGE (c1);
3882 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1 USING (true);
3883 DROP OWNED BY regress_rls_dob_role1;
3884 DROP POLICY p1 ON dob_t1; -- should fail, already gone
3885 ERROR: policy "p1" for table "dob_t1" does not exist
3886 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
3887 DROP OWNED BY regress_rls_dob_role1;
3888 DROP POLICY p1 ON dob_t1; -- should succeed
3889 -- same cases with duplicate polroles entries
3890 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role1 USING (true);
3891 DROP OWNED BY regress_rls_dob_role1;
3892 DROP POLICY p1 ON dob_t1; -- should fail, already gone
3893 ERROR: policy "p1" for table "dob_t1" does not exist
3894 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
3895 DROP OWNED BY regress_rls_dob_role1;
3896 DROP POLICY p1 ON dob_t1; -- should succeed
3897 -- partitioned target
3898 CREATE POLICY p1 ON dob_t2 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
3899 DROP OWNED BY regress_rls_dob_role1;
3900 DROP POLICY p1 ON dob_t2; -- should succeed
3901 DROP USER regress_rls_dob_role1;
3902 DROP USER regress_rls_dob_role2;
3903 -- Bug #15708: view + table with RLS should check policies as view owner
3904 CREATE TABLE ref_tbl (a int);
3905 INSERT INTO ref_tbl VALUES (1);
3906 CREATE TABLE rls_tbl (a int);
3907 INSERT INTO rls_tbl VALUES (10);
3908 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
3909 CREATE POLICY p1 ON rls_tbl USING (EXISTS (SELECT 1 FROM ref_tbl));
3910 GRANT SELECT ON ref_tbl TO regress_rls_bob;
3911 GRANT SELECT ON rls_tbl TO regress_rls_bob;
3912 CREATE VIEW rls_view AS SELECT * FROM rls_tbl;
3913 ALTER VIEW rls_view OWNER TO regress_rls_bob;
3914 GRANT SELECT ON rls_view TO regress_rls_alice;
3915 SET SESSION AUTHORIZATION regress_rls_alice;
3916 SELECT * FROM ref_tbl; -- Permission denied
3917 ERROR: permission denied for table ref_tbl
3918 SELECT * FROM rls_tbl; -- Permission denied
3919 ERROR: permission denied for table rls_tbl
3920 SELECT * FROM rls_view; -- OK
3921 a
3922 ----
3923 10
3924 (1 row)
3926 RESET SESSION AUTHORIZATION;
3927 DROP VIEW rls_view;
3928 DROP TABLE rls_tbl;
3929 DROP TABLE ref_tbl;
3930 -- Leaky operator test
3931 CREATE TABLE rls_tbl (a int);
3932 INSERT INTO rls_tbl SELECT x/10 FROM generate_series(1, 100) x;
3933 ANALYZE rls_tbl;
3934 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
3935 GRANT SELECT ON rls_tbl TO regress_rls_alice;
3936 SET SESSION AUTHORIZATION regress_rls_alice;
3937 CREATE FUNCTION op_leak(int, int) RETURNS bool
3938 AS 'BEGIN RAISE NOTICE ''op_leak => %, %'', $1, $2; RETURN $1 < $2; END'
3939 LANGUAGE plpgsql;
3940 CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int,
3941 restrict = scalarltsel);
3942 SELECT * FROM rls_tbl WHERE a <<< 1000;
3943 a
3944 ---
3945 (0 rows)
3947 DROP OPERATOR <<< (int, int);
3948 DROP FUNCTION op_leak(int, int);
3949 RESET SESSION AUTHORIZATION;
3950 DROP TABLE rls_tbl;
3951 -- Bug #16006: whole-row Vars in a policy don't play nice with sub-selects
3952 SET SESSION AUTHORIZATION regress_rls_alice;
3953 CREATE TABLE rls_tbl (a int, b int, c int);
3954 CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1));
3955 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
3956 ALTER TABLE rls_tbl FORCE ROW LEVEL SECURITY;
3957 INSERT INTO rls_tbl SELECT 10, 20, 30;
3958 EXPLAIN (VERBOSE, COSTS OFF)
3959 INSERT INTO rls_tbl
3960 SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss;
3961 QUERY PLAN
3962 --------------------------------------------------------------------
3963 Insert on regress_rls_schema.rls_tbl
3964 -> Subquery Scan on ss
3965 Output: ss.b, ss.c, NULL::integer
3966 -> Sort
3967 Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a
3968 Sort Key: rls_tbl_1.a
3969 -> Seq Scan on regress_rls_schema.rls_tbl rls_tbl_1
3970 Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a
3971 Filter: (rls_tbl_1.* >= '(1,1,1)'::record)
3972 (9 rows)
3974 INSERT INTO rls_tbl
3975 SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss;
3976 SELECT * FROM rls_tbl;
3977 a | b | c
3978 ----+----+----
3979 10 | 20 | 30
3980 20 | 30 |
3981 (2 rows)
3983 DROP TABLE rls_tbl;
3984 RESET SESSION AUTHORIZATION;
3985 --
3986 -- Clean up objects
3987 --
3988 RESET SESSION AUTHORIZATION;
3989 DROP SCHEMA regress_rls_schema CASCADE;
3990 NOTICE: drop cascades to 29 other objects
3991 DETAIL: drop cascades to function f_leak(text)
3992 drop cascades to table uaccount
3993 drop cascades to table category
3994 drop cascades to table document
3995 drop cascades to table part_document
3996 drop cascades to table dependent
3997 drop cascades to table rec1
3998 drop cascades to table rec2
3999 drop cascades to view rec1v
4000 drop cascades to view rec2v
4001 drop cascades to table s1
4002 drop cascades to table s2
4003 drop cascades to view v2
4004 drop cascades to table b1
4005 drop cascades to view bv1
4006 drop cascades to table z1
4007 drop cascades to table z2
4008 drop cascades to table x1
4009 drop cascades to table y1
4010 drop cascades to table y2
4011 drop cascades to table t1
4012 drop cascades to table t2
4013 drop cascades to table t3
4014 drop cascades to table t4
4015 drop cascades to table current_check
4016 drop cascades to table dep1
4017 drop cascades to table dep2
4018 drop cascades to table dob_t1
4019 drop cascades to table dob_t2
4020 DROP USER regress_rls_alice;
4021 DROP USER regress_rls_bob;
4022 DROP USER regress_rls_carol;
4023 DROP USER regress_rls_dave;
4024 DROP USER regress_rls_exempt_user;
4025 DROP ROLE regress_rls_group1;
4026 DROP ROLE regress_rls_group2;
4027 -- Arrange to have a few policies left over, for testing
4028 -- pg_dump/pg_restore
4029 CREATE SCHEMA regress_rls_schema;
4030 CREATE TABLE rls_tbl (c1 int);
4031 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
4032 CREATE POLICY p1 ON rls_tbl USING (c1 > 5);
4033 CREATE POLICY p2 ON rls_tbl FOR SELECT USING (c1 <= 3);
4034 CREATE POLICY p3 ON rls_tbl FOR UPDATE USING (c1 <= 3) WITH CHECK (c1 > 5);
4035 CREATE POLICY p4 ON rls_tbl FOR DELETE USING (c1 <= 3);
4036 CREATE TABLE rls_tbl_force (c1 int);
4037 ALTER TABLE rls_tbl_force ENABLE ROW LEVEL SECURITY;
4038 ALTER TABLE rls_tbl_force FORCE ROW LEVEL SECURITY;
4039 CREATE POLICY p1 ON rls_tbl_force USING (c1 = 5) WITH CHECK (c1 < 5);
4040 CREATE POLICY p2 ON rls_tbl_force FOR SELECT USING (c1 = 8);
4041 CREATE POLICY p3 ON rls_tbl_force FOR UPDATE USING (c1 = 8) WITH CHECK (c1 >= 5);
4042 CREATE POLICY p4 ON rls_tbl_force FOR DELETE USING (c1 = 8);