PostgreSQL Password forgot

[jfavazza]

How can I recover my password for PostgreSQL  12.5?

[Scotchy · Accepted solution]

G'day, Try this , let me know if it worked for you ?

Steps to Recover PostgreSQL Password

1. Access the PostgreSQL Configuration File:

   • Locate the pg_hba.conf file in your PostgreSQL installation directory. This file controls the client authentication and is usually found in the data directory.
   • Typical path on a Unix/Linux system: /var/lib/pgsql/data/pg_hba.conf
   • Typical path on Windows: C:\Program Files\PostgreSQL\16\data\pg_hba.conf

2. Edit the pg_hba.conf File:

   • Open the pg_hba.conf file in a text editor with administrative privileges.
   • Find the lines that look like this: host all all 127.0.0.1/32 md5 or host all all ::1/128 md5.
   • Temporarily change the md5 to trust. This will allow you to log in without a password.
   • Example change: host all all 127.0.0.1/32 trust

3. Reload the PostgreSQL Service:

   • You'll need to reload the PostgreSQL service to apply these changes.
   • On Unix/Linux, you can typically do this with a command like sudo systemctl reload postgresql.
   • On Windows, restart the PostgreSQL service through the Services management console.

4. Reset the Password:

   • Now, log in to the PostgreSQL command line. You can do this by running psql -U username where username is your PostgreSQL username.
   • Once logged in, run the following SQL command to reset your password: ALTER USER username PASSWORD 'newpassword'; (replace username and newpassword with your actual username and the new password you wish to set).

5. Restore Original Configuration:

   • After resetting your password, don't forget to revert the changes made in the pg_hba.conf file.
   • Change trust back to md5 or its original setting.
   • Reload the PostgreSQL service again as you did in step 3.

6. Test the New Password:

   • Finally, test the new password by logging into PostgreSQL with the new credentials.

Important Notes:

• Ensure that you have the necessary administrative permissions to make these changes.
• Be cautious when editing the pg_hba.conf file, as incorrect settings can affect database security.
• Always back up the pg_hba.conf file before making any changes.