# put 127.0.0.1 db.example into /etc/hosts too

# selfsigned CA root
openssl req -new -x509 -days 3650 -nodes -out root.crt -keyout root.key -subj "/CN=myca"

# server key
openssl req -new -nodes -out server.csr -keyout server.key -subj "/CN=db.example.com"
openssl x509 -req -in server.csr -CA root.crt -CAkey root.key -CAcreateserial -days 365 -out server.crt

# client key
openssl req -new -nodes -out client.csr -keyout client.key -subj "/CN=localuser"
#openssl x509 -req -in client.csr -CA root.crt -CAkey root.key -CAcreateserial -days 365 -out client.crt
mkdir -p demoCA && touch demoCA/index.txt
echo 01 > demoCA/serial
echo "unique_subject = no" > demoCA/index.txt.attr # allow regeneration
START=$(date -u +%Y%m%d%H%M%SZ)
END=$(date -u -d '+2 minute' +%Y%m%d%H%M%SZ)
openssl ca -batch -in client.csr -cert root.crt -keyfile root.key -startdate $START -enddate $END -policy policy_anything -md sha256 -outdir . -out client.crt
chmod 0600 *.crt *.key *.csr

# add to postgresql.auto.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'      # CA used to verify client certs

# add to pg_hba.conf
# TYPE    DATABASE  USER     ADDRESS       METHOD
hostssl   all       all      0.0.0.0/0     cert
#hostssl   all       all      0.0.0.0/0     scram-sha-256 clientcert=verify-full

# restart

# connect
psql "host=db.example.com dbname=postgres user=localuser sslmode=verify-full sslcert=client.crt sslkey=client.key sslrootcert=root.crt"