Setting Up A PostgreSQL Docker Sandbox

Motivation

Sandboxes are very handy for isolated testing and demonstration purposes. As it happens, I began to write a post that was going to use a PostgreSQL Docker container as a sandbox and I decided it was better to break it out into its own post that could be referred to in future posts rather than add complexity to a post where it wasn’t the main focus.

Install Docker

For my docker host system I am using Debian 9 (Stretch). Installation was done following the official Get Docker CE for Debian instructions.

I won’t repeat the commands here as those instructions will be updated appropriately as packages, dependencies and Debian distributions change.

However I will mention I’m not a fan of piping raw URL output directly into sudo commands as some of the official instructions indicate. Anytime the instructions call for it I would suggest instead dumping the output to a file and sanity checking it before feeding it to a command with elevated privileges.

One last installation note is that depending on your environment and requirements you may want to give specific users the ability to control docker. As a result of completing the instructions indicated above a new group ‘docker’ was added, adding users to this group is as easy as:

usermod -aG docker yourUsername

Afterwhich you’ll need to re-login for the group changes to take effect (a simple ‘su – yourUsername’ on the command line you plan to run docker commands on suffices for the short-term)

Firing Up The Sandbox

Downloading and starting the PostgreSQL sandbox is as easy as:

docker run \
            --name pgsandbox1 \
            -d \
            -e "POSTGRES_PASSWORD=password" \
            -p 5432:5432 \
            postgres:10

This tells docker to:

  • Run a (ephemeral) container named ‘pgsandbox1’ based of the postgres:10 image tag
  • In detached mode (-d)
  • Setting the password for the PostgreSQL superuser to ‘password’ via a system environmental variable (-e)
  • And publishes (-p) port 5432 of the container on the host

Kicking Tires

First, we verify the container is running:

$ docker container list

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3b445b6a2dc0 postgres:10 "docker-entrypoint.s…" 3 minutes ago Up 3 minutes 5432/tcp pgsandbox1

Next, we lauch the psql interactive shell

Method 1: Exec psql inside the running docker
This method does not require psql to be installed on the host system:

$ docker exec \
          -it \
          pgsandbox1 \
          psql -U postgres

psql (10.3 (Debian 10.3-1.pgdg90+1))
Type "help" for help.

postgres=#

Method 2: Connect via local psql to the published container port

$ psql \
     --host=127.0.0.1 \
     --port=5432 \
     --username=postgres

Password for user postgres: 

psql (10.3 (Debian 10.3-1.pgdg90+1))
Type "help" for help.

postgres=# 

And as a final check we create a table, add a row to it and run a query:

postgres=# CREATE TABLE test (id SERIAL, name TEXT);
CREATE TABLE

postgres=# \d
             List of relations
 Schema |    Name     |   Type   |  Owner   
--------+-------------+----------+----------
 public | test        | table    | postgres
 public | test_id_seq | sequence | postgres
(2 rows)


postgres=# \d test
                            Table "public.test"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('test_id_seq'::regclass)
 name   | text    |           |          | 


postgres=# INSERT INTO test (name) VALUES ('Pat');
INSERT 0 1

postgres=# SELECT * FROM test;
 id | name 
----+------
 1 | Pat
(1 row)

Important reminder: The docker container running above is ephemeral; it does not retain or persist data between container restarts.

Final Notes

In the end of this we have a minimalist PostgreSQL instance ready for simple testing and examples. There is obviously a lot of room for enhancements and additional functionality. For those interested in modifications to the simple use-case above I would recommend reading through the base postgres docker image page which includes guidance on how best to do so.

