gemma: schema/auth_tests.sql annotate

annotate schema/auth_tests.sql @ 3678:8f58851927c0

client: make layer factory only return new layer config for individual maps instead of each time it is invoked. The purpose of the factory was to support multiple maps with individual layers. But returning a new config each time it is invoked leads to bugs that rely on the layer's state. Now this factory reuses the same objects it created before, per map.

author	Markus Kottlaender <markus@intevation.de>
date	Mon, 17 Jun 2019 17:31:35 +0200
parents	29ef6d41e4af
children	1ecfe5d015b0

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 (
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	78 gauge_location, gauge_validity, validity,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3024 diff changeset	79 bottleneck_id, stretch, area, rb, lb, responsible_country,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	80 revisiting_time, limiting, date_info, source_organization)
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3024 diff changeset	81 SELECT
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	82 location, validity, validity,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 181 diff changeset	83 $1,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 181 diff changeset	84 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 181 diff changeset	85 ('AT', 'XXX', '00001', '00000', 2)::isrs),
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 181 diff changeset	86 $2, 'AT', 'AT', 'AT',
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	87 1, 'depth', current_timestamp, 'testorganization'
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3024 diff changeset	88 FROM waterway.gauges
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	89 WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	90 AND NOT erased;
1853 cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	91 SELECT lives_ok($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	92 EXECUTE bn_insert(
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	93 'test1',
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	94 ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326))
1853 cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	95 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	96 'Waterway admin can insert data within his region');
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	97 SELECT throws_ok($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	98 EXECUTE bn_insert(
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	99 'test2',
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	100 ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326))
1853 cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	101 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	102 42501, NULL,
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	103 'Waterway admin cannot insert data outside his region');
106 ae5bb7a979ff Test RLS policy for responsibility area. Tom Gottfried <tom@intevation.de> parents: 104 diff changeset	104
177 4e2451d561b1 Make schema for templates more realistic Tom Gottfried <tom@intevation.de> parents: 175 diff changeset	105 -- template management
2230 4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	106 SELECT results_eq($$
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	107 SELECT users.current_user_country()
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	108 $$,
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	109 $$
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	110 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	111 VALUES ('New AT', '\x')
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	112 RETURNING country
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	113 $$,
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	114 '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	115
2230 4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	116 SELECT throws_ok($$
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	117 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	118 VALUES ('New RO', '\x', 'RO')
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	119 $$,
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	120 42501, NULL,
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	121 '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	122
1853 cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	123 SELECT isnt_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	124 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	125 WHERE template_name = 'AT' RETURNING *
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	126 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	127 '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	128
1853 cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	129 SELECT is_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	130 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	131 WHERE template_name = 'RO' RETURNING *
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	132 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	133 '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	134
1853 cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	135 SELECT isnt_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	136 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	137 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	138 '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	139
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	140 SELECT is_empty($$
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	141 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	142 $$,
cedadd4e3db5 Use dollar quoting to make tested statements more readable Tom Gottfried <tom@intevation.de> parents: 1339 diff changeset	143 '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	144
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	145 -- import management
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	146 SELECT lives_ok($$
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	147 WITH
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	148 job AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	149 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	150 'test', current_user, 'test') RETURNING id),
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	151 log 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_logs (import_id, msg)
1873 9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	153 SELECT id, 'test' FROM job)
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	154 INSERT INTO import.track_imports
1873 9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	155 SELECT id, 'waterway.bottlenecks', 0 FROM job
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 job 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_at2;
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 job AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	165 UPDATE import.imports SET state = 'accepted'
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 log 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_logs (import_id, msg)
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	169 SELECT id, 'test continued' FROM job)
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	170 SELECT username FROM job
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	171 $$,
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	172 '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	173
1873 9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	174 SELECT lives_ok($$
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	175 WITH
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	176 config AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	177 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	178 'test', current_user) RETURNING id)
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	179 INSERT INTO import.import_configuration_attributes
1873 9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	180 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	181 $$,
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	182 '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	183
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	184 SET SESSION AUTHORIZATION test_admin_at;
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	185 SELECT bag_has($$
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	186 SELECT username FROM users.list_users
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	187 $$,
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	188 $$
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	189 WITH config AS (
2070 c4db392130a4 Fix test Tom Gottfried <tom@intevation.de> parents: 1995 diff changeset	190 UPDATE import.import_configuration SET kind = 'test'
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	191 RETURNING id, username),
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	192 attrib AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	193 INSERT INTO import.import_configuration_attributes
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	194 SELECT id, 'test continued', 'test value' FROM config),
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	195 attrib_upd AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	196 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	197 WHERE import_configuration_id = (SELECT id FROM config))
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	198 SELECT username FROM config
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 '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	201
1873 9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	202 SET SESSION AUTHORIZATION test_admin_ro;
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 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 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	205 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	206 'test')
1873 9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	207 $$,
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	208 42501, NULL,
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1853 diff changeset	209 '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	210
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	211 SELECT throws_ok($$
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	212 DELETE FROM import.track_imports
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	213 WHERE import_id = currval(
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	214 pg_get_serial_sequence('import.imports', 'id'))
1904 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 42501, NULL,
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	217 '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	218
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	219 SELECT throws_ok($$
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	220 INSERT INTO import.import_configuration_attributes
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	221 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	222 'import.import_configuration', 'id')),
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	223 'test', 'test value')
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 42501, NULL,
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	226 '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	227
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	228 SELECT throws_ok($$
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	229 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	230 WHERE import_configuration_id = currval(
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	231 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	232 $$,
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	233 42501, NULL,
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	234 'Waterway admin cannot overwrite attributes of other countries config');

Mercurial > gemma

annotate schema/auth_tests.sql @ 3678:8f58851927c0