How to modify pg_hba.conf on Tanzu SQL with Postgres for Kubernetes 1.x

Products

• VMware Tanzu SQL

Issue/Introduction

This article covers how to add, modify, or remove a line in pg_hba.conf config file when using Postgres Operator (Tanzu SQL with Postgres for Kubernetes 1.x).

[postgres@postgres-operator ~]$ kubectl get all
NAME                                     READY   STATUS    RESTARTS   AGE
pod/postgres-operator-75c5f455bf-w8cs2   1/1     Running   0          37d
pod/postgres-sample-0                    5/5     Running   0          37d
pod/postgres-sample-1                    5/5     Running   0          37d
pod/postgres-sample-monitor-0            4/4     Running   0          37d

Note: If property highAvailability is set to true, you will have two postgres-sample nodes. This procedure would need to be done to both pods. It is not necessary to modify the monitor pod which in this case is postgres-sample-monitor-0.

Environment

Product Version: 1.1

Resolution

By default, Postgres Operator does not ship with a text editor inside the pods (postgres-sample-0, postgres-sample-1).

To work around this issue, follow the steps below. In this example, assume <pod> means to substitute the name of the pod: postgres-sample-0

1.. Copy the config file to your local computer:

kubectl cp <pod>:pgsql/data/pg_hba.conf pg_hba.conf.copy

Where <pod> is the name of the pod. For example:

kubectl cp postgres-sample-0:pgsql/data/pg_hba.conf pg_hba.conf.copy

2. Edit the pg_hba.conf.copy as necessary.

3. Copy the config file back into the pod:

kubectl cp pg_hba.conf.copy <pod>:pgsql/data/pg_hba.conf

4. Reload Postgres to read the updated config file:

kubectl exec <pod> -- pg_ctl reload

5. Check database logs for errors from the reloaded configuration.

kubectl logs <pod> -c instance-logging

The following is a example of errors in reloading the configuration:

2022-01-21 00:20:10.321 UTC [237] LOG:  invalid connection type "bad-connection-type"
2022-01-21 00:20:10.321 UTC [237] CONTEXT:  line 106 of configuration file "/pgsql/data/pg_hba.conf"
2022-01-21 00:20:10.321 UTC [237] LOG:  pg_hba.conf was not reloaded

Note: There will not be any output for a successful config reload.