--- 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 $$