Mercurial > lada > lada-server

annotate db_schema/setup-db.sh @ 1173:5239306ee55e pgaudit

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improbe audit trail configuration. Audit only relevant columns, thus not internal stuff like tree_modified. Audit INSERT also, because we will need it to track initial values.
author Tom Gottfried <tom@intevation.de>
date Fri, 11 Nov 2016 16:50:00 +0100
parents 74e5b9630064
children 2e59a51d914f
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;
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)