# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1632231086 -7200
# Node ID 8797274e2739a4ad57e7c8e28c8f2486972281df
# Parent  0766dcb7e7f84bdb055a217ba238f15745e5875e
Added a active column to users.

diff -r 0766dcb7e7f8 -r 8797274e2739 schema/gemma.sql
--- a/schema/gemma.sql	Tue Sep 21 12:49:30 2021 +0200
+++ b/schema/gemma.sql	Tue Sep 21 15:31:26 2021 +0200
@@ -385,7 +385,8 @@
         country char(2) NOT NULL REFERENCES countries,
         map_extent box2d NOT NULL,
         email_address varchar NOT NULL,
-        report_reciever boolean NOT NULL DEFAULT false
+        report_reciever boolean NOT NULL DEFAULT false,
+        active boolean NOT NULL DEFAULT true
     )
 ;
 
@@ -500,7 +501,8 @@
             p.country,
             p.map_extent,
             p.email_address,
-            p.report_reciever
+            p.report_reciever,
+            p.active
         FROM internal.user_profiles p
             JOIN pg_roles u ON p.username = u.rolname
             JOIN pg_auth_members a ON u.oid = a.member
diff -r 0766dcb7e7f8 -r 8797274e2739 schema/updates/1464/01.active.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1464/01.active.sql	Tue Sep 21 15:31:26 2021 +0200
@@ -0,0 +1,100 @@
+ALTER TABLE internal.user_profiles
+  ADD COLUMN active boolean NOT NULL DEFAULT true;
+
+CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
+    SELECT
+        r.rolname,
+        p.username,
+        CAST('' AS varchar) AS pw,
+        p.country,
+        p.map_extent,
+        p.email_address,
+        p.report_reciever,
+        p.active
+    FROM internal.user_profiles p
+        JOIN pg_roles u ON p.username = u.rolname
+        JOIN pg_auth_members a ON u.oid = a.member
+        JOIN pg_roles r ON a.roleid = r.oid
+    WHERE p.username = current_user
+        OR pg_has_role('waterway_admin', 'MEMBER')
+            AND p.country = (
+                SELECT country FROM internal.user_profiles
+                    WHERE username = current_user)
+            AND r.rolname <> 'sys_admin'
+        OR pg_has_role('sys_admin', 'MEMBER');
+
+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;
+
+    -- Do not leak new password
+    NEW.pw = '';
+    RETURN NEW;
+END;
+$$
+    LANGUAGE plpgsql
+    SECURITY DEFINER;
+
+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);
+
+    -- Do not leak new password
+    NEW.pw = '';
+    RETURN NEW;
+END;
+$$
+    LANGUAGE plpgsql
+    SECURITY DEFINER;
diff -r 0766dcb7e7f8 -r 8797274e2739 schema/version.sql
--- a/schema/version.sql	Tue Sep 21 12:49:30 2021 +0200
+++ b/schema/version.sql	Tue Sep 21 15:31:26 2021 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1463);
+INSERT INTO gemma_schema_version(version) VALUES (1464);