# HG changeset patch # User Tom Gottfried # Date 1531927838 -7200 # Node ID f3a09fc9c1ebe8c38fccfd0568b4b8a2469e3b6c # Parent 4df4e4bf480e0c40c4b300e3e4839bce84899d30 Prepare for having more than one database test script diff -r 4df4e4bf480e -r f3a09fc9c1eb schema/auth_tests.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/auth_tests.sql Wed Jul 18 17:30:38 2018 +0200 @@ -0,0 +1,99 @@ +-- +-- pgTAP test script for privileges and RLS policies +-- +SELECT plan(16); -- Give number of tests that have to be run + +SET search_path TO public, gemma, gemma_waterway, gemma_fairway; + +-- +-- Run tests as unprivileged user +-- +SET SESSION AUTHORIZATION waterway_user; + +SELECT throws_ok('CREATE TABLE test()', 42501, NULL, + 'No objects can be created'); + +SELECT isnt_empty('SELECT * FROM bottlenecks', + 'Staged data should be visible'); +SELECT is_empty('SELECT * FROM bottlenecks WHERE NOT staging_done', + 'Only staged data should be visible'); + +SELECT set_eq('SELECT count(*) FROM gemma.user_profiles', ARRAY[1], + 'User should only see his own profile'); +SELECT results_eq('SELECT username FROM user_profiles', + 'SELECT CAST(current_user AS varchar)', + 'User should only see his own profile'); + +SELECT isnt_empty('SELECT * FROM templates', + 'User should see templates associated to him'); +SELECT is_empty('SELECT * FROM templates + JOIN user_templates USING (template_name) + WHERE username <> current_user', + 'User should only see templates associated to him'); + +-- +-- Run tests as waterway administrator +-- +SET SESSION AUTHORIZATION waterway_admin; + +PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS + INSERT INTO bottlenecks ( + bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, + revisiting_time, limiting, source_organization) + VALUES ( + $1, + ('AT', 'XXX', '00001', '00000', 1)::isrs, + isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, + ('AT', 'XXX', '00001', '00000', 2)::isrs), + $2, 'AT', 'AT', 'AT', + 1, 'depth', 'testorganization' + ); +SELECT lives_ok('EXECUTE bn_insert( + ''test1'', + ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'', + 4326))', + 'Waterway admin can insert data within his region'); +SELECT throws_ok('EXECUTE bn_insert( + ''test2'', + ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'', + 4326))', + 42501, NULL, + 'Waterway admin cannot insert data outside his region'); + +-- template management +SELECT isnt_empty('SELECT * FROM templates + JOIN user_templates USING (template_name) + WHERE username <> current_user', + '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 +-- +SELECT * FROM finish(); diff -r 4df4e4bf480e -r f3a09fc9c1eb schema/run_tests.sh --- a/schema/run_tests.sh Wed Jul 18 16:48:27 2018 +0200 +++ b/schema/run_tests.sh Wed Jul 18 17:30:38 2018 +0200 @@ -2,8 +2,11 @@ dropdb --if-exists gemma_test createdb gemma_test +psql -qv ON_ERROR_STOP= -c 'CREATE EXTENSION pgtap' -d gemma_test psql -qv ON_ERROR_STOP= -f gemma.sql -d gemma_test psql -qv ON_ERROR_STOP= -f auth.sql -d gemma_test -psql -Xf tap_tests.sql -d gemma_test +psql -qv ON_ERROR_STOP= -f tap_tests_data.sql -d gemma_test + +psql -Xf auth_tests.sql -d gemma_test diff -r 4df4e4bf480e -r f3a09fc9c1eb schema/tap_tests.sql --- a/schema/tap_tests.sql Wed Jul 18 16:48:27 2018 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,103 +0,0 @@ --- --- pgTAP test script --- -CREATE EXTENSION pgtap; - -SELECT plan(16); -- Give number of tests that have to be run - -SET search_path TO public, gemma, gemma_waterway, gemma_fairway; - -\i tap_tests_data.sql - --- --- Run tests as unprivileged user --- -SET SESSION AUTHORIZATION waterway_user; - -SELECT throws_ok('CREATE TABLE test()', 42501, NULL, - 'No objects can be created'); - -SELECT isnt_empty('SELECT * FROM bottlenecks', - 'Staged data should be visible'); -SELECT is_empty('SELECT * FROM bottlenecks WHERE NOT staging_done', - 'Only staged data should be visible'); - -SELECT set_eq('SELECT count(*) FROM gemma.user_profiles', ARRAY[1], - 'User should only see his own profile'); -SELECT results_eq('SELECT username FROM user_profiles', - 'SELECT CAST(current_user AS varchar)', - 'User should only see his own profile'); - -SELECT isnt_empty('SELECT * FROM templates', - 'User should see templates associated to him'); -SELECT is_empty('SELECT * FROM templates - JOIN user_templates USING (template_name) - WHERE username <> current_user', - 'User should only see templates associated to him'); - --- --- Run tests as waterway administrator --- -SET SESSION AUTHORIZATION waterway_admin; - -PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS - INSERT INTO bottlenecks ( - bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, - revisiting_time, limiting, source_organization) - VALUES ( - $1, - ('AT', 'XXX', '00001', '00000', 1)::isrs, - isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, - ('AT', 'XXX', '00001', '00000', 2)::isrs), - $2, 'AT', 'AT', 'AT', - 1, 'depth', 'testorganization' - ); -SELECT lives_ok('EXECUTE bn_insert( - ''test1'', - ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'', - 4326))', - 'Waterway admin can insert data within his region'); -SELECT throws_ok('EXECUTE bn_insert( - ''test2'', - ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'', - 4326))', - 42501, NULL, - 'Waterway admin cannot insert data outside his region'); - --- template management -SELECT isnt_empty('SELECT * FROM templates - JOIN user_templates USING (template_name) - WHERE username <> current_user', - '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 --- -SELECT * FROM finish();