Mercurial > lada > lada-server
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