PostgreSQL: How to reset a PostgreSQL password?

                                           

                                                 How to reset a PostgreSQL password?

                            

1. Before starting the procedure, please close Toolbox Client and Server

2. Go to C:\Program Files\PostgreSQL\9.6\data

3. Make a copy/backup of the file pg_hba.conf

4. Open pg_hba.conf with Notepad

5. Find the multiple lines that start with host near the bottom of the file and place a # sign in the beginning of each row e.g.:

#host   all all 127.0.0.1/32 md5

6. Paste the following two lines at the end of the file:

host all all 127.0.0.1/32 trust host all all ::1/128 trust

7. Save the file and restart the PostgreSQL Windows service (see Figure 2 below).

8. Open the command prompt from the Start menu (write cmd from the Start menu)

a. Execute the following commands (Press Enter after each command):

cd\

cd Program Files cd PostgreSQL cd 9.6

cd bin

b. Execute the following command, making sure you follow case sensitivity (Press Enter

after the command):

psql -U postgres -h localhost

c. Execute the command below (ALTER USER….) making sure to include the semicolon at the end. The text in the quotes is the new password, ‘test’ for this example (Press Enter after the command):

ALTER USER postgres with password 'test';

d. “ALTER ROLE” should be displayed as an indication that the previous line was executed successfully (see Figure 1 below).

9. Return the original pg_hba.conf file, or restore the edits you made in items 5 and 6. Keep in mind for name of the file and its extension.

 

10. Once again, restart the PostgreSQL Windows service (see Figure 2 below)

11. Congrats! Now, you should be able to install the Toolbox database using the new password set (‘test’).

Figure 1. Screenshot of the command prompt with the change of the folder, start of the psql program, and the execution of the ALTER USER command:

Figure 2. To restart the PostgreSQL service go to the Task Manager (press Ctrl+Shift+Esc), Services tab, and from the popup menu select “Restart”.