FATAL : Password Authentication failed for user "postgres"

FATAL : Password Authentication failed for user "postgres"

Log Trace : 
  1. FATAL: password authentication failed for user "postgres"
Analysis : 
      During the major upgrade of Postgres say 10.21(From 13006) to 11.17 (From 14300), the Postgres upgrade fails with the above trace due to password authentication failure for the superuser "postgres". 
      If the customer has configured customized password for the user "postgres",  that customized value will not  be called during migration. since framework team uses a hardcoded <default postgres user password>, which is the reason for the failure. This issue even existed in previous major upgrades too say 10500 (9.x to 10.x). 

Issue ID for your reference: SD-111683

WorkAround : 

METHOD 1 :
  1. Navigate to <servicedeskplus_application_home>\bin directory.
  2. Execute the below command.
If you do not know the current password, refer the appendix.
  1. For Windows server : changeDBPassword.bat -U postgres -p <current_password> -P <default postgres user password>
  2. For Linux Server : sh changeDBPassword.sh -U postgres -p <current_password> -P <default postgres user password> 
      3. Now, proceed with the upgrade.


METHOD 2 :
      1. Connect to the postgres database as sdpadmin.
./psql -h 127.0.0.1 -p <port number> -U sdpadmin -d servicedesk 

      2. Update the default postgres user password in "DBCredentialsAudit" Table.
  1. Check for a table named " DBCredentialsAudit " in the CX end using the query : SELECT * FROM TableDetails where TABLE_NAME like '%DBCredentialsAudit%';
  2. If the value is present navigate yourself to servicedesk\conf directory and check the encryption algorithm in the product-config.xml file. The value will be specified as below, inside the xml file :  <property name="encryption.algo" value="aes256"/>
  3. Substitute the default postgres password and algorithm in the following query 
UPDATE DBCredentialsAudit set password=pgp_sym_encrypt('<default postgres user password>','SChar@123Mas!er','s2k-mode=1, cipher-algo=<ALGO>') where username='postgres';
  1. you can check the password value, by using the below query.

select pgp_sym_decrypt(password,'SChar@123Mas!er', 'cipher-algo=<ALGO>') as password from dbcredentialsaudit ;

        3. Now, exit from sdpadmin console.
        4. Reset the Postgres User password to <default postgres user password> . For that,
                        Case 1 : If you Know the customized superuser password 
                                                >     Login as postgres user in psql console. 
./psql -h 127.0.0.1 -p <port number> -U postgres -d servicedesk 
                                                >      Alter the "postgres" user password with default value.
ALTER USER postgres PASSWORD '<default postgres user password>';

                        Case 2 :  If you do not know the current password, refer appendix.
      5. Proceed with the upgrade.

Appendix :
  1. If the current password for the "postgres" user is not known, you can reset the password with this article.

                    New to ADSelfService Plus?