Mercurial > gemma
changeset 463:5611cf72cc92
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 $$;