-
-
Save pveierland/049835e04c1712ddccebdbbd88269c03 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment