Skip to content
Announcing BigAnimal: Fully managed PostgreSQL in the Cloud
Contact usDocsPlans

How to Issue a TLS Certificate for Cloud Native PostgreSQL

Danish Khan4/16/2021
PostgreSQLKubernetes
EDB has released the Cloud Native PostgreSQL (CNP) plugin for kubectl in version 1.1.0 to support useful features. One of these is issuing a client certificate to be used for database authentication. PostgreSQL has native support for TLS/SSL client certificate authentication, meaning a valid and trusted certificate can be used to authenticate a client, in lieu of passwords.
 
The Cloud Native PostgreSQL operator has been designed to work with TLS/SSL for both encryption in transit and authentication, on server and client sides. Clusters created using the CNP operator come with a Certification Authority (CA) to create and sign TLS client certificates, and the “cnp” plugin for kubectl can be used to issue a new TLS client certificate.
 
In the previous blog post, we have introduced this plugin including its installation instructions.
 
This post demonstrates, in a few steps, how easy it is to generate a client certificate and how to use it to securely connect your application to your Cloud Native PostgreSQL cluster (This is limited, for now, to applications deployed inside Kubernetes.).
 

Issuing a new client certificate

Certificates can be issued through the “certificate” command of the plugin. You can use the “help” option to get information about the available options:

kubectl cnp certificate --help


Output:

This command create a new Kubernetes secret containing the crypto-material
needed to configure TLS with Certificate authentication access for an application to
connect to the PostgreSQL cluster.

Usage:
  kubectl cnp certificate [secretName] [flags]

Flags:
      --cnp-cluster string   The name of the PostgreSQL cluster
      --cnp-user string      The name of the PostgreSQL user
      --dry-run              If specified the secret is not created
  -h, --help                 help for certificate
  -o, --output string        Output format. One of json|yaml


A real life example

With the help of the powerful “cnp” plugin for kubectl, we will issue a client certificate and use the same to securely connect an application to a Cloud Native PostgreSQL cluster.
 
The following manifest will create a PostgreSQL cluster with the name cluster-example,using the application username appuser and configuring the HBA rules to allow the TLS client certificate authentication within Postgres:


apiVersion: postgresql.k8s.enterprisedb.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  instances: 3

  storage:
    size: 1Gi

  bootstrap:
    initdb:
      owner: appuser

  postgresql:
    pg_hba:
    - hostssl all all all cert


Step 1: issue a new certificate for app user

Note that this feature is available for any custom user you might want to configure in the cluster.


kubectl cnp certificate cluster-appuser --cnp-cluster cluster-example --cnp-user appuser 


Output:

secret/cluster-appuser created


Step 2: fetch the new TLS client certificate

After the secret is created, you can get it by using the kubectl command:


kubectl get secret cluster-appuser

Output:
NAME           TYPE                DATA   AGE
cluster-cert   kubernetes.io/tls   2      73s


And view the content of the same secret in plain text combining the kubectl command with the jq tool:


kubectl get secret cluster-appuser -o json | jq -r '.data | map(@base64d) | .[]'


Output:


-----BEGIN CERTIFICATE-----
MIIBbTCCARSgAwIBAgIRALeyJdCG4GfoVVdFydd5sgMwCgYIKoZIzj0EAwIwLDEQ
MA4GA1UECxMHZGVmYXVsdDEYMBYGA1UEAxMPcG9zdGdyZXNxbC1jZXJ0MB4XDTIx
MDMwMzEwMzEwOFoXDTIyMDMwMzEwMzEwOFowDjEMMAoGA1UEAxMDYXBwMFkwEwYH
KoZIzj0CAQYIKoZIzj0DAQcDQgAEX/mnSV57RH35CRxRxp/FZddTBEMRh2OqSGp0
loRTiEywBzyqe02MbRwLiigzPHwaW4o+7anLgNCo96J/Oi7ZMqM1MDMwDgYDVR0P
AQH/BAQDAgOIMBMGA1UdJQQMMAoGCCsGAQUFBwMCMAwGA1UdEwEB/wQCMAAwCgYI
KoZIzj0EAwIDRwAwRAIgcF9TZetuD0MDlSyQUaauYbrfYRDWk4kyuFAct7DKT/sC
ICgniN49e7qyDaBZ18+1V8N1EloqWM1CQupI1Fo6eXWv
-----END CERTIFICATE-----

-----BEGIN EC PRIVATE KEY-----
MHcCAQEEIHJFrapxgT6w/CXVY0vZR/elT4gNXL00vnBlRYtU7LigoAoGCCqGSM49
AwEHoUQDQgAEX/mnSV57RH35CRxRxp/FZddTBEMRh2OqSGp0loRTiEywBzyqe02M
bRwLiigzPHwaW4o+7anLgNCo96J/Oi7ZMg==
-----END EC PRIVATE KEY----


Note the Common Name (CN) of the TLS certificate that has been created:

kubectl get secret cluster-appuser -o jsonpath="{.data['tls\.crt']}" | base64 -d | openssl x509 -text -noout | head -n 11


Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            5d:e1:72:8a:39:9f:ce:51:19:9d:21:ff:1e:4b:24:5d
        Signature Algorithm: ecdsa-with-SHA256
        Issuer: OU = default, CN = cluster-example
        Validity
            Not Before: Mar 22 10:22:14 2021 GMT
            Not After : Mar 22 10:22:14 2022 GMT
        Subject: CN = appuser


