jkatz/quickstart-postgresql-cert.sh

## 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
	#!/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