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';
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
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 ...How to Connect External Postgres
Install a External Postgres such that the version of that postgres is currently in the series of inbuilt postgres of our Application. Say for example, In Servicedeskplus Application version 14010, which has postgres version 10.21, then you should ...How to reset administrator password in ServiceDesk Plus.
1. Access your ServiceDesk Plus server and browse to [your drive]:\ManageEngine\ServiceDesk\bin. 2. Click changeDBServer.bat. Information on the configured database will be displayed. If the database is MSSQL, go to the SQL Management Studio, go to ...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, |, ...su: cannot open session: Module is unknown
The upgrade issue arises in several linux editions such as RHEL and CentOS when the bundled postgres database needs to be upgraded in that particular patch and the upgrade is triggered by the root user. The highlighted line in the traces below (su: ...