Mercurial > gemma
annotate schema/update-db.sh @ 4755:dfd990a4ac64
Hide sys_admin accounts to waterway admins
Since imports and import configurations are authorized based on
the visibility of the user that created the import, that way waterway
admins are no longer allowed to see imports and import configurations
created by sys_admins.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 18 Oct 2019 17:55:12 +0200 |
parents | c1fa4cf8a3f4 |
children |
rev | line source |
---|---|
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 #!/bin/bash -e |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 # This is Free Software under GNU Affero General Public License v >= 3.0 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 # without warranty, see README.md and license for details. |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 # |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 # SPDX-License-Identifier: AGPL-3.0-or-later |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 # License-Filename: LICENSES/AGPL-3.0.txt |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 # |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 # Copyright (C) 2019 by via donau |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 # – Österreichische Wasserstraßen-Gesellschaft mbH |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 # Software engineering by Intevation GmbH |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 # |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 # Author(s): |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 # * Sascha Wilde <wilde@intevation.de> |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 ME=`basename "$0"` |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 BASEDIR=`dirname "$0"` |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 usage() |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 { |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 cat <<EOF |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 $ME [OPTION]... |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 Options: |
4115 | 24 -d, --db=NAME update the database NAME. Default: "gemma" |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 -p, --port=PORT connect do the postgresql cluster at PORT. |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 Default is the postgresql standard port 5432 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 --help display this help and exit |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 EOF |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 fatal() |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 { |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 echo >&2 "$1" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 exit 23 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 genpw() |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 # $1 - length |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 { |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
41 PW='' |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 until [ "$(grep '[^[:alnum:]]' <<<$PW)" -a "$(grep '[[:digit:]]' <<<$PW)" ] |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 do |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 PW=$(dd count=1 if=/dev/urandom 2>/dev/null \ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 | tr -cd '[:alnum:],._!?-' | tail -c "$1") |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 done |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 echo "$PW" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
48 } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 # Defaults: |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 db=gemma |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
53 port=5432 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
54 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
55 # Parse options: |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 OPTS=`getopt \ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
58 -l help,db:,port: \ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
59 -o d:p: -n "$ME" -- "$@"` |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
60 [ $? -eq 0 ] || { usage ; exit 1 ; } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
61 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
62 eval set -- "$OPTS" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
63 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
64 while true ; do |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
65 case "$1" in |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
66 --db|-d) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
67 db="$2" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
68 shift 2 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
69 ;; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
70 --port|-p) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
71 port="$2" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
72 shift 2 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
73 ;; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
74 --help) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
75 { usage ; exit 0 ; } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
76 ;; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
77 --) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
78 shift |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
79 break |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
80 ;; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
81 esac |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
82 done |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
83 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
84 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
85 get_version() |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
86 { |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
87 local ver |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
88 if ver=$( psql -qtA -p "$port" -d "$db" \ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
89 -c 'SELECT get_schema_version()' 2>/dev/null ) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
90 then |
3984
2fa052a44300
Set schema version in update-db.sh, not in extra scripts.
Sascha Wilde <wilde@intevation.de>
parents:
3979
diff
changeset
|
91 echo ${ver:--1} |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
92 else |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
93 echo '-1' |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
94 fi |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
95 } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
96 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
97 # Main ------------------------------------------------------------ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
98 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
99 current_ver=$( get_version ) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
100 |
4108
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
101 for d in "$BASEDIR"/updates/* ; do |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
102 new_ver=$( basename $d ) |
3995
074cf9349c71
Fixed schema update-script to ignore text files.
Sascha Wilde <wilde@intevation.de>
parents:
3984
diff
changeset
|
103 if [ -d "$d" ] && [ "$new_ver" -gt $current_ver ] ; then |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
104 echo "Running updates for $new_ver ..." |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
105 |
4108
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
106 file_args="" |
4661
c1fa4cf8a3f4
Added an SQL script to be manually executed after the 1302 update.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4115
diff
changeset
|
107 for f in "$d"/*.sql ; do |
4108
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
108 file_args+=" -f $f" |
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
109 done |
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
110 |
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
111 psql -1qv ON_ERROR_STOP= -p "$port" -d "$db" $file_args \ |
4004
79adff625ed8
Let psql treat each schema update script as a file
Tom Gottfried <tom@intevation.de>
parents:
3995
diff
changeset
|
112 -c "INSERT INTO gemma_schema_version(version) VALUES ($new_ver)" |
3984
2fa052a44300
Set schema version in update-db.sh, not in extra scripts.
Sascha Wilde <wilde@intevation.de>
parents:
3979
diff
changeset
|
113 |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
114 fi |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
115 done |