jkatz · February 1, 2024 23:46 · azizul90 · Nov 30, 2020 · jkatz · Nov 30, 2020
diff --git a/quickstart-postgresql-cert.sh b/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