Creating Self-Signed Certificates Using Keytool

    Background

    Self-signed certificates are useful for testing as well as some limited real-world use-cases. The instructions below cover commands necessary to create self-signed certificates using keytool.

    keytool, being included with Java JDK installations, is more likely to be available on Windows if OpenJDK or Oracle JDK is installed, however Linux/Unix/OSX users will also typically have the more robust openssl program readily available to them. For instructions on how to create certificates using openssl, you can follow these instructions instead.

    Note: To be clear, OpenSSL is also available for Windows however it is rarely found already installed on systems.

    Terminology

    Whenever cryptography is involved, you can expect to a deluge of terminology about complex concepts. Fully fleshing out these concepts is beyond the scope of this piece, but it would be beneficial to roughly summarize some of the terms as they apply to the operations used further down:

    • Keystore or Truststore – Container file designed to hold cryptographic materials such as keys, certificates, etc.
    • PFX – Effectively an alternate name for PKCS12 files. PFX is usually found within Microsoft contexts as PFX was initially a Microsoft format before it eventually became re-defined by RFC 7292 as PKCS12.
    • Key Pair – A public key and its corresponding private key together are commonly referred to as a “key pair”.
    • Private Key – The secret key of a key pair that should be availble only to the owner or owner programs/processes
    • Public Key – The public key of a key pair that is made available to one or more external parties to encrypt data that can (well…should) only be decrypted by someone with access to the corresponding secret key.
    • Certificate – A file format (typically X.509) that includes a key (commonly a public key) along with other relevant information such as expiration, issuer, etc.

    A Quick Note On Approach

    Several best practices are intentionally not followed in the commands below in order to emphasize the overall mechanics. Things like the keystore password being ‘password’, private key password not being set, and passwords included on the command line should not be followed outside an educational exercise context.

    Keytool Instructions

    As mentioned above, keytool is provided with Java JDK installations. It is capable of generating the necessary self-signed certificate materials however is otherwise fairly limited in its capabilities. For example as of the time of this writing there is no way to export a private key into its own dedicated file using keytool, only to another keystore container file.

    Another item to note is that prior to Java 9 the default keystore container format for keytool was JKS, which is a Java-specific format which limits its use by other software and tools. The examples below instruct keytool to use the more widely supported PKCS12 container format instead.

    Keytool primarily deals with keystores, so the approach followed below is to simultaneously generate a new keypair and store it in a new keystore, then afterwards export the public certificate to its own file.

    Create PKCS12 keystore container

    keytool \
        -genkeypair \
        -ext san=dns:localhost \
        -storetype PKCS12 \
        -keystore server.p12 \
        -storepass password \
        -alias myalias \
        -keyalg RSA \
        -keysize 4096 \
        -dname "C=,S=,L=,O=,OU=,CN=localhost"
    

    Export the certificate

    keytool \
      -exportcert \
      -rfc \
      -keystore server.p12 \
      -storepass password \
      -alias myalias \
      -file server.public.crt
    

    (Optional) Examine the keystore

    keytool \
      -list \
      -v \
      -storetype PKCS12 \
      -storepass password \
      -keystore server.p12
    

    Testing with OpenSSL

    Even if you’ve created your certificate with keytool, you can use openssl to quickly test it.

    Note: As keytool does not have a built-in ability to export private keys, prior to testing you’ll need to export the private key from the p12 container using openssl:

    openssl pkcs12 \
    -in server.p12 \
    -nocerts \
    -nodes \
    -passin pass:password | openssl rsa -outform PEM -out server.private.key


    Starting the test server

    The following commands will start the TLS/SSL server bound to TCP port 4430 and instruct it to respond to a simple HTTP GET requests.

    openssl s_server \
      -key server.private.key \
      -cert server.public.crt \
      -accept 4430 \
      -no_dhe \
      -www
    

    Testing client-side with web browser

    Browse to https://localhost:4430 in a browser (you may have to click past warnings). You should see info text provided by test server (ciphers supported, ciphers in common with client, etc)

    Testing client-side on the command line

    1. In a separate teminal window or tab, run: openssl s_client -connect localhost:4430
    2. Followed by: GET / HTTP 1.0
    3. Then the Enter key

    You should see the same HTML used to generate the page for the browser test.

