Skip to content

Instantly share code, notes, and snippets.

@jkatz
Last active February 1, 2024 23:46
Show Gist options
  • Save jkatz/ffd12f4c516e5342cb8a4af70b3ff59c to your computer and use it in GitHub Desktop.
Save jkatz/ffd12f4c516e5342cb8a4af70b3ff59c to your computer and use it in GitHub Desktop.
#!/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
@azizul90
Copy link

azizul90 commented Nov 30, 2020

Hi ,i have tested your postgres script. I found the postgres container keep rebooting, so i checked the container logs, it shows error "cp: cannot open '/pgconf/postgresql.conf' for reading: Permission denied". So i changed the pgconf permission using chmod 644, the container able running fine. However, I noticed the SSL that the script set up not working. I able to login to the Postgres server via PgAdmin without import any certificate and key. Is there any workaround for this issue ?

Docker Container Logs

[root@svr ~]# docker logs postgres
Mon Nov 30 06:48:19 UTC 2020 INFO: Setting PGROOT to /usr/pgsql-11.
Mon Nov 30 06:48:19 UTC 2020 INFO: Cleaning up the old postmaster.pid file..
Mon Nov 30 06:48:19 UTC 2020 INFO: User ID is set to uid=26(postgres) gid=26(postgres) groups=26(postgres).
Mon Nov 30 06:48:20 UTC 2020 INFO: Working on primary..
Mon Nov 30 06:48:20 UTC 2020 INFO: Initializing the primary database..
Mon Nov 30 06:48:20 UTC 2020 INFO: PGDATA is empty. ID is uid=26(postgres) gid=26(postgres) groups=26(postgres). Creating the PGDATA directory..
Mon Nov 30 06:48:20 UTC 2020 INFO: Starting initdb..
Mon Nov 30 06:48:20 UTC 2020 INFO: XLOGDIR not found. Using default pg_wal directory..
Mon Nov 30 06:48:20 UTC 2020 INFO: Data checksums enabled. Setting initdb to use data checksums..
Mon Nov 30 06:48:20 UTC 2020 INFO: Running initdb command: initdb -D /pgdata/postgres --locale=en_US.utf8 --data-checksums > /tmp/initdb.stdout 2> /tmp/initdb.stderr
Mon Nov 30 06:48:22 UTC 2020 INFO: Overlaying PostgreSQL's default configuration with customized settings..
Mon Nov 30 06:48:22 UTC 2020 INFO: Temporarily starting database to run setup.sql..
waiting for server to start....2020-11-30 06:48:23 UTC [53]: [1-1] user=,db=,app=,client=LOG: pgaudit extension initialized
2020-11-30 06:48:23 UTC [53]: [2-1] user=,db=,app=,client=LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-30 06:48:23 UTC [53]: [3-1] user=,db=,app=,client=LOG: redirecting log output to logging collector process
2020-11-30 06:48:23 UTC [53]: [4-1] user=,db=,app=,client=HINT: Future log output will appear in directory "pg_log".
done
server started
Mon Nov 30 06:48:23 UTC 2020 INFO: Waiting for PostgreSQL to start..
/tmp:5432 - accepting connections
Mon Nov 30 06:48:23 UTC 2020 INFO: The database is ready for setup.sql.
SET
CREATE EXTENSION
CREATE EXTENSION
ALTER ROLE
ERROR: role "postgres" already exists
ALTER ROLE
ERROR: role "postgres" already exists
ALTER ROLE
CREATE DATABASE
GRANT
CREATE TABLE
GRANT
You are now connected to database "testdb" as user "postgres".
CREATE EXTENSION
CREATE EXTENSION
You are now connected to database "testdb" as user "postgres".
CREATE SCHEMA
CREATE TABLE
INSERT 0 1
Mon Nov 30 06:48:24 UTC 2020 INFO: Stopping database after primary initialization..
waiting for server to shut down..... done
server stopped
Mon Nov 30 06:48:25 UTC 2020 INFO: Setting ARCHIVE_MODE to off.
Mon Nov 30 06:48:25 UTC 2020 INFO: Setting ARCHIVE_TIMEOUT to 0.
Mon Nov 30 06:48:25 UTC 2020 INFO: Custom /pgconf/postgresql.conf detected. Applying custom configuration..
cp: cannot open '/pgconf/postgresql.conf' for reading: Permission denied
Mon Nov 30 06:48:25 UTC 2020 ERROR: Applying custom configuration: Could not copy /pgconf/postgresql.conf to /pgdata/postgres/postgresql.conf

@jkatz
Copy link
Author

jkatz commented Nov 30, 2020

I'm not following what the actual issue is. What is the problem?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment