Mercurial > gemma

annotate schema/auth_tests.sql @ 3301:6514b943654e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Re-enable checking of gauge availability This partly reverts rev. 1cb6676d1510, which removed this code in favour of handling database errors later. The thinko in this was that possibly many NtS messages for the same gauge would lead to many errors for a single reason and the same amount of unnecessary database round-trips. Checking whether the current user is allowed to import data for a gauge now also handles sys_admin correctly and the import stops with status unchanged if no such gauge is available.
author Tom Gottfried <tom@intevation.de>
date Thu, 16 May 2019 17:22:33 +0200
parents 5470aa3ffb9a
children ec6163c6687d
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
3024
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
18 CREATE FUNCTION test_privs() RETURNS SETOF TEXT AS
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
19 $$
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
20 DECLARE the_schema CONSTANT varchar = 'waterway';
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
21 DECLARE the_table varchar;
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
22 BEGIN
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
23 FOR the_table IN
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
24 SELECT table_name
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
25 FROM information_schema.tables
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
26 WHERE table_schema = the_schema
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
27 LOOP
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
28 RETURN NEXT table_privs_are(
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
29 the_schema,
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
30 the_table,
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
31 'waterway_user',
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
32 ARRAY['SELECT'],
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
33 format('waterway_user can SELECT from %I.%I',
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
34 the_schema, the_table));
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
35 END LOOP;
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
36 END;
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
37 $$ LANGUAGE plpgsql;
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
38 SELECT * FROM test_privs();
5470aa3ffb9a Fix privileges for GeoServer views
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
39
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 --
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 -- Run tests as unprivileged user
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
42 --
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
43 SET SESSION AUTHORIZATION test_user_at;
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
45 SELECT throws_ok($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
46 CREATE TABLE test()
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
47 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
48 42501, NULL,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
49 'No objects can be created');
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
50
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
51 SELECT isnt_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
52 SELECT * FROM waterway.bottlenecks
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
53 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
54 'Staged data should be visible');
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
56 SELECT is_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
57 SELECT * FROM waterway.bottlenecks WHERE NOT staging_done
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
58 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
59 'Only staged data should be visible');
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
61 SELECT isnt_empty($$
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
62 SELECT * FROM users.templates
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
63 $$,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
64 'User should see templates associated to his country');
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
65
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
66 SELECT ok(
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
67 users.current_user_country() = ALL(
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
68 SELECT country FROM users.templates),
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
69 'User should only see templates associated to his country');
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
70
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 --
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
72 -- Run tests as waterway administrator
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
73 --
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
74 SET SESSION AUTHORIZATION test_admin_at;
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
75
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
76 PREPARE bn_insert (varchar, geometry(MULTIPOLYGON, 4326)) AS
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
77 INSERT INTO waterway.bottlenecks (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
78 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
79 revisiting_time, limiting, source_organization)
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
80 VALUES (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
81 $1,
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
82 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
83 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
84 ('AT', 'XXX', '00001', '00000', 2)::isrs),
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
85 $2, 'AT', 'AT', 'AT',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
86 1, 'depth', 'testorganization'
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
87 );
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
88 SELECT lives_ok($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
89 EXECUTE bn_insert(
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
90 'test1',
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
91 ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326))
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
92 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
93 'Waterway admin can insert data within his region');
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
94 SELECT throws_ok($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
95 EXECUTE bn_insert(
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
96 'test2',
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
97 ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326))
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
98 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
99 42501, NULL,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
100 'Waterway admin cannot insert data outside his region');
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
101
177
4e2451d561b1 Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents: 175
diff changeset
102 -- template management
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
103 SELECT results_eq($$
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
104 SELECT users.current_user_country()
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
105 $$,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
106 $$
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
107 INSERT INTO users.templates (template_name, template_data)
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
108 VALUES ('New AT', '\x')
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
109 RETURNING country
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
110 $$,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
111 'Waterway admin can add templates for his country');
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
112
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
113 SELECT throws_ok($$
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
114 INSERT INTO users.templates (template_name, template_data, country)
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
115 VALUES ('New RO', '\x', 'RO')
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
116 $$,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
117 42501, NULL,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
118 'Waterway admin cannot add template for other country');
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
119
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
120 SELECT isnt_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
121 UPDATE users.templates SET template_data = '\xDABE'
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
122 WHERE template_name = 'AT' RETURNING *
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
123 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
124 'Waterway admin can alter templates for own country');
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
125
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
126 SELECT is_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
127 UPDATE users.templates SET template_data = '\xDABE'
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
128 WHERE template_name = 'RO' RETURNING *
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
129 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
130 'Waterway admin cannot alter templates for other country');
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
131
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
132 SELECT isnt_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
133 DELETE FROM users.templates WHERE template_name = 'AT' RETURNING *
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
134 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
135 'Waterway admin can delete templates for own country');
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
136
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
137 SELECT is_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
138 DELETE FROM users.templates WHERE template_name = 'RO' RETURNING *
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
139 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
140 'Waterway admin cannot delete templates for other country');
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
141
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
142 -- import management
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
143 SELECT lives_ok($$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
144 WITH
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
145 job AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
146 INSERT INTO import.imports (kind, username, data) VALUES (
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
147 'test', current_user, 'test') RETURNING id),
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
148 log AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
149 INSERT INTO import.import_logs (import_id, msg)
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
150 SELECT id, 'test' FROM job)
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
151 INSERT INTO import.track_imports
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
152 SELECT id, 'waterway.bottlenecks', 0 FROM job
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
153 $$,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
154 'Waterway admin can add import job and related data');
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
155
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
156 SET SESSION AUTHORIZATION test_admin_at2;
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
157 SELECT bag_has($$
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
158 SELECT username FROM users.list_users
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
159 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
160 $$
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
161 WITH job AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
162 UPDATE import.imports SET state = 'accepted'
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
163 RETURNING id, username),
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
164 log AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
165 INSERT INTO import.import_logs (import_id, msg)
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
166 SELECT id, 'test continued' FROM job)
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
167 SELECT username FROM job
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
168 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
169 'Waterway admin can edit import jobs from his country only');
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
170
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
171 SELECT lives_ok($$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
172 WITH
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
173 config AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
174 INSERT INTO import.import_configuration (kind, username) VALUES (
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
175 'test', current_user) RETURNING id)
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
176 INSERT INTO import.import_configuration_attributes
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
177 SELECT id, 'test key', 'test value' FROM config
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
178 $$,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
179 'Waterway admin can add import config and related data');
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
180
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
181 SET SESSION AUTHORIZATION test_admin_at;
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
182 SELECT bag_has($$
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
183 SELECT username FROM users.list_users
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
184 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
185 $$
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
186 WITH config AS (
2070
c4db392130a4 Fix test
Tom Gottfried <tom@intevation.de>
parents: 1995
diff changeset
187 UPDATE import.import_configuration SET kind = 'test'
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
188 RETURNING id, username),
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
189 attrib AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
190 INSERT INTO import.import_configuration_attributes
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
191 SELECT id, 'test continued', 'test value' FROM config),
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
192 attrib_upd AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
193 UPDATE import.import_configuration_attributes SET v = 'test v'
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
194 WHERE import_configuration_id = (SELECT id FROM config))
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
195 SELECT username FROM config
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
196 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
197 'Waterway admin can edit import config from his country only');
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
198
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
199 SET SESSION AUTHORIZATION test_admin_ro;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
200 SELECT throws_ok($$
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
201 INSERT INTO import.import_logs (import_id, msg)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
202 VALUES (currval(pg_get_serial_sequence('import.imports', 'id')),
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
203 'test')
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
204 $$,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
205 42501, NULL,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
206 'Waterway admin cannot add log messages to other countries imports');
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
207
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
208 SELECT throws_ok($$
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
209 DELETE FROM import.track_imports
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
210 WHERE import_id = currval(
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
211 pg_get_serial_sequence('import.imports', 'id'))
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
212 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
213 42501, NULL,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
214 'Waterway admin cannot delete tracking data of other countries imports');
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
215
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
216 SELECT throws_ok($$
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
217 INSERT INTO import.import_configuration_attributes
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
218 VALUES (currval(pg_get_serial_sequence(
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
219 'import.import_configuration', 'id')),
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
220 'test', 'test value')
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
221 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
222 42501, NULL,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
223 'Waterway admin cannot add attributes to other countries import config');
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
224
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
225 SELECT throws_ok($$
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
226 UPDATE import.import_configuration_attributes SET v = 'evil'
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
227 WHERE import_configuration_id = currval(
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
228 pg_get_serial_sequence('import.import_configuration', 'id'))
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
229 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
230 42501, NULL,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
231 'Waterway admin cannot overwrite attributes of other countries config');