Deploy PostgreSQL With TLS in Kubernetes

Ensuring data can be securely transmitted is a requirement of many production systems. PostgreSQL supports TLS as a means of encrypting network communication, verifying hosts, and allowing for certificate-based authentication.

The TLS functionality of PostgreSQL is extendable into Kubernetes deployments. The Crunchy Data Postgres Operator has provided support for TLS since version 4.3, using Kubernetes Secrets for mounting the TLS components safely to each Pod. The PostgreSQL Operator does not make an opinion about the PKI used to generate TLS certificate, but rather loads the TLS key pair and certificate authority (CA) for the PostgreSQL server.

Let's go through an example of creating a TLS-enabled PostgreSQL cluster.

Prerequisites

This example assumes that you have deployed the Crunchy Data Postgres Operator to Kubernetes using the quickstart.

There are three required items to enable TLS in your PostgreSQL clusters:

• A CA certificate
• A TLS private key
• A TLS certificate

There are a variety of methods available to generate these items. In fact, Kubernetes comes with its own certificate management system! The PostgreSQL documentation also provides an example for how to generate a TLS certificate as well.

It is up to you to decide how you want to manage this for your cluster, but let's walk through an example below.

We first need to generate a CA. Use the command below to generate an ECDSA CA:

openssl req \
  -x509 \
  -nodes \
  -newkey ec \
  -pkeyopt ec_paramgen_curve:prime256v1 \
  -pkeyopt ec_param_enc:named_curve \
  -sha384 \
  -keyout ca.key \
  -out ca.crt \
  -days 3650 \
  -subj "/CN=*"

For a production system, you would likely generate an intermediate CA as well.

Now let's generate a TLS key and certificate that our PostgreSQL cluster will use. In the next section, we're going to create a PostgreSQL cluster named hippo in the namespace pgo (based on the Postgres Operator quickstart). Knowing that, and in accordance of how DNS in Kubernetes works, let's generate a certificate with a CN of hippo.pgo:

openssl req \
  -new \
  -newkey ec \
  -nodes \
  -pkeyopt ec_paramgen_curve:prime256v1 \
  -pkeyopt ec_param_enc:named_curve \
  -sha384 \
  -keyout server.key \
  -out server.csr \
  -days 365 \
  -subj "/CN=hippo.pgo"

Finally, take the the certificate signing request (server.csr) generated by the previous command, and have the CA sign it:

openssl x509 \
  -req \
  -in server.csr \
  -days 365 \
  -CA ca.crt \
  -CAkey ca.key \
  -CAcreateserial \
  -sha384 \
  -out server.crt

We can now move on to deploying a PostgreSQL cluster with TLS in Kubernetes!

Deploying a PostgreSQL Cluster with TLS

To set up TLS for your PostgreSQL cluster, you have to create two Secrets. One that contains the CA certificate, and the other that contains the server TLS key pair.

First, create the Secret that contains your CA certificate. Create the Secret as a generic Secret with the following requirements:

• The Secret must be in the same Namespace as where you are deploying your PostgreSQL cluster
• The name of the key that is holding the CA must be ca.crt

For example, to create a CA Secret with the trusted CA to use for the PostgreSQL clusters in the pgo namespace, you can execute the following command:

kubectl create secret generic postgresql-ca -n pgo --from-file=ca.crt=ca.crt

Note that you can reuse this CA Secret for other PostgreSQL clusters deployed by the Postgres Operator.

Next, create the Secret that contains your TLS key and certificate. You can create this as a Kubernetes TLS Secret:

kubectl create secret tls hippo.tls -n pgo --cert=server.crt --key=server.key

With these Secrets presents, you can create a TLS-enabled PostgreSQL cluster named hippo with the following command:

pgo create cluster hippo \
  --server-ca-secret=postgresql-ca \
  --server-tls-secret=hippo.tls

The --server-ca-secret and --server-tls-secret will automatically enable TLS connections in the deployed PostgreSQL cluster. These flags should reference the CA Secret and the TLS key pair Secret, respectively. If you want to force all connections to be over TLS, you can add the --tls-only flag:

pgo create cluster hippo --tls-only \
  --server-ca-secret=postgresql-ca \
  --server-tls-secret=hippo.tls

For the rest of the examples, I will be using the Postgres cluster deployed with the --tls-only flag enabled.

SSL/TLS Modes & Connecting to a PostgreSQL Cluster with TLS

If deployed successfully, when you connect to the PostgreSQL cluster, assuming your PGSSLMODE is set to prefer or higher, you will see something like this in your psql terminal:

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

