How to Configure PostgreSQL with SSL/TLS support on Kubernetes

SSL is disabled in the default Postgresql configuration and I had to struggle a little bit while making Postgresql on Kubernetes with SSL/TLS support to work. After few research and trials, I was able to resolve the issues and here I'm sharing what I have done to make it work for me.

High Level Steps:

1. Customize postgresql.conf  (to add/edit SSL/TLS configuration) and create configMap object. This way, we don't need to rebuild the Postgres to apply custom postgresql.conf , because ConfigMap allows us you to decouple configuration artifacts from image content. 
2. Create secret type objects for server.key, server.crt, root.crt, ca.crt, and password file.
3. Define and use NFS type PersistentVolume (PV) and PersistentVolumeClaim (PVC)
4. Use securityContext to resolve permission issues.
5. Use '-c config_file=<config-volume-location>/postgresql.conf' to override the default postgresql.conf

Note: all files used in this post can be cloned/downloaded from GitHub https://github.com/pppoudel/postgresql-with-ssl-on-kubernetes.git

Let's get started

In the example, I'm using namespace called 'shared-services' and service account called 'shared-svc-accnt'. You can create your own namespace and service account or use the 'default'. In anyways, I have listed here necessary steps and yaml files can be downloaded from github.

Create namespace and service account

# Create namespace shared-services    $> kubectl create -f shared-services-ns.yml # Create Service Account shared-svc-accnt    $> kubectl create -f shared-svc-accnt.yml # Create a grant for service account shared-svc-accnt. Do this step as per your platform.

Create configMap object

I have put both postgresql.conf and pg_hba.conf under config directory. I have updated postgresql.conf as follows:

ssl = on ssl_cert_file = '/etc/postgresql-secrets-vol/server.crt' ssl_key_file = '/etc/postgresql-secrets-vol/server.key'

Note: the location '/etc/postgresql-config-vol' needs to be mounted while defining 'volumeMounts', which we will discuss later in the post.

Those three above listed are the main configuration items that need to have proper values in order to force Postgresql to support SSL/TLS. If you are using CA signed certificate, you also need to provide value for 'ssl_ca_file' and optionally 'ssl_crl_file'. Read Secure TCP/IP Connections with SSL for more details.
You also need to update the pg_hba.conf (HBA stands for host-based authentication) as necessary. pg_hba.conf is used to manage connection type, control access using a client IP address range, a database name, a user name, and the authentication method etc.

# Sample entries in pg_hba.conf # Trust local connection - no password required. local    all             all                                     trust # Only secured remote connection from given IP-Range accepted and password are encoded using MD5 #hostssl  all             all             < Cluster IP-Range >/< Prefix length >         md5 hostssl  all             all             10.96.0.0/16         md5

$> ls -l config/ -rw-------. 1 osboxes osboxes  4535 Sep 22 17:33 pg_hba.conf -rw-------. 1 osboxes osboxes 22781 Sep 23 03:03 postgresql.conf # Create configMap object $> kubectl create configmap postgresql-config --from-file=config/ -n shared-services configmap "postgresql-config" created # Review created object $> kubectl describe configMap/postgresql-config -n shared-services Name:         postgresql-config Namespace:    shared-services ...

Create secrets

I've created server.key and self signed certificate using OpenSSL. you can either do the same or have CA signed certificates. Here, we are not going to use the client certificate. Read section 18.9.3. Creating Certificates if you need help in creating certificates.

# Create MD5 hashed password to be used with postgresql $> POSTGRES_USER=postgres $> POSTGRES_PASSWORD=myp3qlpwD $> echo "md5$(echo -n $POSTGRES_PASSWORD$POSTGRES_USER | md5sum | cut -d ' ' -f1)" > secrets/postgresql-pwd.txt # Here are all files under secrets directory $> ls -la secrets/ -rw-rw-r--. 1 osboxes osboxes  13 Sep 22 23:42 postgresql-pwd.txt -rw-rw-r--. 1 osboxes osboxes 891 Sep 22 16:51 root.crt -rw-rw-r--. 1 osboxes osboxes 891 Sep 22 16:49 server.crt -r--------. 1 osboxes osboxes 887 Sep 22 16:43 server.key # Create secret postgresql-secrets $> kubectl create secret generic postgresql-secrets --from-file=secrets/ -n shared-services secret "postgresql-secrets" created # Verify $> kubectl describe secrets/postgresql-secrets -n shared-services Name:         postgresql-secrets Namespace:    shared-services Labels:        Annotations:   Type:  Opaque Data ==== server.key:          887 bytes postgresql-pwd.txt:  13 bytes root.crt:            891 bytes server.crt:          891 bytes

