Mercurial > gemma
annotate schema/auth_tests.sql @ 5652:9b3779f649c2 v5.7.1
Bumped version for release.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Mon, 07 Aug 2023 17:59:18 +0200 |
parents | 4c658a8f34da |
children |
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 |
4755
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
7 -- Copyright (C) 2018, 2019 by via donau |
1298
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( |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4158
diff
changeset
|
67 users.user_country() = ALL( |
2230
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 ( |
4118
b785b6bef578
Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents:
4056
diff
changeset
|
78 gauge_location, validity, |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
79 bottleneck_id, stretch, area, rb, lb, responsible_country, |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3302
diff
changeset
|
80 revisiting_time, limiting, date_info, source_organization) |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
81 SELECT |
4118
b785b6bef578
Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents:
4056
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', | |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3302
diff
changeset
|
87 1, 'depth', current_timestamp, 'testorganization' |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
88 FROM waterway.gauges |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
89 WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
90 AND NOT erased; |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
91 SELECT lives_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
92 EXECUTE bn_insert( |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
93 'test1', |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
94 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
|
95 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
96 '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
|
97 SELECT throws_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
98 EXECUTE bn_insert( |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
99 'test2', |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
100 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
|
101 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
102 42501, NULL, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
103 '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
|
104 |
5025
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
105 -- Ensure a USING clause prevents access in an UPDATE |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
106 SELECT is_empty($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
107 WITH a AS (SELECT users.current_user_area_utm() AS a) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
108 UPDATE waterway.bottlenecks |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
109 SET objnam = 'Now it''s mine', |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
110 area = ST_geomfromtext( |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
111 'MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
112 WHERE bottleneck_id = 'testbottleneck3' |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
113 RETURNING * |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
114 $$, |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
115 'Waterway admin cannot move data from outside his region inside'); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
116 |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
117 -- Ensure a WITH CHECK or USING clause prevents writing such rows |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
118 SELECT throws_ok($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
119 WITH a AS (SELECT users.current_user_area_utm() AS a) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
120 UPDATE waterway.bottlenecks |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
121 SET objnam = 'Give-away', |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
122 area = ST_geomfromtext( |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
123 'MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
124 WHERE bottleneck_id = 'testbottleneck2' |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
125 RETURNING * |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
126 $$, |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
127 42501, NULL, |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
128 'Waterway admin cannot move data from inside his region outside'); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
129 |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
130 SELECT is_empty($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
131 WITH a AS (SELECT users.current_user_area_utm() AS a) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
132 DELETE FROM waterway.bottlenecks |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
133 WHERE NOT ST_Covers((SELECT a FROM a), |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
134 ST_Transform( |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
135 CAST(area AS geometry), ST_SRID((SELECT a FROM a)))) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
136 RETURNING * |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
137 $$, |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
138 'Waterway admin cannot delete data outside his region'); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
139 |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
140 -- template management |
4158
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
141 SELECT lives_ok($$ |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
142 INSERT INTO users.templates (template_name, template_data, country) |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
143 VALUES ('New AT', '\x', 'AT') |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
144 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
145 '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
|
146 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
147 SELECT throws_ok($$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
148 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
|
149 VALUES ('New RO', '\x', 'RO') |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
150 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
151 42501, NULL, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
152 '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
|
153 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
154 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
155 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
|
156 WHERE template_name = 'AT' RETURNING * |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
157 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
158 '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
|
159 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
160 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
161 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
|
162 WHERE template_name = 'RO' RETURNING * |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
163 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
164 '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
|
165 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
166 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
167 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
|
168 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
169 '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
|
170 |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
171 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
172 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
|
173 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
174 '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
|
175 |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
176 -- import management |
4755
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
177 SET SESSION AUTHORIZATION test_sys_admin1; |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
178 SELECT lives_ok($$ |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
179 WITH |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
180 job AS ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
181 INSERT INTO import.imports (kind, username, data) VALUES ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
182 'test', current_user, 'test') RETURNING id), |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
183 log AS ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
184 INSERT INTO import.import_logs (import_id, msg) |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
185 SELECT id, 'test' FROM job) |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
186 INSERT INTO import.track_imports (import_id, relation, key) |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
187 SELECT id, 'waterway.bottlenecks', 2 FROM job |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
188 $$, |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
189 'Sys_admin can add import job and related data'); |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
190 |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
191 SELECT lives_ok($$ |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
192 WITH |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
193 config AS ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
194 INSERT INTO import.import_configuration (kind, username) VALUES ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
195 'test', current_user) RETURNING id) |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
196 INSERT INTO import.import_configuration_attributes |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
197 SELECT id, 'test key', 'test value' FROM config |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
198 $$, |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
199 'Sys_admin can add import config and related data'); |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
200 |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
201 SET SESSION AUTHORIZATION test_admin_at; |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
202 SELECT lives_ok($$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
203 WITH |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
204 job AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
205 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
|
206 'test', current_user, 'test') RETURNING id), |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
207 log AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
208 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
|
209 SELECT id, 'test' FROM job) |
4056
1ecfe5d015b0
Fixup rev. 4bf1c8d91bac: take new column into account in tests
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
210 INSERT INTO import.track_imports (import_id, relation, key) |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
211 SELECT id, 'waterway.bottlenecks', 0 FROM job |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
212 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
213 '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
|
214 |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
215 SET SESSION AUTHORIZATION test_admin_at2; |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
216 SELECT bag_has($$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
217 SELECT username FROM users.list_users |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
218 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
219 $$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
220 WITH job AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
221 UPDATE import.imports SET state = 'accepted' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
222 RETURNING id, username), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
223 log AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
224 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
|
225 SELECT id, 'test continued' FROM job) |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
226 SELECT username FROM job |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
227 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
228 '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
|
229 |
4755
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
230 SELECT bag_hasnt($$ |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
231 WITH job AS ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
232 UPDATE import.imports SET state = 'accepted' |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
233 RETURNING id, username), |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
234 log AS ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
235 INSERT INTO import.import_logs (import_id, msg) |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
236 SELECT id, 'test continued' FROM job) |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
237 SELECT username FROM job |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
238 $$, |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
239 $$ |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
240 SELECT username FROM users.list_users WHERE rolname = 'sys_admin' |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
241 $$, |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
242 'Waterway admin cannot edit import jobs of sys_admins'); |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
243 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
244 SELECT lives_ok($$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
245 WITH |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
246 config AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
247 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
|
248 'test', current_user) RETURNING id) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
249 INSERT INTO import.import_configuration_attributes |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
250 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
|
251 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
252 '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
|
253 |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
254 SET SESSION AUTHORIZATION test_admin_at; |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
255 SELECT bag_has($$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
256 SELECT username FROM users.list_users |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
257 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
258 $$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
259 WITH config AS ( |
2070 | 260 UPDATE import.import_configuration SET kind = 'test' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
261 RETURNING id, username), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
262 attrib AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
263 INSERT INTO import.import_configuration_attributes |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
264 SELECT id, 'test continued', 'test value' FROM config), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
265 attrib_upd AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
266 UPDATE import.import_configuration_attributes SET v = 'test v' |
4755
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
267 WHERE import_configuration_id IN (SELECT id FROM config)) |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
268 SELECT username FROM config |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
269 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
270 '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
|
271 |
4755
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
272 SELECT bag_hasnt($$ |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
273 WITH config AS ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
274 UPDATE import.import_configuration SET kind = 'test' |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
275 RETURNING id, username), |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
276 attrib AS ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
277 INSERT INTO import.import_configuration_attributes |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
278 SELECT id, 'test continued 1', 'test value' FROM config), |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
279 attrib_upd AS ( |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
280 UPDATE import.import_configuration_attributes SET v = 'test v' |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
281 WHERE import_configuration_id IN (SELECT id FROM config)) |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
282 SELECT username FROM config |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
283 $$, |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
284 $$ |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
285 SELECT username FROM users.list_users |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
286 WHERE rolname = 'sys_admin' AND country = 'RO' |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
287 $$, |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
288 'Waterway admin cannot edit import config from sys_admin'); |
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
289 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
290 SET SESSION AUTHORIZATION test_admin_ro; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
291 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
292 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
|
293 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
|
294 'test') |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
295 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
296 42501, NULL, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
297 '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
|
298 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
299 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
300 DELETE FROM import.track_imports |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
301 WHERE import_id = currval( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
302 pg_get_serial_sequence('import.imports', 'id')) |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
303 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
304 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
305 '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
|
306 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
307 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
308 INSERT INTO import.import_configuration_attributes |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
309 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
|
310 'import.import_configuration', 'id')), |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
311 'test', 'test value') |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
312 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
313 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
314 '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
|
315 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
316 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
317 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
|
318 WHERE import_configuration_id = currval( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
319 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
|
320 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
321 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
322 'Waterway admin cannot overwrite attributes of other countries config'); |