Mercurial > gemma
view schema/manage_users_tests.sql @ 253:322c3d0e05ef
The column in sys_admin.list_users is called rolname not rolename.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 27 Jul 2018 12:12:48 +0200 |
parents | 946baea3d280 |
children | 92470caf81fd |
line wrap: on
line source
-- -- pgTAP test script for user management functions -- SET search_path TO public, gemma, gemma_waterway, gemma_fairway; SET SESSION AUTHORIZATION test_admin_at; SELECT throws_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test0', 'secret', 'AT', NULL, 'test0') $$, 42501, NULL, 'Less privileged user cannot call function in schema sys_admin'); SET SESSION AUTHORIZATION test_sys_admin1; -- -- Role listing -- SELECT isnt_empty($$ SELECT * FROM sys_admin.list_users $$, 'List of users can be queried'); -- -- Role creation -- SELECT lives_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test1', 'secret', 'AT', NULL, 'test1') $$, 'New waterway user can be added'); SELECT throws_ok($$ SELECT sys_admin.create_user( 'invalid', 'test2', 'secret', 'AT', NULL, 'test2') $$, 42704, NULL, 'Valid role name has to be provided'); SELECT throws_ok($$ SELECT sys_admin.create_user( 'waterway_user', NULL, 'secret', 'AT', NULL, 'test3') $$, 23502, NULL, 'username is mandatory'); -- Though other arguments are mandatory, too, there are no explicit tests SELECT throws_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'waterway_user', 'secret', 'AT', NULL, 'test4') $$, 42710, NULL, 'Reserved role names cannot be used as username'); SELECT throws_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test_user_at', 'secret', 'AT', NULL, 'test4') $$, 23505, NULL, 'No duplicate user name is allowed'); -- -- Role update -- SELECT lives_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test2', 'secret', 'AT', NULL, 'test2'); SELECT sys_admin.update_user('test2', 'waterway_user', 'test2_new', 'new_secret', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test5') $$, 'Existing user can be updated'); SELECT throws_ok($$ SELECT sys_admin.update_user('test_non_existent', 'waterway_user', 'test_non_existent', '', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test5') $$, 42704, NULL, 'Non-existent user cannot be updated'); SELECT throws_ok($$ SELECT sys_admin.update_user(CAST(current_user AS varchar), 'waterway_user', 'test_new_name', 'secret', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test6') $$, '0A000', NULL, 'Name of current user cannot be altered'); SELECT throws_ok($$ SELECT sys_admin.update_user('test_user_at', 'invalid', 'test2', 'secret', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test2') $$, 42704, NULL, 'Valid role name has to be provided'); SELECT throws_ok($$ SELECT sys_admin.update_user('test_user_at', 'waterway_user', NULL, 'secret', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test3') $$, 23502, NULL, 'New username is mandatory'); -- Though other arguments are mandatory, too, there are no explicit tests SELECT throws_ok($$ SELECT sys_admin.update_user('test_user_at', 'waterway_user', 'waterway_user', 'secret', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test4') $$, 42710, NULL, 'Reserved role names cannot be used as username'); SELECT throws_ok($$ SELECT sys_admin.update_user('test_user_at', 'waterway_user', 'test_user_ro', 'secret', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test4') $$, 23505, NULL, 'No duplicate user name is allowed'); -- To compare passwords, we need to run the following tests as superuser RESET SESSION AUTHORIZATION; CREATE TEMP TABLE old_pw_hash AS SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'; SELECT sys_admin.update_user('test_user_at', 'waterway_user', 'test_user_at', NULL, 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'xxx'); 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'); SELECT sys_admin.update_user('test_user_at', 'waterway_user', 'test_user_at', '', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'xxx'); 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'); SELECT sys_admin.update_user('test_user_at', 'waterway_user', 'test_user_at', 'new_pw', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'xxx'); 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($$ SELECT sys_admin.create_user( 'waterway_user', 'test3', 'secret', 'AT', NULL, 'test3'); SELECT sys_admin.delete_user('test3') $$, 'Existing user can be deleted'); SELECT throws_ok($$ SELECT sys_admin.delete_user('test_non_existent') $$, 42704, NULL, 'Non-existent user cannot be deleted'); SELECT throws_ok($$ SELECT sys_admin.delete_user(CAST(current_user AS varchar)) $$, 55006, NULL, 'Current user cannot be deleted');