Note: As seen above, I have created MD5 hash using "md5<password>:<userid>". The reason, I added string "md5" in front of hashed string is that when Postgres sees "md5" as a prefix, it recognizes that the string is already hashed and does not try to hash again and stores as it is.

Create PersistentVolume (PV) and PersistentVolumeClaim (PVC) 

Let's go ahead and create PV and PVC. We will use 'Retain' as persistentVolumeReclaimPolicy, so that data can be retained even when Postgresql pod is destroyed and recreated.
Sample PV yaml file:

## shared-nfs-pv-postgresql.yml apiVersion: v1 kind: PersistentVolume metadata:   name: shared-nfs-pv-postgresql   namespace: shared-services spec:   capacity:     storage: 5Gi   accessModes:   - ReadWriteMany   nfs:     path: /var/postgresql/     server: 192.168.56.101   persistentVolumeReclaimPolicy: Retain

Sample PVC yaml file:

## shared-nfs-pvc-postgresql.yml apiVersion: v1 kind: PersistentVolumeClaim metadata:   name: shared-nfs-pvc-postgresql   namespace: shared-services spec:   accessModes:     - ReadWriteMany   resources:     requests:       storage: 5Gi

PV and PVC creation and verification steps:

# Create persistentvolume $> kubectl create -f yaml/shared-nfs-pv-postgresql.yml persistentvolume "shared-nfs-pv-postgresql" created # Create persistentvolumeclaim $> kubectl create -f yaml/shared-nfs-pvc-postgresql.yml persistentvolumeclaim "shared-nfs-pvc-postgresql" created # Verify and make sure status of persistentvolumeclaim/shared-nfs-pvc-postgresql is Bound $> kubectl get pv,pvc -n shared-services NAME                                        CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS    CLAIM                                       STORAGECLASS   REASON    AGE persistentvolume/shared-nfs-pv-postgresql   5Gi        RWX            Retain           Bound     shared-services/shared-nfs-pvc-postgresql                            32s NAME                                              STATUS    VOLUME                     CAPACITY   ACCESS MODES   STORAGECLASS   AGE persistentvolumeclaim/shared-nfs-pvc-postgresql   Bound     shared-nfs-pv-postgresql   5Gi        RWX                           20s

Create deployment manifest file

Here is the one, I have put together. You can customize it further per your need.

--- # Service definition apiVersion: v1 kind: Service metadata:   name: sysg-postgres-svc   namespace: shared-services spec:   type: ClusterIP   ports:     - port: 5432       targetPort: 5432       protocol: TCP       name: tcp-5432   selector:       app: sysg-postgres-app --- # Deployment definition apiVersion: apps/v1beta2 kind: Deployment metadata:   name: sysg-postgres-dpl   namespace: shared-services spec:   selector:     matchLabels:       app: sysg-postgres-app   replicas: 1   template:     metadata:       labels:         app: sysg-postgres-app     spec:       serviceAccountName: shared-svc-accnt       securityContext:         runAsUser: 70         supplementalGroups: [999,1000]         fsGroup: 70       volumes:         - name: shared-nfs-pv-postgresql           persistentVolumeClaim:             claimName: shared-nfs-pvc-postgresql         - name: secret-vol           secret:             secretName: postgresql-secrets             defaultMode: 0640         - name: config-vol           configMap:             name: postgresql-config       containers:       - name: sysg-postgres-cnt         image: postgres:10.5-alpine         imagePullPolicy: IfNotPresent         args:           - -c           - hba_file=/etc/postgresql-config-vol/pg_hba.conf           - -c           - config_file=/etc/postgresql-config-vol/postgresql.conf         env:           - name: POSTGRES_USER             value: postgres           - name: PGUSER             value: postgres           - name: POSTGRES_DB             value: mmdb           - name: PGDATA             value: /var/lib/postgresql/data/pgdata           - name: POSTGRES_PASSWORD_FILE             value: /etc/postgresql-secrets-vol/postgresql-pwd.txt         ports:          - containerPort: 5432         volumeMounts:           - name: config-vol             mountPath: /etc/postgresql-config-vol           - mountPath: /var/lib/postgresql/data/pgdata             name: shared-nfs-pv-postgresql           - name: secret-vol             mountPath: /etc/postgresql-secrets-vol       nodeSelector:         kubernetes.io/hostname: centosddcwrk01

