Configuring TLS for VMware SQL with Postgres for Kubernetes Instances

This topic describes how to configure TLS for a Postgres instance.

Overview

VMware Postgres Operator supports Transport Layer Security (TLS) encrypted connections to the Postgres server from clients and applications. Clients can connect to the Postgres server and verify the connection using user provided certificates or certificates provided by a corporate Certificate Authority (CA).

For internal Kubernetes communications the Postgres server by default requires cert-manager self-signed certificates. See Prerequisites in the Installing VMware Postgres Operator page.

The VMware Postgres Operator uses a Kubernetes Secret to manage TLS. There are several ways to create the Secret and this topic describes two supported methods:

• Creating the TLS Secret using cert-manager.
• Create the TLS Secret Manually.

For general information about Kubernetes and TLS secrets, see TLS Secrets in the Kubernetes documentation.

Prerequisites

Before you configure TLS for a Postgres instance, you must have:

• The Kubernetes Command Line Interface (kubectl) installed. For more information see Install Tools in the Kubernetes documentation.
• The name of your Postgres instance. Refer to the Configuring a Postgres Instance for an overview of this information. You do not need to create a Postgres instance before configuring TLS.
• The namespace of your Postgres instance.
• The Postgres instance Service type, internal (ClusterIP) or external (LoadBalancer). The instance Service type determines the server hostname that is used during certificate generation.
• Access and admin permissions to the Postgres instance.
• If you're using cert-manager to configure TLS, obtain your custom or corporate CA public key, and the private/public key certificate pair.

Creating the TLS Secret Using cert-manager

This procedure describes how to configure a custom Certificate Authority and custom certificates using cert-manager. To create the TLS Secret through the kubectl interface instead, see Create the TLS Secret Manually.

1. Verify the cert-manager configuration:

   kubectl get all --namespace=cert-manager
   

2. For the CA certificate, create a Kubernetes Secret. For example create a file named my-CA-secret.yaml, with values similar to:

   kind: Secret
   metadata:
     name: my-ca-certificate
     namespace: cert-manager-namespace
   data:
     tls.crt: this is CA public key
     tls.key: this is the CA private key
   

   and apply with:

   kubectl apply -f my-CA-secret.yaml
   

3. Create a CA issuer in cert-manager using a ClusterIssuer resource and associate it with the CA Secret created above. For information about the cert-manager Issuer types, see the cert-manager documentation.

   Note: The Postgres instance TLS secret requires the ca.crt key, therefore VMware does not recommend using the ACME Issuer.

   Create a ClusterIssuer resource using a yaml file, for example my-cluster-issuer.yaml, similar to:

   apiVersion: cert-manager.io/v1
   kind: ClusterIssuer
   metadata:
     name: sample-postgres-ca-certificate-clusterissuer
   spec:
     ca:
       secretName: my-ca-certificate
   

4. Apply the Secret using:

   kubectl apply -f my-cluster-issuer.yaml
   

   For certificate creation troubleshooting see the cert-manager documentation.

5. For new VMware Postgres Operator customers, create the Postgres Operator and set it to use the custom TLS issuer by using a command similar to:

   helm install postgres-operator --set=certManagerClusterIssuerName=sample-postgres-ca-certificate-clusterissuer  operator/
   

   For existing VMware Postgres Operator customers, update the Operator using a command similar to:

   helm upgrade postgres-operator --set=certManagerClusterIssuerName=sample-postgres-ca-certificate-clusterissuer  operator/
   

   Note: If you need to use more than one CA issuer, create another Postgres Operator in a different Kubernetes cluster.

To verify the TLS security setup see Verifying TLS Security for an example using psql.

Creating a TLS Secret Manually

This procedure describes how to create the TLS Secret manually, using kubectl. To create the TLS Secret using cert-manager instead, see Creating the TLS Secret with cert-manager.

Note: Installing cert-manager when you install the VMware Postgres Operator prerequisites does not prevent the manual TLS configuration. Cert-manager is required for internal Postgres Operator communications.

1. Generate a public/private key pair certificate using a certificate generation tool such as OpenSSL, certstrap, or Let's Encrypt. For an example using OpenSSL, see Creating Certificates in the PostgreSQL documentation.
   During the certificate request, ensure that you supply the correct server domain name as the common name or the subject alternative name (SAN) for which the certificate is valid for. The server domain name is the DNS name used to connect to the database, and it depends on the Service type deployment scenario:

   • If you configure ClusterIP as Service type (spec.serviceType) in the Postgres instance yaml (ClusterIP is the default Service type in VMware Postgres Operator release 1.2), your database is deployed in the same Kubernetes cluster as your instance, is not exposed externally, and the hostname has the format *.[instance-name]-agent.[namespace].svc.cluster.local. For example *.postgres-sample-agent.default.svc.cluster.local.

   • If you configure LoadBalancer in the Postgres instance yaml (LoadBalancer is the default Service type in VMware Postgres Operator releases 1.0 and 1.1), your database is accessible outside the Kubernetes cluster, and the hostname is the external DNS name or the IP address of the load balancer. To find the load balancer IP, use a command similar to:

     kubectl get service postgres-sample
     

     which returns:

     NAME             TYPE           CLUSTER-IP       EXTERNAL-IP      PORT(S)          AGE
     postgres-sample  LoadBalancer   10.107.136.143   192.168.64.101   5432:31958/TCP   66s
     

     The load balancer IP is located under the header EXTERNAL-IP.

   Save the required certificates files locally.

