Mercurial > gemma
view schema/manage_users_tests.sql @ 5654:358130887c72
Added tag v5.7.1 for changeset 9b3779f649c2
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Tue, 08 Aug 2023 15:07:00 +0200 |
parents | b33121a54793 |
children |
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, 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- -- pgTAP test script for user management functions -- SET search_path TO public, gemma, gemma_waterway, gemma_fairway; SET SESSION AUTHORIZATION test_user_at; -- -- Utility functions -- SELECT results_eq($$ SELECT ST_SRID(users.current_user_area_utm()) $$, $$ SELECT best_utm(ST_Collect(area::geometry)) FROM users.stretches st JOIN users.stretch_countries stc ON stc.stretch_id = st.id WHERE country = users.user_country() $$, 'Geometry has SRID corresponding to best_utm()'); SELECT ok( ST_IsValid(users.current_user_area_utm()), 'Returns valid geometry for stretches that touch each other'); -- -- Role listing -- SELECT results_eq($$ SELECT username FROM users.list_users $$, $$ SELECT CAST(current_user AS varchar) $$, 'User should only see his own profile'); SET SESSION AUTHORIZATION test_admin_at; SELECT set_eq($$ SELECT DISTINCT country FROM users.list_users $$, ARRAY['AT'], 'Waterway admin should only see profiles of his country'); SET SESSION AUTHORIZATION test_sys_admin1; SELECT set_eq($$ SELECT username FROM users.list_users $$, ARRAY[ 'sysadmin', 'test_admin_at', 'test_admin_at2', 'test_admin_ro', 'test_sys_admin1', 'test_user_at', 'test_user_ro' ], 'System admin can see all users'); -- -- Role creation -- SELECT lives_ok($$ INSERT INTO users.list_users VALUES ( 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1', false, true) $$, 'New waterway user can be added'); SELECT results_eq($$ SELECT pg_has_role('metamorph', 'test1', 'MEMBER') $$, $$ SELECT true $$, 'New role is GRANTed to metamorph after creation'); SELECT throws_ok($$ INSERT INTO users.list_users VALUES ( 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2') $$, 42704, NULL, 'Valid role name has to be provided'); SELECT throws_ok($$ INSERT INTO users.list_users VALUES ( 'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3') $$, 23502, NULL, 'username is mandatory'); -- Though other arguments are mandatory, too, there are no explicit tests SELECT throws_ok($$ INSERT INTO users.list_users VALUES ( 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4') $$, 42710, NULL, 'No existing role name is allowed'); SELECT throws_ok($$ INSERT INTO users.list_users VALUES ( 'waterway_user', 'Test Nutzer AT, Test User RO, Täst Nützer ÄT, Täst Üser RÖ', 'secret1$', 'AT', NULL, 'test4', false, true) $$, 23514, NULL, 'User name length is restricted to 63 bytes'); -- Test password policy SELECT throws_ok($$ INSERT INTO users.list_users VALUES ( 'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2') $$, '28P01', NULL, 'Password with less than 8 characters is not accepted'); SELECT throws_ok($$ INSERT INTO users.list_users VALUES ( 'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2') $$, '28P01', NULL, 'Password without non-alphanumeric character is not accepted'); SELECT throws_ok($$ INSERT INTO users.list_users VALUES ( 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2') $$, '28P01', NULL, 'Password without digit is not accepted'); -- -- Role update -- SET SESSION AUTHORIZATION test_user_at; SELECT results_eq($$ UPDATE users.list_users SET (pw, map_extent, email_address) = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test') RETURNING username $$, $$ SELECT CAST('test_user_at' AS varchar) $$, 'Waterway user can update own password, map extent and email address'); SELECT throws_ok($$ UPDATE users.list_users SET username = 'test_rename', rolname = 'test' $$, 42501, NULL, 'Waterway user cannot update arbitrary user attributes'); SET SESSION AUTHORIZATION test_admin_at; SELECT results_eq($$ UPDATE users.list_users SET (pw, map_extent, email_address) = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test') WHERE country = users.user_country() AND username <> current_user RETURNING * $$, $$ SELECT '' WHERE false -- Empty result set $$, 'Waterway admin cannot update attributes of other users in country'); -- The above test will pass even if the password is actually updated in case -- a trigger returns NULL after ALTER ROLE ... PASSWORD ... has been executed. RESET SESSION AUTHORIZATION; CREATE TEMP TABLE old_pw_hash AS SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'; SET SESSION AUTHORIZATION test_admin_at; UPDATE users.list_users SET pw = 'test_user_at2!' WHERE username = 'test_user_at'; RESET SESSION AUTHORIZATION; SELECT set_eq($$ SELECT rolpassword FROM old_pw_hash $$, $$ SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at' $$, 'Waterway admin cannot update password of other users in country'); SET SESSION AUTHORIZATION test_sys_admin1; SELECT lives_ok($$ INSERT INTO users.list_users VALUES ( 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2', false, true); UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('waterway_user', 'test2_new', 'new_secret1$', 'AT', (SELECT map_extent FROM users.list_users WHERE username = 'test_user_at'), 'test5') WHERE username = 'test2' $$, 'Existing user can be updated'); SELECT throws_ok($$ UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('waterway_user', 'test_new_name', 'secret1$', 'AT', (SELECT map_extent FROM users.list_users WHERE username = 'test_user_at'), 'test6') WHERE username = CAST(current_user AS varchar) $$, '0A000', NULL, 'Name of current user cannot be altered'); SELECT throws_ok($$ UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('invalid', 'test2', 'secret1$', 'AT', (SELECT map_extent FROM users.list_users WHERE username = 'test_user_at'), 'test2') WHERE username = 'test_user_at' $$, 42704, NULL, 'Valid role name has to be provided'); SELECT throws_ok($$ UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('waterway_user', NULL, 'secret1$', 'AT', (SELECT map_extent FROM users.list_users WHERE username = 'test_user_at'), 'test3') WHERE username = 'test_user_at' $$, 23502, NULL, 'New username is mandatory'); -- Though other arguments are mandatory, too, there are no explicit tests SELECT throws_ok($$ UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('waterway_user', 'waterway_user', 'secret1$', 'AT', (SELECT map_extent FROM users.list_users WHERE username = 'test_user_at'), 'test4') WHERE username = 'test_user_at' $$, 42710, NULL, 'Reserved role names cannot be used as username'); SELECT throws_ok($$ UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('waterway_user', 'test_user_ro', 'secret1$', 'AT', (SELECT map_extent FROM users.list_users WHERE username = 'test_user_at'), 'test4') WHERE username = 'test_user_at' $$, 42710, NULL, 'No existing role name is allowed'); -- Test password policy (only one rule to ensure it's also used on update) SELECT throws_ok($$ UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('waterway_user', 'test_user_at', 'secret', 'AT', (SELECT map_extent FROM users.list_users WHERE username = 'test_user_at'), 'test4') WHERE username = 'test_user_at' $$, '28P01', NULL, 'Non-compliant password is not accepted'); -- To compare passwords, we need to run the following tests as superuser RESET SESSION AUTHORIZATION; UPDATE old_pw_hash SET rolpassword = ( SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'); UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('waterway_user', 'test_user_at', NULL, 'AT', (SELECT map_extent FROM internal.user_profiles WHERE username = 'test_user_at'), 'xxx') WHERE username = 'test_user_at'; SELECT set_eq($$ SELECT rolpassword FROM old_pw_hash $$, $$ SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at' $$, 'Giving NULL password does not change password'); UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('waterway_user', 'test_user_at', '', 'AT', (SELECT map_extent FROM internal.user_profiles WHERE username = 'test_user_at'), 'xxx') WHERE username = 'test_user_at'; SELECT set_eq($$ SELECT rolpassword FROM old_pw_hash $$, $$ SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at' $$, 'Giving empty string as password does not change password'); UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT', (SELECT map_extent FROM internal.user_profiles WHERE username = 'test_user_at'), 'xxx') WHERE username = 'test_user_at'; SELECT set_ne($$ SELECT rolpassword FROM old_pw_hash $$, $$ SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at' $$, 'Giving a non-empty password string changes password'); SET SESSION AUTHORIZATION test_sys_admin1; -- -- Role deletion -- -- Note: backend termination is not tested in the following. -- See also comments in function definition. SELECT lives_ok($$ INSERT INTO users.list_users VALUES ( 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3', false, true); DELETE FROM users.list_users WHERE username = 'test3' $$, 'Existing user can be deleted'); SELECT throws_ok($$ DELETE FROM users.list_users WHERE username = CAST(current_user AS varchar) $$, 55006, NULL, 'Current user cannot be deleted');