DSM 2.1.3: Postgres Host-Based Authentication (pg_hba.conf) Configuration API

We have recently released a new update to Data Services Manager (DSM), bringing the latest version to 2.1.3. In the release notes, you will find reference to a new customer-requested feature, namely feature called Postgres Host-Based Authentication Configuration API. In a nutshell, this features enables users to make updates to the pg_hba.conf file via the gateway API available in DSM. This file essentially controls who can access a particular database, and from which network. Definitely a useful feature, and so I wanted to try it out and provide the steps on how to use this new hbaRef API. I created a simple stand-alone (single node) PostgreSQL database running version 16.4+vmware.v2.1.1. This database ships with a default set of pg_hba.conf rules. My objective was to add a new rule to permit all users whose usernames end with @local to authenticate using their database passwords. Here is how to do that.

Step 1: Retrieve the gateway API server kubeconfig

This is quite simple to do. Login to the DSM UI as the user that owns the database and download the kubeconfig from the user dropdown menu as shown here.

With this gateway kubeconfig downloaded, and the kubectl tool available on your desktop, you are ready to interact with the DSM gateway API server.

Step 2: Create secret containing new pg_hba.conf entries

There are a few sub-steps to this. First, you must create a list of the rules that you wish to add to the pg_hba.conf file. Second, base64 encode those rules. Third, create a secret YAML manifest which includes the bas64 encoded rules. Here is how I did it.

% ls
hba_test_secret.yaml rules

% cat rules
# added by cormac
host "all" "/^(.*)@local$" 0.0.0.0/0 scram-sha-256

% cat rules| base64                                                                     
ICMgYWRkZWQgYnkgY29ybWFjCmhvc3QgImFsbCIgIi9eKC4qKUBsb2NhbCQiIDAuMC4wLjAvMCBzY3JhbS1zaGEtMjU2Cg==

% cat hba_test_secret.yaml                                                                  
apiVersion: v1
kind: Secret 
metadata: 
  name: haref-test-custom-pg-hba
  namespace: default
data: 
  pg_hba.conf: |-
    ICMgYWRkZWQgYnkgY29ybWFjCmhvc3QgImFsbCIgIi9eKC4qKUBsb2NhbCQiIDAuMC4wLjAvMCBzY3JhbS1zaGEtMjU2Cg==
immutable: true

Note the indentations within the secret YAML manifest, especially the data section that contains the pg_hba.conf key and the encoded rules. Also note that the secret is immutable. It cannot be changed. The secret can only be deleted and recreated. Thus the manifest needs to include an immutable: true entry.

Update: I have been informed that an alternative way of including the pg_hba.conf information using stringData. Some folks may find this approach easier:

% cat hba_test_secret.yaml                                                                  
apiVersion: v1 
kind: Secret  
metadata:
  name: haref-test-custom-pg-hba   
  namespace: default
stringData:
  pg_hba.conf: |
    host all /^(.*)@local$ all scram-sha-256
immutable: true

Step 3: Create secret, update the database

With the YAML manifest created correctly, it is now time to create the secret. Note that the secret must be in the same namespace as the database, which is the default namespace (as per the metadata in the secret).

% kubectl apply -f hba_test_secret.yaml \
--kubeconfig ~/Downloads/kubeconfig-gateway.yaml
secret/haref-test-custom-pg-hba created

With the secret successfully create, we must now update the database in question to point to the new secret with the additional pg_hba.conf details. Before we do that, let’s first use the following table pg_hba_file_rules view in PostgreSQL to check the current state of the database’s pg_hba.conf:

Click on the image to enlarge it in a new tab. You will see that there are currently 17 rows in the database’s pg_hba.conf. Let’s now edit the database configuration using kubectl and add the new spec.hbaRef to its configuration, highlighted in blue below. The name refers to the secret which we just created.

% kubectl edit postgresclusters hbaref-test \
--kubeconfig ~/Downloads/kubeconfig-gateway.yaml
.
.
spec:
  adminPasswordRef:
    name: hbaref-test-secret-vye11
  adminUsername: pgadmin
  databaseName: hbaref-test
  hbaRef:
    name: haref-test-custom-pg-hba
.
.

Save the configuration and you will notice that the database changes status while it incorporates the new changes to the pg_hba.conf. This only takes a matter of seconds, and the new pg_hba.conf with the additional entry (or entries) from the secret should now be in place. We can verify this once more using the table view used earlier. This time we see a new entry, reflecting the rule that we added via the secret.

This achieves the objective of adding a new rule to the pg_hba.conf for this database via the DSM gateway API, permitting all users whose usernames end with @local to authenticate using their database passwords. Pretty cool!

Thanks for reading this far. While you are here, why don’t you check out some of our Data Services Manager sessions at VMware Explore in Barcelona next month. You can find out the details by clicking here.