Mercurial > gemma

comparison schema/auth_tests.sql @ 183:f3a09fc9c1eb

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Prepare for having more than one database test script
author Tom Gottfried <tom@intevation.de>
date Wed, 18 Jul 2018 17:30:38 +0200
parents schema/tap_tests.sql@4df4e4bf480e
children 5dc8e734487a
comparison
equal deleted inserted replaced
182:4df4e4bf480e 183:f3a09fc9c1eb
1 --
2 -- pgTAP test script for privileges and RLS policies
3 --
4 SELECT plan(16); -- Give number of tests that have to be run
5
6 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
7
8 --
9 -- Run tests as unprivileged user
10 --
11 SET SESSION AUTHORIZATION waterway_user;
12
13 SELECT throws_ok('CREATE TABLE test()', 42501, NULL,
14 'No objects can be created');
15
16 SELECT isnt_empty('SELECT * FROM bottlenecks',
17 'Staged data should be visible');
18 SELECT is_empty('SELECT * FROM bottlenecks WHERE NOT staging_done',
19 'Only staged data should be visible');
20
21 SELECT set_eq('SELECT count(*) FROM gemma.user_profiles', ARRAY[1],
22 'User should only see his own profile');
23 SELECT results_eq('SELECT username FROM user_profiles',
24 'SELECT CAST(current_user AS varchar)',
25 'User should only see his own profile');
26
27 SELECT isnt_empty('SELECT * FROM templates',
28 'User should see templates associated to him');
29 SELECT is_empty('SELECT * FROM templates
30 JOIN user_templates USING (template_name)
31 WHERE username <> current_user',
32 'User should only see templates associated to him');
33
34 --
35 -- Run tests as waterway administrator
36 --
37 SET SESSION AUTHORIZATION waterway_admin;
38
39 PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS
40 INSERT INTO bottlenecks (
41 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
42 revisiting_time, limiting, source_organization)
43 VALUES (
44 $1,
45 ('AT', 'XXX', '00001', '00000', 1)::isrs,
46 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
47 ('AT', 'XXX', '00001', '00000', 2)::isrs),
48 $2, 'AT', 'AT', 'AT',
49 1, 'depth', 'testorganization'
50 );
51 SELECT lives_ok('EXECUTE bn_insert(
52 ''test1'',
53 ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'',
54 4326))',
55 'Waterway admin can insert data within his region');
56 SELECT throws_ok('EXECUTE bn_insert(
57 ''test2'',
58 ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'',
59 4326))',
60 42501, NULL,
61 'Waterway admin cannot insert data outside his region');
62
63 -- template management
64 SELECT isnt_empty('SELECT * FROM templates
65 JOIN user_templates USING (template_name)
66 WHERE username <> current_user',
67 'Waterway admin should see templates of other users');
68
69 SELECT lives_ok('INSERT INTO templates (template_name, template_data)
70 VALUES (''New AT'', ''\x'');
71 INSERT INTO user_templates
72 VALUES (''waterway_user'', ''New AT'')',
73 'Waterway admin can add templates for users in his country');
74
75 SELECT throws_ok('INSERT INTO user_templates
76 VALUES (''waterway_user2'', ''AT'')',
77 42501, NULL,
78 'Waterway admin cannot add template for other country');
79
80 SELECT isnt_empty('UPDATE templates SET template_data = ''\xDABE''
81 WHERE template_name = ''AT'' RETURNING *',
82 'Waterway admin can alter templates for own country');
83
84 SELECT is_empty('UPDATE templates SET template_data = ''\xDABE''
85 WHERE template_name = ''RO'' RETURNING *',
86 'Waterway admin cannot alter templates for other country');
87
88 SELECT isnt_empty('DELETE FROM templates WHERE template_name = ''AT''
89 RETURNING *',
90 'Waterway admin can delete templates for own country');
91
92 SELECT is_empty('DELETE FROM templates WHERE template_name = ''RO''
93 RETURNING *',
94 'Waterway admin cannot delete templates for other country');
95
96 --
97 -- finish tests
98 --
99 SELECT * FROM finish();