Howto Enable SSL for postgres client connections

[Elexy]

I am looking for a way to enable ssl certs for postgres client connections. Looks like it can be done with stolonctl pgparameters.

Would it be in the scope of this project to make that an option in the chart?

[lwolf]

It is not something I need right now, but pull requests are very welcome

[Elexy]

I'll let you know what I come up with.

[royeectu]

Hi guys, Do you have any updates regarding this issue? I would really like to use this project but without SSL support it is useless for me.

[Elexy]

I have it working in my cluster, just haven't had time to do the PR. To help you out a bit until I get time, here is a small writeup.

I followed the postgres docs on creating key and crt files, then created a secret containing these files in the cluster. This is how I added it into the keeper yaml:

{{- if .Values.keeper.client_ssl.enabled }}
        - mountPath: /etc/secrets/ssl
          name: certs
{{- end }}
        resources:
{{ toYaml .Values.keeper.resources | indent 10 }}
      volumes:
        - name: stolon
          secret:
            secretName: {{ template "fullname" . }}
{{- if .Values.keeper.client_ssl.enabled }}
        - name: certs
          secret:
            secretName: {{ .Values.keeper.client_ssl.certs_secret_name }}
            defaultMode: 0600
{{- end }}

changed the command like this:

            ....
            export STOLON_DATA=/stolon-data
            chown stolon:stolon $STOLON_DATA
{{- if .Values.keeper.client_ssl.enabled }}
            chown -R stolon:stolon /etc/secrets/ssl
{{- end }}
            exec gosu stolon stolon-keeper --data-dir $STOLON_DATA

Also change the job that creates the cluster:

          - init
          - --cluster-name={{ .Values.clusterName }}
          - --store-backend={{ .Values.store.backend }}
          - --store-endpoints=http://{{ .Values.store.endpoint }}:2379
          - --yes
  {{- if .Values.keeper.client_ssl.enabled }}
          - '{  "initMode": "new", "pgParameters": { "ssl": "on", "ssl_cert_file": "/etc/secrets/ssl/server.crt", "ssl_key_file": "/etc/secrets/ssl/server.key"{{ if .Values.slow_queries.enabled }}, "log_min_duration_statement" :  "{{ .Values.slow_queries.min_duration }}" {{ end }} } }'
  {{- else if .Values.slow_queries.enabled }}
          - '{  "initMode": "new", "pgParameters": { "log_min_duration_statement" :  {{ .Values.slow_queries.min_duration }} } }'
  {{- end }}

beware, this also includes the query logging of >100ms just remove "log_min_duration_statement" : "{{ .Values.slow_queries.min_duration }}" if you don't want that.

add this to the values.yaml under the keeper key:

 ## configure ssl for client access
  # create certificates according to these instructions: https://www.postgresql.org/docs/9.6/static/ssl-tcp.html
  # to enable encrypted traffic, servert.crt and server.key are required
  # the use of ** Client Certificates ** is not supported
  client_ssl:
    enabled: false
    certs_secret_name: pg-cert-secret

for slow queries add:

## log slow queries
# disabled by default
slow_queries:
  enabled: false
  min_duration: 300

Hope that helps. I'm swamped right now :-)

[DediBar]

+1

[royeectu]

Thanks @Elexy, it looks like you also need to create the cert k8s secret by adding the pg-cert-secret in the secret.yaml file. Right?

[Elexy]

Yes, sorry forgot about that.

I made a bash script to do that for me:

#!/bin/bash

BASEDIR=${PWD}
SCRIPTDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
PROJECTBASE=$SCRIPTDIR/../..

set -euo pipefail

usage() {
cat << EOF

Usage: ${0##*/} -k [key filename] -c [cert filename] [-n [namespace]] [-r] [-h]

creates a secret named 'pg-cert-secret' in the given namespace
from the files specified. They will be used to enable ssl access client access to Postgres

    -h               display this help and exit
    -k               path to the 'server.key' file (required)
    -c               path to the 'server.crt' file (required)
    -n               kubernetes namespace defaults to 'persistence'
    -r               replace the old secret (optional)

EOF
}

namespace="persistence"
keyfilename="server.key"
certfilename="server.crt"
secretname="pg-cert-secret"
key=""
cert=""
replace=false

# getopts & validations
OPTIND=1 # Reset is necessary if getopts was used previously in the script.  It is a good idea to make this local in a function.
while getopts "hk:c:n:r" opt; do
  case "$opt" in
    h)
        usage
        exit 0
        ;;
    k)
        key="${OPTARG}"
        ;;
    c)
        cert="${OPTARG}"
        ;;
    n)
        namespace=${OPTARG}
        ;;
    r)
        replace=true
        ;;
    \?)
        usage >&2
        exit 1
        ;;
  esac
done
shift "$((OPTIND-1))" # Shift off the options and optional --.

if [ -z "$key" ]; then
  echo error: no [key] file specified
  usage
  exit 1
else
  if [ ! -f "$key" ]; then
    echo error: [$key] file does not exist
    usage
    exit 1
  fi
fi

if [ -z "$cert" ]; then
  echo error: no [cert] file specified
  usage
  exit 1
else
  if [ ! -f "$cert" ]; then
    echo error: [$cert] file does not exist
    usage
    exit 1
  fi
fi

cp $key /tmp/$keyfilename
cp $cert /tmp/$certfilename

if [ "$replace" = true ]; then
  kubectl delete secret $secretname || true
fi
kubectl --namespace=$namespace create secret generic $secretname --from-file=/tmp/$keyfilename --from-file=/tmp/$certfilename

[royeectu]

Thanks @Elexy, in addition I think that you should also add the pgHBA param to the stolonctl in the cluster-create-job.yaml file so it will generate the pg_hba.conf with ssl configuration. What do you think?

[Elexy]

@royeectu I am no PG expert, what does that add?

[royeectu]

@Elexy for example, according to: https://www.howtoforge.com/postgresql-ssl-certificates you need add the following line to the pg_hba.conf in order to enable ssl: hostssl all all 0.0.0.0/0 md5 clientcert=1

[Elexy]

I understood from https://www.postgresql.org/docs/9.6/static/ssl-tcp.html that that is needed for client certificates. In my setup I don't have the pg_hba.conf lines and the ssl encryption works as well although it is optional.

[Elexy]

PR is here: https://github.com/lwolf/stolon-chart/pull/7

[royeectu]

Thank you so much @Elexy

[lwolf]

Thanks for your work @Elexy. I traveling right now and will be able to test it only next week. @royeectu Did you try to use this setup?

[royeectu]

@lwolf I'm sorry for my late reply but somehow I missed it. I have tested the solution and it works

[lwolf]

thanks @royeectu