changeset 5499:a30b6c6541e0 deactivate-users

Moved logic to delete deactivate users into plpgsql function.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 21 Sep 2021 22:06:43 +0200
parents 59cbd1b48c3a
children f0c668bc4082
files pkg/controllers/user.go schema/auth.sql schema/manage_users.sql schema/updates/1465/01.delete_user.sql schema/version.sql
diffstat 5 files changed, 129 insertions(+), 66 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/user.go	Tue Sep 21 19:02:53 2021 +0200
+++ b/pkg/controllers/user.go	Tue Sep 21 22:06:43 2021 +0200
@@ -28,7 +28,6 @@
 	"github.com/gorilla/mux"
 
 	"gemma.intevation.de/gemma/pkg/auth"
-	"gemma.intevation.de/gemma/pkg/common"
 	"gemma.intevation.de/gemma/pkg/log"
 	"gemma.intevation.de/gemma/pkg/misc"
 	"gemma.intevation.de/gemma/pkg/models"
@@ -40,10 +39,10 @@
 
 const (
 	createUserSQL = `INSERT INTO users.list_users
-  VALUES ($1, $2, $3, $4, NULL, $5, $6)`
+  VALUES ($1, $2, $3, $4, NULL, $5, $6, true)`
 	createUserExtentSQL = `INSERT INTO users.list_users
   VALUES ($1, $2, $3, $4,
-  ST_MakeBox2D(ST_Point($5, $6), ST_Point($7, $8)), $9, $10)`
+  ST_MakeBox2D(ST_Point($5, $6), ST_Point($7, $8)), $9, $10, true)`
 
 	updateUserUnprivSQL = `UPDATE users.list_users
   SET (pw, map_extent, email_address)
@@ -58,18 +57,7 @@
   = ($2, $3, $4, $5, ST_MakeBox2D(ST_Point($6, $7), ST_Point($8, $9)), $10, $11)
   WHERE username = $1`
 
-	usersExistsSQL = `SELECT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = $1)`
-
-	userIsAdminSQL = `SELECT EXISTS (SELECT 1 FROM pg_roles
-  WHERE pg_has_role($1, oid, 'member') AND rolname in ('waterway_admin', 'sys_admin'))`
-
-	adminHasImportsSQL = `SELECT EXISTS (SELECT 1 FROM import.imports
-  WHERE username = $1)`
-
-	deleteUserSQL = `DELETE FROM users.list_users WHERE username = $1`
-
-	deactivateUserSQL = `UPDATE users.list_users
-  SET (pw, email_address, report_reciever, active) = ($1, 'nomail@example.com', false, false)`
+	deleteUserSQL = `SELECT delete_user($1)`
 
 	listUsersSQL = `SELECT
   rolname,
@@ -147,24 +135,6 @@
 
 	db := mw.JSONConn(req)
 
-	// TODO: Move this into the transaction.
-
-	// First check if user exists
-
-	var userExists bool
-	if err = db.QueryRowContext(ctx, usersExistsSQL).Scan(&userExists); err != nil {
-		log.Errorf("%v\n", err)
-		return
-	}
-
-	if !userExists {
-		err = mw.JSONError{
-			Code:    http.StatusNotFound,
-			Message: fmt.Sprintf("Cannot find user %s.", user),
-		}
-		return
-	}
-
 	// Remove scheduled tasks.
 	ids, err2 := scheduler.ScheduledUserIDs(ctx, db, user)
 	if err2 == nil {
@@ -175,43 +145,22 @@
 		log.Errorf("%v\n", err2)
 	}
 
-	var tx *sql.Tx
-	if tx, err = db.BeginTx(ctx, nil); err != nil {
-		return
-	}
-	defer tx.Rollback()
-
-	var isAdmin bool
-	if err = tx.QueryRowContext(ctx, userIsAdminSQL).Scan(&isAdmin); err != nil {
-		log.Errorf("%v\n", err)
+	var result int64
+	if err = db.QueryRowContext(ctx, deleteUserSQL, user).Scan(&result); err != nil {
 		return
 	}
 
-	if !isAdmin { // All none admins can be deleted directly.
-		if _, err = tx.ExecContext(ctx, deleteUserSQL, user); err != nil {
-			log.Errorf("%v\n", err)
-			return
-		}
-	} else {
-		var hasImports bool
-		if err = tx.QueryRowContext(ctx, adminHasImportsSQL).Scan(&hasImports); err != nil {
-			log.Errorf("%v\n", err)
-			return
+	switch result {
+	case 0:
+		err = mw.JSONError{
+			Code:    http.StatusNotFound,
+			Message: fmt.Sprintf("Cannot find user %s.", user),
 		}
-		if !hasImports { // An admin w/o can also be simply deleted.
-			if _, err = tx.ExecContext(ctx, deleteUserSQL, user); err != nil {
-				log.Errorf("%v\n", err)
-				return
-			}
-		} else { // Admin user with imports needs to be deactivated.
-			pw := common.RandomString(30)
-			if _, err = tx.ExecContext(ctx, deactivateUserSQL, pw); err != nil {
-				return
-			}
-		}
-	}
-	if err = tx.Commit(); err != nil {
 		return
+	case 1:
+		log.Debugf("user '%s' was deleted.\n", user)
+	case 2:
+		log.Debugf("user '%s' was deactivated.\n", user)
 	}
 
 	// Running in a go routine should not be necessary.
--- a/schema/auth.sql	Tue Sep 21 19:02:53 2021 +0200
+++ b/schema/auth.sql	Tue Sep 21 22:06:43 2021 +0200
@@ -63,6 +63,7 @@
 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	Tue Sep 21 19:02:53 2021 +0200
+++ b/schema/manage_users.sql	Tue Sep 21 22:06:43 2021 +0200
@@ -212,6 +212,65 @@
 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
+    user_exists bool;
+    is_admin    bool;
+    has_imports bool;
+BEGIN
+    SELECT EXISTS (SELECT 1 FROM pg_roles
+        WHERE rolname = rolename)
+        INTO user_exists;
+
+    IF NOT user_exists THEN
+        RETURN 0;
+    END IF;
+
+    SELECT EXISTS (SELECT 1 FROM pg_roles
+        WHERE pg_has_role(rolename, oid, 'member') AND
+            rolname IN ('waterway_admin', 'sys_admin'))
+        INTO is_admin;
+
+    -- 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;
+
+    -- Do not allow to login any more.
+    EXECUTE format(
+        'ALTER ROLE %I NOLOGIN', 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
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1465/01.delete_user.sql	Tue Sep 21 22:06:43 2021 +0200
@@ -0,0 +1,54 @@
+
+CREATE OR REPLACE FUNCTION delete_user(rolename text) RETURNS int
+AS $$
+DECLARE
+    user_exists bool;
+    is_admin    bool;
+    has_imports bool;
+BEGIN
+    SELECT EXISTS (SELECT 1 FROM pg_roles
+        WHERE rolname = rolename)
+        INTO user_exists;
+
+    IF NOT user_exists THEN
+        RETURN 0;
+    END IF;
+
+    SELECT EXISTS (SELECT 1 FROM pg_roles
+        WHERE pg_has_role(rolename, oid, 'member') AND
+            rolname IN ('waterway_admin', 'sys_admin'))
+        INTO is_admin;
+
+    -- 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;
+
+    -- Do not allow to login any more.
+    EXECUTE format(
+        'ALTER ROLE %I NOLOGIN', rolename);
+
+    RETURN 2;
+END;
+$$
+LANGUAGE plpgsql
+SECURITY DEFINER;
+
+GRANT EXECUTE ON FUNCTION delete_user to sys_admin;
--- a/schema/version.sql	Tue Sep 21 19:02:53 2021 +0200
+++ b/schema/version.sql	Tue Sep 21 22:06:43 2021 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1464);
+INSERT INTO gemma_schema_version(version) VALUES (1465);