DEV Community

Abhishek Govind
Abhishek Govind

Posted on

๐Ÿ” How to Reset Your Forgotten PostgreSQL Password (Windows Guide)

๐Ÿ” How to Reset Your Forgotten PostgreSQL Password (Windows Guide)

Losing access to your PostgreSQL superuser account can be frustratingโ€”but donโ€™t worry, Iโ€™ve got you covered with this simple and secure step-by-step guide. Whether you're a beginner or someone who just needs a quick refresher, follow these steps to reset the PostgreSQL password without compromising your system.

Image description

๐Ÿงญ Step 1: Locate PostgreSQL Configuration Files

  1. Navigate to:
   C:\Program Files\PostgreSQL\17\data
Enter fullscreen mode Exit fullscreen mode
  1. Look for the file named:
   pg_hba.conf
Enter fullscreen mode Exit fullscreen mode
  1. Important: Before editing, create a backup:
    • Create a new folder (e.g., Backup-Config)
    • Copy pg_hba.conf into this folder for safety.

Image description

๐Ÿ“ Step 2: Modify Authentication Method

  1. Open the original pg_hba.conf file using a text editor (e.g., Notepad).
  2. Scroll to the bottom until you find a table with lines like:
   127.0.0.1/32   ...   md5
   ::1/128        ...   md5
Enter fullscreen mode Exit fullscreen mode

Image description

  1. Change the method column from md5 (or scram-sha-256) to trust for both IPv4 and IPv6 lines:
   127.0.0.1/32   ...   trust
   ::1/128        ...   trust
Enter fullscreen mode Exit fullscreen mode

Image description

  1. Save the file.

๐Ÿ”’ Note: This change disables password checking temporarily, so use it only as a temporary fix.


๐Ÿ” Step 3: Restart PostgreSQL Services

  1. Press Win + R and type:
   services.msc
Enter fullscreen mode Exit fullscreen mode
  1. In the Services window, find:
   postgresql-x64-17 (or similar)
Enter fullscreen mode Exit fullscreen mode

Image description

  1. Right-click and select Restart.

Image description

๐Ÿ’ป Step 4: Log in Without Password

  1. Open psql or SQL Shell.
  2. Press Enter for all prompts (host, port, username, database, etc.).
  3. You will now be logged in without needing a password.

Image description

๐Ÿ”‘ Step 5: Set a New Password

Once inside the psql terminal, run:

ALTER USER postgres WITH PASSWORD 'your_new_password';
Enter fullscreen mode Exit fullscreen mode

โœ… You should see:

ALTER ROLE
Enter fullscreen mode Exit fullscreen mode

Then exit the shell:

\q
Enter fullscreen mode Exit fullscreen mode

๐Ÿ›ก Step 6: Restore Secure Configuration

  1. Go back to your backup folder and copy the original pg_hba.conf file.
  2. Replace the modified file in:
   C:\Program Files\PostgreSQL\17\data
Enter fullscreen mode Exit fullscreen mode

Image description

๐Ÿ” Step 7: Restart PostgreSQL Again

To re-enable secure authentication:

  1. Go back to services.msc
  2. Restart the PostgreSQL service one more time.

๐ŸŽ‰ You're Done!

Youโ€™ve successfully reset your PostgreSQL password securely and restored your database to a safe configuration. Remember to store your credentials in a secure password manager to avoid repeating this process.


Image description

๐Ÿง  Why This Method Works

By setting the authentication method to trust, PostgreSQL allows local connections without requiring a password. This gives you access to reset the superuser password. But once done, always revert to md5 or scram-sha-256 to maintain database security.


๐Ÿงพ Final Tips

  • ๐Ÿ’พ Always back up config files before making changes.
  • ๐Ÿ” Never leave PostgreSQL in trust mode longer than necessary.
  • ๐Ÿ” Use strong, unique passwords for database accounts.

๐Ÿ’ฌ Got stuck somewhere? Drop a comment or connect with meโ€”Iโ€™d love to help!

If you found this helpful, consider sharing it to help others too. โค๏ธ

Top comments (0)