Mercurial > gemma
annotate 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 |
rev | line source |
---|---|
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 -- |
183
f3a09fc9c1eb
Prepare for having more than one database test script
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
2 -- pgTAP test script for privileges and RLS policies |
f3a09fc9c1eb
Prepare for having more than one database test script
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
3 -- |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
4 SELECT plan(16); -- Give number of tests that have to be run |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
6 SET search_path TO public, gemma, gemma_waterway, gemma_fairway; |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 -- Run tests as unprivileged user |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
10 -- |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 SET SESSION AUTHORIZATION waterway_user; |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 SELECT throws_ok('CREATE TABLE test()', 42501, NULL, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 'No objects can be created'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
16 SELECT isnt_empty('SELECT * FROM bottlenecks', |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
17 'Staged data should be visible'); |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 SELECT is_empty('SELECT * FROM bottlenecks WHERE NOT staging_done', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 'Only staged data should be visible'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
21 SELECT set_eq('SELECT count(*) FROM gemma.user_profiles', ARRAY[1], |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 'User should only see his own profile'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 SELECT results_eq('SELECT username FROM user_profiles', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 'SELECT CAST(current_user AS varchar)', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 'User should only see his own profile'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
27 SELECT isnt_empty('SELECT * FROM templates', |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
28 'User should see templates associated to him'); |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
29 SELECT is_empty('SELECT * FROM templates |
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
30 JOIN user_templates USING (template_name) |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
31 WHERE username <> current_user', |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
32 'User should only see templates associated to him'); |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
33 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 -- |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
35 -- Run tests as waterway administrator |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
36 -- |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
37 SET SESSION AUTHORIZATION waterway_admin; |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
38 |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
39 PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS |
182 | 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))', | |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
55 'Waterway admin can insert data within his region'); |
182 | 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, | |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
61 'Waterway admin cannot insert data outside his region'); |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
62 |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
63 -- template management |
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
64 SELECT isnt_empty('SELECT * FROM templates |
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
65 JOIN user_templates USING (template_name) |
112 | 66 WHERE username <> current_user', |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
67 'Waterway admin should see templates of other users'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
68 |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
69 SELECT lives_ok('INSERT INTO templates (template_name, template_data) |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
70 VALUES (''New AT'', ''\x''); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
71 INSERT INTO user_templates |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
72 VALUES (''waterway_user'', ''New AT'')', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
73 'Waterway admin can add templates for users in his country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
74 |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
75 SELECT throws_ok('INSERT INTO user_templates |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
76 VALUES (''waterway_user2'', ''AT'')', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
77 42501, NULL, |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
78 'Waterway admin cannot add template for other country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
79 |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
80 SELECT isnt_empty('UPDATE templates SET template_data = ''\xDABE'' |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
81 WHERE template_name = ''AT'' RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
82 'Waterway admin can alter templates for own country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
83 |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
84 SELECT is_empty('UPDATE templates SET template_data = ''\xDABE'' |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
85 WHERE template_name = ''RO'' RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
86 'Waterway admin cannot alter templates for other country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
87 |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
88 SELECT isnt_empty('DELETE FROM templates WHERE template_name = ''AT'' |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
89 RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
90 'Waterway admin can delete templates for own country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
91 |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
92 SELECT is_empty('DELETE FROM templates WHERE template_name = ''RO'' |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
93 RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
94 'Waterway admin cannot delete templates for other country'); |
112 | 95 |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
96 -- |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
97 -- finish tests |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
98 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 SELECT * FROM finish(); |