Mercurial > gemma

annotate schema/auth_tests.sql @ 5684:536e842d9bfa sr-v2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Reorder vertices in tins to minimize delta distances.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sun, 11 Feb 2024 22:32:55 +0100
parents 4c658a8f34da
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
83 $1,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
84 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
85 ('AT', 'XXX', '00001', '00000', 2)::isrs),
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
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
c4db392130a4 Fix test
Tom Gottfried <tom@intevation.de>
parents: 1995
diff changeset
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');