PostgreSQL ssl

PGO

https://blog.crunchydata.com/blog/set-up-tls-for-postgresql-in-kubernetes

https://loganmarchione.com/2020/10/securing-postgres-connections-using-lets-encrypt-certificates/

https://www.postgresql.org/docs/current/ssl-tcp.html

https://crypto.stackexchange.com/questions/43697/what-is-the-difference-between-pem-csr-key-and-crt-and-other-such-file-ext#:~:text=crt%20or%20.,public%20key%2C%20of%20course).

Simple script fun

#!/usr/bin/env bash
set -e

openssl req \
  -x509 \
  -nodes \
  -newkey ec \
  -pkeyopt ec_paramgen_curve:prime256v1 \
  -pkeyopt ec_param_enc:named_curve \
  -sha384 \
  -keyout ca.key \
  -out ca.crt \
  -days 3650 \
  -subj "/CN=*"


openssl req \
  -new \
  -newkey ec \
  -nodes \
  -pkeyopt ec_paramgen_curve:prime256v1 \
  -pkeyopt ec_param_enc:named_curve \
  -sha384 \
  -keyout server.key \
  -out server.csr \
  -days 365 \
  -subj "/CN=hippo.pgo"


openssl x509 \
  -req \
  -in server.csr \
  -days 365 \
  -CA ca.crt \
  -CAkey ca.key \
  -CAcreateserial \
  -sha384 \
  -out server.crt


kubectl delete secret generic postgresql-ca -n pgo | true
kubectl create secret generic postgresql-ca -n pgo --from-file=ca.crt=ca.crt

kubectl delete secret tls hippo.tls -n pgo | true
kubectl create secret tls hippo.tls -n pgo --cert=server.crt --key=server.key

pgo delete cluster hippo -n pgo --no-prompt | true
sleep 30
pgo create cluster hippo --tls-only \
  --server-ca-secret=postgresql-ca \
  --server-tls-secret=hippo.tls \
  --service-type=NodePort \
  --replica-count=2 \
  --pod-anti-affinity=required

pgo -n pgo show user hippo --show-system-accounts
kubectl get svc -n pgo --field-selector metadata.name=hippo --no-headers


# pgport=$(kubectl get svc hippo -n pgo -o json | jq .spec.ports[].nodePort | tail -n 1)
pgport=$(kubectl get svc hippo -n pgo -o jsonpath='{.spec.ports[1].nodePort}')
userpass=$(pgo -n pgo show user hippo --show-system-accounts | grep postgres | awk '{print $3}')
username=postgres
# cmd = "PGPASSWORD=\"${userpass}\" psql -h kub1 -U $username -p $pgport -d postgres"
cmd="PGPASSWORD=\"${pgpass}\" psql postgresql://$pguser@$pghost:$pgport/$pgname?sslmode=require"  # sslmode=verify-ca or verify-full
echo $cmd