2. Create the TLS Secret by running:

   kubectl create secret generic <some-tls-secret> \ 
        --type kubernetes.io/tls \
        --from-file=ca.crt=/path/to/ca.crt \
        --from-file=tls.crt=/path/to/tls.crt \
        --from-file=tls.key=/path/to/tls.key \
        --namespace <postgres-instance-namespace> 
   

   Where:

   • <some-tls-secret> is the TLS Secret.
   • /path/to/ca.crt is the file path to the CA's public key that is used by clients or applications to verify that the certificate used to communicate with the Postgres Server was issued using the specified Certificate Authority.
   • /path/to/tls.crt is the file path to the public key of the certificate created in step 1 above.
   • /path/to/tls.key is the file path to the private key of the certificate created in step 1 above.
   • <instance-namespace> is the namespace for the Postgres instance.

   For example:

   kubectl create secret generic postgres-tls-secret \
         --type kubernetes.io/tls \
         --from-file=tls.crt=/path/server.crt \
         --from-file=tls.key=/path/server.key \
         --from-file=ca.crt=/path/server_ca.crt \
         --namespace sample-postgres-namespace 
   

Use the TLS Secret in the instance yaml file during instance creation, or apply the TLS secret after the instance creation by updating the instance yaml:

1. Edit the postgres.yaml file for the Postgres instance, and add postgres-tls-secret as the name of the TLS Secret created in the sample-postgres-namespace namespace. For example:

   spec:
      certificateSecretName: postgres-tls-secret
   

2. Update the Postgres instance:

   kubectl apply -f postgres.yaml -n sample-postgres-namespace
   

3. Restart the instance and the monitor:

   kubectl rollout restart statefulset/postgres-sample
   kubectl rollout restart statefulset/postgres-sample-monitor
   

After the Postgres instance restarts, client connections use TLS security. See Verifying TLS Security for an example scenario on how to verify the TLS setup.

Verifying TLS Security Using psql

To verify a successful TLS implementation, use psql as an example client to confirm the certificate usage. The example scenario uses the jq command line tool, which you need to install on your local computer.

The credential values required for the psql command are base64 encoded. For example, for the instance postgres-sample the values would look similar to:

kubectl get secret postgres-sample-db-secret -o json | jq '.data | map_values(@base64d)'

{
  "dbname": "postgres-sample",
  "instancename": "postgres-sample",
  "namespace": "default",
  "password": "e0AJi1xiRr51m6gZKqb5Cwj0a6i53L",
  "username": "pgadmin"
}

or for the application user:

kubectl get secret/postgres-sample-app-user-db-secret -o json | jq '.data | map_values(@base64d)'

{
  "database": "postgres-sample",
  "host": "postgres-sample.default",
  "password": "w6J0U600C7jbpfSQl677Cjnid8Dcjz",
  "port": "5432",
  "provider": "vmware",
  "type": "postgresql",
  "uri": "postgresql://pgappuser:[email protected]:5432/postgres-sample",
  "username": "pgappuser"
}

Acquire the Service IP address using a command similar to:

kubectl get service postgres-sample -o jsonpath='{.status.loadBalancer.ingress[0].ip}'

192.168.64.100

First test the psql connection with the acquired credentials but without using TLS validation:

psql "host=192.168.64.100 \
      port=5432 \
      dbname=postgres-sample \
      user=pgadmin \
      password=e0AJi1xiRr51m6gZKqb5Cwj0a6i53L \
      target_session_attrs=read-write"

Get your Certificate Authority public key and place it in a file like /tmp/ca.crt:

kubectl exec -q -ti postgres-sample-0 -- bash -c 'cat /etc/postgres_ssl/ca.crt' > /tmp/ca.crt

Connect using TLS with the acquired credentials, and validate the signing CA (sslmode=verify-ca):

psql "host=192.168.64.100 \
      port=5432 \
      dbname=postgres-sample \
      user=pgadmin \
      password=e0AJi1xiRr51m6gZKqb5Cwj0a6i53L \
      target_session_attrs=read-write \
      sslmode=verify-ca \
      sslrootcert=/tmp/ca.crt"

Connect using TLS with the acquired credentials, validate the signing CA, and validate that the certificate matches the correct hostname (verify-full):

psql "host=192.168.64.100 \
      port=5432 \
      dbname=postgres-sample \
      password=e0AJi1xiRr51m6gZKqb5Cwj0a6i53L \
      user=pgadmin \
      target_session_attrs=read-write \
      sslmode=verify-full \
      sslrootcert=/tmp/ca.crt"

which returns similar to:

psql (11.13 (VMware Postgres 11.13.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres-sample=#

To test the connection within the cluster, use the DNS of the Service which resolves to the ClusterIP, similar to:

kubectl exec -it pod/postgres-sample-monitor-0 -- bash

postgres@postgres-sample-monitor-0:/psql "host=postgres-sample.default.svc.cluster.local dbname=postgres-sample user=pgadmin password=e0AJi1xiRr51m6gZKqb5Cwj0a6i53L port=5432"

which returns similar to:

psql (11.13 (VMware Postgres 11.13.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres-sample=#