Multifactor SSO Authentication for Postgres on...

Did you know that PostgreSQL 12 introduced a way for you to provide multifactor (aka "two-factor") authentication to your database?

This comes from the ability to set clientcert=verify-full as part of your pg_hba.conf file, which manages how clients can authenticate to PostgreSQL. When you specify clientcert=verify-full, PostgreSQL requires a connecting client to provide a certificate that is valid against its certificate authority (CA) and the certificate's common name (CN) matches the username the client is authenticating as. This is similar to using the cert method of authentication.

Where does the second factor come in? You can add clientcert=verify-full to another authentication method, such as the password-based scram-sha-256. When you do this, your client has to provide both a valid certificate AND password. Cool!

If you have a public key infrastructure (PKI) set up, you effectively have a single-sign on system for your PostgreSQL databases. You can then treat the password for the user in a local database as a "second factor" for logging in. Again, cool!

Let's put this all together, and see how we can deploy a multifactor single sign-on (SSO) authentication system for Postgres on Kubernetes using cert-manager and PGO, the open source Postgres Operator from Crunchy Data!

Prerequisites

In a previous blog post I described how you can use cert-manager to deploy your TLS infrastructure when running Postgres on Kubernetes. cert-manager handles our PKI and acts as our SSO system. Additionally, you can tailor how long you want your certificates to be valid for and how often to renew them, which adds another layer of entropy to your authentication system.

For the sake of this exercise, I will assume that you have already installed cert-manager.

You will also need to ensure that you have created a cert-manager issuer, which handles certificate issuance. There is an example of a cluster-wide certificate issuer in the Postgres Operator Examples repository. Fork this repository (we also will need it to create a Postgres cluster) and run the following command:

kubectl apply -k kustomize/certmanager/certman

With your certificate issuer set up, let's create a user certificate that can be used across multiple Postgres clusters!

Creating a User Certificate

Let's say we have a user in our system called rhino. The first thing we need to do is create a certificate. We can do so with the following YAML:

apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: certmanager-sso-rhino
  namespace: postgres-operator
spec:
  secretName: certmanager-sso-rhino
  duration: 720h # 30d
  renewBefore: 48h # 2d
  subject:
    organizations:
      - hippo-org
  commonName: rhino
  isCA: false
  privateKey:
    algorithm: ECDSA
    size: 256
  usages:
    - digital signature
    - key encipherment
  # At least one of a DNS Name, URI, or IP address is required.
  emailAddresses:
    - rhino@postgres-operator.pgo
  issuerRef:
    name: ca-issuer
    kind: ClusterIssuer
    group: cert-manager.io

Ensure you update the metadata.namespace attribute to reflect the namespace you are working in.

With the user certificate in hand, we can now create a Postgres cluster.

Create a Postgres Cluster

Because we are letting cert-manager manage our PKI, we will borrow from the cert-manager example as described in the setting up TLS for Postgres with cert-manager article.

First, ensure there are two certificates create for both the Postgres cluster's TLS and for the replication user:

---
apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: certmanager-hippo-cluster-tls
spec:
  secretName: certmanager-hippo-cluster-tls
  duration: 2160h # 90d
  renewBefore: 360h # 15d
  subject:
    organizations:
      - hippo-org
  commonName: hippo-primary
  isCA: false
  privateKey:
    algorithm: ECDSA
    size: 256
  usages:
    - digital signature
    - key encipherment
  dnsNames:
    - hippo-primary
    - hippo-primary.postgres-operator
    - hippo-primary.postgres-operator.svc
    - hippo-primary.postgres-operator.svc.cluster.local
  issuerRef:
    name: ca-issuer
    kind: ClusterIssuer
    group: cert-manager.io
---
apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: certmanager-hippo-repl
spec:
  secretName: certmanager-hippo-repl
  duration: 2160h # 90d
  renewBefore: 360h # 15d
  subject:
    organizations:
      - hippo-org
  commonName: _crunchyrepl
  isCA: false
  privateKey:
    algorithm: ECDSA
    size: 256
  usages:
    - digital signature
    - key encipherment
  dnsNames:
    - _crunchyrepl
  issuerRef:
    name: ca-issuer
    kind: ClusterIssuer
    group: cert-manager.io

Next, create the Postgres cluster. The manifest should look like this:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-14.0-0
  postgresVersion: 14
  customReplicationTLSSecret:
    name: certmanager-hippo-repl
  customTLSSecret:
    name: certmanager-hippo-cluster-tls
  instances:
    - replicas: 2
      dataVolumeClaimSpec:
        accessModes:
          - 'ReadWriteOnce'
        resources:
          requests:
            storage: 1Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.35-0
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - 'ReadWriteOnce'
              resources:
                requests:
                  storage: 1Gi
  patroni:
    dynamicConfiguration:
      postgresql:
        pg_hba:
          - hostssl all all all scram-sha-256 clientcert=verify-full
  users:
    - name: rhino
      databases:
        - hippo

