Trusted Docker Image for PostgreSQL

This image contains a basic install of PostgreSQL with contrib, PostGIS, & pgRouting.

No roles/users or databases have been created.

Setup

1. Create a directory on the host to hold the pgdata files

   mkdir /path/to/pgdata

2. Start & stop the container to initialize the database

   docker run --name pg --restart=always -d -p 5432:5432 -v /path/to/pgdata:/pgdata hopsoft/postgres
   docker stop pg

3. Update the config files

   This config setup is insecure & temporary. We only use it to create a superuser.

   vim /path/to/pgdata/postgresql.conf
   
   # set the following
   # listen_addresses = '*'

   vim /path/to/pgdata/pg_hba.conf
   
   # add the following line
   # TYPE    DATABASE        USER            ADDRESS                 METHOD
   # host    all             postgres        0.0.0.0/0               trust

4. Start the container & create a superuser

   docker start pg
   psql -h "$(docker inspect pg | grep IPAddress | cut -d '"' -f 4)" -U postgres

   CREATE ROLE root SUPERUSER LOGIN PASSWORD 'secret';
   CREATE DATABASE root OWNER root;

   Be sure to change the username/password to something more secure.

5. Stop the container

   docker stop pg

Production Use

1. Modify the configuration files for production use

   vim /path/to/pgdata/pg_hba.conf
   
   # delete this line
   # TYPE    DATABASE        USER            ADDRESS                 METHOD
   # host    all             postgres        0.0.0.0/0               trust
   #
   # add one of the following line(s)... the second is more secure
   # TYPE    DATABASE        USER            ADDRESS                 METHOD
   # host    all             root            0.0.0.0/0               md5
   # host    all             root            HOST_IP_ADDRESS/32      md5

   Optional

   vim /path/to/pgdata/postgresql.conf
   
   # optionally set the following
   # it will tighten security by only allowing the host to connect
   # listen_addresses = 'CONTAINER_IP_ADDRESS'

2. Start the container for production use

   docker start pg

3. Connect to postgres

   psql -h "$(docker inspect pg | grep IPAddress | cut -d '"' -f 4)" -U root

Configuration Changes

1. Modify the configuration files

   vim /path/to/pgdata/postgresql.conf
   vim /path/to/pgdata/pg_hba.conf

2. Restart the container

   docker restart pg

Building the Image

For those who want to build the image manually.

Linux

git clone https://github.com/hopsoft/docker-postgres.git
cd docker-postgres
docker build -t hopsoft/postgres /vagrant

OSX

git clone https://github.com/hopsoft/docker-postgres.git
cd docker-postgres
vagrant up
vagrant ssh
sudo docker build -t hopsoft/postgres /vagrant