Mercurial > lada > lada-server

annotate db_schema/Dockerfile @ 1328:01b17bf4d13e pgaudit

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Audit trail is now based on trigger functions.
author Tom Gottfried <tom@intevation.de>
date Mon, 27 Mar 2017 15:39:58 +0200
parents 2e59a51d914f
children
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 #
1174
2e59a51d914f Integrate Audit Log Analyzer.
Tom Gottfried <tom@intevation.de>
parents: 1169
diff changeset
112 CMD /usr/lib/postgresql/9.5/bin/postgres --config_file=$PGCONF & \
2e59a51d914f Integrate Audit Log Analyzer.
Tom Gottfried <tom@intevation.de>
parents: 1169
diff changeset
113 pgaudit/analyze/bin/pgaudit_analyze /var/log/postgresql/ \
2e59a51d914f Integrate Audit Log Analyzer.
Tom Gottfried <tom@intevation.de>
parents: 1169
diff changeset
114 --log-file /var/log/postgresql/pgaudit_analyze.log
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)