POSTGESQL FOR HELM: Getting SQLSTATE 28P01 password authentication failed for user in PostgreSQL

If the PostgreSql password is not invalid in a Kubernetes Pod and we are unable to determine the correct password for PostgreSql for any postgresql pod we can follow these steps to reset back to the needed password.  This will work for both Artifactory and Xray PostgreSql pod.

Diagnosing the Issue

If we are receiving an error in the log that we are unable to connect to the database such as:

server error (FATAL: password authentication failed for user "<user name>" (SQLSTATE 28P01))

then we need to validate the database password.  We can do this by  running the following kubernetes command to get the password from the secret:

kubectl get secret -n <namespace> <postgres secret name> -o jsonpath="{.data.postgresql-password}" | base64 --decode

Once we have the password we can enter the postgresql pod by doing the following:

kubectl exec -it <postgres pod name> -n <namespace> -- bash

Once we are in the pod do the following to verify the password:

psql -h xray-postgresql -U <user_name> -d <database_name>

If we can log in then we need to verify that the correct password was in the secret at the time the pods were starting up.  If the password is invalid and we need to reset please follow the next section.
 

How to reset the PostgreSql password? 

To reset the PostgreSql password follow the steps below:
1. Log into the postgreSql pod using 

kubectl exec -it <postgres pod name> -n <namespace> -- bash

2. Update the pg_hba.conf file with the following command so we can log into without a password

echo "local    all             all                                     trust" >/opt/bitnami/postgresql/conf/pg_hba.conf

 
3. Run the following command to reload the pg_hba.conf file

/opt/bitnami/postgresql/bin/pg_ctl reload

4. Log into psql as postgres user and then update the user password to the password currently in the secret(Note: please name sure this is the name password being passed in postgresql.postgresqlPassword parameter in the values.yaml)

psql -U postgres
ALTER USER user_name WITH PASSWORD 'new_password;
\q

5. Exit the pods and then do a helm upgrade –install to redeploy the pods.  Please make sure that both the application and the postgres pods are restarted.

Once this is complete we should have the correct password in both the secret and PostgreSql database and we should no longer be receiving an invalid password error.