row level security on conflict do update

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?