How to reset database users' Password in bundled Postgres?
We have built several database users in our product with bundled postgres, including "rouser," a read-only account used to configure the external reporting tool, "sdpadmin," who handles database activities integrated with our product, and "postgres," who serves as superuser. To encourage security and privacy, we are giving our customers the option to change the password for these users.
This article is intended to help the customers to change the password of the database users in their bundled postgres and reset the password of such users, if they forgotten the current password.
Note: If the instructions mentioned in this article are not followed correctly, this could lead to production failure due to its high level of sensitivity. Please be very cautious when following these instructions, and contact our assistance as soon as possible if you run into any problems or any difficulties. Follow the article with the guidance of the support technician if any clarifications are required in the steps mentioned in the article
Scenario 1 :
If you know the current password of database user and you want to change it to new one, please follow the below instructions under this section.
a. Navigate to <Product_Installation_Directory>\bin directory.
b. Shutdown the application by executing "shutdown" script.
b. Execute the below command to change the password.
For Windows server :
- changeDBPassword.bat -U <database_username> -p <current_password> -P <new password>
For Linux Server :
- sh changeDBPassword.sh -U <database_username> -p <current_password> -P <new password>
Now, If the you received the final output as " Password changed successfully ", this means the process of password change is completed and you can start the application and use it.
Scenario 2 :
If you do not know or forgotten the current password of a database user, you can reset the password using this instructions. This instructions include two sections.
1. Alter the password of database user in Postgresql Server
2. Update the altered password into the application.
section 1 : Altering the postgres user password in the database
a. Shutdown the application and database by executing "shutdown" script under <Product_Installation_Directory>\bin.
b. Open the pg_hba.conf file, located at <Product_Installation_Directory>\pgsql\data\ folder and edit the authentication method for the database users to "trust". The default authentication method is md5, but we need to change it to trust. This will allow to connect to PostgreSQL without a password.
Make sure that you have a backup of the pg_hba.conf file before you make any changes.
| Current Version | Should be modified to |
| host all all 127.0.0.1/32 md5 | host all all 127.0.0.1/32 trust |
c. Save the changes to the pg_hba.conf file.
d. Start the PostgreSQL server by invoking "startDB " script under <Product_Installation_Directory>\bin.
e. Connect to psql console as the postgres user with the following command. This time you will not be prompted for a password.
./psql -h 127.0.0.1 -p 65432 -d servicedesk -U postgres
f. Execute the below query to alter the database user password.
ALTER USER <database username> WITH PASSWORD '<new password>';
For example, If you want to reset the "postgres" user password,
- ALTER USER postgres WITH PASSWORD '<new password>';
Additionally for altering "sdpadmin" password,
- ALTER USER sdpadmin WITH PASSWORD '<new password>';
g. Now close the postgres console using the command "\q"
h. Stop the database server by executing " stopDB " script under <<Product_Installation_Directory> \ bin.
i. Revert the changes done in pg_hba.conf. (i-e: change authentication method back to md5).
If you start the server the next time, you need to enter the new password you have configured in the PostgreSQL database.
section 2 : Updating the database user password into Product
As mentioned above we use these users to perform database operations integrated with our product. Hence it is vital to update the password into the product when you changed any of these database users password. However, the way of updating the password into the application differs with respect to the users.
case I : Updating the password of "sdpadmin"
a. Navigate to <Product_Installation_Directory> \ bin.
b. Execute changeDBServer script from this directory
c. Enter the password of sdpadmin in the changeDBServer wizard > password field
case II : Updating the password of "postgres"
a. Navigate to <Product_Installation_Directory> \ bin directory and Start the database by invoking "startDB" script.
b. Navigate yourself to <Product_Installation_Directory> \ conf directory and check the encryption algorithm value specified in the product-config.xml file. The value will be specified as below, inside the xml file, under postgres configuration :
<property name="encryption.algo" value="aes256"/>
Here in this case, it is specified as "aes256". Note down this algorithm value.
c. Navigate to <Product_Installation_Directory> \ pgsql \ bin directory
d. Connect to postgres console as the sdpadmin user with the following command and enter the configured password when prompted
./psql -h 127.0.0.1 -p 65432 -d servicedesk -U sdpadmin
e. Execute the below query, by substituting the algorithm value noted above and the password you have configured for postgres.
- UPDATE DBCredentialsAudit set password=pgp_sym_encrypt('<password configured for postgres user>','SChar@123Mas!er','s2k-mode=1, cipher-algo=<algorithm value>') where username='postgres';
f. Now close the postgres console using the command "\q"
g. Stop the database server by executing "stopDB " script under <Product_Installation_Directory> \ bin.
Now the password of the postgres users are altered and successfully updated in the Application. Now you can start the application and proceed with your production.
New to ADSelfService Plus?
Related Articles
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 ...Remote read only access to database for Postgres customers
Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow ...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 ...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 ...