Mercurial > lada > lada-server

annotate db_schema/Dockerfile @ 1173:5239306ee55e pgaudit

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improbe audit trail configuration. Audit only relevant columns, thus not internal stuff like tree_modified. Audit INSERT also, because we will need it to track initial values.
author Tom Gottfried <tom@intevation.de>
date Fri, 11 Nov 2016 16:50:00 +0100
parents eefd0ca9e42f
children 2e59a51d914f
rev   line source
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
1 # Docker file for the LADA database on Debian
743
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 # build with e.g. `docker build --force-rm=true -t koala/lada_db .',
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 # then run with e.g.
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 # `docker run --name lada_db -dp 2345:5432 koala/lada_db:latest'
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 #
1086
259a6b638968 Prepare for easier schema update testing.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
7 # For easier testing of schema or example data changes, it can be useful to add
259a6b638968 Prepare for easier schema update testing.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
8 # `-v $PWD:/opt/lada_sql/' and run setup-db.sh within the container.
259a6b638968 Prepare for easier schema update testing.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
9 #
743
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 FROM debian:jessie
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 MAINTAINER tom.gottfried@intevation.de
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 # Use utf-8
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 RUN echo \
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 "locales locales/locales_to_be_generated multiselect en_US.UTF-8 UTF-8" | \
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 debconf-set-selections && \
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 echo "locales locales/default_environment_locale select en_US.UTF-8" | \
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 debconf-set-selections
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 RUN apt-get update -y && apt-get install -y locales
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 ENV LC_ALL en_US.UTF-8
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 # Install packages
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 RUN apt-get update && \
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
31 apt-get install -y curl unzip make gcc
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
32 RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
33 >> /etc/apt/sources.list
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
34 RUN curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
35 RUN apt-get update && \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
36 apt-get install -y --no-install-recommends \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
37 postgresql-9.5-postgis-2.3 postgresql-9.5-postgis-scripts postgis \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
38 postgresql-server-dev-9.5 \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
39 libdbi-perl libdbd-pg-perl # for pgaudit/analyze
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
40
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
41 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
42 # Add context as working directory
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
43 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
44 ADD . /opt/lada_sql/
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
45 WORKDIR /opt/lada_sql/
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
46
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
47 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
48 # Set environment variables
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
49 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
50 ENV PGCONF /etc/postgresql/9.5/main/postgresql.conf
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
51 ENV PGDATA /var/lib/postgresql/9.5/main
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
52
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
53 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
54 # Install pgaudit
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
55 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
56 # run `git clone https://github.com/pgaudit/pgaudit.git' within context
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
57 # before building image!
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
58 RUN sed -i '/^USE_PGXS/b;1iUSE_PGXS = yes' pgaudit/Makefile
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
59 RUN cd pgaudit && make install
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
60 RUN echo "shared_preload_libraries = 'pgaudit'" >> $PGCONF
743
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 # Use user postgres to run the next commands
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 USER postgres
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66
1162
e0a959e652c4 Remove creation of unused superuser and create missing directory.
Tom Gottfried <tom@intevation.de>
parents: 1161
diff changeset
67 # XXX: Seems to fail on initdb issued by package installation
e0a959e652c4 Remove creation of unused superuser and create missing directory.
Tom Gottfried <tom@intevation.de>
parents: 1161
diff changeset
68 # (due to /usr/sbin/policy-rc.d ?).
e0a959e652c4 Remove creation of unused superuser and create missing directory.
Tom Gottfried <tom@intevation.de>
parents: 1161
diff changeset
69 # See also https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=739276
e0a959e652c4 Remove creation of unused superuser and create missing directory.
Tom Gottfried <tom@intevation.de>
parents: 1161
diff changeset
70 RUN mkdir /var/run/postgresql/9.5-main.pg_stat_tmp
743
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 # Adjust PostgreSQL configuration so that remote connections to the
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 # database are possible.
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 RUN echo "host all all 0.0.0.0/0 md5" >> \
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
77 /etc/postgresql/9.5/main/pg_hba.conf
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
78 RUN echo "listen_addresses='*'" >> $PGCONF
743
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
80 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
81 # Configure logging collector
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
82 # (because we use postgres directly in CMD,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
83 # the usual collection from stderr does not work)
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
84 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
85 RUN echo "logging_collector = on" >> $PGCONF
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
86 RUN echo "log_directory = '/var/log/postgresql'" >> $PGCONF
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
87 #RUN echo "log_filename = 'postgresql-9.5-main.log'" >> $PGCONF
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
88 # for pgaudit/analyze
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
89 RUN echo "log_filename = '%F'" >> $PGCONF
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
90 RUN echo "log_destination = 'csvlog'" >> $PGCONF
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
91 RUN echo "log_connections = on" >> $PGCONF
743
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 # Expose the PostgreSQL port
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 EXPOSE 5432
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 # Create database
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 # Don't mind scary messages like
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 # 'FATAL: the database system is starting up'.
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 # It's because of the -w
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 #
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
105 RUN /usr/lib/postgresql/9.5/bin/pg_ctl start -wo "--config_file=$PGCONF" && \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
106 /opt/lada_sql/setup-db.sh && \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
107 /usr/lib/postgresql/9.5/bin/pg_ctl stop
743
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 # Set the default command to run when starting the container
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 #
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
112 CMD ["/usr/lib/postgresql/9.5/bin/postgres", \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
113 "--config_file=/etc/postgresql/9.5/main/postgresql.conf"]
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
114
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
115 # To use pgaudit/analyze from within the container:
1169
eefd0ca9e42f Add hint for creation of schema objects for log analyzer.
Tom Gottfried <tom@intevation.de>
parents: 1162
diff changeset
116 # psql -f pgaudit/analyze/sql/audit.sql lada
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
117 # cd pgaudit/analyze/bin
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
118 # ./pgaudit_analyze /var/log/postgresql/ \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
119 # --log-file /var/log/postgresql/pgaudit_analyze.log
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)