Mercurial > gemma

annotate schema/auth_tests.sql @ 3024:5470aa3ffb9a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix privileges for GeoServer views
author Tom Gottfried <tom@intevation.de>
date Fri, 12 Apr 2019 11:41:24 +0200
parents 4374d942b23d
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');