Deploy the Postgresql

Below steps show the creation of service and deployment as well as step to make sure that the Postgres is running with SSL enabled mode.

# Deploy $> kubectl apply -f yaml/postgres-deploy.yml service "sysg-postgres-svc" created deployment.apps "sysg-postgres-dpl" created # Verify $> kubectl get pods,svc -n shared-services NAME                                     READY     STATUS    RESTARTS   AGE pod/sysg-postgres-dpl-596754d5d4-mc8fm   1/1       Running   0          1h NAME                        TYPE        CLUSTER-IP    EXTERNAL-IP   PORT(S)    AGE service/sysg-postgres-svc   ClusterIP   10.96.90.30           5432/TCP   1h # sh into the postgresql pod: $> kubectl exec -it sysg-postgres-dpl-596754d5d4-mc8fm /bin/sh -n shared-services / $ # Launch psql / $ psql -U postgres psql (10.5) Type "help" for help. # Verify SSL is enabled postgres=# SHOW ssl;  ssl -----  on (1 row) # Check the stored password. It should match the hashed value of "<password><user>" with "md5" prepended. postgres=#  select usename,passwd from pg_catalog.pg_shadow;  usename  |               passwd ----------+-------------------------------------  postgres | md5db59316e90b1afb5334a331081618af6 # Connect remotely. You need to provide password. $> kubectl exec -it sysg-postgres-dpl-596754d5d4-mc8fm -n shared-services -- psql "sslmode=require host=10.96.90.30 port=5432 dbname=mmdb" --username=postgres Password for user postgres: psql (10.5) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. (1 row)

Few key points

1) Using customized configuration file:
As you have seen above,  I have created configMap object postgresql-config and used it using option '-c config_file=/etc/postgresql-config-vol/postgresql.conf'. ConfigMap object postgresql-config is mapped to path '/etc/postgresql-config-vol' in volumeMounts definition.

containers: - name: sysg-postgres-cnt   imagePullPolicy: IfNotPresent   args:     - -c     - hba_file=/etc/postgresql-config-vol/pg_hba.conf     - -c     - config_file=/etc/postgresql-config-vol/postgresql.conf

volumeMounts:   - name: config-vol     mountPath: /etc/postgresql-config-vol

2) Creating environment variable from secrets:

env:   - name: POSTGRES_PASSWORD_FILE     value: /etc/postgresql-secrets-vol/postgresql-pwd.txt

And the secret is mapped to path /etc/postgresql-secrets-vol

volumeMounts:   - name: secret-vol     mountPath: /etc/postgresql-secrets-vol

3) PGDATA environment variable: 
The default value is '/var/lib/postgresql/data'. However, Postgres recommends "... if the data volume you're using is a fs mountpoint (like with GCE persistent disks), Postgres initdb recommends a subdirectory (for example /var/lib/postgresql/data/pgdata ) be created to contain the data.". Refer to https://hub.docker.com/_/postgres/

Here we assign /var/lib/postgresql/data/pgdata:

env:   - name: PGDATA     value: /var/lib/postgresql/data/pgdata

Troubleshooting

1) Make sure server.key, server.crt, and root.crt all have appropriate permissions that is 0400 (if owned by postgres process owner) or 0640 (if owned by root). If proper permissions is not applied, Postgresql will not start. and in log, you will see following FATAL message.

2018-09-22 18:26:22.391 UTC [1] FATAL:  private key file "/etc/postgresql-secrets-vol/server.key" has group or world access 2018-09-22 18:26:22.391 UTC [1] DETAIL: File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root. 2018-09-22 18:26:22.391 UTC [1] LOG:  database system is shut down

In order to apply proper permission in file level, you can use 'defaultMode', I'm using defaultMode: 0644 as shown below (fragment from postgres-deploy.yml)

- name: secret-vol   secret:     secretName: postgresql-secrets     defaultMode: 0640

