Mercurial > gemma

changeset 4413:a554d7ca26ee

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do not ignore any errors in database setup Previously, errors in role creation were ignored and setup continued. Since this makes it quite likely that newly introduced errors are overlooked e.g. when running database tests, handle all role creation more gracefully and stop the setup script on any error. To achieve this more easily, made code in std_login_roles.sql a bit more reusable.
author Tom Gottfried <tom@intevation.de>
date Tue, 17 Sep 2019 17:24:03 +0200
parents 14de1ea7000b
children b5290f4a35f4
files schema/install-db.sh schema/roles.sql schema/std_login_roles.sql
diffstat 3 files changed, 93 insertions(+), 28 deletions(-) [+]
line wrap: on
line diff
--- a/schema/install-db.sh	Tue Sep 17 17:05:14 2019 +0200
+++ b/schema/install-db.sh	Tue Sep 17 17:24:03 2019 +0200
@@ -121,9 +121,9 @@
 if [[ drop -eq 0 ]] ; then
   # Default operation: create schema
   createdb -p "$port" "$db"
-  psql -q -p "$port" -f "$BASEDIR/roles.sql" -d "$db"
   psql -qtv ON_ERROR_STOP= -p "$port" -d "$db" \
        -c "SET client_min_messages TO WARNING;" \
+       -f "$BASEDIR/roles.sql" \
        -f "$BASEDIR/isrs.sql" \
        -f "$BASEDIR/gemma.sql" \
        -f "$BASEDIR/geo_functions.sql" \
@@ -139,7 +139,7 @@
 
   # setup initial login roles with given passwords:
   psql -qt -P pager=off -p "$port" -d "$db" \
-       -v adminpw="$adminpw" -v metapw="$metapw" \
+       -v ON_ERROR_STOP= -v adminpw="$adminpw" -v metapw="$metapw" \
        -f "$BASEDIR/std_login_roles.sql"
 
   if [[ $demo -eq 1 ]] ; then
--- a/schema/roles.sql	Tue Sep 17 17:05:14 2019 +0200
+++ b/schema/roles.sql	Tue Sep 17 17:24:03 2019 +0200
@@ -4,25 +4,84 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018 by via donau
+-- Copyright (C) 2018, 2019 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
 -- Author(s):
 --  * Tom Gottfried <tom@intevation.de>
 
---
--- Primary GEMMA roles
---
-CREATE ROLE waterway_user;
-CREATE ROLE waterway_admin IN ROLE waterway_user;
-CREATE ROLE sys_admin IN ROLE waterway_admin;
+CREATE PROCEDURE create_roles()
+AS $$
+DECLARE
+    -- Role names
+    wwuser CONSTANT varchar = 'waterway_user';
+    wwadmin CONSTANT varchar = 'waterway_admin';
+    sysadmin CONSTANT varchar = 'sys_admin';
+    metarole CONSTANT varchar = 'metamorph';
+
+    -- Messages
+    warn_message CONSTANT varchar = 'Role %I already exists';
+    warn_detail CONSTANT varchar =
+        'Role attributes and memberships are kept as is';
+    err_message CONSTANT varchar =
+        'Role %I already exists but lacks necessary privileges';
+BEGIN
+    --
+    -- Primary GEMMA roles
+    --
+    IF to_regrole(wwuser) IS NULL THEN
+        EXECUTE format('CREATE ROLE %I', wwuser);
+    ELSE
+        RAISE WARNING USING
+            MESSAGE = format(warn_message, wwuser),
+            DETAIL = warn_detail;
+    END IF;
 
---
--- Special roles
---
+    IF to_regrole(wwadmin) IS NULL THEN
+        EXECUTE format('CREATE ROLE %I IN ROLE %I', wwadmin, wwuser);
+    ELSE
+        IF pg_has_role(wwadmin, wwuser, 'USAGE') THEN
+            RAISE WARNING USING
+                MESSAGE = format(warn_message, wwadmin),
+                DETAIL = warn_detail;
+        ELSE
+            RAISE USING MESSAGE = format(err_message, wwadmin);
+        END IF;
+    END IF;
+
+    IF to_regrole(sysadmin) IS NULL THEN
+        EXECUTE format('CREATE ROLE %I IN ROLE %I', sysadmin, wwadmin);
+    ELSE
+        IF pg_has_role(sysadmin, wwadmin, 'USAGE') THEN
+            RAISE WARNING USING
+                MESSAGE = format(warn_message, sysadmin),
+                DETAIL = warn_detail;
+        ELSE
+            RAISE USING MESSAGE = format(err_message, sysadmin);
+        END IF;
+    END IF;
 
--- A role that is intended to be used for backend- or
--- GeoServer-connections on which SET ROLE has to be used to
--- gain privileges of a specific role
-CREATE ROLE metamorph NOINHERIT;
+    --
+    -- Special roles
+    --
+
+    -- A role that is intended to be used for backend- or
+    -- GeoServer-connections on which SET ROLE has to be used to
+    -- gain privileges of a specific role
+    IF to_regrole(metarole) IS NULL THEN
+        EXECUTE format('CREATE ROLE %I NOINHERIT', metarole);
+    ELSE
+        IF (SELECT NOT rolinherit FROM pg_roles WHERE rolname = metarole) THEN
+            RAISE WARNING USING
+                MESSAGE = format(warn_message, metarole),
+                DETAIL = warn_detail;
+        ELSE
+            RAISE USING MESSAGE = format(err_message, metarole);
+        END IF;
+    END IF;
+
+END;
+$$ LANGUAGE plpgsql;
+CALL create_roles();
+DROP PROCEDURE create_roles();
--- a/schema/std_login_roles.sql	Tue Sep 17 17:05:14 2019 +0200
+++ b/schema/std_login_roles.sql	Tue Sep 17 17:24:03 2019 +0200
@@ -4,7 +4,7 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018 by via donau
+-- Copyright (C) 2018, 2019 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
@@ -25,12 +25,21 @@
 AS $$
 DECLARE
     dummy_country CONSTANT varchar = '--';
+
+    -- Role names and attributes
     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';
+
+    -- Messages
+    warn_message CONSTANT varchar = 'Role %I already exists';
+    warn_detail CONSTANT varchar =
+        'Password, role attributes and memberships are kept as is';
+    err_message CONSTANT varchar =
+        'Role %I already exists but lacks necessary privileges';
 BEGIN
     --
     -- Admin User
@@ -48,15 +57,14 @@
             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';
+            RAISE WARNING USING
+                MESSAGE = format(warn_message, admin),
+                DETAIL = warn_detail;
             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;
+            RAISE USING MESSAGE = format(err_message, admin);
         END IF;
     END IF;
 
@@ -72,13 +80,11 @@
             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';
+            RAISE WARNING USING
+                MESSAGE = format(warn_message, meta),
+                DETAIL = warn_detail;
         ELSE
-            RAISE
-                'Role ''%'' already exists but lacks necessary privileges',
-                meta;
+            RAISE USING MESSAGE = format(err_message, admin);
         END IF;
     END IF;