Mercurial > gemma
annotate schema/auth_tests.sql @ 874:da526b58c9c4 geo-style
Added a flag geoserver-clean to force deletion of the gemma workspace before configuring the GeoServer again.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 30 Sep 2018 15:36:17 +0200 |
parents | 642df1164aca |
children | 6590208e3ee1 |
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 -- |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 -- Run tests as unprivileged user |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
7 -- |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
8 SET SESSION AUTHORIZATION test_user_at; |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 SELECT throws_ok('CREATE TABLE test()', 42501, NULL, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 'No objects can be created'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
13 SELECT isnt_empty('SELECT * FROM waterway.bottlenecks', |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
14 'Staged data should be visible'); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
15 SELECT is_empty('SELECT * FROM waterway.bottlenecks WHERE NOT staging_done', |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 'Only staged data should be visible'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
18 SELECT isnt_empty('SELECT * FROM users.templates', |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
19 'User should see templates associated to him'); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
20 SELECT is_empty('SELECT * FROM users.templates |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
21 JOIN users.user_templates USING (template_name) |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
22 WHERE username <> current_user', |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
23 'User should only see templates associated to him'); |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
24 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 -- |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
26 -- Run tests as waterway administrator |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
27 -- |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
28 SET SESSION AUTHORIZATION test_admin_at; |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
29 |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
30 PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
31 INSERT INTO waterway.bottlenecks ( |
182 | 32 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, |
33 revisiting_time, limiting, source_organization) | |
34 VALUES ( | |
35 $1, | |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
36 ('AT', 'XXX', '00001', 'G0001', 1)::isrs, |
182 | 37 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, |
38 ('AT', 'XXX', '00001', '00000', 2)::isrs), | |
39 $2, 'AT', 'AT', 'AT', | |
40 1, 'depth', 'testorganization' | |
41 ); | |
42 SELECT lives_ok('EXECUTE bn_insert( | |
43 ''test1'', | |
44 ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'', | |
45 4326))', | |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
46 'Waterway admin can insert data within his region'); |
182 | 47 SELECT throws_ok('EXECUTE bn_insert( |
48 ''test2'', | |
49 ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'', | |
50 4326))', | |
51 42501, NULL, | |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
52 'Waterway admin cannot insert data outside his region'); |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
53 |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
54 -- template management |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
55 SELECT isnt_empty('SELECT * FROM users.templates |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
56 JOIN users.user_templates USING (template_name) |
112 | 57 WHERE username <> current_user', |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
58 'Waterway admin should see templates of other users'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
59 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
60 SELECT lives_ok('INSERT INTO users.templates (template_name, template_data) |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
61 VALUES (''New AT'', ''\x''); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
62 INSERT INTO users.user_templates |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
63 VALUES (''test_user_at'', ''New AT'')', |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
64 '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
|
65 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
66 SELECT throws_ok('INSERT INTO users.user_templates |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
67 VALUES (''waterway_user2'', ''AT'')', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
68 42501, NULL, |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
69 'Waterway admin cannot add template for other country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
70 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
71 SELECT isnt_empty('UPDATE users.templates SET template_data = ''\xDABE'' |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
72 WHERE template_name = ''AT'' RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
73 'Waterway admin can alter templates for own country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
74 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
75 SELECT is_empty('UPDATE users.templates SET template_data = ''\xDABE'' |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
76 WHERE template_name = ''RO'' RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
77 'Waterway admin cannot alter templates for other country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
78 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
79 SELECT isnt_empty('DELETE FROM users.templates WHERE template_name = ''AT'' |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
80 RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
81 'Waterway admin can delete templates for own country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
82 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
83 SELECT is_empty('DELETE FROM users.templates WHERE template_name = ''RO'' |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
84 RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
85 'Waterway admin cannot delete templates for other country'); |