Mercurial > gemma

changeset 5504:5e3e3d9e2c23 deactivate-users

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Removed delete_user plpgsql function.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 22 Sep 2021 18:50:16 +0200
parents 076b6b17c4a9
children 2604beecbd1a
files pkg/controllers/user.go schema/auth.sql schema/manage_users.sql schema/updates/1465/01.delete_user.sql
diffstat 4 files changed, 37 insertions(+), 104 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/user.go	Wed Sep 22 17:25:47 2021 +0200
+++ b/pkg/controllers/user.go	Wed Sep 22 18:50:16 2021 +0200
@@ -57,7 +57,23 @@
   = ($2, $3, $4, $5, ST_MakeBox2D(ST_Point($6, $7), ST_Point($8, $9)), $10, $11)
   WHERE username = $1`
 
-	deleteUserSQL = `SELECT delete_user($1)`
+	deleteUserSQL = `
+  WITH del AS (
+    DELETE FROM users.list_users
+	WHERE username = $1 AND (
+	  rolname NOT IN ('waterway_admin', 'sys_admin')
+	  OR NOT EXISTS (SELECT 1 FROM import.imports WHERE username = $1))
+	RETURNING *
+  ),
+  up AS (
+    UPDATE users.list_users
+	SET (email_address, report_reciever, active) = ('nomail@example.com', false, false)
+	WHERE username = $1 AND NOT EXISTS (SELECT * FROM del)
+	RETURNING *
+  )
+  SELECT
+    EXISTS (SELECT * FROM del) AS deleted,
+	EXISTS (SELECT * FROM up)  AS updated`
 
 	listUsersSQL = `SELECT
   rolname,
@@ -145,28 +161,41 @@
 		log.Errorf("%v\n", err2)
 	}
 
-	var result int64
-	if err = db.QueryRowContext(ctx, deleteUserSQL, user).Scan(&result); err != nil {
+	var deleted, updated bool
+	if err = db.QueryRowContext(ctx, deleteUserSQL, user).Scan(&deleted, &updated); err != nil {
 		return
 	}
 
-	switch result {
-	case 0:
+	var action string
+
+	switch {
+	case !deleted && !updated:
 		err = mw.JSONError{
 			Code:    http.StatusNotFound,
 			Message: fmt.Sprintf("Cannot find user %s.", user),
 		}
 		return
-	case 1:
+	case deleted:
 		log.Debugf("user '%s' was deleted.\n", user)
-	case 2:
+		action = "deleted"
+	case updated:
 		log.Debugf("user '%s' was deactivated.\n", user)
+		action = "deactivated"
+	default:
+		log.Errorf("Should not happen: user '%s' updated and deleted.\n", user)
+		action = "deleted"
 	}
 
 	// Running in a go routine should not be necessary.
 	go func() { auth.Sessions.Logout(user) }()
 
-	jr = mw.JSONResult{Code: http.StatusNoContent}
+	jr = mw.JSONResult{
+		Result: struct {
+			Action string `json:"action"`
+		}{
+			Action: action,
+		},
+	}
 	return
 }
 
--- a/schema/auth.sql	Wed Sep 22 17:25:47 2021 +0200
+++ b/schema/auth.sql	Wed Sep 22 18:50:16 2021 +0200
@@ -63,7 +63,6 @@
 GRANT INSERT, DELETE, UPDATE ON sys_admin.password_reset_requests TO sys_admin;
 GRANT DELETE ON import.imports, import.import_logs TO sys_admin;
 GRANT SELECT, INSERT, DELETE, UPDATE ON sys_admin.stats_updates TO sys_admin;
-GRANT EXECUTE ON FUNCTION delete_user to sys_admin;
 
 --
 -- Privileges assigned directly to metamorph
--- a/schema/manage_users.sql	Wed Sep 22 17:25:47 2021 +0200
+++ b/schema/manage_users.sql	Wed Sep 22 18:50:16 2021 +0200
@@ -234,56 +234,6 @@
 CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW
     EXECUTE PROCEDURE internal.delete_user();
 
---
--- delete_user deletes a user if she/he is not an admin or an admin w/o imports.
--- It deactivates admins w/ imports.
--- Returns 0 if the role does not exsist, 1 if the role was deleted, 2 if it was
--- deactivated.
---
-CREATE OR REPLACE FUNCTION delete_user(rolename text) RETURNS int
-AS $$
-DECLARE
-    is_admin    bool;
-    has_imports bool;
-BEGIN
-    SELECT rolname IN ('waterway_admin', 'sys_admin') INTO is_admin
-        FROM  users.list_users
-        WHERE username = rolename;
-
-    IF NOT FOUND THEN
-        RETURN 0;
-    END IF;
-
-    -- None admins can be deleted.
-    IF NOT is_admin THEN
-        DELETE FROM users.list_users WHERE username = rolename;
-        RETURN 1;
-    END IF;
-
-    SELECT EXISTS (SELECT 1 FROM import.imports
-        WHERE username = rolename)
-        INTO has_imports;
-
-    -- Admins w/o imports can be deleted.
-    IF NOT has_imports THEN
-        DELETE FROM users.list_users WHERE username = rolename;
-        RETURN 1;
-    END IF;
-
-    -- Admins w/ imports have to be deactivated.
-    UPDATE users.list_users
-        SET (email_address, report_reciever, active) =
-            ('nomail@example.com', false, false)
-        WHERE username = rolename;
-
-    RETURN 2;
-END;
-$$
-    LANGUAGE plpgsql
-    SECURITY DEFINER;
-
-
-
 -- To set a role from a hex-encoded user name (which is save from SQL injections).
 CREATE OR REPLACE FUNCTION public.setrole(role text) RETURNS void
 AS $$
--- a/schema/updates/1465/01.delete_user.sql	Wed Sep 22 17:25:47 2021 +0200
+++ b/schema/updates/1465/01.delete_user.sql	Wed Sep 22 18:50:16 2021 +0200
@@ -96,48 +96,3 @@
 $$
     LANGUAGE plpgsql
     SECURITY DEFINER;
-
-
-CREATE OR REPLACE FUNCTION delete_user(rolename text) RETURNS int
-AS $$
-DECLARE
-    is_admin    bool;
-    has_imports bool;
-BEGIN
-    SELECT rolname IN ('waterway_admin', 'sys_admin') INTO is_admin
-        FROM  users.list_users
-        WHERE username = rolename;
-
-    IF NOT FOUND THEN
-        RETURN 0;
-    END IF;
-
-    -- None admins can be deleted.
-    IF NOT is_admin THEN
-        DELETE FROM users.list_users WHERE username = rolename;
-        RETURN 1;
-    END IF;
-
-    SELECT EXISTS (SELECT 1 FROM import.imports
-        WHERE username = rolename)
-        INTO has_imports;
-
-    -- Admins w/o imports can be deleted.
-    IF NOT has_imports THEN
-        DELETE FROM users.list_users WHERE username = rolename;
-        RETURN 1;
-    END IF;
-
-    -- Admins w/ imports have to be deactivated.
-    UPDATE users.list_users
-        SET (email_address, report_reciever, active) =
-            ('nomail@example.com', false, false)
-        WHERE username = rolename;
-
-    RETURN 2;
-END;
-$$
-LANGUAGE plpgsql
-SECURITY DEFINER;
-
-GRANT EXECUTE ON FUNCTION delete_user to sys_admin;