As you can see, TLS client certificates by default are created with one year of validity, and with a simple CN that corresponds to the Postgres username. This is necessary to leverage the cert authentication method for hostssl entries in pg_hba.conf, as explained in the PostgreSQL documentation.
 

Step 3: connect to a Postgres cluster using a TLS certificate

Now, we will use this client certificate to configure a client application which will connect to our Cloud Native PostgreSQL cluster. For ease of use, we have selected a container image that already has the psql client installed. In this case, I am picking up the leonardoce/webtest:1.0.0 open source image (made by my colleague Leonardo Cecchi, one of the lead developers of the CNP operator) as it is tested in-house and publicly available.
 
To ease up the process, use the following YAML to create an application Pod in the namespace where your database cluster is running:
$ cat > cert-test.yaml <<EOF


apiVersion: apps/v1
kind: Deployment
metadata:
  name: cert-test
spec:
  replicas: 1
  selector:
    matchLabels:
      app: webtest
  template:
    metadata:
      labels:
        app: webtest
    spec:
      containers:
        - image: leonardoce/webtest:1.0.0
          name: cert-test
          volumeMounts:
            - name: secret-volume-root-ca
              mountPath: /etc/secrets/ca
            - name: secret-volume-appuser
              mountPath: /etc/secrets/appuser
          ports:
            - containerPort: 8080
          env:
            - name: DATABASE_URL
              value: >
                sslkey=/etc/secrets/appuser/tls.key
                sslcert=/etc/secrets/appuser/tls.crt
                sslrootcert=/etc/secrets/ca/ca.crt
                host=cluster-example-rw.default.svc
                dbname=app
                user=appuser
                sslmode=verify-full
            - name: SQL_QUERY
              value: SELECT 1
          readinessProbe:
            httpGet:
              port: 8080
              path: /tx
      volumes:
        - name: secret-volume-root-ca
          secret:
            secretName: cluster-example-ca
            defaultMode: 0600
        - name: secret-volume-appuser
          secret:
            secretName: cluster-appuser
            defaultMode: 0600

This Pod will mount secrets managed by the Cloud Native PostgreSQL operator that contain the TLS client certificate, the TLS client certificate private key and the TLS Certification Authority certificate. They will be used to create the default resources that psql (and other libpq based applications like pgbench) requires to establish a TLS encrypted connection to the Postgres database.

 
By default, psql searches for certificates inside the ~/.postgresql directory of the current user, but we can use the sslkey, sslcert, sslrootcert options to point libpq to the actual location of the cryptographic material.

The content of the above files is gathered from the secrets that were previously created by using the “cnp” plugin for kubectl.

 
Now, create a Deployment using the above manifest with the following command:

kubectl create -f cert-test.yaml


Output:

deployment.apps/cert-test created


Wait for the cert-test Pod to be in ready state:

kubectl get pods


Output:

NAME                         READY   STATUS    RESTARTS   AGE
cert-test-6dbcdbdcfd-c2l5l   1/1     Running   0          2m19s
cluster-example-1            1/1     Running   0          4m38s
cluster-example-2            1/1     Running   0          4m24s
cluster-example-3            1/1     Running   0          4m5s


Then we will use the cert-test-6dbcdbdcfd-c2l5l Pod as PostgreSQL client to validate SSL connection and authentication using TLS certificates we just created.
 
A readiness probe has been configured to ensure that the application is ready when the database server can be reached.
 
The following command will execute an interactive bash inside the Pod’s container to run psql using the necessary options. The PostgreSQL server is exposed through the read-write Kubernetes service. We will point the psql command to connect to this service:

kubectl exec -it cert-test-6dbcdbdcfd-c2l5l -- bash -c "psql 'sslkey=/etc/secrets/appuser/tls.key sslcert=/etc/secrets/appuser/tls.crt sslrootcert=/etc/secrets/ca/ca.crt host=cluster-example-rw.default.svc dbname=app user=appuser sslmode=verify-full'  -c 'select version();'"


Output:



                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
(1 row)


For more details on the exposed services managed by the Cloud Native PostgreSQL operator please refer to the official CNP documentation.
Additional information about the sslmode option can be found in the official PostgreSQL documentation.

 

Conclusion

I'm sure you can now easily figure out how useful this “cnp” plugin for kubectl is going to be for any Sysadmin/DBA when it comes to issuing TLS client certificates and managing them within a Kubernetes environment. This plugin is backed by the powerful CNP operator behind.
Due to the “cnp” plugin’s thoughtful and simplistic design, we were able to issue a TLS certificate in just a few steps.
 
Now that we have an idea of how the “cnp” plugin for kubectl works in terms of issuing TLS client certificates, go ahead and start using it to connect to your Cloud Native PostgreSQL cluster!
 

Danish Khan is a Principal Software Engineer at EDB. His core areas of expertise include Infrastructure automation, Virtualization, Kubernetes and Containers, Automation testing, Python, and Cloud technologies. Prior to working at EDB, Danish worked as an Automation Engineer, Infrastucture consultan ...