Mercurial > gemma

annotate schema/install-db.sh @ 345:b97b3172c61a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add staging feature to more tables Added tables currently only have limited visibility for waterway_user but not yet policies for write access.
author Tom Gottfried <tom@intevation.de>
date Mon, 06 Aug 2018 15:19:05 +0200
parents a85f56207d80
children 220a893318fa
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
1 #!/bin/bash
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.
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
22 --drop drop database and all roles
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 --help display this help and exit
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 EOF
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
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 fatal()
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 echo >&2 "$1"
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31 exit 23
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32 }
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
33
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
34 genpw()
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
35 # $1 - length
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
36 {
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
37 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
38 | 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
39 }
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 # Defaults:
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 db=gemma
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 port=5432
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 demo=0
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
46 drop=0
331
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
47 adminpw=`genpw 15`
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
48 servicepw=`genpw 15`
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50 # Parse options:
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
52 OPTS=`getopt \
331
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
53 -l help,demo,db:,port:,drop,adminpw:,servicepw: \
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54 -o Dd:p: -n "$ME" -- "$@"`
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
55 [ $? -eq 0 ] || { usage ; exit 1 ; }
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
56
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
57 eval set -- "$OPTS"
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
58
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
59 while true ; do
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
60 case "$1" in
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
61 --db|-d)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
62 db="$2"
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
63 shift 2
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
64 ;;
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
65 --port|-p)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
66 port="$2"
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
67 shift 2
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
68 ;;
331
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
69 --adminpw)
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
70 adminpw="$2"
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
71 shift 2
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
72 ;;
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
73 --servicepw)
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
74 servicepw="$2"
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
75 shift 2
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
76 ;;
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
77 --demo|-D)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
78 demo=1
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
79 shift 1
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
80 ;;
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
81 --drop)
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
82 drop=1
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
83 shift 1
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
84 ;;
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
85 --help)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
86 { usage ; exit 0 ; }
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
87 ;;
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
88 --)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
89 shift
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
90 break
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
91 ;;
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
92 esac
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
93 done
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 # Main ------------------------------------------------------------
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
97
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
98 if [[ drop -eq 0 ]] ; then
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
99 # Default operation: create schema
256
1ff9aec9326f db-setup script: Be more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents: 255
diff changeset
100 psql -q -p "$port" -f "$BASEDIR/roles.sql"
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
101 createdb -p "$port" "$db"
259
f1280801ba8c db-setup script: Be even more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents: 256
diff changeset
102 psql -qt -p "$port" -d "$db" \
f1280801ba8c db-setup script: Be even more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents: 256
diff changeset
103 -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
104 -f "$BASEDIR/gemma.sql" \
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
105 -f "$BASEDIR/manage_users.sql" \
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
106 -f "$BASEDIR/auth.sql" \
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
107 -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
108
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
109 if [[ $demo -eq 1 ]] ; then
256
1ff9aec9326f db-setup script: Be more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents: 255
diff changeset
110 psql -q -p "$port" -f "$BASEDIR/demo-data/responsibility_areas.sql" \
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
111 -d "$db"
256
1ff9aec9326f db-setup script: Be more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents: 255
diff changeset
112 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
113 -f "$BASEDIR/demo-data/users.sql" -d "$db"
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
114 fi
331
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
115 # set passwords:
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
116 psql -qt -p "$port" -d "$db" \
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
117 -c "ALTER ROLE sysadmin PASSWORD '$adminpw'"
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
118 psql -qt -p "$port" -d "$db" \
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
119 -c "ALTER ROLE gemma_service PASSWORD '$servicepw'"
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
120 echo "Default admin user 'sysadmin' created with password '$adminpw'."
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
121 echo "Back end user 'gemma_service' created with password '$servicepw'."
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
122 else
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
123 # Evil mode: drop everything gemma
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
124 echo "Really drop database '$db' and alle gemma roles? [type 'yes']: "
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
125 read a
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
126 if [[ $a == "yes" ]] ; then
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
127 dropdb -p "$port" "$db"
273
61f1374f0c44 Removed hard coded (and in my case wrong) port from dropping users in install-db.sh script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 260
diff changeset
128 for r in `psql -p $port -t -c '\du' | awk -F '|' \
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 282
diff changeset
129 '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin|pw_reset/ \
282
dfb989088158 Simpified awk to filter users to drop in install-db.sh.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 273
diff changeset
130 {print $1}'`
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
131 do
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
132 dropuser -p "$port" "$r"
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
133 done
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
134 else
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
135 echo "No harm done."
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
136 fi
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
137 fi