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.