Creating Self-Signed Certificates Using OpenSSL

    Background

    Self-signed certificates are useful for testing as well as some limited real-world use-cases. The instructions below cover commands necessary to create self-signed certificates using openssl.

    As as alternative to openssl, there is also the keytool utility included with the JDK that can be used to generate certificates. Refer to these instructions which cover using keytool to generate self-signed certificates instead.

    Terminology

    Whenever cryptography is involved, you can expect to a deluge of terminology about complex concepts. Fully fleshing out these concepts is beyond the scope of this piece, but it would be beneficial to roughly summarize some of the terms as they apply to the operations used further down:

    • Keystore or Truststore – Container file designed to hold cryptographic materials such as keys, certificates, etc.
    • PFX – Effectively an alternate name for PKCS12 files. PFX is usually found within Microsoft contexts as PFX was initially a Microsoft format before it eventually became re-defined by RFC 7292 as PKCS12.
    • Key Pair – A public key and its corresponding private key together are commonly referred to as a “key pair”.
    • Private Key – The secret key of a key pair that should be availble only to the owner or owner programs/processes
    • Public Key – The public key of a key pair that is made available to one or more external parties to encrypt data that can (well…should) only be decrypted by someone with access to the corresponding secret key.
    • Certificate – A file format (typically X.509) that includes a key (commonly a public key) along with other relevant information such as expiration, issuer, etc.

    A Quick Note On Approach

    Several best practices are intentionally not followed in the commands below in order to emphasize the overall mechanics. Things like the keystore password being ‘password’, private key password not being set, and passwords included on the command line should not be followed outside an educational exercise context.

    OpenSSL Instructions

    Here we will create the individual components (the private key and public cert) then package them up after into a PKCS12 file.

    Configure SubjectAltName

    If you create a certificate the way you see on most posts on the Internet, you may find you’ll get warnings from some clients similar to the following from python3:

    SubjectAltNameWarning: Certificate for localhost has no `subjectAltName`, falling back to check for a `commonName` for now. 
    This feature is being removed by major browsers and deprecated by RFC 2818. (See https://github.com/shazow/urllib3/issues/497 for details.)
    

    As the warning indicates there is no subjectAltName field in the certificate. To do so requires either modifying the system openssl.cnf (or a copy of it) for OpenSSL pre-1.1.1 or using additional command line options if using OpenSSL 1.1.1 and onwards. OpenSSL 1.1.1 was just released Sept 11 2018 and hasn’t yet made its way into most distributions. The commands below will show the pre-1.1.1 method using a copy of openssl.cnf which should also continue to work when 1.1.1 becomes more widely available.

    Note: Your system’s openssl.cnf can be in several different places, common places to look:

    • /etc/ssl/openssl.cnf
    • /usr/lib/ssl/openssl.cnf
    • /usr/local/ssl/openssl.cnf
    • /usr/local/etc/openssl.cnf
    • /System/Library/OpenSSL/openssl.cnf (OSX)
    • /etc/pki/tls/openssl.cnf (RHEL)

    Prepare local openssl.cnf

    1. Make a local copy of the system openssl.cnf:
      cp /path/to/openssl.cnf openssl.cnf.tmp
    2. Add subjectAltName entry in copy:
      echo -e "[SAN]\nsubjectAltName=DNS:localhost\n" >> openssl.cnf.tmp

    Create certificate and key

    openssl req \
           -newkey rsa:4096 \
           -sha256 \
           -x509 \
           -days 3650 \
           -nodes \
           -subj "/C=C8/ST=ST/L=L/O=O/OU=OU/CN=localhost/[email protected]" \
           -keyout server.private.key \
           -config openssl.cnf.tmp \
           -extensions SAN \
           -out server.public.crt
    

    If you get the following error:

    problems making Certificate Request
    139634576708864:error:0D07A098:asn1 encoding routines:ASN1_mbstring_ncopy:string too short:../crypto/asn1/a_mbstr.c:102:minsize=2
    

    This indicates that one of the fields in the -subj text is either empty or too short

    Assemble the PKCS12 container

    openssl pkcs12 \
      -export \
      -inkey server.private.key \
      -in server.public.crt \
      -passout pass:password \
      -out server.p12
    

    (Optional) Examine the keystore

    openssl pkcs12 \
      -nokeys \
      -info \
      -in server.p12 \
      -passin pass:password
    

    Testing with OpenSSL

    You can also use openssl to quickly test your new certificate.

    Starting the test server

    The following commands will start the TLS/SSL server bound to TCP port 4430 and instruct it to respond to a simple HTTP GET requests.

    openssl s_server \
      -key server.private.key \
      -cert server.public.crt \
      -accept 4430 \
      -no_dhe \
      -www
    

    Testing client-side with web browser

    Browse to https://localhost:4430 in a browser (you may have to click past warnings). You should see info text provided by test server (ciphers supported, ciphers in common with client, etc)

    Testing client-side on the command line

    1. In a separate teminal window or tab, run: openssl s_client -connect localhost:4430
    2. Followed by: GET / HTTP 1.0
    3. Then the Enter key

    You should see the same HTML used to generate the page for the browser test.

Parsing Apache Logs Into A Database

Background

Parsing Apache HTTPD access logs into a database structure allows you to easily run queries and reports against it to better understand web traffic and detect issues. By using the pip installable apache_log_parser[1] package and the sqlite3 python module[2] that comes as part of the standard python library, we can quickly parse these access logs and insert entries into a sqlite3 database.

Approach Notes

Couple things to note on the approach taken in the code:

  • sqlite3 was selected for its native support and ease of use, but other DBMSes can be similarly used
  • An auto-incremented id column was not included in the schema as sqlite3 does not recommend such a column unless necessary[4]. You will likely want to add one for other database systems
  • apache_log_parser produces a dictionary of apache log entry parts. Given this, the available dictionary keys are dependent on the log pattern of the access.log which supplied to the make_parser() function
  • The columns in the code snippet are just a subset of the total keys/values returned by the parser. You can easily see all keys and values generated from a given line by calling pprint() on it
  • An additional ‘date’ key and value are added in the code (in a sqlite date function friendly way) to allow for date based queries and criteria

Code

#!/usr/bin/env python3

import sys
import sqlite3
import apache_log_parser

if len(sys.argv) != 2:
    print("Usage:", sys.argv[0], "/path/to/access.log")
    exit(1)

conn = sqlite3.connect('/tmp/logs.db')
cur = conn.cursor()

cur.execute("""
                CREATE TABLE IF NOT EXISTS logs (
                    status INTEGER,
                    request_method TEXT,
                    request_url TEXT,
                    date TEXT
                )
            """)

# Pattern below is from the LogFormat setting in apache2.conf/httpd.conf file
# You will likely need to change this value to the pattern your system uses
parser = apache_log_parser.make_parser(
                   "%h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\""
         )

log_file = sys.argv[1]

with open(log_file) as f:

    for line in f:

        d = parser(line)

        # Line below adds minimalistic date stamp column 
        # in format that sqlite3 date functions can work with
        d['date'] = d['time_received_datetimeobj'].date().isoformat()

        cur.execute("""
                        INSERT INTO logs ( status,  request_method,  request_url,  date)
                                  VALUES (:status, :request_method, :request_url, :date)
                        """, d)

cur.close()

conn.commit();
conn.close();

Example Run

Sample access.log entries:

# cat /var/log/apache2/access.log
::1 - - [25/Nov/2018:20:13:28 -0500] "GET /index.html HTTP/1.1" 200 3380 "-" "User agent string"
::1 - - [25/Nov/2018:12:15:26 -0500] "GET /notthere HTTP/1.1" 404 498 "-" "User agent string"

Run the code above:

$ ./httpd_log_parser.py /var/log/apache2/access.log

Examine the results:

$ sqlite3 -header -column /tmp/logs.db 'SELECT * FROM logs'
status      request_method  request_url  date      
----------  --------------  -----------  ----------
200         GET             /index.html  2018-11-25
404         GET             /notthere    2018-11-25

Sample Queries

After loading log entries you can start running useful queries on them.

  • Show number of requests by response status:
      SELECT count(1) as nReqs,
             status
        FROM logs
    GROUP BY (status)
    ORDER BY nReqs DESC;
    
    nReqs       status    
    ----------  ----------
    4           200       
    2           404   
    
  • What are the most common 404’d URLs:
      SELECT count(1) as nReqs,
             request_url
        FROM logs
       WHERE status = 404
    GROUP BY (request_url)
    ORDER BY nReqs DESC;
    
    nReqs       request_url
    ----------  -----------
    2           /notthere 
    

References

  1. GitHub – rory/apache-log-parser – https://github.com/rory/apache-log-parser
  2. [Python] sqlite3 — DB-API 2.0 interface for SQLite databases – https://docs.python.org/3/library/sqlite3.html
  3. [SQLite] Date And Time Functions – https://www.sqlite.org/lang_datefunc.html
  4. SQLite Autoincrement – https://www.sqlite.org/autoinc.html

Photo Slideshows Using Raspberry Pi

Background

At a family event just shy of a year ago I wanted to set up a slideshow (two in fact) to run continuously. The event was outdoors and leaving two laptops exposed to the elements for an extended period of time was obviously not very appealing. Instead a couple of Raspberry Pis together with the feh image viewer proved to be the perfect fit.

System Info

  • Pi: Raspberry Pi 3
  • OS: Raspbian 9 (Stretch)
  • Desktop: LXDE

Feh Installation and Test

First install feh:

apt-get install feh

Then run the following command in the terminal:


feh \
    --recursive \
    --randomize \
    --fullscreen \
    --quiet \
    --hide-pointer \
    --slideshow-delay 6 \
    yourPicDir

The command line switches are for the most part self-explanatory. The idea is to give feh a directory containing pictures you’ve copied to the Pi and then have it cycle through them randomly, displaying each for six seconds. Other options are documented in the man pages if you’d like to customize the usage.

To exit the slideshow press the Esc key.

Disabling Screen Blanking

By default the screen will blank after a few minutes of mouse and keyboard inactivity. In order to disable the screen blanking we’ll need to modify the lightdm.conf file:

nano /etc/lightdm/lightdm.conf

and add the following line under the [Seat:*] section:

xserver-command=X -s 0 dpms

Auto-Start Slideshow On Boot

To have the slideshow automatically start on boot, first create a small shell script file to hold the feh command:

nano /home/pi/slideshow.sh

and paste the following:

#!/bin/bash


feh \
    --recursive \
    --randomize \
    --fullscreen \
    --quiet \
    --hide-pointer \
    --slideshow-delay 6 \
    yourPicDir

Be sure to make the new shell script executable:

chmod 755 /home/pi/slideshow.sh

And finally add it to the pi user’s LXDE autostart file:

echo "@/home/pi/slideshow.sh" >> /home/pi/.config/lxsession/LXDE-pi/autostart

When you reboot your Raspberry Pi it should now automatically start the slideshow.

To exit the slideshow, hit the Esc key and you will see the normal LXDE desktop.