Mercurial > gemma
annotate schema/auth_tests.sql @ 3136:e52ba854bfe8
merge
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Tue, 30 Apr 2019 15:33:05 +0200 |
parents | 5470aa3ffb9a |
children | ec6163c6687d |
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 ( |
182 | 78 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, |
79 revisiting_time, limiting, source_organization) | |
80 VALUES ( | |
81 $1, | |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
82 ('AT', 'XXX', '00001', 'G0001', 1)::isrs, |
182 | 83 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, |
84 ('AT', 'XXX', '00001', '00000', 2)::isrs), | |
85 $2, 'AT', 'AT', 'AT', | |
86 1, 'depth', 'testorganization' | |
87 ); | |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
88 SELECT lives_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
89 EXECUTE bn_insert( |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
90 'test1', |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
91 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
|
92 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
93 '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
|
94 SELECT throws_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
95 EXECUTE bn_insert( |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
96 'test2', |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
97 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
|
98 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
99 42501, NULL, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
100 '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
|
101 |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
102 -- template management |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
103 SELECT results_eq($$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
104 SELECT users.current_user_country() |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
105 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
106 $$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
107 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
|
108 VALUES ('New AT', '\x') |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
109 RETURNING country |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
110 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
111 '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
|
112 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
113 SELECT throws_ok($$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
114 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
|
115 VALUES ('New RO', '\x', 'RO') |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
116 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
117 42501, NULL, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
118 '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
|
119 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
120 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
121 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
|
122 WHERE template_name = 'AT' RETURNING * |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
123 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
124 '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
|
125 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
126 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
127 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
|
128 WHERE template_name = 'RO' RETURNING * |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
129 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
130 '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
|
131 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
132 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
133 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
|
134 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
135 '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
|
136 |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
137 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
138 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
|
139 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
140 '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
|
141 |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
142 -- import management |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
143 SELECT lives_ok($$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
144 WITH |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
145 job AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
146 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
|
147 'test', current_user, 'test') RETURNING id), |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
148 log AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
149 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
|
150 SELECT id, 'test' FROM job) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
151 INSERT INTO import.track_imports |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
152 SELECT id, 'waterway.bottlenecks', 0 FROM job |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
153 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
154 '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
|
155 |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
156 SET SESSION AUTHORIZATION test_admin_at2; |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
157 SELECT bag_has($$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
158 SELECT username FROM users.list_users |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
159 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
160 $$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
161 WITH job AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
162 UPDATE import.imports SET state = 'accepted' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
163 RETURNING id, username), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
164 log AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
165 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
|
166 SELECT id, 'test continued' FROM job) |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
167 SELECT username FROM job |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
168 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
169 '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
|
170 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
171 SELECT lives_ok($$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
172 WITH |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
173 config AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
174 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
|
175 'test', current_user) RETURNING id) |
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_attributes |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
177 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
|
178 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
179 '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
|
180 |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
181 SET SESSION AUTHORIZATION test_admin_at; |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
182 SELECT bag_has($$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
183 SELECT username FROM users.list_users |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
184 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
185 $$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
186 WITH config AS ( |
2070 | 187 UPDATE import.import_configuration SET kind = 'test' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
188 RETURNING id, username), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
189 attrib AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
190 INSERT INTO import.import_configuration_attributes |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
191 SELECT id, 'test continued', 'test value' FROM config), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
192 attrib_upd AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
193 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
|
194 WHERE import_configuration_id = (SELECT id FROM config)) |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
195 SELECT username FROM config |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
196 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
197 '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
|
198 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
199 SET SESSION AUTHORIZATION test_admin_ro; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
200 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
201 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
|
202 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
|
203 'test') |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
204 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
205 42501, NULL, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
206 '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
|
207 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
208 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
209 DELETE FROM import.track_imports |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
210 WHERE import_id = currval( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
211 pg_get_serial_sequence('import.imports', 'id')) |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
212 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
213 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
214 '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
|
215 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
216 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
217 INSERT INTO import.import_configuration_attributes |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
218 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
|
219 'import.import_configuration', 'id')), |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
220 'test', 'test value') |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
221 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
222 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
223 '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
|
224 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
225 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
226 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
|
227 WHERE import_configuration_id = currval( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
228 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
|
229 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
230 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
231 'Waterway admin cannot overwrite attributes of other countries config'); |