Mercurial > gemma

diff schema/tap_tests.sql @ 180:0423eab4ad45

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve RLS policies for template data The removed POLICY manage_templates missed a WITH CHECK (true), because the USING clause is applied to new rows, too, if no WITH CHECK is provided, thus implying a dead-lock situation with the FK constraint on user_templates (the POLICY requiring a row in user_templates while INSERTing such row requires a row in templates). New POLICY on user_templates prevents waterway_admin from relating templates to users from other countries and allows to write other policies more compact.
author Tom Gottfried <tom@intevation.de>
date Tue, 17 Jul 2018 19:08:18 +0200
parents 2111ad473961
children e509eccff303
line wrap: on
line diff
--- a/schema/tap_tests.sql	Tue Jul 17 18:21:56 2018 +0200
+++ b/schema/tap_tests.sql	Tue Jul 17 19:08:18 2018 +0200
@@ -3,7 +3,7 @@
 --
 CREATE EXTENSION pgtap;
 
-SELECT plan(10); -- Give number of tests that have to be run
+SELECT plan(16); -- Give number of tests that have to be run
 
 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
 
@@ -68,7 +68,34 @@
 SELECT isnt_empty('SELECT * FROM templates
                    JOIN user_templates USING (template_name)
                    WHERE username <> current_user',
-                  'Waterway admin should see templates of users in country');
+                  'Waterway admin should see templates of other users');
+
+SELECT lives_ok('INSERT INTO templates (template_name, template_data)
+                 VALUES (''New AT'', ''\x'');
+                 INSERT INTO user_templates
+                 VALUES (''waterway_user'', ''New AT'')',
+                'Waterway admin can add templates for users in his country');
+
+SELECT throws_ok('INSERT INTO user_templates
+                  VALUES (''waterway_user2'', ''AT'')',
+                 42501, NULL,
+                 'Waterway admin cannot add template for other country');
+
+SELECT isnt_empty('UPDATE templates SET template_data = ''\xDABE''
+                   WHERE template_name = ''AT'' RETURNING *',
+                  'Waterway admin can alter templates for own country');
+
+SELECT is_empty('UPDATE templates SET template_data = ''\xDABE''
+                 WHERE template_name = ''RO'' RETURNING *',
+                'Waterway admin cannot alter templates for other country');
+
+SELECT isnt_empty('DELETE FROM templates WHERE template_name = ''AT''
+                   RETURNING *',
+                  'Waterway admin can delete templates for own country');
+
+SELECT is_empty('DELETE FROM templates WHERE template_name = ''RO''
+                 RETURNING *',
+                'Waterway admin cannot delete templates for other country');
 
 --
 -- finish tests