# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1568656693 -7200
# Node ID 7657640f0c8d47ee84696e85abd0da61e84ace85
# Parent  b0c974fc7d3405a34077b266d4aa059955623bb8
Gracefully handle existing default login roles

If multiple databases are created in the same PostgreSQL cluster,
existing roles are now properly re-used. This also fixes running
database tests on a cluster that already has these roles, which
was broken since rev. 5e38667f740c, because it silently fixed a test
for existance of roles that failed if proper creation of the
role 'sysadmin' failed during setup of the test database.

diff -r b0c974fc7d34 -r 7657640f0c8d schema/std_login_roles.sql
--- a/schema/std_login_roles.sql	Mon Sep 16 17:15:33 2019 +0200
+++ b/schema/std_login_roles.sql	Mon Sep 16 19:58:13 2019 +0200
@@ -21,32 +21,70 @@
 -- NOTE: Passwords for these roles must be set during initial setup of
 --       the database.
 
---
--- Admin User
---
--- We need an empty dummy country for the default admin, as the user is
--- not supposed to work on data, it should be only used to create
--- personalized accounts.
-INSERT INTO countries (country_code) VALUES ('--');
+CREATE PROCEDURE create_login_roles(adminpw varchar, metapw varchar)
+AS $$
+DECLARE
+    dummy_country CONSTANT varchar = '--';
+    admin CONSTANT varchar = 'sysadmin';
+    adminrole CONSTANT varchar = 'sys_admin';
+    box CONSTANT box2d = 'BOX(9.52115482500011 46.3786430870001,
+        17.1483378500001 49.0097744750001)';
+    meta CONSTANT varchar = 'meta_login';
+    metarole CONSTANT varchar = 'metamorph';
+BEGIN
+    --
+    -- Admin User
+    --
+    -- We need an empty dummy country for the default admin, as the user is
+    -- not supposed to work on data, it should be only used to create
+    -- personalized accounts.
+    INSERT INTO countries (country_code) VALUES (dummy_country);
 
--- This initial Admin account is used to bootstrap the personalized accounts.
-INSERT INTO users.list_users VALUES (
-    'sys_admin',
-    'sysadmin',
-    :'adminpw',
-    '--',
-    'BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001)',
-    '');
+    -- Initial Admin account used to bootstrap the personalized accounts
+    IF to_regrole(admin) IS NULL THEN
+        INSERT INTO users.list_users VALUES (
+            adminrole, admin, adminpw, dummy_country, box, '');
+        RAISE NOTICE 'Default admin user ''%'' created with password ''%''',
+            admin, adminpw;
+    ELSE
+        IF pg_has_role(admin, adminrole, 'USAGE') THEN
+            RAISE WARNING
+                'Role ''%'' already exists', admin
+                USING DETAIL = 'Password and role memberships are kept as is';
+            INSERT INTO internal.user_profiles (
+                username, map_extent, email_address, country)
+                VALUES (admin, box, '', dummy_country);
+        ELSE
+            RAISE 'Role ''%'' already exists but lacks necessary privileges',
+                admin;
+        END IF;
+    END IF;
 
---
--- Functional Users
---
+    --
+    -- Functional Users
+    --
 
--- Used by GeoServer and backend
-CREATE ROLE meta_login IN ROLE metamorph LOGIN PASSWORD :'metapw';
+    -- Used by GeoServer and backend
+    IF to_regrole(meta) IS NULL THEN
+        EXECUTE format('CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
+            meta, metarole, metapw);
+        RAISE NOTICE 'Backend user ''%'' created with password ''%''',
+            meta, metapw;
+    ELSE
+        IF pg_has_role(meta, metarole, 'USAGE') THEN
+            RAISE WARNING
+                'Role ''%'' already exists', meta
+                USING DETAIL = 'Password and role memberships are kept as is';
+        ELSE
+            RAISE
+                'Role ''%'' already exists but lacks necessary privileges',
+                meta;
+        END IF;
+    END IF;
 
--- Emit messages to the client if everything went ok
-SELECT 'Default admin user ''sysadmin'' created with password ' || :'adminpw';
-SELECT 'Backend user ''meta_login'' created with password ' || :'metapw';
+END;
+$$ LANGUAGE plpgsql;
+CALL create_login_roles(:'adminpw', :'metapw');
+DROP PROCEDURE create_login_roles(varchar, varchar);
 
 COMMIT;