# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1532606228 -7200
# Node ID 4859aa6c96be9afd7a76b92993d95c515a90510e
# Parent  694f959ba3e709844489d6628109d928b055edd0
Add database function to delete role and user profile

diff -r 694f959ba3e7 -r 4859aa6c96be schema/manage_users.sql
--- 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;
diff -r 694f959ba3e7 -r 4859aa6c96be schema/manage_users_tests.sql
--- 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');
diff -r 694f959ba3e7 -r 4859aa6c96be schema/run_tests.sh
--- 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()'