Mercurial > gemma

changeset 463:5611cf72cc92

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add metamorphic database role and user e.g. for GeoServer
author Tom Gottfried <tom@intevation.de>
date Wed, 22 Aug 2018 16:48:56 +0200
parents 62a0adc8cdcd
children b31eb28d1fcb
files example_conf.toml schema/Dockerfile schema/auth.sql schema/demo-data/roles.sql schema/install-db.sh schema/manage_users.sql schema/manage_users_tests.sql schema/roles.sql schema/run_tests.sh schema/std_login_roles.sql
diffstat 10 files changed, 108 insertions(+), 31 deletions(-) [+]
line wrap: on
line diff
--- a/example_conf.toml	Wed Aug 22 16:20:09 2018 +0200
+++ b/example_conf.toml	Wed Aug 22 16:48:56 2018 +0200
@@ -5,3 +5,5 @@
 sessions = "/tmp/gemma_session.data"
 web = "./web"
 geoserver-url = "http://gemma_geoserver:8080/geoserver"
+metamorph-db-user = "meta_login"
+metamorph-db-password = "geo2Serv"
--- a/schema/Dockerfile	Wed Aug 22 16:20:09 2018 +0200
+++ b/schema/Dockerfile	Wed Aug 22 16:48:56 2018 +0200
@@ -31,7 +31,7 @@
 COPY *.sql *.sh ./
 COPY demo-data ./demo-data/
 RUN $PGBIN/pg_ctl start -wo "--config_file=$PGCONF" && \
-    ./install-db.sh --demo --servicepw "pw2Reset4" && \
+    ./install-db.sh --demo --servicepw "pw2Reset4" --metapw "geo2Serv" && \
     $PGBIN/pg_ctl stop -m smart
 
 # Set the default command to run when starting the container
--- a/schema/auth.sql	Wed Aug 22 16:20:09 2018 +0200
+++ b/schema/auth.sql	Wed Aug 22 16:48:56 2018 +0200
@@ -41,6 +41,11 @@
 GRANT UPDATE (pw) ON pw_reset.list_users TO pw_reset;
 GRANT INSERT, SELECT, DELETE ON pw_reset.password_reset_requests TO pw_reset;
 
+--
+-- Privileges assigned directly to metamorph
+--
+-- Needed for GeoServer's system inspection run before session startup SQL
+GRANT USAGE ON SCHEMA public TO metamorph;
 
 --
 -- RLS policies for waterway_user
--- a/schema/demo-data/roles.sql	Wed Aug 22 16:20:09 2018 +0200
+++ b/schema/demo-data/roles.sql	Wed Aug 22 16:48:56 2018 +0200
@@ -1,34 +1,62 @@
 -- System Administrator
-CREATE ROLE sophie IN ROLE sys_admin LOGIN PASSWORD 'so2Phie4';
-CREATE ROLE "Dipl.-Inform. Stefan-Albert Zimmermann" IN ROLE sys_admin LOGIN PASSWORD 'zi2Mmer3';
-CREATE ROLE "Dipl.-Inform.Stefan-AlbertZimmermann" IN ROLE sys_admin LOGIN PASSWORD 'zi2Mmer3';
+CREATE ROLE sophie
+    IN ROLE sys_admin ROLE metamorph LOGIN PASSWORD 'so2Phie4';
+CREATE ROLE "Dipl.-Inform. Stefan-Albert Zimmermann"
+    IN ROLE sys_admin ROLE metamorph LOGIN PASSWORD 'zi2Mmer3';
+CREATE ROLE "Dipl.-Inform.Stefan-AlbertZimmermann"
+    IN ROLE sys_admin ROLE metamorph LOGIN PASSWORD 'zi2Mmer3';
 
 -- Water Way Administrators
-CREATE ROLE lucian IN ROLE waterway_admin LOGIN PASSWORD 'lu2Cian3';
-CREATE ROLE penka IN ROLE waterway_admin LOGIN PASSWORD 'pe2Nka3';
-CREATE ROLE "David Wagner" IN ROLE waterway_admin LOGIN PASSWORD 'da2Div3';
-CREATE ROLE "Christina Kovačević" IN ROLE waterway_admin LOGIN PASSWORD 'ch2Ris3';
-CREATE ROLE "Andrej Kovačič" IN ROLE waterway_admin LOGIN PASSWORD 'an2Dre3';
+CREATE ROLE lucian
+    IN ROLE waterway_admin ROLE metamorph LOGIN PASSWORD 'lu2Cian3';
+CREATE ROLE penka
+    IN ROLE waterway_admin ROLE metamorph LOGIN PASSWORD 'pe2Nka3';
+CREATE ROLE "David Wagner"
+    IN ROLE waterway_admin ROLE metamorph LOGIN PASSWORD 'da2Div3';
+CREATE ROLE "Christina Kovačević"
+    IN ROLE waterway_admin ROLE metamorph LOGIN PASSWORD 'ch2Ris3';
+CREATE ROLE "Andrej Kovačič"
+    IN ROLE waterway_admin ROLE metamorph LOGIN PASSWORD 'an2Dre3';
 
 -- Water Way Users
