IntroductionHow to change postgres account password? In case of forgotten password, hence unable to access the database management GUI. This is my experience when using Windows. It might be the same with other operating system.
The Command Prompt WayFirst let me put a note using command prompt technique. The GUI technique is much simpler and presented later in this same writeup.
To do this using command prompt. It is good to know that in windows there will be 2 account. One is postgres windows user account. The other is database admin account. Which is also called postgres.
- Edit E:\PostgreSQL\9.1\pg_hba.conf and set the localhost method to trust instead of md5.
Do not forget to save.
- Use the usual Windows way to reset the password of windows user account.
- Open up a command prompt and use runas to open another command prompt as postgres user.
- open up Services manager and restart postgresql service. There might be need to update the postgres user account password setting in the service property at this step.
- Now running psql will not ask any password.
- Use the following sql to set the user password
ALTER USER Postgres WITH PASSWORD '<newpassword>'
- Revert the pg_hba.conf localhost method back to md5 .
- Restart postgresql service in Services manager.
Using pgAdmin to Graphically Reset the PasswordThis is simpler and I would say faster. Just accidentally found that if pg_hba.conf localhost method is set to trust. There is no need to enter password when connecting to the database using pgAdmin.
Some of the step are similar to when using command prompt technique.
1. Edit E:\PostgreSQL\9.1\pg_hba.conf and set the localhost method to trust instead of md5. Do not forget to save.
2. Open up Services manager and restart postgresql service.
3. Run pgAdmin and connect to the database. Note that it will not ask the password since we are in the trust mode.
4. Scroll down to look for postgres database user under Login Roles and open up the properties windows.
5. Surprisingly there is a tab named Definition that allow for password change. Do the necessary!
6. After this is done, revert back changes to pg_hba.conf file to md5 and restart the postgresql service in Services manager.
7. You should be able to connect to the database. This time using the new password!
In ClosingI learn the hard way about not writing up this stuff earlier. Wasting time looking for good tutorial on how to do this ;)
| 
