Mercurial > gemma
changeset 5501:2ce85b6fcb76 deactivate-users
Added missing trigger functions to migration.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 22 Sep 2021 16:55:15 +0200 |
parents | f0c668bc4082 |
children | 26b7921190fa |
files | schema/updates/1465/01.delete_user.sql |
diffstat | 1 files changed, 99 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/updates/1465/01.delete_user.sql Wed Sep 22 12:02:03 2021 +0200 +++ b/schema/updates/1465/01.delete_user.sql Wed Sep 22 16:55:15 2021 +0200 @@ -1,3 +1,102 @@ + +CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger +AS $$ +BEGIN + IF NEW.map_extent IS NULL + THEN + NEW.map_extent = ST_Extent(CAST(area AS geometry)) + FROM users.stretches st + JOIN users.stretch_countries stc ON stc.stretch_id = st.id + WHERE stc.country = NEW.country; + END IF; + + IF NEW.username IS NOT NULL + -- otherwise let the constraint on user_profiles speak + THEN + EXECUTE format( + 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', + NEW.username, + NEW.rolname, + internal.check_password(NEW.pw)); + END IF; + + INSERT INTO internal.user_profiles ( + username, country, map_extent, email_address, + report_reciever, active) + VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, + NEW.report_reciever, NEW.active); + + IF NEW.active THEN + EXECUTE format( + 'ALTER ROLE %I LOGIN', NEW.username); + ELSE + EXECUTE format( + 'ALTER ROLE %I NOLOGIN', NEW.username); + END IF; + + -- Do not leak new password + NEW.pw = ''; + RETURN NEW; +END; +$$ + LANGUAGE plpgsql + SECURITY DEFINER; + +CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger +AS $$ +DECLARE + cur_username varchar; +BEGIN + cur_username = OLD.username; + + IF NEW.username <> cur_username + THEN + EXECUTE format( + 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username); + cur_username = NEW.username; + END IF; + + UPDATE internal.user_profiles p + SET (username, country, map_extent, email_address, + report_reciever, active) + = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, + NEW.report_reciever, NEW.active) + WHERE p.username = cur_username; + + IF NEW.rolname <> OLD.rolname + THEN + EXECUTE format( + 'REVOKE %I FROM %I', OLD.rolname, cur_username); + EXECUTE format( + 'GRANT %I TO %I', NEW.rolname, cur_username); + END IF; + + IF NEW.pw IS NOT NULL AND NEW.pw <> '' + THEN + EXECUTE format( + 'ALTER ROLE %I PASSWORD %L', + cur_username, + internal.check_password(NEW.pw)); + END IF; + + IF NEW.active <> OLD.active THEN + IF NEW.active THEN + EXECUTE format( + 'ALTER ROLE %I LOGIN', cur_username); + ELSE + EXECUTE format( + 'ALTER ROLE %I NOLOGIN', cur_username); + END IF; + END IF; + + -- Do not leak new password + NEW.pw = ''; + RETURN NEW; +END; +$$ + LANGUAGE plpgsql + SECURITY DEFINER; + CREATE OR REPLACE FUNCTION delete_user(rolename text) RETURNS int AS $$