Mercurial > gemma
changeset 5504:5e3e3d9e2c23 deactivate-users
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;