# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1533551826 -7200
# Node ID c6bd6ed18942f55a5fde613870bdb4f7da7570ab
# Parent  889517f254f580ae0374deaf6740fd97038b8217
Use INSTEAD OF trigger for user deletion

As we already have this for updating, it's more symmetric to make
the whole thing look like a real table. TODO: make it happen also
for user creation.

diff -r 889517f254f5 -r c6bd6ed18942 controllers/user.go
--- a/controllers/user.go	Mon Aug 06 12:36:20 2018 +0200
+++ b/controllers/user.go	Mon Aug 06 12:37:06 2018 +0200
@@ -28,7 +28,7 @@
   = ($2, $3, $4, $5, ST_MakeBox2D(ST_Point($6, $7), ST_Point($8, $9)), $10)
   WHERE username = $1`
 
-	deleteUserSQL = `SELECT sys_admin.delete_user($1)`
+	deleteUserSQL = `DELETE FROM users.list_users WHERE username = $1`
 
 	listUsersSQL = `SELECT
   rolname,
@@ -66,7 +66,17 @@
 		return
 	}
 
-	if _, err = db.Exec(deleteUserSQL, user); err != nil {
+	var res sql.Result
+
+	if res, err = db.Exec(deleteUserSQL, user); err != nil {
+		return
+	}
+
+	if n, err2 := res.RowsAffected(); err2 == nil && n == 0 {
+		err = JSONError{
+			Code:    http.StatusNotFound,
+			Message: fmt.Sprintf("Cannot find user %s.", user),
+		}
 		return
 	}
 
diff -r 889517f254f5 -r c6bd6ed18942 schema/manage_users.sql
--- a/schema/manage_users.sql	Mon Aug 06 12:36:20 2018 +0200
+++ b/schema/manage_users.sql	Mon Aug 06 12:37:06 2018 +0200
@@ -141,16 +141,14 @@
     EXECUTE PROCEDURE internal.update_user();
 
 
-CREATE OR REPLACE FUNCTION sys_admin.delete_user(
-       username internal.user_profiles.username%TYPE
-    )
-    RETURNS void
+CREATE OR REPLACE FUNCTION internal.delete_user() RETURNS trigger
 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
+    FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = OLD.username
+    LOOP
         PERFORM pg_terminate_backend(bid);
     END LOOP;
     -- Note that any backend that might be started during the transaction
@@ -158,14 +156,19 @@
     -- without any privileges after commiting this transaction
 
     -- Delete user
-    EXECUTE format('DROP ROLE %I', username);
+    EXECUTE format('DROP ROLE %I', OLD.username);
     DELETE FROM internal.user_profiles p
-        WHERE p.username = delete_user.username;
+        WHERE p.username = OLD.username;
+
+    RETURN OLD;
 END;
 $$
     LANGUAGE plpgsql
     SECURITY DEFINER;
 
+CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW
+    EXECUTE PROCEDURE internal.delete_user();
+
 
 CREATE OR REPLACE VIEW pw_reset.list_users AS
     SELECT username, pw, email_address FROM users.list_users;
diff -r 889517f254f5 -r c6bd6ed18942 schema/manage_users_tests.sql
--- a/schema/manage_users_tests.sql	Mon Aug 06 12:36:20 2018 +0200
+++ b/schema/manage_users_tests.sql	Mon Aug 06 12:37:06 2018 +0200
@@ -278,18 +278,12 @@
 SELECT lives_ok($$
     SELECT sys_admin.create_user(
         'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
-    SELECT sys_admin.delete_user('test3')
+    DELETE FROM users.list_users WHERE username = '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))
+    DELETE FROM users.list_users WHERE username = CAST(current_user AS varchar)
     $$,
     55006, NULL,
     'Current user cannot be deleted');
diff -r 889517f254f5 -r c6bd6ed18942 schema/run_tests.sh
--- a/schema/run_tests.sh	Mon Aug 06 12:36:20 2018 +0200
+++ b/schema/run_tests.sh	Mon Aug 06 12:37:06 2018 +0200
@@ -16,7 +16,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f tap_tests_data.sql \
-    -c 'SELECT plan(45)' \
+    -c 'SELECT plan(44)' \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
     -c 'SELECT * FROM finish()'