Let's see this in action and try out connecting to a PostgreSQL cluster and experiment with a few of the "TLS modes" that PostgreSQL provides. For simplicity, in a separate terminal window, create a Kubernetes port-forward from our host machine to the PostgreSQL cluster:

kubectl -n pgo port-forward svc/hippo 5432:5432

For the purposes of this exercise, we'll log in as the postgres superuser. We'll use a Kubernetes trick to dynamically grab the password from the Secret before we log in and populate it into the PGPASSWORD environment variable. For our hippo cluster, it looks similar to this:

PGPASSWORD=$(kubectl -n pgo get secrets hippo-postgres-secret -o jsonpath="{.data.password}" | base64 -d)

First, let's look at sslmode disable -- as the name suggests, it means that a PostgreSQL client will only connect to a server without TLS and reject any attempts to connect over TLS. If I attempt to connect to my "TLS only" cluster with this mode, the connection will be refused:

PGSSLMODE=disable PGPASSWORD=$(kubectl -n pgo get secrets hippo-postgres-secret -o jsonpath="{.data.password}" | base64 -d) psql -h localhost -U postgres hippo
psql: error: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "hippo", SSL off

The next sslmode to consider is prefer, which is the default mode that Postgres uses. prefer will first attempt to connect to a PostgreSQL cluster over TLS, but if TLS is unavailable, it will fall back to using an unencrypted connection.

Then, there's sslmode require, in which the client will refuse to connect to a PostgreSQL server unless the connection is over TLS:

PGSSLMODE=require PGPASSWORD=$(kubectl -n pgo get secrets hippo-postgres-secret -o jsonpath="{.data.password}" | base64 -d) psql -h localhost -U postgres hippo
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

While the require sslmode will protect your connections from eavesdropping, it will not protect against potential MITM attacks as it does not perform any identity verification: all it does is check that it can connect to a server over TLS. To protect against MITM, you will need to use the verify-full sslmode (though in some limited cases, verify-ca may be good enough).

Let's first see what it takes to use the verify-ca sslmode. Let's take the connection string above and use verify-ca option for sslmode:

PGSSLMODE=verify-ca PGPASSWORD=$(kubectl -n pgo get secrets hippo-postgres-secret -o jsonpath="{.data.password}" | base64 -d) psql -h localhost -U postgres hippo
psql: error: root certificate file "~/.postgresql/root.crt" does not exist

Either provide the file or change sslmode to disable server certificate verification.

Both verify-ca and verify-full need to be aware of a trusted certificate authority bundle so that they can verify the authenticity of the server. The prompt above suggests that the PostgreSQL client can use a trusted CA bundle by storing it in a particular location off of the $HOME directory, but we can also use the PGSSLROOTCERT environment variable to point at the CA certificate we had generated earlier:

PGSSLMODE=verify-ca PGSSLROOTCERT=ca.crt PGPASSWORD=$(kubectl -n pgo get secrets hippo-postgres-secret -o jsonpath="{.data.password}" | base64 -d) psql -h localhost -U postgres hippo
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

verify-full adds additional protection by performing identity verification of the server by checking against the Subject Alternate Names or Common Name of the certificate and ensuring that it matches the host (-h) value. Using the above example, if you were to switch the sslmode to verify-full you would see the following:

PGSSLMODE=verify-full PGSSLROOTCERT=ca.crt PGPASSWORD=$(kubectl -n pgo get secrets hippo-postgres-secret -o jsonpath="{.data.password}" | base64 -d) psql -h localhost -U postgres hippo
psql: error: server certificate for "hippo.pgo" does not match host name "localhost"

Given this is an example using the port-forward strategy above, we would not be able to connect over verify-full unless we add an alias to the hosts file. For example, purposes, add the following to your hosts file:

echo '127.0.0.1 hippo.pgo' | sudo tee -a /etc/hosts

and try connecting again:

PGSSLMODE=verify-full PGSSLROOTCERT=ca.crt PGPASSWORD=$(kubectl -n pgo get secrets hippo-postgres-secret -o jsonpath="{.data.password}" | base64 -d) psql -h localhost -U postgres hippo
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

For production clusters where you want to ensure proper TLS verification, you will want to use sslmode verify-full.

Next Steps

When deploying PostgreSQL clusters to production environments or anywhere where you are operating in an untrusted network, it is paramount that you deploy them with TLS. The PostgreSQL Operator simplifies the process of creating TLS-enabled Postgres clusters, but you will have to decide which level of identity verification you want to provide for your applications connecting to your databases.