# HG changeset patch # User Sascha L. Teichmann # Date 1632329416 -7200 # Node ID 5e3e3d9e2c23617d9910aa6217bd02aa2bc613bb # Parent 076b6b17c4a9dd930296bfc282c700659a7c6b64 Removed delete_user plpgsql function. diff -r 076b6b17c4a9 -r 5e3e3d9e2c23 pkg/controllers/user.go --- 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 } diff -r 076b6b17c4a9 -r 5e3e3d9e2c23 schema/auth.sql --- 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 diff -r 076b6b17c4a9 -r 5e3e3d9e2c23 schema/manage_users.sql --- 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 $$ diff -r 076b6b17c4a9 -r 5e3e3d9e2c23 schema/updates/1465/01.delete_user.sql --- 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;