Mercurial > gemma
changeset 232:4859aa6c96be
Add database function to delete role and user profile
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 26 Jul 2018 13:57:08 +0200 |
parents | 694f959ba3e7 |
children | 531d1f8a2b4b |
files | schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh |
diffstat | 3 files changed, 52 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/manage_users.sql Thu Jul 26 13:47:38 2018 +0200 +++ b/schema/manage_users.sql Thu Jul 26 13:57:08 2018 +0200 @@ -81,3 +81,28 @@ $$ LANGUAGE plpgsql SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION sys_admin.delete_user( + username users.user_profiles.username%TYPE + ) + RETURNS void +AS $$ +DECLARE + bid int; +BEGIN + -- Terminate the users backends started before the current transaction + FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = username LOOP + PERFORM pg_terminate_backend(bid); + END LOOP; + -- Note that any backend that might be started during the transaction + -- in which this function is executed will not be terminated but lost + -- without any privileges after commiting this transaction + + -- Delete user + EXECUTE format('DROP ROLE %I', username); + DELETE FROM users.user_profiles p WHERE p.username = delete_user.username; +END; +$$ + LANGUAGE plpgsql + SECURITY DEFINER;
--- a/schema/manage_users_tests.sql Thu Jul 26 13:47:38 2018 +0200 +++ b/schema/manage_users_tests.sql Thu Jul 26 13:57:08 2018 +0200 @@ -169,3 +169,29 @@ 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');
--- a/schema/run_tests.sh Thu Jul 26 13:47:38 2018 +0200 +++ b/schema/run_tests.sh Thu Jul 26 13:57:08 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(33)' \ + -c 'SELECT plan(36)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'