Mercurial > gemma
comparison schema/auth_tests.sql @ 183:f3a09fc9c1eb
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(); |