Mercurial > lada > lada-server
diff 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 |
line wrap: on
line diff
--- a/db_schema/setup-db.sh Tue Nov 08 17:46:06 2016 +0100 +++ b/db_schema/setup-db.sh Tue Nov 08 19:21:24 2016 +0100 @@ -31,6 +31,7 @@ echo "ROLE_PW = $ROLE_PW" DB_NAME=${3:-$ROLE_NAME} echo "DB_NAME = $DB_NAME" +AUDITOR_ROLE="auditor" # if variable DB_SRV and otional DB_PORT is set a remote database connection will be used if [ -n "$DB_SRV" ] ; then DB_CONNECT_STRING="-h $DB_SRV" ; fi @@ -45,6 +46,14 @@ psql $DB_CONNECT_STRING --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';" fi +if [ $(psql $DB_CONNECT_STRING -t --command \ + "SELECT count(*) FROM pg_roles WHERE rolname = '$AUDITOR_ROLE'") \ + -eq 0 ] +then + echo create user $AUDITOR_ROLE + psql $DB_CONNECT_STRING --command "CREATE ROLE $AUDITOR_ROLE" +fi + if [ "$DROP_DB" = "true" ] && psql $DB_CONNECT_STRING -l | grep -q "^ $DB_NAME " ; then echo drop db $DB_NAME psql $DB_CONNECT_STRING --command "DROP DATABASE $DB_NAME" @@ -54,15 +63,17 @@ psql $DB_CONNECT_STRING --command \ "CREATE DATABASE $DB_NAME WITH OWNER = $ROLE_NAME ENCODING = 'UTF8'" -echo create postgis extension +echo create extensions psql $DB_CONNECT_STRING -d $DB_NAME --command \ - "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public" + "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public; + CREATE EXTENSION IF NOT EXISTS pgaudit;" echo create stammdaten schema psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql echo create lada schema psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql + echo set grants psql $DB_CONNECT_STRING -d $DB_NAME --command \ "GRANT USAGE ON SCHEMA stammdaten, land TO $ROLE_NAME; @@ -71,6 +82,20 @@ GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA stammdaten, land TO $ROLE_NAME;" +echo configure audit trail +psql $DB_CONNECT_STRING -d $DB_NAME --command \ + "ALTER DATABASE $DB_NAME SET pgaudit.role TO '$AUDITOR_ROLE'; + GRANT UPDATE, DELETE ON + land.probe, + land.kommentar_p, + land.ortszuordnung, + land.zusatz_wert, + land.messung, + land.kommentar_m, + land.messwert, + land.status_protokoll + TO $AUDITOR_ROLE;" + if [ "$NO_DATA" != "true" ]; then echo import stammdaten psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data.sql