Mercurial > lada > lada-server
comparison db_schema/setup-db.sh @ 1161:ea6b062e5305 pgaudit
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 |
comparison
equal
deleted
inserted
replaced
| 1160:5d2c68a4c344 | 1161:ea6b062e5305 |
|---|---|
| 29 echo "ROLE_NAME = $ROLE_NAME" | 29 echo "ROLE_NAME = $ROLE_NAME" |
| 30 ROLE_PW=${2:-$ROLE_NAME} | 30 ROLE_PW=${2:-$ROLE_NAME} |
| 31 echo "ROLE_PW = $ROLE_PW" | 31 echo "ROLE_PW = $ROLE_PW" |
| 32 DB_NAME=${3:-$ROLE_NAME} | 32 DB_NAME=${3:-$ROLE_NAME} |
| 33 echo "DB_NAME = $DB_NAME" | 33 echo "DB_NAME = $DB_NAME" |
| 34 AUDITOR_ROLE="auditor" | |
| 34 | 35 |
| 35 # if variable DB_SRV and otional DB_PORT is set a remote database connection will be used | 36 # if variable DB_SRV and otional DB_PORT is set a remote database connection will be used |
| 36 if [ -n "$DB_SRV" ] ; then DB_CONNECT_STRING="-h $DB_SRV" ; fi | 37 if [ -n "$DB_SRV" ] ; then DB_CONNECT_STRING="-h $DB_SRV" ; fi |
| 37 if [ -n "$DB_SRV" -a -n "$DB_PORT" ] ; then | 38 if [ -n "$DB_SRV" -a -n "$DB_PORT" ] ; then |
| 38 DB_CONNECT_STRING="$DB_CONNECT_STRING -p $DB_PORT" | 39 DB_CONNECT_STRING="$DB_CONNECT_STRING -p $DB_PORT" |
| 43 if [ `psql $DB_CONNECT_STRING -t --command "SELECT count(*) FROM pg_catalog.pg_user WHERE usename = '$ROLE_NAME'"` -eq 0 ] ; then | 44 if [ `psql $DB_CONNECT_STRING -t --command "SELECT count(*) FROM pg_catalog.pg_user WHERE usename = '$ROLE_NAME'"` -eq 0 ] ; then |
| 44 echo create user $ROLE_NAME | 45 echo create user $ROLE_NAME |
| 45 psql $DB_CONNECT_STRING --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';" | 46 psql $DB_CONNECT_STRING --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';" |
| 46 fi | 47 fi |
| 47 | 48 |
| 49 if [ $(psql $DB_CONNECT_STRING -t --command \ | |
| 50 "SELECT count(*) FROM pg_roles WHERE rolname = '$AUDITOR_ROLE'") \ | |
| 51 -eq 0 ] | |
| 52 then | |
| 53 echo create user $AUDITOR_ROLE | |
| 54 psql $DB_CONNECT_STRING --command "CREATE ROLE $AUDITOR_ROLE" | |
| 55 fi | |
| 56 | |
| 48 if [ "$DROP_DB" = "true" ] && psql $DB_CONNECT_STRING -l | grep -q "^ $DB_NAME " ; then | 57 if [ "$DROP_DB" = "true" ] && psql $DB_CONNECT_STRING -l | grep -q "^ $DB_NAME " ; then |
| 49 echo drop db $DB_NAME | 58 echo drop db $DB_NAME |
| 50 psql $DB_CONNECT_STRING --command "DROP DATABASE $DB_NAME" | 59 psql $DB_CONNECT_STRING --command "DROP DATABASE $DB_NAME" |
| 51 fi | 60 fi |
| 52 | 61 |
| 53 echo create db $DB_NAME | 62 echo create db $DB_NAME |
| 54 psql $DB_CONNECT_STRING --command \ | 63 psql $DB_CONNECT_STRING --command \ |
| 55 "CREATE DATABASE $DB_NAME WITH OWNER = $ROLE_NAME ENCODING = 'UTF8'" | 64 "CREATE DATABASE $DB_NAME WITH OWNER = $ROLE_NAME ENCODING = 'UTF8'" |
| 56 | 65 |
| 57 echo create postgis extension | 66 echo create extensions |
| 58 psql $DB_CONNECT_STRING -d $DB_NAME --command \ | 67 psql $DB_CONNECT_STRING -d $DB_NAME --command \ |
| 59 "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public" | 68 "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public; |
| 69 CREATE EXTENSION IF NOT EXISTS pgaudit;" | |
| 60 | 70 |
| 61 echo create stammdaten schema | 71 echo create stammdaten schema |
| 62 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql | 72 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql |
| 63 | 73 |
| 64 echo create lada schema | 74 echo create lada schema |
| 65 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql | 75 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql |
| 76 | |
| 66 echo set grants | 77 echo set grants |
| 67 psql $DB_CONNECT_STRING -d $DB_NAME --command \ | 78 psql $DB_CONNECT_STRING -d $DB_NAME --command \ |
| 68 "GRANT USAGE ON SCHEMA stammdaten, land TO $ROLE_NAME; | 79 "GRANT USAGE ON SCHEMA stammdaten, land TO $ROLE_NAME; |
| 69 GRANT USAGE | 80 GRANT USAGE |
| 70 ON ALL SEQUENCES IN SCHEMA stammdaten, land TO $ROLE_NAME; | 81 ON ALL SEQUENCES IN SCHEMA stammdaten, land TO $ROLE_NAME; |
| 71 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES | 82 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES |
| 72 ON ALL TABLES IN SCHEMA stammdaten, land TO $ROLE_NAME;" | 83 ON ALL TABLES IN SCHEMA stammdaten, land TO $ROLE_NAME;" |
| 84 | |
| 85 echo configure audit trail | |
| 86 psql $DB_CONNECT_STRING -d $DB_NAME --command \ | |
| 87 "ALTER DATABASE $DB_NAME SET pgaudit.role TO '$AUDITOR_ROLE'; | |
| 88 GRANT UPDATE, DELETE ON | |
| 89 land.probe, | |
| 90 land.kommentar_p, | |
| 91 land.ortszuordnung, | |
| 92 land.zusatz_wert, | |
| 93 land.messung, | |
| 94 land.kommentar_m, | |
| 95 land.messwert, | |
| 96 land.status_protokoll | |
| 97 TO $AUDITOR_ROLE;" | |
| 73 | 98 |
| 74 if [ "$NO_DATA" != "true" ]; then | 99 if [ "$NO_DATA" != "true" ]; then |
| 75 echo import stammdaten | 100 echo import stammdaten |
| 76 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data.sql | 101 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data.sql |
| 77 | 102 |