Let's note a few things. First, note the user of the cert-manager provided Secrets for the cluster:

spec:
  customReplicationTLSSecret:
    name: certmanager-hippo-repl
  customTLSSecret:
    name: certmanager-hippo-cluster-tls

Next, note the customization that we are adding to the pg_hba.conf file that will allow us to perform multifactor authentication:

spec:
  patroni:
    dynamicConfiguration:
      postgresql:
        pg_hba:
          - hostssl all all all scram-sha-256 clientcert=verify-full

This says that anyone can connect over a TLS connection and authenticate using a SCRAM password as long as the client presents a valid certificate whose common name (CN) matches the Postgres user.

Finally, note we create a user called rhino and give it access to the hippo database:

spec:
  users:
    - name: rhino
      databases:
        - hippo

Alright, we're ready to validate that we can use multifactor authentication against our database!

Validating Multifactor Authentication

Let's conduct a simpler test of this by trying to connect through our database over a port-forward.

First, set up a port-forward from the primary database to your local environment:

PG_CLUSTER_PRIMARY_POD=$(kubectl get pod -n postgres-operator -o name \
  -l postgres-operator.crunchydata.com/cluster=hippo,postgres-operator.crunchydata.com/role=master)
kubectl -n postgres-operator port-forward "${PG_CLUSTER_PRIMARY_POD}" 5432:5432

Next, download the key and certificate for the rhino user, as well as a copy of the certificate authority. These files will need to have strict ownership rules:

kubectl -n postgres-operator get secrets certmanager-sso-rhino -o jsonpath='{.data.ca\.crt}' | base64 -d > ca.crt
kubectl -n postgres-operator get secrets certmanager-sso-rhino -o jsonpath='{.data.tls\.crt}' | base64 -d > rhino.crt
kubectl -n postgres-operator get secrets certmanager-sso-rhino -o jsonpath='{.data.tls\.key}' | base64 -d > rhino.key
chmod u+rw-x,go-rwx *.crt *.key

Finally, get a copy of the password for the rhino Postgres user. You can store this in an environmental variable called PGPASSWRD which is a special variable that the PostgreSQL client uses to connect with a password.

export PGPASSWORD=$(kubectl -n postgres-operator get secrets hippo-pguser-rhino -o jsonpath='{.data.password}' | base64 -d)

Alright, let's try connecting to our database! You can do so with the command below:

PGSSLROOTCERT=ca.crt PGSSLCERT=rhino.crt PGSSLKEY=rhino.key psql -h localhost -U rhino hippo

If you set up your client correctly, you should be logged in!

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

hippo=>

For further proof that we are using multifactor authentication, try varying the password:

PGPASSWORD=invalid PGSSLROOTCERT=ca.crt PGSSLCERT=rhino.crt PGSSLKEY=rhino.key psql -h localhost -U rhino hippo

You should see something like:

psql: error: FATAL:  password authentication failed for user "rhino"
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "rhino", database "hippo", no encryption

Now, use the valid password but use an invalid certificate:

openssl req -x509 -nodes -newkey ec -pkeyopt ec_paramgen_curve:prime256v1 -pkeyopt ec_param_enc:named_curve \
  -sha384 -keyout bad-rhino.key -out bad-rhino.crt -days 3650 -subj "/CN=*"
PGSSLROOTCERT=ca.crt PGSSLCERT=bad-rhino.crt PGSSLKEY=bad-rhino.key psql -h localhost -U rhino hippo

You should get an error like:

psql: error: SSL error: certificate verify failed
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "rhino", database "hippo", no encryption

Awesome, we have successful set up multifactor authentication! And given the certificate credentials for rhino come from our PKI managed by cert-manager, this is a SSO system!

Next Steps

There is even more we can do with this setup in the name of security. We can actually use mutual TLS authentication (mTLS) in this example by setting PGSSLMODE to be verify-full. This allows for the client to also validate the identity of the Postgres server!

Running Postgres on Kubernetes can provide a lot of conveniences for managing lots of databases and users. Combining cert-manager with PGO provides a convenient way to build your own certificate-based SSO system with multifactor authentication while simplifying the management of your Postgres clusters.

(Interested in seeing PGO in action? Join us for a webinar on Wednesday, Nov 17th.)

Latest Articles

Multifactor SSO Authentication for Postgres on Kubernetes