FATAL : Password Authentication failed for user "postgres"
Log Trace :
- 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 :
- Navigate to <servicedeskplus_application_home>\bin directory.
- Execute the below command.
If you do not know the current password, refer the appendix.
- For Windows server : changeDBPassword.bat -U postgres -p <current_password> -P <default postgres user password>
- 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.
- Check for a table named " DBCredentialsAudit " in the CX end using the query : SELECT * FROM TableDetails where TABLE_NAME like '%DBCredentialsAudit%';
- 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"/>
- 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';
- 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 :
- If the current password for the "postgres" user is not known, you can reset the password with this article.
New to ADSelfService Plus?
Related Articles
How to Connect External Postgres
To install an external PostgreSQL server, ensure the PostgreSQL version matches the inbuilt PostgreSQL version series of the ServiceDesk Plus application. For example: If ServiceDesk Plus version is 14010 with PostgreSQL version 10.21, install an ...Script to reset password and enable local authentication
Purpose : Interactive mode of resetting the password of a user by entering his username. Also, to enable local authentication in the application. This script can be used in builds on or above 9400 version How the script works ? Invoking the script ...Migration failure for Postgres Database with a blank space in it
Trace : \ManageEngine\ServiceDesk\Patch\AdventNet_ManageEngine_ServiceDesk_Plus-14.2.0-SP-1.0.0\SERVICEDESK\PreInstall\pg_migrate\pgsql_old\bin\pg_dumpall.exe", -U, postgres, -p, 65433, -h, 127.0.0.1, -r, -w, |, ...8.1 Reset global admin Password from the database
MYSQL query to reset Administrator's password: Follow the steps as mentioned below which would help you to reset the "admin"/"administrator" password as "admin". 1. Connect to MySQL Server on Port 33356 using the command from the SC+ server ...Script to reset the password of a user by entering his username and enable local authentication without connecting to DB.
Script to reset the password of a user by entering his username. Also, to enable local authentication in the application without connecting to DB. How the script works ? Invoking the script will show two options. 1. Enable local authentication in the ...