Mercurial > gemma
view schema/manage_users_tests.sql @ 268:72062ca52746
Make user_profiles table invisible for users
users.list_users should be the single point to access user profile data.
Keeping user_profiles visible would imply having to maintain RLS policies
that are otherwise obsolete.
Tests run as superuser still use user_profiles, because list_users does
not show any data to a superuser.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 30 Jul 2018 11:38:09 +0200 |
parents | 13ad969a9138 |
children | 750a9c9cd965 |
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', 'secret1$', 'AT', NULL, 'test0') $$, 42501, NULL, 'Less privileged user cannot call function in schema sys_admin'); -- -- Role listing -- SET SESSION AUTHORIZATION test_user_at; 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 count(*) FROM users.list_users $$, ARRAY[4], 'System admin can see all users'); -- -- Role creation -- SELECT lives_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1') $$, 'New waterway user can be added'); SELECT throws_ok($$ SELECT sys_admin.create_user( 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2') $$, 42704, NULL, 'Valid role name has to be provided'); SELECT throws_ok($$ SELECT sys_admin.create_user( '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($$ SELECT sys_admin.create_user( 'waterway_user', 'waterway_user', 'secret1$', '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', 'secret1$', 'AT', NULL, 'test4') $$, 23505, NULL, 'No duplicate user name is allowed'); -- Test password policy SELECT throws_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2') $$, '28P01', NULL, 'Password with less than 8 characters is not accepted'); SELECT throws_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2') $$, '28P01', NULL, 'Password without non-alphanumeric character is not accepted'); SELECT throws_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2') $$, '28P01', NULL, 'Password without digit is not accepted'); -- -- Role update -- SELECT lives_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2'); SELECT sys_admin.update_user('test2', 'waterway_user', 'test2_new', 'new_secret1$', 'AT', (SELECT map_extent FROM users.list_users 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.list_users 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', 'secret1$', 'AT', (SELECT map_extent FROM users.list_users 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', 'secret1$', 'AT', (SELECT map_extent FROM users.list_users 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, 'secret1$', 'AT', (SELECT map_extent FROM users.list_users 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', 'secret1$', 'AT', (SELECT map_extent FROM users.list_users 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', 'secret1$', 'AT', (SELECT map_extent FROM users.list_users WHERE username = 'test_user_at'), 'test4') $$, 23505, NULL, 'No duplicate user name is allowed'); -- Test password policy (only one rule to ensure it's also used on update) SELECT throws_ok($$ SELECT sys_admin.update_user('test_user_at', 'waterway_user', 'test_user_at', 'secret', 'AT', (SELECT map_extent FROM users.list_users WHERE username = 'test_user_at'), 'test4') $$, '28P01', NULL, 'Non-compliant password is not accepted'); -- 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 internal.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 internal.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_pw1$', 'AT', (SELECT map_extent FROM internal.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', 'secret1$', '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');