Mercurial > gemma

annotate schema/auth_tests.sql @ 4594:db4e7fecad0d iso-areas

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Updated SLD templates for sounding results and differences.
author Raimund Renkert <raimund@renkert.org>
date Wed, 09 Oct 2019 13:29:43 +0200
parents 5466562cca60
children 2440d2f86f4e
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
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
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
94 $1,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
95 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
96 ('AT', 'XXX', '00001', '00000', 2)::isrs),
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
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
c4db392130a4 Fix test
Tom Gottfried <tom@intevation.de>
parents: 1995
diff changeset
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');