Mercurial > gemma

annotate schema/auth_tests.sql @ 2455:54c9fe587fe6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.
author Tom Gottfried <tom@intevation.de>
date Fri, 01 Mar 2019 18:38:02 +0100
parents 4374d942b23d
children 5470aa3ffb9a
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
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 --
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 -- Run tests as unprivileged user
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
20 --
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
21 SET SESSION AUTHORIZATION test_user_at;
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
23 SELECT throws_ok($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
24 CREATE TABLE test()
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
25 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
26 42501, NULL,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
27 'No objects can be created');
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
28
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
29 SELECT isnt_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
30 SELECT * FROM waterway.bottlenecks
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
31 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
32 'Staged data should be visible');
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
34 SELECT is_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
35 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
36 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
37 'Only staged data should be visible');
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
39 SELECT isnt_empty($$
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
40 SELECT * FROM users.templates
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
41 $$,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
42 '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
43
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
44 SELECT ok(
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
45 users.current_user_country() = ALL(
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
46 SELECT country FROM users.templates),
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
47 '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
48
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 --
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
50 -- Run tests as waterway administrator
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
51 --
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
52 SET SESSION AUTHORIZATION test_admin_at;
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
53
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
54 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
55 INSERT INTO waterway.bottlenecks (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
56 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
57 revisiting_time, limiting, source_organization)
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
58 VALUES (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
59 $1,
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
60 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
61 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
62 ('AT', 'XXX', '00001', '00000', 2)::isrs),
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
63 $2, 'AT', 'AT', 'AT',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
64 1, 'depth', 'testorganization'
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
65 );
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
66 SELECT lives_ok($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
67 EXECUTE bn_insert(
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
68 'test1',
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
69 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
70 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
71 '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
72 SELECT throws_ok($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
73 EXECUTE bn_insert(
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
74 'test2',
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
75 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
76 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
77 42501, NULL,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
78 '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
79
177
4e2451d561b1 Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents: 175
diff changeset
80 -- template management
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
81 SELECT results_eq($$
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
82 SELECT users.current_user_country()
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
83 $$,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
84 $$
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
85 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
86 VALUES ('New AT', '\x')
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
87 RETURNING country
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
88 $$,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
89 '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
90
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
91 SELECT throws_ok($$
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
92 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
93 VALUES ('New RO', '\x', 'RO')
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
94 $$,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
95 42501, NULL,
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
96 '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
97
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
98 SELECT isnt_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
99 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
100 WHERE template_name = 'AT' RETURNING *
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 '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
103
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
104 SELECT is_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
105 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
106 WHERE template_name = 'RO' RETURNING *
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
107 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
108 '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
109
1853
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
110 SELECT isnt_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
111 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
112 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
113 '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
114
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
115 SELECT is_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
116 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
117 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable
Tom Gottfried <tom@intevation.de>
parents: 1339
diff changeset
118 '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
119
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
120 -- import management
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
121 SELECT lives_ok($$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
122 WITH
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
123 job AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
124 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
125 'test', current_user, 'test') RETURNING id),
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
126 log AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
127 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
128 SELECT id, 'test' FROM job)
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
129 INSERT INTO import.track_imports
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
130 SELECT id, 'waterway.bottlenecks', 0 FROM job
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
131 $$,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
132 '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
133
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
134 SET SESSION AUTHORIZATION test_admin_at2;
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
135 SELECT bag_has($$
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
136 SELECT username FROM users.list_users
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
137 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
138 $$
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
139 WITH job AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
140 UPDATE import.imports SET state = 'accepted'
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
141 RETURNING id, username),
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
142 log AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
143 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
144 SELECT id, 'test continued' FROM job)
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
145 SELECT username FROM job
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
146 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
147 '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
148
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
149 SELECT lives_ok($$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
150 WITH
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
151 config AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
152 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
153 'test', current_user) RETURNING id)
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
154 INSERT INTO import.import_configuration_attributes
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
155 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
156 $$,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
157 '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
158
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
159 SET SESSION AUTHORIZATION test_admin_at;
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
160 SELECT bag_has($$
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
161 SELECT username FROM users.list_users
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
162 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
163 $$
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
164 WITH config AS (
2070
c4db392130a4 Fix test
Tom Gottfried <tom@intevation.de>
parents: 1995
diff changeset
165 UPDATE import.import_configuration SET kind = 'test'
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
166 RETURNING id, username),
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
167 attrib AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
168 INSERT INTO import.import_configuration_attributes
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
169 SELECT id, 'test continued', 'test value' FROM config),
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
170 attrib_upd AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
171 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
172 WHERE import_configuration_id = (SELECT id FROM config))
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
173 SELECT username FROM config
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
174 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
175 '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
176
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
177 SET SESSION AUTHORIZATION test_admin_ro;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
178 SELECT throws_ok($$
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
179 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
180 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
181 'test')
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
182 $$,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
183 42501, NULL,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1853
diff changeset
184 '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
185
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
186 SELECT throws_ok($$
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
187 DELETE FROM import.track_imports
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
188 WHERE import_id = currval(
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
189 pg_get_serial_sequence('import.imports', 'id'))
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
190 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
191 42501, NULL,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
192 '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
193
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
194 SELECT throws_ok($$
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
195 INSERT INTO import.import_configuration_attributes
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
196 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
197 'import.import_configuration', 'id')),
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
198 'test', 'test value')
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
199 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
200 42501, NULL,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
201 '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
202
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
203 SELECT throws_ok($$
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
204 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
205 WHERE import_configuration_id = currval(
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
206 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
207 $$,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
208 42501, NULL,
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
209 'Waterway admin cannot overwrite attributes of other countries config');