quickstart-postgresql-cert.sh

#!/bin/bash

# SET THIS TO BE YOUR DESIRED USERNAME
export MY_USER_NAME_FOR_CERT=`whoami`

# This directory is optional, but will use it to keep the CA root key safe
mkdir keys certs
chmod og-rwx keys certs

# Set up a directory that will serve as the pgconf mount
mkdir pgconf

# Create a key-pair that will serve both as the root CA and the server key-pair
# the "ca.crt" name is used to match what it expects later
openssl req -new -x509 -days 365 -nodes -out certs/ca.crt \
  -keyout keys/ca.key -subj "/CN=root-ca"
cp certs/ca.crt pgconf/ca.crt

# Create the server key and CSR and sign with root key
openssl req -new -nodes -out server.csr \
  -keyout pgconf/server.key -subj "/CN=localhost"

openssl x509 -req -in server.csr -days 365 \
    -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \
    -out pgconf/server.crt

# remove the CSR as it is no longer needed
rm server.csr

# we will need to customize the postgresql.conf file to ensure SSL is turned on
cat << EOF > pgconf/postgresql.conf
# here are some sane defaults given we will be unable to use the container
# variables
# general connection
listen_addresses = '*'
port = 5432
max_connections = 20
# memory
shared_buffers = 128MB
temp_buffers = 8MB
work_mem = 4MB
# WAL / replication
wal_level = replica
max_wal_senders = 3
# these shared libraries are available in the Crunchy PostgreSQL container
shared_preload_libraries = 'pgaudit.so,pg_stat_statements.so'
# this is here because SCRAM is awesome, but it's not needed for this setup
password_encryption = 'scram-sha-256'

# here are the SSL specific settings
ssl = on # this enables SSL
ssl_cert_file = '/pgconf/server.crt' # this specifies the server certificacte
ssl_key_file = '/pgconf/server.key' # this specifies the server private key
ssl_ca_file = '/pgconf/ca.crt' # this specific which CA certificate to trust
EOF

# create a pg_hba.conf file that will only accept certificate authentication
# requests, though allow the "postgres" superuser account to connect with peer
# auth
cat << EOF > pgconf/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
# do not let the "postgres" superuser login via a certificate
hostssl all             postgres        ::/0                    reject
hostssl all             postgres        0.0.0.0/0               reject
#
hostssl all             all             ::/0                    cert
hostssl all             all             0.0.0.0/0               cert
EOF

# lock down all the files in the pgconf mount
# in particular key/cert files must be locked down otherwise PostgreSQL won't
# enable SSL
chmod og-rwx pgconf/*

# create the container configuration, including your desire username
# first, create a volume + network to run the container on
docker volume create --driver local --name=pgvolume
docker network create --driver bridge pgnetwork

# setup the environment file to build the container. substitute PG_USER with
# your desired username
# some of these are not needed based on the custom configuration
cat << EOF > pg-env.list
PG_MODE=primary
PG_PRIMARY_PORT=5432
PG_PRIMARY_USER=postgres
PG_DATABASE=testdb
PG_PRIMARY_PASSWORD=does
PG_PASSWORD=not
PG_ROOT_PASSWORD=matter
PG_USER=${MY_USER_NAME_FOR_CERT}
EOF

# get the container up and running
docker run --publish 5432:5432 \
  --volume=pgvolume:/pgdata \
  --volume=`pwd`/pgconf:/pgconf \
  --env-file=pg-env.list \
  --name="postgres" \
  --hostname="postgres" \
  --network="pgnetwork" \
  --detach \
crunchydata/crunchy-postgres:centos7-11.4-2.4.1

# create the client certificate
# by default, PostgreSQL will looks for these in the ~/.postgresql directory
# but we will do it a little differently in case you want to have certificates
# for logging into different PostgreSQL databases managed by different CAs
# NOTE: on a production system, you will not be storing your personal key next
# to the key of the CA. But on a production system, you would not be doing most
# of this setup ;-)
openssl req -new -nodes -out client.csr \
  -keyout keys/client.key -subj "/CN=${MY_USER_NAME_FOR_CERT}"
chmod og-rwx keys/*

openssl x509 -req -in client.csr -days 365 \
    -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \
    -out certs/client.crt
rm client.csr

# Success! Let's make a connection. First, set some helpful environmental
# variables.
#
# Set the PostgreSQL connection variables to point to these certs
# use "verify-full" mode, which will verify that the server certificate was
# signed by the trusted root CA AND that the hostname matches that on the server
# certificate
# export PGSSLMODE="verify-full"
# the following two parameters point to the client key/certificate
# export PGSSLCERT="`pwd`/certs/client.crt"
# export PGSSLKEY="`pwd`/keys/client.key"
# this parameter points to the trusted root CA certificate
# export PGSSLROOTCERT="`pwd`/certs/ca.crt"
#
# Lastly, run this command:
# psql -h localhost -p 5432 -U $MY_USER_NAME_FOR_CERT postgres
#
# So...if you need it all in one fell swoop:
#
# export PGSSLMODE="verify-full"
# export PGSSLCERT="`pwd`/certs/client.crt"
# export PGSSLKEY="`pwd`/keys/client.key"
# export PGSSLROOTCERT="`pwd`/certs/ca.crt"
# psql -h localhost -p 5432 -U $MY_USER_NAME_FOR_CERT postgres