changeset 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 (2016-11-08)
parents 5d2c68a4c344
children e0a959e652c4
files db_schema/Dockerfile db_schema/setup-db.sh
diffstat 2 files changed, 82 insertions(+), 14 deletions(-) [+]
line wrap: on
line diff
--- 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
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)