-CREATE ROLE oana IN ROLE waterway_user LOGIN PASSWORD 'oa2Na2';
-CREATE ROLE "Johanna Pichler" IN ROLE waterway_user LOGIN PASSWORD 'jo2Han3';
-CREATE ROLE "JohannaPichler" IN ROLE waterway_user LOGIN PASSWORD 'jo2Han3';
-CREATE ROLE "Katharina Gruber" IN ROLE waterway_user LOGIN PASSWORD 'ka2Tha3';
-CREATE ROLE "Maximilian Wimmer" IN ROLE waterway_user LOGIN PASSWORD 'ma2Xim3';
-CREATE ROLE "Ivo Marković" IN ROLE waterway_user LOGIN PASSWORD 'iv2Oma3';
-CREATE ROLE "Leonard Vuković" IN ROLE waterway_user LOGIN PASSWORD 'le2Ona3';
-CREATE ROLE "Neven Novak" IN ROLE waterway_user LOGIN PASSWORD 'ne2Ven3';
-CREATE ROLE "Elena Popescu" IN ROLE waterway_user LOGIN PASSWORD 'el2Ena3';
-CREATE ROLE "Eugen Munteanu" IN ROLE waterway_user LOGIN PASSWORD 'au2Gen3';
-CREATE ROLE "Gabriela Gheorghe" IN ROLE waterway_user LOGIN PASSWORD 'ga2Bri3';
-CREATE ROLE "Marina Stoica" IN ROLE waterway_user LOGIN PASSWORD 'ma2Rin3';
-CREATE ROLE "Борис Даманиов" IN ROLE waterway_user LOGIN PASSWORD 'bo2Ris3';
-CREATE ROLE "БорисДаманиов" IN ROLE waterway_user LOGIN PASSWORD 'bo2Ris3';
-CREATE ROLE "Фидан Дубарова" IN ROLE waterway_user LOGIN PASSWORD 'fi2Dan3';
-CREATE ROLE "Дипл. Инж. Константин Ясенов" IN ROLE waterway_user LOGIN PASSWORD 'ko2Nst3';
-CREATE ROLE "Люба Ходкивич" IN ROLE waterway_user LOGIN PASSWORD 'Lj2Uba3';
-CREATE ROLE "Milana Božič" IN ROLE waterway_user LOGIN PASSWORD 'mi2Lan3';
-CREATE ROLE "Vladislav Zupančič" IN ROLE waterway_user LOGIN PASSWORD 'vl2Adi3';
-CREATE ROLE "Zuzanna Korošec" IN ROLE waterway_user LOGIN PASSWORD 'zu2Zan3';
+CREATE ROLE oana
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'oa2Na2';
+CREATE ROLE "Johanna Pichler"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'jo2Han3';
+CREATE ROLE "JohannaPichler"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'jo2Han3';
+CREATE ROLE "Katharina Gruber"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'ka2Tha3';
+CREATE ROLE "Maximilian Wimmer"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'ma2Xim3';
+CREATE ROLE "Ivo Marković"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'iv2Oma3';
+CREATE ROLE "Leonard Vuković"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'le2Ona3';
+CREATE ROLE "Neven Novak"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'ne2Ven3';
+CREATE ROLE "Elena Popescu"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'el2Ena3';
+CREATE ROLE "Eugen Munteanu"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'au2Gen3';
+CREATE ROLE "Gabriela Gheorghe"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'ga2Bri3';
+CREATE ROLE "Marina Stoica"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'ma2Rin3';
+CREATE ROLE "Борис Даманиов"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'bo2Ris3';
+CREATE ROLE "БорисДаманиов"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'bo2Ris3';
+CREATE ROLE "Фидан Дубарова"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'fi2Dan3';
+CREATE ROLE "Дипл. Инж. Константин Ясенов"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'ko2Nst3';
+CREATE ROLE "Люба Ходкивич"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'Lj2Uba3';
+CREATE ROLE "Milana Božič"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'mi2Lan3';
+CREATE ROLE "Vladislav Zupančič"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'vl2Adi3';
+CREATE ROLE "Zuzanna Korošec"
+    IN ROLE waterway_user ROLE metamorph LOGIN PASSWORD 'zu2Zan3';
 
--- a/schema/install-db.sh	Wed Aug 22 16:20:09 2018 +0200
+++ b/schema/install-db.sh	Wed Aug 22 16:48:56 2018 +0200
@@ -19,6 +19,8 @@
                    Default is a random password.
       --servicepw  set the password to use for the "gemma_service" account.
                    Default is a random password.
+      --metapw     set the password to use for the "meta_login" account.
+                   Default is a random password.
       --drop       drop database and all roles
       --help       display this help and exit
 
