Mercurial > lada > lada-server

annotate db_schema/setup-db.sh @ 1174:2e59a51d914f pgaudit

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Integrate Audit Log Analyzer. XXX: Startup of the Log Analyzer will only be successful when patched because of a bug that prevents it from recovering from errors (i.e. while postgres is starting).
author Tom Gottfried <tom@intevation.de>
date Fri, 11 Nov 2016 16:52:49 +0100
parents 5239306ee55e
children
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 \
1174
2e59a51d914f Integrate Audit Log Analyzer.
Tom Gottfried <tom@intevation.de>
parents: 1173
diff changeset
68 "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;"
2e59a51d914f Integrate Audit Log Analyzer.
Tom Gottfried <tom@intevation.de>
parents: 1173
diff changeset
69 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/pgaudit/analyze/sql/audit.sql
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;
1173
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
89 GRANT DELETE ON
1161
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.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.messwert,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
95 land.status_protokoll
1173
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
96 TO $AUDITOR_ROLE;
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
97 GRANT INSERT, UPDATE, DELETE ON
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
98 land.kommentar_p,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
99 land.kommentar_m
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
100 TO $AUDITOR_ROLE;
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
101 GRANT INSERT, UPDATE (
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
102 id_alt,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
103 test,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
104 mst_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
105 labor_mst_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
106 hauptproben_nr,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
107 datenbasis_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
108 ba_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
109 probenart_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
110 media_desk,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
111 media,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
112 umw_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
113 probeentnahme_beginn,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
114 probeentnahme_ende,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
115 mittelungsdauer,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
116 erzeuger_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
117 probe_nehmer_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
118 mpl_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
119 mpr_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
120 solldatum_beginn,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
121 solldatum_ende
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
122 ) ON land.probe TO $AUDITOR_ROLE;
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
123 GRANT INSERT, UPDATE (
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
124 ort_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
125 ortszuordnung_typ,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
126 ortszusatztext
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
127 ) ON land.ortszuordnung TO $AUDITOR_ROLE;
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
128 GRANT INSERT, UPDATE (
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
129 pzs_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
130 messwert_pzs,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
131 messfehler,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
132 nwg_zu_messwert
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
133 ) ON land.zusatz_wert TO $AUDITOR_ROLE;
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
134 GRANT INSERT, UPDATE (
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
135 id_alt,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
136 nebenproben_nr,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
137 mmt_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
138 messdauer,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
139 messzeitpunkt,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
140 fertig,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
141 geplant
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
142 ) ON land.messung TO $AUDITOR_ROLE;
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
143 GRANT INSERT, UPDATE (
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
144 messgroesse_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
145 messwert_nwg,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
146 messwert,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
147 messfehler,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
148 nwg_zu_messwert,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
149 meh_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
150 grenzwertueberschreitung
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
151 ) ON land.messwert TO $AUDITOR_ROLE;
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
152 GRANT INSERT, UPDATE (
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
153 mst_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
154 datum,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
155 text,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
156 messungs_id,
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
157 status_kombi
5239306ee55e Improbe audit trail configuration.
Tom Gottfried <tom@intevation.de>
parents: 1170
diff changeset
158 ) ON land.status_protokoll TO $AUDITOR_ROLE;"
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
159
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
160 if [ "$NO_DATA" != "true" ]; then
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
161 echo import stammdaten
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
162 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
163
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
164 echo import lada test data
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
165 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
166
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
167 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
168 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
169
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
170 TS="0101"
1056
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
171 cd /tmp
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
172 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
173 curl -O \
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
174 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
175 fi
1056
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
176 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
177 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
178 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
179 cd /tmp
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
180 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
181 fi
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)