Configure the pg_hba.conf File

This topic describes how to configure the pg_hba file in new or existing VMware Postgres Operator instances.

Note
By default, the VMware Postgres Operator automatically configures the pg_hba.conf file for the Postgres roles created via the Kubernetes resource. Users who attempt to perform further configuration of the pg_hba.conf file should include entries for the created Postgres roles in their ConfigMap.

Configuring the pg_hba file

Prerequisites

Ensure that you have a running VMware Postgres Operator. For details, refer to Installing a Postgres Operator.
Ensure that you have a running Postgres instance, or familiarity with creating a new Postgres Instance. For details review Deploying a New Postgres Instance. Ensure that you are familiar with your instance's YAML manifest file.

Procedure

In the same namespace as your Postgres instance, create a ConfigMap that contains your desired pg_hba entries. For details refer to Kubernetes ConfigMaps in the Kubernetes documentation. An example ConfigMap is shown below:
```
apiVersion: v1
kind: ConfigMap
metadata:
  name: my-postgresql-hba-configmap
  labels:
    app: postgres
data:     
  pg_hba.custom.conf: |
    host "postgres-sample" "pgappuser" ::0/0 scram-sha-256
```
Apply the ConfigMap to your instance:
```
kubectl apply -f my-postgresql-hba-configmap-file.yaml -n <namespace>
```
where my-postgresql-configmap-file.yaml is an example ConfigMap.

The command output is similar to:
```
configmap/my-postgresql-hba-configmap created
```
Edit your instance's YAML file, and alter the customConfig field to reflect the name of the ConfigMap that you created:
```
......
spec:
  customConfig:
    pghba:
      name: my-postgresql-hba-configmap
......
```
Deploy or redeploy the instance with the new customConfig setting using:
```
kubectl apply -f postgres.yaml -n <namespace>
```
where postgres.yamlis the example name of the Kubernetes manifest created for this instance.

The command output is similar to:
```
postgres.sql.tanzu.vmware.com/postgres-sample created
```
where postgres-sample is the Postgres instance name defined in the YAML file.

Updating pg_hba entries

Prerequisites

Your instance has been configured with a ConfigMap. For details see Configuring the pg_hba file.

Procedure

Get the name of the instance's ConfigMap:

kubectl get postgres postgres-sample -n <namespace> -o jsonpath={.spec.customConfig.pghba.name}

The command output will reflect the name of the ConfigMap being utilized:

my-postgresql-hba-configmap

Make your changes by editing the ConfigMap data file:

kubectl edit configmap my-postgresql-hba-configmap -n <namespace>

apiVersion: v1
kind: ConfigMap
metadata:
  name: my-postgresql-hba-configmap
  labels:
    app: postgres
data:     
  pg_hba.custom.conf: |
    host "postgres-sample" "pgappuser" ::0/0 scram-sha-256
    host "postgres-sample" "pgadmin" ::0/0 scram-sha-256

Apply the changes:

kubectl apply -f my-postgresql-hba-configmap-file.yaml -n <namespace>

The command output is similar to:

configmap/my-postgresql-hba-configmap configured

After Kubernetes updates the volume mount it applies your changes, and the Postgres server is reloaded to include your changes:

kubectl logs -l postgres-instance=postgres-sample,type=data -c reconfigure-instance

2023-04-13T15:54:28.283Z	INFO	start updating pg_hba.conf
2023-04-13T15:54:28.284Z	INFO	finished updating pg_hba.conf
server signaled
2023-04-13T15:54:29.074Z	INFO	reloading postgres configs
2023-04-13T15:54:30.179Z	INFO	postgres config changes have been successfully applied
2023-04-13T15:54:30.179Z	INFO	reloaded based on file changes

Verifying the pg_hba configuration

Run the following command to ensure that your instance has a "Running" status. Use kubectl get to review the field:

kubectl get postgres/postgres-sample

The output is similar to:

NAME              STATUS    BACKUP LOCATION         AGE
postgres-sample   Running   backuplocation-sample   17m

Run the following command to check the conditions of the instance. If the custom configuration has been applied successfully, there should be a condition of type CustomConfigStatus with the Status as true.
```
kubectl describe postgres <instance-name> -n <namespace-name>
```
Alternatively, you can log into the pods using kubectl exec, and run the following command to start the psql tool:
```
kubectl exec -it pod/postgres-sample-1 -- psql
```
The output is similar to:
```
psql (15.2 (VMware Postgres 15.2.0))
Type "help" for help.
```

Run the select * from pg_hba_file_rules; command to verify your changes:

postgres=# select * from pg_hba_file_rules;

The output includes the entries provided in the ConfigMap:

line_number |  type   |     database      |          user_name          |                                  address                                  |                 netmask                 |  auth_method  | options | error
-------------+---------+-------------------+-----------------------------+---------------------------------------------------------------------------+-----------------------------------------+---------------+--
107 | host    | {postgres-sample} | {pgappuser}                 | ::                                                                        | ::                                      | scram-sha-256 | |
108 | host    | {postgres-sample} | {pgadmin}                   | ::                                                                        | ::                                      | scram-sha-256 | |

To troubleshoot any errors or undesired parameter output, refer to Troubleshooting ConfigMap changes.