Mercurial > gemma
annotate schema/auth_tests.sql @ 4701:ef2cf9c413e9
Merged
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 16 Oct 2019 16:51:09 +0200 |
parents | 5466562cca60 |
children | 2440d2f86f4e |
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 |
4158
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
18 -- Helper function: |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
19 CREATE OR REPLACE FUNCTION users.current_user_country() |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
20 RETURNS internal.user_profiles.country%TYPE |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
21 AS $$ |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
22 SELECT country FROM users.list_users |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
23 WHERE username = current_user |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
24 $$ |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
25 LANGUAGE SQL |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
26 STABLE PARALLEL SAFE; |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
27 |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
28 |
3024
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
29 CREATE FUNCTION test_privs() RETURNS SETOF TEXT AS |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
30 $$ |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
31 DECLARE the_schema CONSTANT varchar = 'waterway'; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
32 DECLARE the_table varchar; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
33 BEGIN |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
34 FOR the_table IN |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
35 SELECT table_name |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
36 FROM information_schema.tables |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
37 WHERE table_schema = the_schema |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
38 LOOP |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
39 RETURN NEXT table_privs_are( |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
40 the_schema, |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
41 the_table, |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
42 'waterway_user', |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
43 ARRAY['SELECT'], |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
44 format('waterway_user can SELECT from %I.%I', |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
45 the_schema, the_table)); |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
46 END LOOP; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
47 END; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
48 $$ LANGUAGE plpgsql; |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
49 SELECT * FROM test_privs(); |
5470aa3ffb9a
Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents:
2230
diff
changeset
|
50 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 -- Run tests as unprivileged user |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
53 -- |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
54 SET SESSION AUTHORIZATION test_user_at; |
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 throws_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
57 CREATE TABLE test() |
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 42501, NULL, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
60 'No objects can be created'); |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
61 |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
62 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
63 SELECT * FROM waterway.bottlenecks |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
64 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
65 'Staged data should be visible'); |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
67 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
68 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
|
69 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
70 'Only staged data should be visible'); |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
72 SELECT isnt_empty($$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
73 SELECT * FROM users.templates |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
74 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
75 '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
|
76 |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
77 SELECT ok( |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
78 users.current_user_country() = ALL( |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
79 SELECT country FROM users.templates), |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
80 '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
|
81 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 -- |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
83 -- Run tests as waterway administrator |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
84 -- |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
85 SET SESSION AUTHORIZATION test_admin_at; |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
86 |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
87 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
|
88 INSERT INTO waterway.bottlenecks ( |
4118
b785b6bef578
Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents:
4056
diff
changeset
|
89 gauge_location, validity, |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
90 bottleneck_id, stretch, area, rb, lb, responsible_country, |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3302
diff
changeset
|
91 revisiting_time, limiting, date_info, source_organization) |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
92 SELECT |
4118
b785b6bef578
Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents:
4056
diff
changeset
|
93 location, validity, |
182 | 94 $1, |
95 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, | |
96 ('AT', 'XXX', '00001', '00000', 2)::isrs), | |
97 $2, 'AT', 'AT', 'AT', | |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3302
diff
changeset
|
98 1, 'depth', current_timestamp, 'testorganization' |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3024
diff
changeset
|
99 FROM waterway.gauges |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
100 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
|
101 AND NOT erased; |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
102 SELECT lives_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
103 EXECUTE bn_insert( |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
104 'test1', |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
105 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
|
106 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
107 '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
|
108 SELECT throws_ok($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
109 EXECUTE bn_insert( |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
110 'test2', |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
111 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
|
112 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
113 42501, NULL, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
114 '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
|
115 |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
116 -- template management |
4158
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
117 SELECT lives_ok($$ |
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
4118
diff
changeset
|
118 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
|
119 VALUES ('New AT', '\x', 'AT') |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
120 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
121 '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
|
122 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
123 SELECT throws_ok($$ |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
124 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
|
125 VALUES ('New RO', '\x', 'RO') |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
126 $$, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
127 42501, NULL, |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
128 '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
|
129 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
130 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
131 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
|
132 WHERE template_name = 'AT' RETURNING * |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
133 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
134 '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
|
135 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
136 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
137 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
|
138 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 alter templates for other country'); |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
141 |
1853
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
142 SELECT isnt_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
143 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
|
144 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
145 '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
|
146 |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
147 SELECT is_empty($$ |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
148 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
|
149 $$, |
cedadd4e3db5
Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents:
1339
diff
changeset
|
150 '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
|
151 |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
152 -- import management |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
153 SELECT lives_ok($$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
154 WITH |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
155 job AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
156 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
|
157 'test', current_user, 'test') RETURNING id), |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
158 log AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
159 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
|
160 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
|
161 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
|
162 SELECT id, 'waterway.bottlenecks', 0 FROM job |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
163 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
164 '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
|
165 |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
166 SET SESSION AUTHORIZATION test_admin_at2; |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
167 SELECT bag_has($$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
168 SELECT username FROM users.list_users |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
169 $$, |
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 WITH job AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
172 UPDATE import.imports SET state = 'accepted' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
173 RETURNING id, username), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
174 log AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
175 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
|
176 SELECT id, 'test continued' FROM job) |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
177 SELECT username FROM job |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
178 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
179 '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
|
180 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
181 SELECT lives_ok($$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
182 WITH |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
183 config AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
184 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
|
185 'test', current_user) RETURNING id) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
186 INSERT INTO import.import_configuration_attributes |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
187 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
|
188 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
189 '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
|
190 |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
191 SET SESSION AUTHORIZATION test_admin_at; |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
192 SELECT bag_has($$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
193 SELECT username FROM users.list_users |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
194 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
195 $$ |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
196 WITH config AS ( |
2070 | 197 UPDATE import.import_configuration SET kind = 'test' |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
198 RETURNING id, username), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
199 attrib AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
200 INSERT INTO import.import_configuration_attributes |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
201 SELECT id, 'test continued', 'test value' FROM config), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
202 attrib_upd AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
203 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
|
204 WHERE import_configuration_id = (SELECT id FROM config)) |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
205 SELECT username FROM config |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
206 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
207 '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
|
208 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
209 SET SESSION AUTHORIZATION test_admin_ro; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
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 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
|
212 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
|
213 'test') |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
214 $$, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
215 42501, NULL, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1853
diff
changeset
|
216 '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
|
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 DELETE FROM import.track_imports |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
220 WHERE import_id = currval( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
221 pg_get_serial_sequence('import.imports', 'id')) |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
222 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
223 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
224 '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
|
225 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
226 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
227 INSERT INTO import.import_configuration_attributes |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
228 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
|
229 'import.import_configuration', 'id')), |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
230 'test', 'test value') |
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 add attributes to other countries import config'); |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
234 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
235 SELECT throws_ok($$ |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
236 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
|
237 WHERE import_configuration_id = currval( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
238 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
|
239 $$, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
240 42501, NULL, |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
241 'Waterway admin cannot overwrite attributes of other countries config'); |