@@ -46,11 +48,12 @@
 drop=0
 adminpw=`genpw 15`
 servicepw=`genpw 15`
+metapw=`genpw 15`
 
 # Parse options:
 
 OPTS=`getopt \
-      -l help,demo,db:,port:,drop,adminpw:,servicepw: \
+      -l help,demo,db:,port:,drop,adminpw:,servicepw:,metapw: \
       -o Dd:p: -n "$ME" -- "$@"`
 [ $? -eq 0 ] || { usage ; exit 1 ; }
 
@@ -74,6 +77,10 @@
       servicepw="$2"
       shift 2
       ;;
+    --metapw)
+      metapw="$2"
+      shift 2
+      ;;
     --demo|-D)
       demo=1
       shift 1
@@ -120,8 +127,11 @@
        -c "ALTER ROLE sysadmin PASSWORD '$adminpw'"
   psql -qt -p "$port" -d "$db" \
        -c "ALTER ROLE gemma_service PASSWORD '$servicepw'"
+  psql -qt -p "$port" -d "$db" \
+       -c "ALTER ROLE meta_login PASSWORD '$metapw'"
   echo "Default admin user 'sysadmin' created with password '$adminpw'."
   echo "Back end user 'gemma_service' created with password '$servicepw'."
+  echo "Back end user 'meta_login' created with password '$metapw'."
 else
   # Evil mode: drop everything gemma
   echo "Really drop database '$db' and all gemma roles? [type 'yes']: "
--- a/schema/manage_users.sql	Wed Aug 22 16:20:09 2018 +0200
+++ b/schema/manage_users.sql	Wed Aug 22 16:48:56 2018 +0200
@@ -84,6 +84,22 @@
 CREATE TRIGGER create_user INSTEAD OF INSERT ON users.list_users FOR EACH ROW
     EXECUTE PROCEDURE internal.create_user();
 
+CREATE OR REPLACE FUNCTION internal.update_metamorph() RETURNS trigger
+AS $$
+BEGIN
+    EXECUTE format('GRANT %I TO metamorph', NEW.username);
+    RETURN NEW;
+END;
+$$
+    LANGUAGE plpgsql
+    SECURITY DEFINER;
+
+-- Note that PostgreSQL fires triggers for the same event in alphabetical
+-- order! Make sure that the new role is created before this trigger is fired.
+CREATE TRIGGER update_metamorph INSTEAD OF INSERT ON users.list_users
+    FOR EACH ROW
+    EXECUTE PROCEDURE internal.update_metamorph();
+
 
 -- Prevent roles other than sys_admin and pw_reset to update any user but
 -- themselves (affects waterway_admin)
--- a/schema/manage_users_tests.sql	Wed Aug 22 16:20:09 2018 +0200
+++ b/schema/manage_users_tests.sql	Wed Aug 22 16:48:56 2018 +0200
@@ -39,6 +39,14 @@
     $$,
     'New waterway user can be added');
 
+SELECT results_eq($$
+    SELECT pg_has_role('metamorph', 'test1', 'MEMBER')
+    $$,
+    $$
+    SELECT true
+    $$,
+    'New role is GRANTed to metamorph after creation');
+
 SELECT throws_ok($$
     INSERT INTO users.list_users VALUES (
         'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2')
--- a/schema/roles.sql	Wed Aug 22 16:20:09 2018 +0200
+++ b/schema/roles.sql	Wed Aug 22 16:48:56 2018 +0200
@@ -11,3 +11,8 @@
 
 -- A role that is intended to be used for password reset only
 CREATE ROLE pw_reset;
+
+-- 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;
--- a/schema/run_tests.sh	Wed Aug 22 16:20:09 2018 +0200
+++ b/schema/run_tests.sh	Wed Aug 22 16:48:56 2018 +0200
@@ -16,7 +16,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f tap_tests_data.sql \
-    -c 'SELECT plan(45)' \
+    -c 'SELECT plan(46)' \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
     -c 'SELECT * FROM finish()'
--- a/schema/std_login_roles.sql	Wed Aug 22 16:20:09 2018 +0200
+++ b/schema/std_login_roles.sql	Wed Aug 22 16:48:56 2018 +0200
@@ -32,6 +32,8 @@
 -- Used by the back end (gemma)
 CREATE ROLE gemma_service IN ROLE pw_reset LOGIN;
 
+-- Used by GeoServer and backend
+CREATE ROLE meta_login IN ROLE metamorph LOGIN;
 
 --
 -- Remind the caller of his duties
@@ -40,6 +42,7 @@
 BEGIN
   RAISE NOTICE 'Don''t forget to set a password for the user "sysadmin".';
   RAISE NOTICE 'Don''t forget to set a password for the user "gemma_service".';
+  RAISE NOTICE 'Don''t forget to set a password for the user "meta_login".';
 END
 $$;