Mercurial > lada > lada-server

annotate db_schema/setup-db.sh @ 1161:ea6b062e5305 pgaudit

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use pgaudit to generate an audit trail. Upgrade to PostgreSQL 9.5 because it is a requirement for pgaudit. pgaudit/analyze can be used to transfer the audit trail into the database, but it seems to be easy to do this with pgaudit directly with some changes to the code.
author Tom Gottfried <tom@intevation.de>
date Tue, 08 Nov 2016 19:21:24 +0100
parents 186d602e031a
children 74e5b9630064
rev   line source
933
56d2dd058906 Stop on error.
Tom Gottfried <tom@intevation.de>
parents: 932
diff changeset
1 #!/bin/sh -e
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
2 # SYNOPSIS
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
3 # ./setup-db.sh [-cn] [ROLE_NAME] [ROLE_PW] [DB_NAME]
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
4 # -c clean - drop an existing database
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
5 # -n no data - do not import example data
934
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
6 # ROLE_NAME name of db user (default = lada)
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
7 # ROLE_PW login password (default = ROLE_NAME)
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
8 # DB_NAME name of the databaes (default = ROLE_NAME)
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
9 #
934
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
10 # There will be used a remote database server if there exists the
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
11 # enviroment variable DB_SRV and optional DB_PORT
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
12
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 DIR=`dirname $0`
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
15 while getopts "cn" opt; do
932
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
16 case "$opt" in
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
17 c)
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
18 DROP_DB="true"
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
19 ;;
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
20 n)
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
21 NO_DATA="true"
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
22 ;;
932
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
23 esac
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
24 done
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
25
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
26 shift $((OPTIND-1))
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
27
854
e797391714a0 Allow setting variables via commandline.
Tom Gottfried <tom@intevation.de>
parents: 764
diff changeset
28 ROLE_NAME=${1:-lada}
934
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
29 echo "ROLE_NAME = $ROLE_NAME"
854
e797391714a0 Allow setting variables via commandline.
Tom Gottfried <tom@intevation.de>
parents: 764
diff changeset
30 ROLE_PW=${2:-$ROLE_NAME}
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
31 echo "ROLE_PW = $ROLE_PW"
854
e797391714a0 Allow setting variables via commandline.
Tom Gottfried <tom@intevation.de>
parents: 764
diff changeset
32 DB_NAME=${3:-$ROLE_NAME}
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
33 echo "DB_NAME = $DB_NAME"
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
34 AUDITOR_ROLE="auditor"
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
36 # if variable DB_SRV and otional DB_PORT is set a remote database connection will be used
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
37 if [ -n "$DB_SRV" ] ; then DB_CONNECT_STRING="-h $DB_SRV" ; fi
934
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
38 if [ -n "$DB_SRV" -a -n "$DB_PORT" ] ; then
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
39 DB_CONNECT_STRING="$DB_CONNECT_STRING -p $DB_PORT"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
40 fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
41 DB_CONNECT_STRING="$DB_CONNECT_STRING -U postgres"
928
6ab17d8b5558 erase mpr_id values in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 925
diff changeset
42 echo "DB_CONNECT_STRING = $DB_CONNECT_STRING"
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents: 854
diff changeset
43
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
44 if [ `psql $DB_CONNECT_STRING -t --command "SELECT count(*) FROM pg_catalog.pg_user WHERE usename = '$ROLE_NAME'"` -eq 0 ] ; then
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
45 echo create user $ROLE_NAME
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
46 psql $DB_CONNECT_STRING --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
47 fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
48
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
49 if [ $(psql $DB_CONNECT_STRING -t --command \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
50 "SELECT count(*) FROM pg_roles WHERE rolname = '$AUDITOR_ROLE'") \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
51 -eq 0 ]
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
52 then
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
53 echo create user $AUDITOR_ROLE
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
54 psql $DB_CONNECT_STRING --command "CREATE ROLE $AUDITOR_ROLE"
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
55 fi
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
56
932
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
57 if [ "$DROP_DB" = "true" ] && psql $DB_CONNECT_STRING -l | grep -q "^ $DB_NAME " ; then
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
58 echo drop db $DB_NAME
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
59 psql $DB_CONNECT_STRING --command "DROP DATABASE $DB_NAME"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
60 fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
61
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
62 echo create db $DB_NAME
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
63 psql $DB_CONNECT_STRING --command \
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
64 "CREATE DATABASE $DB_NAME WITH OWNER = $ROLE_NAME ENCODING = 'UTF8'"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
65
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
66 echo create extensions
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
67 psql $DB_CONNECT_STRING -d $DB_NAME --command \
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
68 "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
69 CREATE EXTENSION IF NOT EXISTS pgaudit;"
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents: 854
diff changeset
70
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
71 echo create stammdaten schema
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
72 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
73
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
74 echo create lada schema
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
75 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
76
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
77 echo set grants
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
78 psql $DB_CONNECT_STRING -d $DB_NAME --command \
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
79 "GRANT USAGE ON SCHEMA stammdaten, land TO $ROLE_NAME;
764
9d2070b84238 Hibernate needs nextval to insert new datasets.
Tom Gottfried <tom@intevation.de>
parents: 752
diff changeset
80 GRANT USAGE
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
81 ON ALL SEQUENCES IN SCHEMA stammdaten, land TO $ROLE_NAME;
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
83 ON ALL TABLES IN SCHEMA stammdaten, land TO $ROLE_NAME;"
908
51051e85348f Added data dumps.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 866
diff changeset
84
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
85 echo configure audit trail
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
86 psql $DB_CONNECT_STRING -d $DB_NAME --command \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
87 "ALTER DATABASE $DB_NAME SET pgaudit.role TO '$AUDITOR_ROLE';
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
88 GRANT UPDATE, DELETE ON
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
89 land.probe,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
90 land.kommentar_p,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
91 land.ortszuordnung,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
92 land.zusatz_wert,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
93 land.messung,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
94 land.kommentar_m,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
95 land.messwert,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
96 land.status_protokoll
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
97 TO $AUDITOR_ROLE;"
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
98
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
99 if [ "$NO_DATA" != "true" ]; then
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
100 echo import stammdaten
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
101 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data.sql
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
102
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
103 echo import lada test data
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
104 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_data.sql
1056
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
105
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
106 echo create user $ROLE_NAME
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
107 psql $DB_CONNECT_STRING -d $DB_NAME --command "CREATE SCHEMA geo AUTHORIZATION $ROLE_NAME"
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
108
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
109 TS="0101"
1056
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
110 cd /tmp
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
111 if [ ! -f vg250_${TS}.utm32s.shape.ebenen.zip ]; then
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
112 curl -O \
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
113 http://sg.geodatenzentrum.de/web_download/vg/vg250_${TS}/utm32s/shape/vg250_${TS}.utm32s.shape.ebenen.zip
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
114 fi
1056
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
115 unzip vg250_${TS}.utm32s.shape.ebenen.zip "*VG250_GEM*"
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
116 cd vg250_${TS}.utm32s.shape.ebenen/vg250_ebenen/
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
117 shp2pgsql VG250_GEM geo.gem_utm | psql -q $DB_CONNECT_STRING -d $DB_NAME
1056
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
118 cd /tmp
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
119 rm -rf vg250_${TS}.utm32s.shape.ebenen
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
120 fi
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)