How to Resolve Password Authentication Failed Errors in PostgreSQL®

Overview

If your server returns a Password authentication failed for user error when you try to access MySQL® resources, the most likely culprit is an invalid or missing PostgreSQL® user password.

Solution

Check the /root/.pgpass password file to confirm that the password exists in ::*:postgres:PASSWORD format.

If the password does not exist, you must edit the /var/lib/pgsql/data/pg_hba.conf file directly. To do this:

1. Stop Tailwatch. To do this, run the following commands:

   1 2	touch /etc/tailwatchddisable killall tailwatchd

2. Edit the /var/lib/pgsql/data/pg_hba.conf file and change the md5 value to the trust value.

3. Restart PostgreSQL. To do this, run the /usr/local/cpanel/scripts/restartsrv_postgresql command.

4. Change the PostgreSQL password. To do this, run the following command, where new_pass represents the new password:

   postgres=# alter user postgres with encrypted password =new_pass postgres=# q

5. Edit the /var/lib/pgsql/data/pg_hba.conf file and change the trust value to the md5 value.

6. Remove the Tailwatch touch file and restart the service. To do this, run the following commands:

   1 2	rm /etc/tailwatchddisable /usr/local/cpanel/scripts/restartsrv_tailwatchd

Previously, the system stored passwords in the /var/lib/pgsql/.pgpass file.

If the /root/.pgpass file does not exist, the system copies the .pgpass file to the /root/.pgpass file.