Mercurial > gemma
view schema/auth_tests.sql @ 4615:32d3e0cecf4f geoserver_sql_views
Merge default into geoserver_sql_views
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 09 Sep 2019 17:46:51 +0200 |
parents | 5466562cca60 |
children | 2440d2f86f4e |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- -- pgTAP test script for privileges and RLS policies -- -- Helper function: CREATE OR REPLACE FUNCTION users.current_user_country() RETURNS internal.user_profiles.country%TYPE AS $$ SELECT country FROM users.list_users WHERE username = current_user $$ LANGUAGE SQL STABLE PARALLEL SAFE; CREATE FUNCTION test_privs() RETURNS SETOF TEXT AS $$ DECLARE the_schema CONSTANT varchar = 'waterway'; DECLARE the_table varchar; BEGIN FOR the_table IN SELECT table_name FROM information_schema.tables WHERE table_schema = the_schema LOOP RETURN NEXT table_privs_are( the_schema, the_table, 'waterway_user', ARRAY['SELECT'], format('waterway_user can SELECT from %I.%I', the_schema, the_table)); END LOOP; END; $$ LANGUAGE plpgsql; SELECT * FROM test_privs(); -- -- Run tests as unprivileged user -- SET SESSION AUTHORIZATION test_user_at; SELECT throws_ok($$ CREATE TABLE test() $$, 42501, NULL, 'No objects can be created'); SELECT isnt_empty($$ SELECT * FROM waterway.bottlenecks $$, 'Staged data should be visible'); SELECT is_empty($$ SELECT * FROM waterway.bottlenecks WHERE NOT staging_done $$, 'Only staged data should be visible'); SELECT isnt_empty($$ SELECT * FROM users.templates $$, 'User should see templates associated to his country'); SELECT ok( users.current_user_country() = ALL( SELECT country FROM users.templates), 'User should only see templates associated to his country'); -- -- Run tests as waterway administrator -- SET SESSION AUTHORIZATION test_admin_at; PREPARE bn_insert (varchar, geometry(MULTIPOLYGON, 4326)) AS INSERT INTO waterway.bottlenecks ( gauge_location, validity, bottleneck_id, stretch, area, rb, lb, responsible_country, revisiting_time, limiting, date_info, source_organization) SELECT location, validity, $1, isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), $2, 'AT', 'AT', 'AT', 1, 'depth', current_timestamp, 'testorganization' FROM waterway.gauges WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs AND NOT erased; SELECT lives_ok($$ EXECUTE bn_insert( 'test1', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)) $$, 'Waterway admin can insert data within his region'); SELECT throws_ok($$ EXECUTE bn_insert( 'test2', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326)) $$, 42501, NULL, 'Waterway admin cannot insert data outside his region'); -- template management SELECT lives_ok($$ INSERT INTO users.templates (template_name, template_data, country) VALUES ('New AT', '\x', 'AT') $$, 'Waterway admin can add templates for his country'); SELECT throws_ok($$ INSERT INTO users.templates (template_name, template_data, country) VALUES ('New RO', '\x', 'RO') $$, 42501, NULL, 'Waterway admin cannot add template for other country'); SELECT isnt_empty($$ UPDATE users.templates SET template_data = '\xDABE' WHERE template_name = 'AT' RETURNING * $$, 'Waterway admin can alter templates for own country'); SELECT is_empty($$ UPDATE users.templates SET template_data = '\xDABE' WHERE template_name = 'RO' RETURNING * $$, 'Waterway admin cannot alter templates for other country'); SELECT isnt_empty($$ DELETE FROM users.templates WHERE template_name = 'AT' RETURNING * $$, 'Waterway admin can delete templates for own country'); SELECT is_empty($$ DELETE FROM users.templates WHERE template_name = 'RO' RETURNING * $$, 'Waterway admin cannot delete templates for other country'); -- import management SELECT lives_ok($$ WITH job AS ( INSERT INTO import.imports (kind, username, data) VALUES ( 'test', current_user, 'test') RETURNING id), log AS ( INSERT INTO import.import_logs (import_id, msg) SELECT id, 'test' FROM job) INSERT INTO import.track_imports (import_id, relation, key) SELECT id, 'waterway.bottlenecks', 0 FROM job $$, 'Waterway admin can add import job and related data'); SET SESSION AUTHORIZATION test_admin_at2; SELECT bag_has($$ SELECT username FROM users.list_users $$, $$ WITH job AS ( UPDATE import.imports SET state = 'accepted' RETURNING id, username), log AS ( INSERT INTO import.import_logs (import_id, msg) SELECT id, 'test continued' FROM job) SELECT username FROM job $$, 'Waterway admin can edit import jobs from his country only'); SELECT lives_ok($$ WITH config AS ( INSERT INTO import.import_configuration (kind, username) VALUES ( 'test', current_user) RETURNING id) INSERT INTO import.import_configuration_attributes SELECT id, 'test key', 'test value' FROM config $$, 'Waterway admin can add import config and related data'); SET SESSION AUTHORIZATION test_admin_at; SELECT bag_has($$ SELECT username FROM users.list_users $$, $$ WITH config AS ( UPDATE import.import_configuration SET kind = 'test' RETURNING id, username), attrib AS ( INSERT INTO import.import_configuration_attributes SELECT id, 'test continued', 'test value' FROM config), attrib_upd AS ( UPDATE import.import_configuration_attributes SET v = 'test v' WHERE import_configuration_id = (SELECT id FROM config)) SELECT username FROM config $$, 'Waterway admin can edit import config from his country only'); SET SESSION AUTHORIZATION test_admin_ro; SELECT throws_ok($$ INSERT INTO import.import_logs (import_id, msg) VALUES (currval(pg_get_serial_sequence('import.imports', 'id')), 'test') $$, 42501, NULL, 'Waterway admin cannot add log messages to other countries imports'); SELECT throws_ok($$ DELETE FROM import.track_imports WHERE import_id = currval( pg_get_serial_sequence('import.imports', 'id')) $$, 42501, NULL, 'Waterway admin cannot delete tracking data of other countries imports'); SELECT throws_ok($$ INSERT INTO import.import_configuration_attributes VALUES (currval(pg_get_serial_sequence( 'import.import_configuration', 'id')), 'test', 'test value') $$, 42501, NULL, 'Waterway admin cannot add attributes to other countries import config'); SELECT throws_ok($$ UPDATE import.import_configuration_attributes SET v = 'evil' WHERE import_configuration_id = currval( pg_get_serial_sequence('import.import_configuration', 'id')) $$, 42501, NULL, 'Waterway admin cannot overwrite attributes of other countries config');