# HG changeset patch # User Tom Gottfried # Date 1478629284 -3600 # Node ID ea6b062e53059ae95d22984c045f65397c741879 # Parent 5d2c68a4c344eb8bdcddd8a90a3b8d886186acae 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. diff -r 5d2c68a4c344 -r ea6b062e5305 db_schema/Dockerfile --- 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 diff -r 5d2c68a4c344 -r ea6b062e5305 db_schema/setup-db.sh --- 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