--- a/db_schema/Dockerfile	Tue Nov 08 17:46:06 2016 +0100
+++ b/db_schema/Dockerfile	Tue Nov 08 19:21:24 2016 +0100
@@ -1,4 +1,4 @@
-# Docker file for postgresql 9.4 on debain
+# Docker file for the LADA database on Debian
 #
 # build with e.g. `docker build --force-rm=true -t koala/lada_db .',
 # then run with e.g.
@@ -28,7 +28,36 @@
 # Install packages
 #
 RUN apt-get update && \
-    apt-get install -y postgresql-9.4-postgis-2.1 postgis curl unzip
+    apt-get install -y curl unzip make gcc
+RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" \
+    >> /etc/apt/sources.list
+RUN curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
+RUN apt-get update && \
+    apt-get install -y --no-install-recommends \
+            postgresql-9.5-postgis-2.3 postgresql-9.5-postgis-scripts postgis \
+            postgresql-server-dev-9.5 \
+            libdbi-perl libdbd-pg-perl # for pgaudit/analyze
+
+#
+# Add context as working directory
+#
+ADD . /opt/lada_sql/
+WORKDIR /opt/lada_sql/
+
+#
+# Set environment variables
+#
+ENV PGCONF /etc/postgresql/9.5/main/postgresql.conf
+ENV PGDATA /var/lib/postgresql/9.5/main
+
+#
+# Install pgaudit
+#
+# run `git clone https://github.com/pgaudit/pgaudit.git' within context
+# before building image!
+RUN sed -i '/^USE_PGXS/b;1iUSE_PGXS = yes' pgaudit/Makefile
+RUN cd pgaudit && make install
+RUN echo "shared_preload_libraries = 'pgaudit'" >> $PGCONF
 
 #
 # Use user postgres to run the next commands
@@ -43,9 +72,21 @@
 # database are possible.
 #
 RUN echo "host all  all    0.0.0.0/0  md5" >> \
-    /etc/postgresql/9.4/main/pg_hba.conf
+    /etc/postgresql/9.5/main/pg_hba.conf
+RUN echo "listen_addresses='*'" >> $PGCONF
 
-RUN echo "listen_addresses='*'" >> /etc/postgresql/9.4/main/postgresql.conf
+#
+# Configure logging collector
+# (because we use postgres directly in CMD,
+# the usual collection from stderr does not work)
+#
+RUN echo "logging_collector = on" >> $PGCONF
+RUN echo "log_directory = '/var/log/postgresql'" >> $PGCONF
+#RUN echo "log_filename = 'postgresql-9.5-main.log'" >> $PGCONF
+# for pgaudit/analyze
+RUN echo "log_filename = '%F'" >> $PGCONF
+RUN echo "log_destination = 'csvlog'" >> $PGCONF
+RUN echo "log_connections = on" >> $PGCONF
 
 #
 # Expose the PostgreSQL port
@@ -59,15 +100,17 @@
 # 'FATAL: the database system is starting up'.
 # It's because of the -w
 #
-ADD . /opt/lada_sql/
-WORKDIR /opt/lada_sql/
-
-RUN /usr/lib/postgresql/9.4/bin/pg_ctl start -wD /etc/postgresql/9.4/main/ && \
-    /opt/lada_sql/setup-db.sh
+RUN /usr/lib/postgresql/9.5/bin/pg_ctl start -wo "--config_file=$PGCONF" && \
+    /opt/lada_sql/setup-db.sh && \
+    /usr/lib/postgresql/9.5/bin/pg_ctl stop
 
 #
 # Set the default command to run when starting the container
 #
-CMD ["/usr/lib/postgresql/9.4/bin/postgres", "-D", \
-     "/var/lib/postgresql/9.4/main", "-c", \
-     "config_file=/etc/postgresql/9.4/main/postgresql.conf"]
+CMD ["/usr/lib/postgresql/9.5/bin/postgres", \
+     "--config_file=/etc/postgresql/9.5/main/postgresql.conf"]
+
+# To use pgaudit/analyze from within the container:
+# cd pgaudit/analyze/bin
+# ./pgaudit_analyze /var/log/postgresql/ \
+#     --log-file /var/log/postgresql/pgaudit_analyze.log

--- 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