Mercurial > gemma
annotate schema/auth_tests.sql @ 1544:5f80ec319a4f
If a user is removed or renamed kill her/his schedule. Needs more case on renaming.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Mon, 10 Dec 2018 13:17:50 +0100 |
parents | 1d1fc92fc3ea |
children | cedadd4e3db5 |
rev | line source |
---|---|
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
2 -- without warranty, see README.md and license for details. |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
3 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
4 -- SPDX-License-Identifier: AGPL-3.0-or-later |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
5 -- License-Filename: LICENSES/AGPL-3.0.txt |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
6 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
7 -- Copyright (C) 2018 by via donau |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
10 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
11 -- Author(s): |
1301
2304778c4432
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1298
diff
changeset
|
12 -- * Tom Gottfried <tom@intevation.de> |
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
579
diff
changeset
|
13 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 -- |
183
f3a09fc9c1eb
Prepare for having more than one database test script
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
15 -- 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
|
16 -- |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 -- Run tests as unprivileged user |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
20 -- |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
21 SET SESSION AUTHORIZATION test_user_at; |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 SELECT throws_ok('CREATE TABLE test()', 42501, NULL, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 'No objects can be created'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
26 SELECT isnt_empty('SELECT * FROM waterway.bottlenecks', |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
27 'Staged data should be visible'); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
28 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
|
29 'Only staged data should be visible'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
31 SELECT isnt_empty('SELECT * FROM users.templates', |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
32 '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
|
33 SELECT is_empty('SELECT * FROM users.templates |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
34 JOIN users.user_templates USING (template_name) |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
35 WHERE username <> current_user', |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
36 'User should only see templates associated to him'); |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
37 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 -- |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
39 -- Run tests as waterway administrator |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
40 -- |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
41 SET SESSION AUTHORIZATION test_admin_at; |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
42 |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
43 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
|
44 INSERT INTO waterway.bottlenecks ( |
182 | 45 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, |
46 revisiting_time, limiting, source_organization) | |
47 VALUES ( | |
48 $1, | |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
49 ('AT', 'XXX', '00001', 'G0001', 1)::isrs, |
182 | 50 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, |
51 ('AT', 'XXX', '00001', '00000', 2)::isrs), | |
52 $2, 'AT', 'AT', 'AT', | |
53 1, 'depth', 'testorganization' | |
54 ); | |
55 SELECT lives_ok('EXECUTE bn_insert( | |
56 ''test1'', | |
57 ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'', | |
58 4326))', | |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
59 'Waterway admin can insert data within his region'); |
182 | 60 SELECT throws_ok('EXECUTE bn_insert( |
61 ''test2'', | |
62 ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'', | |
63 4326))', | |
64 42501, NULL, | |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
65 'Waterway admin cannot insert data outside his region'); |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
66 |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
67 -- template management |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
68 SELECT isnt_empty('SELECT * FROM users.templates |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
69 JOIN users.user_templates USING (template_name) |
112 | 70 WHERE username <> current_user', |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
71 'Waterway admin should see templates of other users'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
72 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
73 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
|
74 VALUES (''New AT'', ''\x''); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
75 INSERT INTO users.user_templates |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
76 VALUES (''test_user_at'', ''New AT'')', |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
77 '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
|
78 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
79 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
|
80 VALUES (''waterway_user2'', ''AT'')', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
81 42501, NULL, |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
82 'Waterway admin cannot add template for other country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
83 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
84 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
|
85 WHERE template_name = ''AT'' RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
86 'Waterway admin can alter templates for own country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
87 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
88 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
|
89 WHERE template_name = ''RO'' RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
90 'Waterway admin cannot alter templates for other country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
91 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
92 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
|
93 RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
94 'Waterway admin can delete templates for own country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
95 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
96 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
|
97 RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
98 'Waterway admin cannot delete templates for other country'); |