diff options
Diffstat (limited to 'src/test/regress/expected/rowsecurity.out')
| -rw-r--r-- | src/test/regress/expected/rowsecurity.out | 58 |
1 files changed, 45 insertions, 13 deletions
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 4e549766184..97ca9bf72c5 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -2121,10 +2121,10 @@ ALTER TABLE document ADD COLUMN dnotes text DEFAULT ''; CREATE POLICY p1 ON document FOR SELECT USING (true); -- one may insert documents only authored by them CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user); --- one may only update documents in 'novel' category +-- one may only update documents in 'novel' category and new dlevel must be > 0 CREATE POLICY p3 ON document FOR UPDATE USING (cid = (SELECT cid from category WHERE cname = 'novel')) - WITH CHECK (dauthor = current_user); + WITH CHECK (dlevel > 0); -- one may only delete documents in 'manga' category CREATE POLICY p4 ON document FOR DELETE USING (cid = (SELECT cid from category WHERE cname = 'manga')); @@ -2148,12 +2148,12 @@ SELECT * FROM document; (14 rows) SET SESSION AUTHORIZATION regress_rls_bob; --- Fails, since update violates WITH CHECK qual on dauthor +-- Fails, since update violates WITH CHECK qual on dlevel MERGE INTO document d USING (SELECT 1 as sdid) s ON did = s.sdid WHEN MATCHED THEN - UPDATE SET dnotes = dnotes || ' notes added by merge1 ', dauthor = 'regress_rls_alice'; + UPDATE SET dnotes = dnotes || ' notes added by merge1 ', dlevel = 0; ERROR: new row violates row-level security policy for table "document" -- Should be OK since USING and WITH CHECK quals pass MERGE INTO document d @@ -2161,12 +2161,12 @@ USING (SELECT 1 as sdid) s ON did = s.sdid WHEN MATCHED THEN UPDATE SET dnotes = dnotes || ' notes added by merge2 '; --- Even when dauthor is updated explicitly, but to the existing value +-- Even when dlevel is updated explicitly, but to the existing value MERGE INTO document d USING (SELECT 1 as sdid) s ON did = s.sdid WHEN MATCHED THEN - UPDATE SET dnotes = dnotes || ' notes added by merge3 ', dauthor = 'regress_rls_bob'; + UPDATE SET dnotes = dnotes || ' notes added by merge3 ', dlevel = 1; -- There is a MATCH for did = 3, but UPDATE's USING qual does not allow -- updating an item in category 'science fiction' MERGE INTO document d @@ -2205,6 +2205,14 @@ WHEN MATCHED AND dnotes <> '' THEN WHEN MATCHED THEN DELETE; ERROR: target row violates row-level security policy (USING expression) for table "document" +-- OK if DELETE is replaced with DO NOTHING +MERGE INTO document d +USING (SELECT 4 as sdid) s +ON did = s.sdid +WHEN MATCHED AND dnotes <> '' THEN + UPDATE SET dnotes = dnotes || ' notes added by merge ' +WHEN MATCHED THEN + DO NOTHING; SELECT * FROM document WHERE did = 4; did | cid | dlevel | dauthor | dtitle | dnotes -----+-----+--------+-----------------+----------------+-------- @@ -2253,30 +2261,53 @@ WHEN MATCHED THEN WHEN NOT MATCHED THEN INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel'); -- drop and create a new SELECT policy which prevents us from reading --- any document except with category 'magna' +-- any document except with category 'novel' RESET SESSION AUTHORIZATION; DROP POLICY p1 ON document; CREATE POLICY p1 ON document FOR SELECT - USING (cid = (SELECT cid from category WHERE cname = 'manga')); + USING (cid = (SELECT cid from category WHERE cname = 'novel')); SET SESSION AUTHORIZATION regress_rls_bob; -- MERGE can no longer see the matching row and hence attempts the -- NOT MATCHED action, which results in unique key violation MERGE INTO document d -USING (SELECT 1 as sdid) s +USING (SELECT 7 as sdid) s ON did = s.sdid WHEN MATCHED THEN UPDATE SET dnotes = dnotes || ' notes added by merge5 ' WHEN NOT MATCHED THEN INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel'); ERROR: duplicate key value violates unique constraint "document_pkey" +-- UPDATE action fails if new row is not visible +MERGE INTO document d +USING (SELECT 1 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge6 ', + cid = (SELECT cid from category WHERE cname = 'technology'); +ERROR: new row violates row-level security policy for table "document" +-- but OK if new row is visible +MERGE INTO document d +USING (SELECT 1 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge7 ', + cid = (SELECT cid from category WHERE cname = 'novel'); +-- OK to insert a new row that is not visible +MERGE INTO document d +USING (SELECT 13 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge8 ' +WHEN NOT MATCHED THEN + INSERT VALUES (13, 44, 1, 'regress_rls_bob', 'new manga'); RESET SESSION AUTHORIZATION; -- drop the restrictive SELECT policy so that we can look at the -- final state of the table DROP POLICY p1 ON document; -- Just check everything went per plan SELECT * FROM document; - did | cid | dlevel | dauthor | dtitle | dnotes ------+-----+--------+-------------------+----------------------------------+----------------------------------------------------------------------- + did | cid | dlevel | dauthor | dtitle | dnotes +-----+-----+--------+-------------------+----------------------------------+---------------------------------------------------------------------------------------------- 3 | 22 | 2 | regress_rls_bob | my science fiction | 5 | 44 | 2 | regress_rls_bob | my second manga | 6 | 22 | 1 | regress_rls_carol | great science fiction | @@ -2290,8 +2321,9 @@ SELECT * FROM document; 78 | 33 | 1 | regress_rls_bob | some technology novel | 79 | 33 | 1 | regress_rls_bob | technology book, can only insert | 12 | 11 | 1 | regress_rls_bob | another novel | - 1 | 11 | 1 | regress_rls_bob | my first novel | notes added by merge2 notes added by merge3 notes added by merge4 -(14 rows) + 1 | 11 | 1 | regress_rls_bob | my first novel | notes added by merge2 notes added by merge3 notes added by merge4 notes added by merge7 + 13 | 44 | 1 | regress_rls_bob | new manga | +(15 rows) -- -- ROLE/GROUP |