Feature Request: SSL Configuration

[movitto]

First of all, thanks for the great project. We've only recently found it but it's already proving to be very useful to setup postgres on kubernetes in a very simple way.

Having recently used kubegres to setup postgres on our Kubernetes cluster, along with SSL support (issue #58 helped us resolve issues with the importing of ssl certificates) we couldn't help but think that it would be nice if kubegres took care of this configuration internally, without users having to go through the hasstle of key/cert management and configuration. Our current workflow is as follows:

1. Generate server key and certificate via openssl locally

openssl req -new -text -passout pass:secret -subj /CN=mypostgres-db -out server.req -keyout server.pem
openssl rsa -in server.pem -passin pass:secret -out server.key
openssl req -x509 -in server.req -text -key server.key -out server.crt

2. Generate client key and certificate signing request. Sign the CSR to generate client cert

openssl req -new -text -passout pass:secret -subj /CN=client -out client.req -keyout client.pem
openssl rsa -in client.pem -passin pass:secret -out client.key
openssl req -new -key client.key -out client.csr -subj '/CN=client'
openssl x509 -req -days 365  -in client.csr -CA server.crt -CAkey server.key -out client.crt -CAcreateserial

3. Create kubernetes TLS secrets for the server key/cert and client key/cert

kubectl create secret tls mypostgres-server-tls --cert=server.crt --key=server.key
kubectl create secret tls mypostgres-client-tls --cert=client.crt --key=client.key

4. Create a secret for postgres superuser & replication credentials

apiVersion: v1
kind: Secret
metadata:
  name: mypostgres-secret
  namespace: default
type: Opaque
stringData:
  superUserPassword: supersecret
  replicationUserPassword: donttellanyone
  mypostgresDB: mypostgres
  mypostgresUserName: mypostgres
  mypostgresUserPassword: the_truth_is_out_there
  mypostgresDBURI: postgresql://mypostgres:the_truth_is_out_there@mypostgres-db/mypostgres

5. Apply it:

kubectl apply -f mypostgres-secret.yaml

6. Create a kuberges configuration, setting up ssl:

apiVersion: v1
kind: ConfigMap
metadata:
  name: mypostgres-conf
  namespace: default
data:
  postgres.conf: |
    # Default stuff from kubegres
    listen_addresses = '*'
    max_wal_senders = 10
    max_connections = 100
    shared_buffers = 128MB

    # Our stuff:
    ssl = on
    ssl_ca_file = '/mypostgres/.postgresql/tls.crt'
    ssl_cert_file = '/mypostgres/.postgresql/tls.crt'
    ssl_key_file = '/mypostgres/.postgresql/tls.key'

  pg_hba.conf: |
    local all all md5
    host replication replication all md5
    hostssl all all all md5 clientcert=verify-ca
    hostssl all all 0.0.0.0/0 reject

  primary_init_script.sh: |
    #!/bin/bash
    set -e

    psql -v ON_ERROR_STOP=1  -h /var/run/postgresql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE DATABASE $POSTGRES_MYPOSTGRES_DB;
    CREATE USER $POSTGRES_MYPOSTGRES_USER WITH PASSWORD '$POSTGRES_MYPOSTGRES_PASSWORD';
    GRANT ALL PRIVILEGES ON DATABASE "$POSTGRES_MYPOSTGRES_DB" to $POSTGRES_MYPOSTGRES_USER;
    EOSQL

Note that we only permit local unix-domain connections, replicator connections, and external ssl connections. Also in the primary_init_script, we connect to postgres via the local unix-domain socket

6. Apply it

kubectl apply -f mypostgres-conf.yaml

7. Create a kubergres config:

apiVersion: kubegres.reactive-tech.io/v1
kind: Kubegres
metadata:
  name: mypostgres-db
  namespace: default
spec:
   replicas: 3
   image: postgres:14.1
   database:
      size: 10Gi
   customConfig: mypostgres-db-conf
   securityContext:
     fsGroup: 999
   volume:
     volumeMounts:
       - mountPath: "/mypostgres/.postgresql"
         name: mypostgres-db-server-tls
         readOnly: true
     volumes:
       - name: mypostgres-db-server-tls
         secret:
           secretName: mypostgres-db-server-tls
           defaultMode: 0400
   env:
     - name: POSTGRES_PASSWORD
       valueFrom:
         secretKeyRef:
           name: mypostgres-db-secret
           key: superUserPassword
     - name: POSTGRES_REPLICATION_PASSWORD
       valueFrom:
         secretKeyRef:
           name: mypostgres-db-secret
           key: replicationUserPassword
     - name: POSTGRES_MYPOSTGRES_DB
       valueFrom:
         secretKeyRef:
           name: mypostgres-db-secret
           key: mypostgresDB
     - name: POSTGRES_MYPOSTGRES_USER
       valueFrom:
         secretKeyRef:
           name: mypostgres-db-secret
           key: lmypostgresUserName
     - name: POSTGRES_MYPOSTGRES_PASSWORD
       valueFrom:
         secretKeyRef:
           name: mypostgres-db-secret
           key: mypostgresUserPassword

Apply it and wait for the server to start:

kubectl apply -f mypostgres.yaml

8. Define a kubernetes control file for our custom application image (here it's a job, but could be a deployment, statefulset or whatever):

apiVersion: batch/v1
kind: Job
metadata:
  name: awesomeapp-init-job
spec:
  template:
    spec:
      containers:
      - name: awesomeapp-init-job
        image: devnullprod/awesomeapp-init-job
        env:
        - name: MYPOSTGRES_CONFIG
          valueFrom:
             secretKeyRef:
                name: mypostgres-db-secret
                key: mypostgresDBURI
        volumeMounts:
          - mountPath: "/mypostgres/.postgresql"
            name: mypostgres-db-client-tls
            readOnly: true
          - mountPath: "/mypostgres/.postgresql/server"
            name: mypostgres-db-server-tls
            readOnly: true
      imagePullSecrets:
      - name: mypostgres-auth
      restartPolicy: Never
      volumes:
        - name: mypostgres-db-client-tls
          secret:
            secretName: mypostgres-db-client-tls
            defaultMode: 0400
        - name: mypostgres-db-server-tls
          secret:
            secretName: mypostgres-db-server-tls
            defaultMode: 0400
            items:
              - key: tls.crt
                path: tls.crt
  backoffLimit: 4

Note how we import the database client key and cert here and the server cert (to use as the certificate authority in the database connection credentials). For reference here is a snippet of our applications database configuration (sequelize based)

const fs = require('fs');

var ssl_opts = {}
if(fs.existsSync('/mypostgres/.postgresql')){
  ssl_opts["key"]  = fs.readFileSync("/mypostgres/.postgresql/tls.key")
  ssl_opts["cert"] = fs.readFileSync("/mypostgres/.postgresql/tls.crt")
  ssl_opts["ca"]   = fs.readFileSync("/mypostgres/.postgresql/server/tls.crt")
}

module.exports = {
  "production": {
    "use_env_variable" : "MYPOSTGRES_CONFIG" 
    "dialectOptions" : {
      "ssl" : ssl_opts,
    },
    "dialect": "postgres"
  },
...
}

9. Finally start it:

kubectl apply -f awesomeapp.yaml

Phew that was a hasstle!

TLDR:

Wouldn't it be great if kubegres took care of alot of this for us. Specifically if we could add the following options to our kubegres database config:

ssl : true # enable ssl
ssl_require : true # disable non-ssl connections
ssl_clients : N # generate N client certificates

With the result being kubergres:

• generating the ssl key/certs for both the server and the client(s)
• Creating the kubernetes secrets for those
• Mounting the server key/crt in locations accessible by postgres (with the correct file permissions, etc)
• Settting up the necessary postgres config options

Thoughts?

(And thanks again for the great project and reading this lengthy issue!)

[alex-arica]

@movitto thank you so much for the fine grained details that you have kindly provided to the community.

One of the philosophy behind Kubegres is to avoid creating a custom Postgres container containing custom libraries. We are using the official Docker Postgres container. Kubegres can only uses the libraries available in that container.

Before I look into the details, my question is the following: do you think we can implement the feature that you have suggested without creating a custom set of libraries and therefore having to package them in a custom Postgres container?

[movitto]

@alex-arica Thanks for the response. As far as I can tell, the only additional component that is needed here is the 'openssl' command. Having just checked, I verified that yes, openssl is available in the stock postgresql container. Now the only question is do we want to run it there?

There are two possibilities:

• Run openssl on the primary postgresql container, then somehow 'export' the the client and server credentials to kubernetes such that secrets can be created, for import into the replicas and client containers.

• Run the openssl commands on the user's local system (from which they are issuing the commands to deploy kubegres), and then proceed in the manner described in the steps above.

The benefit of approach one is that all the software is ready out of the box, kubegres can take care of running the existing commands on the container. The only question is that I'm not sure how we export the key and cert files from the primary container so that is available to the other server and client containers.

The downside to the second strategy is that it requires the user to have openssl installed on their local system. The benefit to this though is that it's the standard kubernetes workflow after that, and the key/cert credentials are available locally for backup purposes.

---

I'd lean towards the second approach as it would be pretty straightforward.

1. Support 'ssl' and 'ssl_require' config options as mentioned in the comment above
2. Also add support for the following config options:

   ssl_credentials:
   server:
    key: // Path to key file
    cert: // Path to cert
    ca: // Path to CA (can be same as cert)
   clients:
   - key: // Path to first key file
   cert: // Path to first cert file
   - key: // Path to second key file
   cert: // Path to second cert file
   - ...

Alternatively instead of paths, the actual key / certs can be stored (or both option formats supported), though it's probably not advisable to store the keys directly in the kubegres config.

3. Detect if ssl: true is set but server key/cert is missing and produce error in kubgres config validation phase
4. Expand documentation to specify how to generate key/cert credentials (steps 1 and 2 above). Optionally provide a help script that the user can run locally to do so
5. Proceed implementing the steps above in kubegres
6. Detect when the key and cert files are updated in subsequent invocations of kubectl apply -f and refresh the pods accordingly

---

So long answer short, it can be done with the official postgresql containers! :smile:

[hxtk]

Personally I'm achieving the same result in terms of SSL configuration declaratively in Kubernetes by using JetStack CertManager with a Self-Signed Issuer to create a private CA using a Certificate resource, and then a CA issuer that depends on that private CA to generate the server certificate and any client certificates I need.

CertManager then automatically handles certificate rotation and generation with a fully declarative configuration that does not require secrets to be stored anywhere. I'm wonder if our use-cases are all that different or perhaps something similar would work for you.

[movitto]

@hxtk Certainly there are many use cases / workflows for certificate management with postgres / kubegres. While there is some overlap with our approaches, ours (self-managed certificates) work for us for the time being as we prefer not to leverage an external use case for both. That being said it would be nice if kubegres supported both, both internally generated certs (managed automatically by the cluster) and externally installed / managed certs.