# HG changeset patch # User Tom Gottfried # Date 1532536014 -7200 # Node ID 8b9cae6d3a21713f22e8c4c58be47dc0b3b5189f # Parent 57dfab80973c468025a05bedc0f6295d0641e4fb Add database function to update role and user profile diff -r 57dfab80973c -r 8b9cae6d3a21 schema/gemma.sql --- a/schema/gemma.sql Wed Jul 25 13:34:46 2018 +0200 +++ b/schema/gemma.sql Wed Jul 25 18:26:54 2018 +0200 @@ -149,7 +149,8 @@ FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE user_templates ( - username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE, + username varchar NOT NULL + REFERENCES user_profiles ON DELETE CASCADE ON UPDATE CASCADE, template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE, PRIMARY KEY (username, template_name) ) diff -r 57dfab80973c -r 8b9cae6d3a21 schema/manage_users.sql --- a/schema/manage_users.sql Wed Jul 25 13:34:46 2018 +0200 +++ b/schema/manage_users.sql Wed Jul 25 18:26:54 2018 +0200 @@ -27,3 +27,57 @@ $$ LANGUAGE plpgsql SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION sys_admin.update_user( + username users.user_profiles.username%TYPE, + new_userrole varchar, + new_username users.user_profiles.username%TYPE, + new_pw varchar, + new_country users.user_profiles.country%TYPE, + new_map_extent users.user_profiles.map_extent%TYPE, + new_email_adress users.user_profiles.email_adress%TYPE + ) + RETURNS void +AS $$ +DECLARE + cur_username name; + cur_userrole name; +BEGIN + cur_username = username; + + UPDATE users.user_profiles p + SET (username, country, map_extent, email_adress) + = (new_username, new_country, new_map_extent, new_email_adress) + WHERE p.username = cur_username; + + IF new_username <> cur_username + THEN + EXECUTE format( + 'ALTER ROLE %I RENAME TO %I', username, new_username); + cur_username = new_username; + END IF; + + cur_userrole = rolname FROM pg_roles r + JOIN pg_auth_members a ON r.oid = a.roleid + WHERE member = ( + SELECT oid FROM pg_roles WHERE rolname = cur_username); + IF new_userrole <> cur_userrole + THEN + EXECUTE format( + 'REVOKE %I FROM %I', cur_userrole, cur_username); + END IF; + -- GRANT new_userrole unconditionally to ensure it's an error to upgrade + -- a non-existent cur_username (GRANTing a role twice is not an error) + EXECUTE format( + 'GRANT %I TO %I', new_userrole, cur_username); + + IF new_pw IS NOT NULL AND new_pw <> '' + THEN + EXECUTE format( + 'ALTER ROLE %I PASSWORD %L', cur_username, new_pw); + END IF; +END; +$$ + LANGUAGE plpgsql + SECURITY DEFINER; diff -r 57dfab80973c -r 8b9cae6d3a21 schema/manage_users_tests.sql --- a/schema/manage_users_tests.sql Wed Jul 25 13:34:46 2018 +0200 +++ b/schema/manage_users_tests.sql Wed Jul 25 18:26:54 2018 +0200 @@ -15,6 +15,9 @@ SET SESSION AUTHORIZATION test_sys_admin1; +-- +-- Role creation +-- SELECT lives_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test1', 'secret', 'AT', NULL, 'test1') @@ -56,3 +59,113 @@ $$, 23505, NULL, 'No duplicate e-mail adress 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'); diff -r 57dfab80973c -r 8b9cae6d3a21 schema/run_tests.sh --- a/schema/run_tests.sh Wed Jul 25 13:34:46 2018 +0200 +++ b/schema/run_tests.sh Wed Jul 25 18:26:54 2018 +0200 @@ -19,7 +19,7 @@ psql -qXv ON_ERROR_STOP= -v -d gemma_test \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c 'SELECT plan(23)' \ + -c 'SELECT plan(33)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'