In this post I will explain how to provide a secure postgres server docker container. This is useful when developing certain applications, for example a Django application. You can only run a this script and it will automatically detect if an old version of the container exists, delete it and deploy a new one. Or just to deploy a quick and secure by default postgres docker container. The limit is your imagination!

The files used in this post are part of my DevOps Tools Github repository. Please visit it and look around, you might get some good ideas from it.

Problem

I am developing a Django application and during the development I need to create a secure by default postgres docker container. I also need to quickly reset the db constantly for testing purposes.

Proposal

Create a script to automate the process and minimize human error.

Requirements

This environment was built with:

• Python 3.6.8
• Pipenv version 11.9.0
• Docker version 19.03.8

TL;DR

Quick and dirty way of doing this. Use on your own risk!

$ mkdir djangoproject
$ cd djangoproject 
$ pipenv --three
$ pipenv shell
$ pipenv install django dj-database-url django-heroku
$ django-admin startproject myproject
$ mv myproject myproject-delme
$ mv  myproject-delme/* .
$ rm -fr myproject-delme 
$ python manage.py startapp myapp
$ grep "SECRET_KEY" myproject/settings.py | tr -d ' ' > .env 
$ curl -s https://raw.githubusercontent.com/ch0ks/devops-tools/master/files-misc/django-sample-dburl-and-heroku-settings.py > myproject/settings.py 
$ curl -s https://raw.githubusercontent.com/ch0ks/devops-tools/master/docker-secure-postgres.sh | sudo bash 2>&1 | tee /dev/stderr | egrep '(DATABASE_URL|PGPASSWORD)' >> .env
$ exit
$ pipenv shell
$ python manage.py migrate
$ python manage.py createsuperuser 
$ python manage.py runserver 

Now point your browser to http://127.0.0.1:8000/admin. Profit!

Glossary

In this section you will find definitions and explanations for the technologies that we are going to use.

What is PostgresSQL?

PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL.

— Wikipedia entry on PostgresSQL, April 20, 2020

Website: https://www.postgresql.org/

What is Docker?

Docker is a set of platform as a service (PaaS) products that uses OS-level virtualization to deliver software in packages called containers. Containers are isolated from one another and bundle their own software, libraries and configuration files; they can communicate with each other through well-defined channels. All containers are run by a single operating system kernel and therefore use fewer resources than virtual machines.

— Wikipedia entry on Docker (software), April 20, 2020

Website: https://www.docker.com/*

What is Pipenv?

Pipenv is a tool that aims to bring the best of all packaging worlds (bundler, composer, npm, cargo, yarn, etc.) to the Python world. Windows is a first-class citizen, in our world.

It automatically creates and manages a virtualenv for your projects, as well as adds/removes packages from your Pipfile as you install/uninstall packages. It also generates the ever-important Pipfile.lock, which is used to produce deterministic builds.

— Pipenv: Python Dev Workflow for Humans, April 20, 2020

Great guide to understand pyenv: https://realpython.com/intro-to-pyenv/

What is Django?

Django, stylised as django is a Python-based free and open-source web framework, which follows the model-template-view (MTV) architectural pattern. It is maintained by the Django Software Foundation (DSF), an independent organization established as a 501(c)(3) non-profit.

— Wikipedia entry on Django (web framework), April 20, 2020

Website: https://www.djangoproject.com/

How Everything Come Together?

You can download the script from here: docker-secure-postgres.sh

Let’s analyze it by parts. Here we can check which user is executing the script, if is it not root then it autoexecute itself with sudo.

#!/usr/bin/env bash
#title          :Secure Postgress Docker Container for Development
#description    :This is a script that I created to expedite the 
#        creation of a secure docker container during the 
#                development of a Django application
#file_nam       :docker-secure-postgres.sh
#author         :Adrian Puente Z.
#date           :20200315
#version        :1.0
#bash_version   :GNU bash, version 5.0.3(1)-release (x86_64-pc-linux-gnu)
#==================================================================

set -euo pipefail

[ $(id -u) -ne 0 ] && echo "Only root can do that! sudoing..."
if [ "${EUID}" != 0 ]; then sudo $(which ${0}) ${@}; exit; fi

At this point the script is now running as root so now it is defining the variables that will use during the execution. You should configure these variables if needed, for example is you want to use another user or name the database something else.

Using Python it will generate two 32 character long strings using numbers, upper and lower case letter and symbols and assign them to the variables variables APPDBPASSWD and PGPASSWORD. They will be used as the postgres user password and the django user database password. These passwords will be configured later in the script.

## Configure these variables if needed
APPDBHOSTNAME="localhost"
APPDBSRVPORT="5432"
APPUSRNAME="appusr"
APPDBNAME="appdb"
APPDBPASSWD=$(python -c "import random ; print(''.join([random.SystemRandom().choice('abcdefghijklmnopqrstuvwxyz0123456789%^&*(-_+)') for i in range(25)]))")
PGPASSWORD=$(python -c "import random ; print(''.join([random.SystemRandom().choice('abcdefghijklmnopqrstuvwxyz0123456789%^&*(-_+)') for i in range(25)]))")
DOCKERSRVNAME="postgres-securesrv"
SQLFILE=$(mktemp)
True=0
False=1

The piece below checks if a copy of the container is running in the machine, if so it will stop it and delete it, otherwise continues with the execution of the script.

# Destroys any old docker container with the same name.
if docker ps -a | grep ${DOCKERSRVNAME} >/dev/null 2>&1 
then 
  echo "Deleting existing ${DOCKERSRVNAME} container."
  docker stop ${DOCKERSRVNAME} > /dev/null 2>&1
  docker rm ${DOCKERSRVNAME} > /dev/null 2>&1
fi

Then it will generate a self signed SSL certificate to use it in the postgres server. You can comment the openssl commands and just add your own certificate by adding the server certificate as a file named server.crt and the key as a file named server.key.

# You can comment this part and add your own certificates. Be sure
# to copy them in this directory and to name them accordingly.
openssl req -new -text -passout pass:abcd -subj /CN=${APPDBHOSTNAME} -out server.req -keyout privkey.pem
openssl rsa -in privkey.pem -passin pass:abcd -out server.key
openssl req -x509 -in server.req -text -key server.key -out server.crt
## Setting the right permissions for the postgress user
chmod 600 server.key
chown 999:999 server.key

Once the pre-requisites are met then Docker will check if the official container image for PostgreSQL exists in the machine, if not it will download a copy from from Docker Hub. Once the image is in the machine it will configure a new postgres docker container by adding the certificates mentioned before and the postgres user password contained in the variable PGPASSWORD, the database base administrator user of the server.

docker run -d --name ${DOCKERSRVNAME} \
       -v "${PWD}/server.crt:/var/lib/postgresql/server.crt:ro" \
       -v "${PWD}/server.key:/var/lib/postgresql/server.key:ro" \
       -e POSTGRES_PASSWORD=${PGPASSWORD} \
       -p ${APPDBSRVPORT}:${APPDBSRVPORT} \
       postgres \
       -c ssl=on \
       -c ssl_cert_file=/var/lib/postgresql/server.crt \
       -c ssl_key_file=/var/lib/postgresql/server.key 

Finally, once the configuration is completed, it will make six tries waiting five seconds in between each to detect if the postgres container is up and running.

echo "Waiting for the container to initialize."
FAILED=${True}
# Waits up to 30 seconds for the container to initialize.
for ((i=0 ; i<6 ; i++))
do
  sleep 5
  if docker ps | grep ${DOCKERSRVNAME} > /dev/null 2>&1 
  then
    if  PGPASSWORD="${PGPASSWORD}" \
      pg_isready -h ${APPDBHOSTNAME} \
             -p ${APPDBSRVPORT} \
             -U postgres
    then
      FAILED=${False}
      break
    fi
  fi
done

if [ ${FAILED} -eq ${True} ]
then
  echo "Container execution failed, showing the logs"
  docker logs ${DOCKERSRVNAME}
  exit 1
fi

Once it finds a functional connection it will connect as the dba using the password in the variable PGPASSWORD and configures:

• The app database name using the variable APPDBNAME
• The app user using the variable APPUSRNAME
• The app user password using the variable APPDBPASSWD

Notice how it uses the postgres client command line psql and the password configured before reusing the password contained in the variable PGPASSWORD.

echo "Creating sample database."
cat > ${SQLFILE} << _END
CREATE DATABASE ${APPDBNAME};
CREATE USER ${APPUSRNAME} WITH PASSWORD '${APPDBPASSWD}';
ALTER ROLE ${APPUSRNAME} SET client_encoding TO 'utf8';
ALTER ROLE ${APPUSRNAME} SET default_transaction_isolation TO 'read committed';
ALTER ROLE ${APPUSRNAME} SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE ${APPDBNAME} TO ${APPUSRNAME};
_END

# Creating the sample database.
PGPASSWORD="${PGPASSWORD}" psql -h ${APPDBHOSTNAME} -U postgres -f ${SQLFILE}
rm -fr ${SQLFILE}

echo "Sample database created successfully"
echo -en "Save both strings below in your .env file and restart the pipenv environment.\n\n"
echo "DATABASE_URL=\"postgres://${APPUSRNAME}:${APPDBPASSWD}@${APPDBHOSTNAME}:${APPDBSRVPORT}/${APPDBNAME}\""
echo "PGPASSWORD=\" ${PGPASSWORD}\""
exit 0

Once all is done it will show you both password in the right format for you to add them to your environmental variables.

DATABASE_URL="postgres://appusr:gz)i+jq5xwr0^(3vc-jpbg6t3@localhost:5432/appdb"
PGPASSWORD="a^-mu-*gfm70mrl&nwh5ci_(a"

In this case I am using pipenv and I will add them to the .env file. Have in mind that the DATABASE_URL variable follows the database url that is a platform independent way of addressing a database. A database URL is of the form service://[user]:[password]@[hostname]:[port]/[databasename]. I also recommend adding the libraries dj-database-url and django_heroku to your python projects to use this format.

Sample Django Project

Let's play a little with this new script. First let's create a proper development environment with pipenv:

~/github
$ mkdir django-sample
~/github
$ cd django-sample
~/github/django-sample
$ pipenv --three
Creating a virtualenv for this project…
Using ~/.pyenv/versions/3.6.8/bin/python3 (3.6.8) to create virtualenv…
?Running virtualenv with interpreter ~/.pyenv/versions/3.6.8/bin/python3
Already using interpreter ~/.pyenv/versions/3.6.8/bin/python3
Using base prefix '~/.pyenv/versions/3.6.8'
New python executable in ~/.local/share/virtualenvs/django-sample-2uB5phZ-/bin/python3
Also creating executable in ~/.local/share/virtualenvs/django-sample-2uB5phZ-/bin/python
Installing setuptools, pip, wheel...
done.

Virtualenv location: ~/.local/share/virtualenvs/django-sample-2uB5phZ-
Creating a Pipfile for this project…
~/github/django-sample
$ pipenv shell
Spawning environment shell (/usr/bin/zsh). Use 'exit' to leave.
OK
. ~/.local/share/virtualenvs/django-sample-2uB5phZ-/bin/activate
. ~/.local/share/virtualenvs/django-sample-2uB5phZ-/bin/activate
~/github/django-sample
$ 

Now let's install the django module and the dj-database-url library:

~/github/django-sample
$ pipenv install django dj-database-url django_heroku
Installing django…
-----8<----------8<----------8<----------8<----------8<-----
----->8---------->8---------->8---------->8---------->8-----
Installing collected packages: sqlparse, pytz, asgiref, django
Successfully installed asgiref-3.2.7 django-3.0.5 pytz-2019.3 sqlparse-0.3.1

Installing django_heroku…
Looking in indexes: https://pypi.python.org/simple
Collecting django_heroku
  Downloading django_heroku-0.3.1-py2.py3-none-any.whl (6.2 kB)
-----8<----------8<----------8<----------8<----------8<-----
----->8---------->8---------->8---------->8---------->8-----
Installing collected packages: psycopg2, whitenoise, django-heroku
Successfully installed django-heroku-0.3.1 psycopg2-2.8.5 whitenoise-5.0.1

Adding django to Pipfile's [packages]…
Installing dj-database-url…
-----8<----------8<----------8<----------8<----------8<-----
----->8---------->8---------->8---------->8---------->8----- (5.5 kB)
Installing collected packages: dj-database-url
Successfully installed dj-database-url-0.5.0

Adding dj-database-url to Pipfile's [packages]…
Pipfile.lock not found, creating…
Locking [dev-packages] dependencies…
Locking [packages] dependencies…
Updated Pipfile.lock (9a4335)!
Installing dependencies from Pipfile.lock (9a4335)…
  ####   ######################################## 5/5 — 00:00:00
~/github/django-sample
$ 

Now let's create the django project and it's application

~/github/django-sample
$ ls 
Pipfile  Pipfile.lock
~/github/django-sample
$ django-admin startproject myproject
~/github/django-sample
$ ls
myproject  Pipfile  Pipfile.lock
~/github/django-sample
$ mv myproject myproject-delme 
~/github/django-sample
$ mv myproject-delme/* .
~/github/django-sample
$ rm -fr myproject-delme 
~/github/django-sample
$ ls
manage.py  myproject  Pipfile  Pipfile.lock
~/github/django-sample
$ python manage.py startapp myapp
~/github/django-sample
$ ls
manage.py  myapp  myproject  Pipfile  Pipfile.lock
~/github/django-sample
$  

I know it looks confusing so let me try to explain. After installing the django module and the dj-database-url library I created a new project using django-admin startproject myproject. This will create a new directory named myproject with all the configuration files. I like to move these files to the current working directory to avoid confusions then using python manage.py startapp myapp to create the application. You can see that the command creates another directory named myapp that contains all the files needed for the application. It is important to have the project and application directory at the same level than the manage.py file or it won't work.

This is the final tree directory:

~/github/django-sample
$ tree
.
|-- manage.py
|-- myapp
|   |-- admin.py
|   |-- apps.py
|   |-- __init__.py
|   |-- migrations
|   |   `-- __init__.py
|   |-- models.py
|   |-- tests.py
|   `-- views.py
|-- myproject
|   |-- asgi.py
|   |-- __init__.py
|   |-- settings.py
|   |-- urls.py
|   `-- wsgi.py
|-- Pipfile
`-- Pipfile.lock


3 directories, 15 files
~/github/django-sample
$ 

Now let's create the postgres database:

~/github/django-sample
$ sudo bash docker-secure-postgres.sh
Deleting existing postgres-securesrv container.
Generating a RSA private key
..........................................................................+++++
...............................................................+++++
writing new private key to 'privkey.pem'
-----
writing RSA key
37f96111773e465bb9d02b52098101c72cff3cc3c1fa92e0f01cc3afa1451cbe
Waiting for the container to initialize.
localhost:5432 - rejecting connections
localhost:5432 - accepting connections
Creating sample database.
CREATE DATABASE?p=755#how-everything-comes-together
CREATE ROLE
ALTER ROLE
ALTER ROLE
ALTER ROLE
GRANT
Sample database created successfully
Save both strings below in your .env file and restart the pipenv environment.

DATABASE_URL="postgres://appusr:+x6odg_mrmvt+ktnd35_9-795@localhost:5432/appdb"
PGPASSWORD="1%isf7s9u7xqqplzqwk)wt9z0"
~/github/django-sample
$ 

Save the DATABASE_URL and the PGPASSWORD variables, we will use them later.

Finally we will configure the environment for Django to work as expected. Follow these steps:

1. At the top of the file myproject/settings.py file add the following:

import os <--- After this library
import dj_database_url
import django_heroku

###################################
## Code and other configurations ##
###################################

## At the very bottom of the file
STATIC_URL = '/static/' <--- After this value

django_heroku.settings(locals())

1. In the same file look for the variable SECRET_KEY, save it somewhere else and delete it from the file
2. In the same file also look for the DATABASE variable and change it like this:

# Database
# https://docs.djangoproject.com/en/3.0/ref/settings/#databases

#DATABASES = {
#    'default': {
#        'ENGINE': 'django.db.backends.sqlite3',
#        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
#    }
#}

DATABASES = {
    'default': dj_database_url.config()
}

1. Add the variables DATABASE_URL, PGPASSWORD and SECRET_KEY to the file .env (if it does not exist create it), it should look like this:

~/github/django-sample
$ cat .env 
DATABASE_URL="postgres://appusr:+x6odg_mrmvt+ktnd35_9-795@localhost:5432/appdb"
PGPASSWORD="1%isf7s9u7xqqplzqwk)wt9z0"
SECRET_KEY="cj4k*%2y%7nz&)3chs*%+ti&o40l)l)jm*^4zk)pkp7tt)cqfn"

~/github/django-sample
$ 

Now restart the python environment. This is important or the environmental variables won't be taken into account. Do it like this:

~/github/django-sample
$ exit
~/github/django-sample
$ pipenv shell
Loading .env environment variables…
Spawning environment shell (/usr/bin/zsh). Use 'exit' to leave.
OK
. ~/.local/share/virtualenvs/django-sample-2uB5phZ-/bin/activate
. ~/.local/share/virtualenvs/django-sample-2uB5phZ-/bin/activate
~/github/django-sample
$ env | egrep '(PASS|KEY|URL)'
DATABASE_URL=postgres://appusr:+x6odg_mrmvt+ktnd35_9-795@localhost:5432/appdb
PGPASSWORD=1%isf7s9u7xqqplzqwk)wt9z0
SECRET_KEY=7f4d%*zn1f5muug2(eu118++-cm)98gy
~/github/django-sample
$ 

Perfect! At this point you should be able to start your project and initialize your database.

~/github/django-sample
$ python manage.py migrate 
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying sessions.0001_initial... OK
~/github/django-sample
$ python manage.py createsuperuser 
Username (leave blank to use 'apuente'): apuente
Email address: [email protected]
Password: 
Password (again): 
Superuser created successfully.
~/github/django-sample
$ psql $(echo ${DATABASE_URL}) -c "select id,is_superuser,username,email from auth_user" 
 id | is_superuser | username |          email          
----+--------------+----------+-------------------------
  1 | t            | apuente  | [email protected]
(1 row)
~/github/django-sample
$ python manage.py runserver
Watching for file changes with StatReloader
Performing system checks...

System check identified no issues (0 silenced).
April 20, 2020 - 23:24:35
Django version 3.0.5, using settings 'myproject.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CONTROL-C.

Now point your browser to http://127.0.0.1:8000/admin. Profit!