2) Make sure to have right ownership - whether the files/directories are related to secret volume, config volume or persistence storage volume. Below is the error related PV path:

initdb: could not change permissions of directory "/var/lib/postgresql/data/pgdata": Operation not permitted

In order to resolve the above issue, you need to use Kubernetes' provided securityContext options like 'runAsUser', 'fsGroup', 'supplementalGroups' and/or capabilities. SecurityContext can be defined in both pod level and container level. In my case, I've defined it in pod level as shown below (fragment from postgres-deploy.yml)

securityContext:   runAsUser: < specify your run as user >   fsGroup: < specify group >   supplementalGroups: [< comma delimited list of supplementalGroups >]

Read Configure a Security Context for a Pod or Container chapter from official Kubernetes site. I've also given some troubleshooting tips while using NFS type Persistent Volume and Claim in my previous blog How to Create, Troubleshoot and Use NFS type Persistent Storage Volume in Kubernetes

Below, I'm showing file permission per my configuration. Files are owned by root:postgres.

# Get running Kubernetes pod $> kubectl get pods -n shared-services NAME                                 READY     STATUS    RESTARTS   AGE sysg-postgres-dpl-596754d5d4-mc8fm   1/1       Running   0          12s # sh to running Kubernetes pod $> kubectl exec -it sysg-postgres-dpl-596754d5d4-mc8fm /bin/sh -n shared-services # Explore files / $ cd /etc/postgresql-secrets-vol/..data/ /etc/postgresql-secrets-vol/..2018_09_22_19_24_52.289379695 $ ls -la drwxr-sr-x    2 root     postgres       120 Sep 23 01:19 . drwxrwsrwt    3 root     postgres       160 Sep 23 01:19 .. -rw-r-----    1 root     postgres        13 Sep 23 01:19 postgresql-pwd.txt -rw-r-----    1 root     postgres       891 Sep 23 01:19 root.crt -rw-r-----    1 root     postgres       891 Sep 23 01:19 server.crt -rw-r-----    1 root     postgres       887 Sep 23 01:19 server.key

3) psql: FATAL:  no pg_hba.conf entry for host "10.0.2.15", user "postgres" ... This FATAL message usually appears when you are trying to establish connection to Postgres, but the way you are trying to  authenticate is not defined in pg_hba.conf. Either the source IP (from where the connection originates is out of range, security option is not supported. Check your pg_hba.conf file and make sure right entry has been added.

[Optional]  Creating custom Postgres Docker image with customized postgresql.conf

If you prefer to create custom Docker image with custom postgresql.conf rather than creating configMap and using '-c config_file' option, you can do so. Here is how:

Create Dockerfile:

FROM postgres:10.5-alpine COPY config/postgresql.conf /tmp/postgresql.conf COPY scripts/_updateConfig.sh /docker-entrypoint-initdb.d/ RUN chmod 755 /docker-entrypoint-initdb.d/_updateConfig.sh && chmod 644 /tmp/postgresql.conf

My custom postgresql.conf is located under config directory locally. It will be copied to /tmp when Docker image is created. _updateConfig.sh is located under scripts directory locally and copied to /docker-entrypoint-initdb.d/ in build time.

Create script file _updateConfig.sh as shown below. It assumes that default PGDATA value '/var/lib/postgresql/data' is being used.

#!/usr/bin/env bash cat /tmp/postgresql.conf > /var/lib/postgresql/data/postgresql.conf

Important: we can not directly copy the custom postgresql.conf into $PGDATA directory in build time because that directory does not exist yet.

Build the image:

Directory and files shown below are local:

$> ls -la postgresql/ drwxrwxr-x.  2 osboxes osboxes 4096 Sep 23 13:01 config -rwxr-xr--.  1 osboxes osboxes  227 Sep 22 19:14 Dockerfile drwxrwxr-x.  2 osboxes osboxes 4096 Sep 22 18:39 scripts drwxrwxr-x.  2 osboxes osboxes 4096 Sep 22 23:42 secrets drwxrwxr-x.  2 osboxes osboxes 4096 Sep 23 12:11 yaml $>cd postgresql # docker build -t <image tag> . # In my case I am using osboxes/postgres:10.5-sysg as image name and tag. $> docker build -t osboxes/postgres:10.5-sysg .

If you use custom Docker image built this way, you don't need to define configMap to use custom postgresql.conf.