annotate db_schema/setup-db.sh @ 1170:74e5b9630064 pgaudit

Log parameters in statements. This will allow later on to fetch parameters that changed and show them to the client.
author Tom Gottfried <tom@intevation.de>
date Wed, 09 Nov 2016 20:01:00 +0100
parents ea6b062e5305
children 5239306ee55e
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';
1170
74e5b9630064 Log parameters in statements.
Tom Gottfried <tom@intevation.de>
parents: 1161
diff changeset
88 ALTER DATABASE $DB_NAME SET pgaudit.log_parameter TO on;
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
89 GRANT UPDATE, DELETE ON
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
90 land.probe,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
91 land.kommentar_p,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
92 land.ortszuordnung,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
93 land.zusatz_wert,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
94 land.messung,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
95 land.kommentar_m,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
96 land.messwert,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
97 land.status_protokoll
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
98 TO $AUDITOR_ROLE;"
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
99
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
100 if [ "$NO_DATA" != "true" ]; then
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
101 echo import stammdaten
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
102 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
103
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
104 echo import lada test data
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
105 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
106
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
107 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
108 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
109
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
110 TS="0101"
1056
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
111 cd /tmp
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
112 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
113 curl -O \
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
114 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
115 fi
1056
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
116 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
117 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
118 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
119 cd /tmp
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
120 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
121 fi
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)