Mercurial > gemma
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;