This topic describes how to configure the pg_hba file in new or existing VMware Postgres Operator instances.
NoteBy 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 thepg_hba.conf
file should include entries for the created Postgres roles in their ConfigMap.
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.
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.yaml
is 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.
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
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.