Mercurial > gemma
annotate schema/auth_tests.sql @ 3312:00548f0e81c3
client: sections layer: adjusted colors
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Fri, 17 May 2019 13:29:23 +0200 |
parents | ec6163c6687d |
children | 02951a62e8c6 |
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 |
3024
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
18 CREATE FUNCTION test_privs() RETURNS SETOF TEXT AS |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
19 $$ |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
20 DECLARE the_schema CONSTANT varchar = 'waterway'; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
21 DECLARE the_table varchar; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
22 BEGIN |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
23 FOR the_table IN |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
24 SELECT table_name |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
25 FROM information_schema.tables |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
26 WHERE table_schema = the_schema |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
27 LOOP |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
28 RETURN NEXT table_privs_are( |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
29 the_schema, |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
30 the_table, |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
31 'waterway_user', |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
32 ARRAY['SELECT'], |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
33 format('waterway_user can SELECT from %I.%I', |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
34 the_schema, the_table)); |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
35 END LOOP; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
36 END; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
37 $$ LANGUAGE plpgsql; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
38 SELECT * FROM test_privs(); |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
39 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 -- Run tests as unprivileged user |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
42 -- |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
43 SET SESSION AUTHORIZATION test_user_at; |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
45 SELECT throws_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
46 CREATE TABLE test() |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
47 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
48 42501, NULL, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
49 'No objects can be created'); |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
50 |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
51 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
52 SELECT * FROM waterway.bottlenecks |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
53 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
54 'Staged data should be visible'); |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
56 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
57 SELECT * FROM waterway.bottlenecks WHERE NOT staging_done |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
58 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
59 'Only staged data should be visible'); |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
61 SELECT isnt_empty($$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
62 SELECT * FROM users.templates |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
63 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
64 'User should see templates associated to his country'); |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
65 |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
66 SELECT ok( |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
67 users.current_user_country() = ALL( |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
68 SELECT country FROM users.templates), |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
69 'User should only see templates associated to his country'); |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
70 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 -- |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
72 -- Run tests as waterway administrator |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
73 -- |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
74 SET SESSION AUTHORIZATION test_admin_at; |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
75 |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
76 PREPARE bn_insert (varchar, geometry(MULTIPOLYGON, 4326)) AS |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
77 INSERT INTO waterway.bottlenecks ( |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
78 gauge_location, gauge_validity, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
79 bottleneck_id, stretch, area, rb, lb, responsible_country, |
182 | 80 revisiting_time, limiting, source_organization) |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
81 SELECT |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
82 location, validity, |
182 | 83 $1, |
84 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, | |
85 ('AT', 'XXX', '00001', '00000', 2)::isrs), | |
86 $2, 'AT', 'AT', 'AT', | |
87 1, 'depth', 'testorganization' | |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
88 FROM waterway.gauges |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
89 WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs; |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
90 SELECT lives_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
91 EXECUTE bn_insert( |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
92 'test1', |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
93 ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)) |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
94 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
95 'Waterway admin can insert data within his region'); |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
96 SELECT throws_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
97 EXECUTE bn_insert( |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
98 'test2', |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
99 ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326)) |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
100 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
101 42501, NULL, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
102 'Waterway admin cannot insert data outside his region'); |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
103 |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
104 -- template management |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
105 SELECT results_eq($$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
106 SELECT users.current_user_country() |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
107 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
108 $$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
109 INSERT INTO users.templates (template_name, template_data) |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
110 VALUES ('New AT', '\x') |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
111 RETURNING country |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
112 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
113 'Waterway admin can add templates for his country'); |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
114 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
115 SELECT throws_ok($$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
116 INSERT INTO users.templates (template_name, template_data, country) |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
117 VALUES ('New RO', '\x', 'RO') |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
118 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
119 42501, NULL, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
120 'Waterway admin cannot add template for other country'); |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
121 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
122 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
123 UPDATE users.templates SET template_data = '\xDABE' |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
124 WHERE template_name = 'AT' RETURNING * |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
125 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
126 'Waterway admin can alter templates for own country'); |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
127 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
128 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
129 UPDATE users.templates SET template_data = '\xDABE' |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
130 WHERE template_name = 'RO' RETURNING * |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
131 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
132 'Waterway admin cannot alter templates for other country'); |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
133 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
134 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
135 DELETE FROM users.templates WHERE template_name = 'AT' RETURNING * |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
136 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
137 'Waterway admin can delete templates for own country'); |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
138 |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
139 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
140 DELETE FROM users.templates WHERE template_name = 'RO' RETURNING * |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
141 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
142 'Waterway admin cannot delete templates for other country'); |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
143 |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
144 -- import management |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
145 SELECT lives_ok($$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
146 WITH |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
147 job AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
148 INSERT INTO import.imports (kind, username, data) VALUES ( |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
149 'test', current_user, 'test') RETURNING id), |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
150 log AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
151 INSERT INTO import.import_logs (import_id, msg) |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
152 SELECT id, 'test' FROM job) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
153 INSERT INTO import.track_imports |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
154 SELECT id, 'waterway.bottlenecks', 0 FROM job |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
155 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
156 'Waterway admin can add import job and related data'); |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
157 |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
158 SET SESSION AUTHORIZATION test_admin_at2; |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
159 SELECT bag_has($$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
160 SELECT username FROM users.list_users |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
161 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
162 $$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
163 WITH job AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
164 UPDATE import.imports SET state = 'accepted' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
165 RETURNING id, username), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
166 log AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
167 INSERT INTO import.import_logs (import_id, msg) |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
168 SELECT id, 'test continued' FROM job) |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
169 SELECT username FROM job |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
170 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
171 'Waterway admin can edit import jobs from his country only'); |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
172 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
173 SELECT lives_ok($$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
174 WITH |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
175 config AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
176 INSERT INTO import.import_configuration (kind, username) VALUES ( |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
177 'test', current_user) RETURNING id) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
178 INSERT INTO import.import_configuration_attributes |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
179 SELECT id, 'test key', 'test value' FROM config |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
180 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
181 'Waterway admin can add import config and related data'); |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
182 |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
183 SET SESSION AUTHORIZATION test_admin_at; |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
184 SELECT bag_has($$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
185 SELECT username FROM users.list_users |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
186 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
187 $$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
188 WITH config AS ( |
2070 | 189 UPDATE import.import_configuration SET kind = 'test' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
190 RETURNING id, username), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
191 attrib AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
192 INSERT INTO import.import_configuration_attributes |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
193 SELECT id, 'test continued', 'test value' FROM config), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
194 attrib_upd AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
195 UPDATE import.import_configuration_attributes SET v = 'test v' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
196 WHERE import_configuration_id = (SELECT id FROM config)) |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
197 SELECT username FROM config |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
198 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
199 'Waterway admin can edit import config from his country only'); |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
200 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
201 SET SESSION AUTHORIZATION test_admin_ro; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
202 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
203 INSERT INTO import.import_logs (import_id, msg) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
204 VALUES (currval(pg_get_serial_sequence('import.imports', 'id')), |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
205 'test') |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
206 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
207 42501, NULL, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
208 'Waterway admin cannot add log messages to other countries imports'); |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
209 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
210 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
211 DELETE FROM import.track_imports |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
212 WHERE import_id = currval( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
213 pg_get_serial_sequence('import.imports', 'id')) |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
214 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
215 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
216 'Waterway admin cannot delete tracking data of other countries imports'); |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
217 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
218 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
219 INSERT INTO import.import_configuration_attributes |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
220 VALUES (currval(pg_get_serial_sequence( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
221 'import.import_configuration', 'id')), |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
222 'test', 'test value') |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
223 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
224 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
225 'Waterway admin cannot add attributes to other countries import config'); |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
226 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
227 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
228 UPDATE import.import_configuration_attributes SET v = 'evil' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
229 WHERE import_configuration_id = currval( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
230 pg_get_serial_sequence('import.import_configuration', 'id')) |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
231 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
232 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
233 'Waterway admin cannot overwrite attributes of other countries config'); |