gemma: schema/install-db.sh annotate

annotate schema/install-db.sh @ 470:638371a0e557

Do not touch existing roles on database setup install-db.sh used to change passwords of standard login roles if they already existed, e.g. when running run_tests.sh, thus breaking everything relying on the previously set passwords. Further, the messages stating the users had been created were misleading in such cases. It is an error now to run std_login_roles.sql without giving passwords as variables to be interpolated. Simple SELECT statements are used now to emit messages, because variable interpolation is not possible within plpgsql code.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 22 Aug 2018 18:46:30 +0200
parents	ab07eb290fa3
children	3af7ca761f6a

rev	line source
377 220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	1 #!/bin/bash -e
244 4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	2 # Author(s):
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	3 # Sascha Wilde <wilde@intevation.de>
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	4
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	5 ME=`basename "$0"`
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	6 BASEDIR=`dirname "$0"`
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	7
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	8 usage()
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	9 {
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	10 cat <<EOF
260 c2cef3ce8853 db-setup script: Improved help message. Sascha Wilde <wilde@intevation.de> parents: 259 diff changeset	11 $ME [OPTION]...
244 4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	12
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	13 Options:
260 c2cef3ce8853 db-setup script: Improved help message. Sascha Wilde <wilde@intevation.de> parents: 259 diff changeset	14 -d, --db=NAME create the database NAME. Default: "gemma"
c2cef3ce8853 db-setup script: Improved help message. Sascha Wilde <wilde@intevation.de> parents: 259 diff changeset	15 -p, --port=PORT connect do the postgresql cluster at PORT.
c2cef3ce8853 db-setup script: Improved help message. Sascha Wilde <wilde@intevation.de> parents: 259 diff changeset	16 Default is the postgresql standard port 5432
c2cef3ce8853 db-setup script: Improved help message. Sascha Wilde <wilde@intevation.de> parents: 259 diff changeset	17 -D, --demo also install demo accounts and data
331 a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	18 --adminpw set the password to use for the "sysadmin" account.
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	19 Default is a random password.
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	20 --servicepw set the password to use for the "gemma_service" account.
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	21 Default is a random password.
463 5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 436 diff changeset	22 --metapw set the password to use for the "meta_login" account.
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 436 diff changeset	23 Default is a random password.
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	24 --drop drop database and all roles
244 4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	25 --help display this help and exit
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	26
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	27 EOF
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	28 }
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	29
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	30 fatal()
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	31 {
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	32 echo >&2 "$1"
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	33 exit 23
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	34 }
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	35
330 fd04bccae6ca Create standard roles as part of the base schema. Sascha Wilde <wilde@intevation.de> parents: 319 diff changeset	36 genpw()
fd04bccae6ca Create standard roles as part of the base schema. Sascha Wilde <wilde@intevation.de> parents: 319 diff changeset	37 # $1 - length
fd04bccae6ca Create standard roles as part of the base schema. Sascha Wilde <wilde@intevation.de> parents: 319 diff changeset	38 {
fd04bccae6ca Create standard roles as part of the base schema. Sascha Wilde <wilde@intevation.de> parents: 319 diff changeset	39 dd count=1 if=/dev/urandom 2>/dev/null \
fd04bccae6ca Create standard roles as part of the base schema. Sascha Wilde <wilde@intevation.de> parents: 319 diff changeset	40 \| tr -cd '[:alnum:],._!?-' \| tail -c "$1"
fd04bccae6ca Create standard roles as part of the base schema. Sascha Wilde <wilde@intevation.de> parents: 319 diff changeset	41 }
244 4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	42
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	43 # Defaults:
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	44
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	45 db=gemma
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	46 port=5432
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	47 demo=0
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	48 drop=0
331 a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	49 adminpw=`genpw 15`
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	50 servicepw=`genpw 15`
463 5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 436 diff changeset	51 metapw=`genpw 15`
244 4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	52
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	53 # Parse options:
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	54
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	55 OPTS=`getopt \
463 5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 436 diff changeset	56 -l help,demo,db:,port:,drop,adminpw:,servicepw:,metapw: \
244 4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	57 -o Dd:p: -n "$ME" -- "$@"`
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	58 [ $? -eq 0 ] \|\| { usage ; exit 1 ; }
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	59
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	60 eval set -- "$OPTS"
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	61
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	62 while true ; do
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	63 case "$1" in
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	64 --db\|-d)
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	65 db="$2"
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	66 shift 2
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	67 ;;
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	68 --port\|-p)
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	69 port="$2"
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	70 shift 2
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	71 ;;
331 a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	72 --adminpw)
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	73 adminpw="$2"
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	74 shift 2
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	75 ;;
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	76 --servicepw)
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	77 servicepw="$2"
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	78 shift 2
a85f56207d80 db-setup script: Allow to set passwords manually. Sascha Wilde <wilde@intevation.de> parents: 330 diff changeset	79 ;;
463 5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 436 diff changeset	80 --metapw)
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 436 diff changeset	81 metapw="$2"
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 436 diff changeset	82 shift 2
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 436 diff changeset	83 ;;
244 4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	84 --demo\|-D)
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	85 demo=1
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	86 shift 1
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	87 ;;
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	88 --drop)
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	89 drop=1
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	90 shift 1
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	91 ;;
244 4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	92 --help)
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	93 { usage ; exit 0 ; }
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	94 ;;
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	95 --)
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	96 shift
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	97 break
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	98 ;;
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	99 esac
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	100 done
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	101
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	102
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	103 # Main ------------------------------------------------------------
4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	104
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	105 if [[ drop -eq 0 ]] ; then
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	106 # Default operation: create schema
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	107 createdb -p "$port" "$db"
378 88aa790d1437 Do not assume existance of a specific database in setup Tom Gottfried <tom@intevation.de> parents: 377 diff changeset	108 psql -q -p "$port" -f "$BASEDIR/roles.sql" -d "$db"
377 220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	109 psql -qtv ON_ERROR_STOP= -p "$port" -d "$db" \
259 f1280801ba8c db-setup script: Be even more quiet during creation. Sascha Wilde <wilde@intevation.de> parents: 256 diff changeset	110 -c "SET client_min_messages TO WARNING;" \
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	111 -f "$BASEDIR/gemma.sql" \
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	112 -f "$BASEDIR/manage_users.sql" \
377 220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	113 -f "$BASEDIR/auth.sql"
470 638371a0e557 Do not touch existing roles on database setup Tom Gottfried <tom@intevation.de> parents: 465 diff changeset	114
638371a0e557 Do not touch existing roles on database setup Tom Gottfried <tom@intevation.de> parents: 465 diff changeset	115 # setup initial login roles with given passwords:
377 220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	116 psql -qt -p "$port" -d "$db" \
470 638371a0e557 Do not touch existing roles on database setup Tom Gottfried <tom@intevation.de> parents: 465 diff changeset	117 -v adminpw="$adminpw" -v servicepw="$servicepw" -v metapw="$metapw" \
330 fd04bccae6ca Create standard roles as part of the base schema. Sascha Wilde <wilde@intevation.de> parents: 319 diff changeset	118 -f "$BASEDIR/std_login_roles.sql"
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 244 diff changeset	119
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	120 if [[ $demo -eq 1 ]] ; then
377 220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	121 psql -qv ON_ERROR_STOP= -p "$port" \
220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	122 -f "$BASEDIR/demo-data/responsibility_areas.sql" \
220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	123 -f "$BASEDIR/demo-data/users.sql" -d "$db"
220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	124 psql -q -p "$port" -f "$BASEDIR/demo-data/roles.sql" \
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	125 -d "$db"
377 220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	126
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	127 fi
470 638371a0e557 Do not touch existing roles on database setup Tom Gottfried <tom@intevation.de> parents: 465 diff changeset	128
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	129 else
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	130 # Evil mode: drop everything gemma
377 220a893318fa Ignore only role creation errors in database setup Tom Gottfried <tom@intevation.de> parents: 331 diff changeset	131 echo "Really drop database '$db' and all gemma roles? [type 'yes']: "
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	132 read a
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	133 if [[ $a == "yes" ]] ; then
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	134 dropdb -p "$port" "$db"
436 8feb64128c34 Fixed install db script to cope with user roles containing whitespace. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 378 diff changeset	135 psql -p $port -A -t -c '\du' \| awk -F '\|' -v port=$port \
465 ab07eb290fa3 Drop metamorph role too when tearing down database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	136 '$1 "." $3 ~ /waterway_user\|waterway_admin\|sys_admin\|pw_reset\|metamorph/ \
436 8feb64128c34 Fixed install db script to cope with user roles containing whitespace. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 378 diff changeset	137 { system("dropuser -p " port " \"" $1 "\"") }'
255 c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	138 else
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	139 echo "No harm done."
c36bd39782c1 Added simple --drop to db setup script. Sascha Wilde <wilde@intevation.de> parents: 252 diff changeset	140 fi
244 4ebb791b8278 Added simple script to install db schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	141 fi

Mercurial > gemma

annotate schema/install-db.sh @ 470:638371a0e557