git.postgresql.org Git - postgresql.git/blob - src/test/regress/sql/rowsecurity.sql <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c>
> 58 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l58> > CREATE TABLE category ( > 59 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l59> > cid int primary key, > 60 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l60> > cname text > 61 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l61> > ); > 62 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l62> > GRANT ALL ON category TO public; > 63 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l63> > INSERT INTO category VALUES > 64 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l64> > (11, 'novel'), > 65 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l65> > (22, 'science fiction'), > 66 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l66> > (33, 'technology'), > 67 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l67> > (44, 'manga'); > 68 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l68> > 69 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l69> > CREATE TABLE document ( > 70 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l70> > did int primary key, > 71 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l71> > cid int references category(cid), > 72 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l72> > dlevel int not null, > 73 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l73> > dauthor name, > 74 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l74> > dtitle text > 75 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l75> > ); > 76 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l76> > GRANT ALL ON document TO public; > 77 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l77> > INSERT INTO document VALUES > 78 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l78> > ( 1, 11, 1, 'regress_rls_bob', 'my first novel'), > 79 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l79> > ( 2, 11, 2, 'regress_rls_bob', 'my second novel'), > 80 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l80> > ( 3, 22, 2, 'regress_rls_bob', 'my science fiction'), > 81 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l81> > ( 4, 44, 1, 'regress_rls_bob', 'my first manga'), > 82 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l82> > ( 5, 44, 2, 'regress_rls_bob', 'my second manga'), > 83 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l83> > ( 6, 22, 1, 'regress_rls_carol', 'great science fiction'), > 84 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l84> > ( 7, 33, 2, 'regress_rls_carol', 'great technology book'), > 85 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l85> > ( 8, 44, 1, 'regress_rls_carol', 'great manga'), > 86 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l86> > ( 9, 22, 1, 'regress_rls_dave', 'awesome science fiction'), > 87 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l87> > (10, 33, 2, 'regress_rls_dave', 'awesome technology book'); > 88 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l88> > 89 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l89> > ALTER TABLE document ENABLE ROW LEVEL SECURITY; 707 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l707> > CREATE POLICY p1 ON document FOR SELECT USING (true); > 708 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l708> > CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user); > 709 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l709> > CREATE POLICY p3 ON document FOR UPDATE > 710 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l710> > USING (cid = (SELECT cid from category WHERE cname = 'novel')) > 711 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l711> > WITH CHECK (dauthor = current_user); > 712 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l712> > 713 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l713> > SET SESSION AUTHORIZATION regress_rls_bob; > 714 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l714> > 715 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l715> > -- Exists... > 716 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l716> > SELECT * FROM document WHERE did = 2; > 717 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l717> > 718 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l718> > -- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since > 719 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l719> > -- alternative UPDATE path happens to be taken): > 720 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l720> > INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = > 'novel'), 1, 'regress_rls_carol', 'my first novel') > 721 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l721> > ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = > EXCLUDED.dauthor; > 722 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l722> > 723 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l723> > -- Violates USING qual for UPDATE policy p3. > 724 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l724> > -- > 725 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l725> > -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be > 726 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l726> > -- updated is not a "novel"/cid 11 (row is not leaked, even though we have > 727 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l727> > -- SELECT privileges sufficient to see the row in this instance): > 728 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l728> > INSERT INTO document VALUES (33, 22, 1, 'regress_rls_bob', 'okay science > fiction'); -- preparation for next statement > 729 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l729> > INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = > 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi') -- takes > UPDATE path > 730 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l730> > ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle; > 731 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l731> > -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs > 732 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l732> > -- not violated): > 733 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l733> > INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = > 'novel'), 1, 'regress_rls_bob', 'my first novel') > 734 > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l734> > ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; SELECT cid from category WHERE cname = 'novel' == *11* POLICY "p3" FOR UPDATE > USING ((cid = ( SELECT category.cid > FROM category > WHERE (category.cname = 'novel'::text)))) > WITH CHECK ((dauthor = CURRENT_USER)) row level security p3, if I understand correctly: the *dauthor *shall be *currernt_user* and updated cid value shall not be *11* Line 729 to 730 fails. That makes sense to me. but I don't get the line *733 to 734. * Also in the comment section, what does `quals + WCOs` mean?
