# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1532612599 -7200
# Node ID 664fe6536141f951a6178c6843379f9eb6e3ce9c
# Parent  7d1f0ffdfa41d2142a43f7a2e3e40830f906a116# Parent  55dce2f649bc5c158e38dbf6a5aced4cb0d74746
merged.

diff -r 7d1f0ffdfa41 -r 664fe6536141 schema/demo-data/users.sql
--- a/schema/demo-data/users.sql	Thu Jul 26 15:42:57 2018 +0200
+++ b/schema/demo-data/users.sql	Thu Jul 26 15:43:19 2018 +0200
@@ -5,7 +5,7 @@
 -- responsibility areas from responsibility_areas.sql are imported
 
 -- Fill in Profiles
-COPY users.user_profiles (username, country, email_adress, map_extent) FROM stdin;
+COPY users.user_profiles (username, country, email_address, map_extent) FROM stdin;
 sophie	AT	sophie@example.com	BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001)
 lucian	RO	lucian@example.com	BOX(20.2428259690001 43.6500499480001,29.6995548840001 48.2748322560001)
 oana	RO	oana@example.com	BOX(20.2428259690001 43.6500499480001,29.6995548840001 48.2748322560001)
diff -r 7d1f0ffdfa41 -r 664fe6536141 schema/gemma.sql
--- a/schema/gemma.sql	Thu Jul 26 15:42:57 2018 +0200
+++ b/schema/gemma.sql	Thu Jul 26 15:43:19 2018 +0200
@@ -137,7 +137,7 @@
         username varchar PRIMARY KEY,
         country char(2) NOT NULL REFERENCES responsibility_areas,
         map_extent box2d NOT NULL,
-        email_adress varchar NOT NULL UNIQUE
+        email_address varchar NOT NULL
     )
 
     CREATE TABLE templates (
diff -r 7d1f0ffdfa41 -r 664fe6536141 schema/manage_users.sql
--- a/schema/manage_users.sql	Thu Jul 26 15:42:57 2018 +0200
+++ b/schema/manage_users.sql	Thu Jul 26 15:43:19 2018 +0200
@@ -9,7 +9,7 @@
        pw varchar,
        country users.user_profiles.country%TYPE,
        map_extent users.user_profiles.map_extent%TYPE,
-       email_adress users.user_profiles.email_adress%TYPE
+       email_address users.user_profiles.email_address%TYPE
     )
     RETURNS void
 AS $$
@@ -20,7 +20,7 @@
             WHERE ra.country = create_user.country;
     END IF;
     INSERT INTO users.user_profiles VALUES (
-        username, country, map_extent, email_adress);
+        username, country, map_extent, email_address);
     EXECUTE format(
         'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw);
 END;
@@ -36,7 +36,7 @@
        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
+       new_email_address users.user_profiles.email_address%TYPE
     )
     RETURNS void
 AS $$
@@ -47,8 +47,8 @@
     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)
+        SET (username, country, map_extent, email_address)
+        = (new_username, new_country, new_map_extent, new_email_address)
         WHERE p.username = cur_username;
 
     IF new_username <> cur_username
@@ -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 7d1f0ffdfa41 -r 664fe6536141 schema/manage_users_tests.sql
--- a/schema/manage_users_tests.sql	Thu Jul 26 15:42:57 2018 +0200
+++ b/schema/manage_users_tests.sql	Thu Jul 26 15:43:19 2018 +0200
@@ -53,13 +53,6 @@
     23505, NULL,
     'No duplicate user name is allowed');
 
-SELECT throws_ok($$
-    SELECT sys_admin.create_user(
-        'waterway_user', 'test2', 'secret', 'AT', NULL, 'xxx')
-    $$,
-    23505, NULL,
-    'No duplicate e-mail adress is allowed');
-
 --
 -- Role update
 --
@@ -169,3 +162,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 7d1f0ffdfa41 -r 664fe6536141 schema/run_tests.sh
--- a/schema/run_tests.sh	Thu Jul 26 15:42:57 2018 +0200
+++ b/schema/run_tests.sh	Thu Jul 26 15:43:19 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(35)' \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
     -c 'SELECT * FROM finish()'