Mercurial > gemma

diff schema/manage_users.sql @ 4723:baabc2b2f094

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid creating user profiles without matching role The INSTEAD OF triggers on users.list_users did that already, but profile data coming e.g. via restoring a dump had been added also if there was no matching database role in the cluster. This also unifies the errors occuring on creation of users with existing role names that differed between roles with and without profile before. Note this is no referential integrity. A dropped role still leaves an orphaned profile behind.
author Tom Gottfried <tom@intevation.de>
date Thu, 17 Oct 2019 18:56:59 +0200
parents 5e38667f740c
children 2440d2f86f4e
line wrap: on
line diff
--- a/schema/manage_users.sql	Thu Oct 17 16:36:58 2019 +0200
+++ b/schema/manage_users.sql	Thu Oct 17 18:56:59 2019 +0200
@@ -99,14 +99,20 @@
                 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)
         VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);
-    EXECUTE format(
-        'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
-        NEW.username,
-        NEW.rolname,
-        internal.check_password(NEW.pw));
 
     -- Do not leak new password
     NEW.pw = '';
@@ -167,11 +173,6 @@
 BEGIN
     cur_username = OLD.username;
 
-    UPDATE internal.user_profiles p
-        SET (username, country, map_extent, email_address)
-        = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address)
-        WHERE p.username = cur_username;
-
     IF NEW.username <> cur_username
     THEN
         EXECUTE format(
@@ -179,6 +180,11 @@
         cur_username = NEW.username;
     END IF;
 
+    UPDATE internal.user_profiles p
+        SET (username, country, map_extent, email_address)
+        = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address)
+        WHERE p.username = cur_username;
+
     IF NEW.rolname <> OLD.rolname
     THEN
         EXECUTE format(