If you have forgotten the PostgreSQL master password you can use these steps to reset the master password.
This is also useful for fixing a problem with the following symptoms:
- The error message is "Unable to connect to the server. HTTP error 500: Internal server error."
- The Spire desktop app loads the Company list, but the Licensed To says *unlicensed* and the company list comes up with !Unknown Error where the company names should be.
Important: Please try Solution A first. If it doesn't work try Solution B. If you are still having problems please contact Spire Support.
Steps to change the Postgres master password:
1. Make a backup copy of your pg_hba.conf file. The default location is C:\ProgramData\PostgreSQL\9.3\data\pg_hba.conf
2. Open the original pg_hba.conf file using a text editor. You'll have to run the text editor with administrator rights in order to save changes to the file. For example, search for notepad in the Windows start menu, right-click it and select "run as administrator".
3. Add these 2 lines to the top of the file and save it. Please make sure the file name and extension don't change.
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
4. If you've been saving the pgAdmin password, please browse to the folder C:\Users\your_user_account\AppData\Roaming\postgresql and delete the file pgpass.conf.
5. Launch PGAdmin. Double click on the Server to open it. Leave password blank, make sure Store password is unchecked and click OK.
6. Go to File/Change Password. Leave ‘Current Password’ blank. Enter new password twice. Click OK.
7. Open the original pg_hba.conf file using a text editor that was started with administrator rights.
8. Remove the 2 lines at the top of the pg_hba.conf file and save the file. Please make sure the file name and extension don't change.
Note: If you cannot save changes to the file even if you run the text editor as administrator, you can copy the original file to a secondary backup file and delete the original file. Then, edit the new copy of the file, make the changes, and use the save-as feature to save it back to the original filename.
1. If your original pg_hba.conf file has been modified due to attempts at using Solution A, please revert to your original file.
2. Click the Windows Start button, search for pgAdmin, right-click pgAdmin and select "run as administrator".
3. Double-click (open) the server entry.
4. Expand the "Login Roles" section.
5. Right-click "postgres" and select "Properties". Note that if "postgres" doesn't exist, there is probably another master role, such as "pgadmin" or "pgsql".
6. Go to the "Definition" tab and enter your new password twice, then click OK:
7. Exit pgAdmin and then run it again.
8. Open the server node - you will most likely get a fatal password error (just dismiss it).
9. Open the server node again. Enter your new password (you can check the "Store password" option for future easy usability, but you can also leave it unchecked if you have safety concerns).
10. Click OK to verify that the new password is accepted (you can exit pgAdmin).
11. Run the "Services" app.
12. Right-click the "postgresql-x64-9.3 - PostgreSQL Server 9.3" (or similar) entry and select "Restart".
13. The service should be running properly without any errors.
If you are still having problems please contact Spire Support for assistance.