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?

Reply via email to