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
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 ...How to reset Admin password (SDOrg) for SupportCenter Plus 11.0 version and above
This is applicable only above SupportCenter Plus 11.0 version. If you are using any other build , please contact support with the details. You can get the build number from About us present in the top right of the user interface. 1. Access your ...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 ...Default Postgres Database password from Builds 14620 and above
For users in ServiceDesk Plus MSP build 14620 and above , the default PostgreSQL database password will now be auto-generated for sdpadmin. For Builds 14620 and above: For security reasons, the default Postgres database password will now be randomly ...How to reset administrator password in ServiceDesk Plus (or Asset Explorer).
1. Access your application server and browse to [your drive]:\ManageEngine\<application_name>\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 ...