Mercurial > lada > lada-server

annotate db_schema/Dockerfile @ 1161:ea6b062e5305 pgaudit

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
parents 259a6b638968
children e0a959e652c4
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
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 RUN /etc/init.d/postgresql start && \
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 psql --command "CREATE USER admin WITH SUPERUSER PASSWORD 'secret';"
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 # Adjust PostgreSQL configuration so that remote connections to the
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 # database are possible.
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 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
75 /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
76 RUN echo "listen_addresses='*'" >> $PGCONF
743
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
78 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
79 # Configure logging collector
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
80 # (because we use postgres directly in CMD,
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
81 # 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
82 #
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
83 RUN echo "logging_collector = on" >> $PGCONF
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
84 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
85 #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
86 # for pgaudit/analyze
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
87 RUN echo "log_filename = '%F'" >> $PGCONF
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
88 RUN echo "log_destination = 'csvlog'" >> $PGCONF
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
89 RUN echo "log_connections = on" >> $PGCONF
743
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 # Expose the PostgreSQL port
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 5432
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 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 # Create database
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 # Don't mind scary messages like
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 # 'FATAL: the database system is starting up'.
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 # It's because of the -w
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 #
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
103 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
104 /opt/lada_sql/setup-db.sh && \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
105 /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
106
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 #
c7fcc46c6a57 Add Dockerfile for dockerised DB-server.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 # 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
109 #
1161
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
110 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
111 "--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
112
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
113 # To use pgaudit/analyze from within the container:
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
114 # cd pgaudit/analyze/bin
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
115 # ./pgaudit_analyze /var/log/postgresql/ \
ea6b062e5305 Use pgaudit to generate an audit trail.
Tom Gottfried <tom@intevation.de>
parents: 1086
diff changeset
116 # --log-file /var/log/postgresql/pgaudit_analyze.log
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)