🔐 How to Reset Your Forgotten PostgreSQL Password (Windows Guide)
#postgres#database#databaseadministration#opensource
Abhishek Govind

Abhishek Govind @demader

About: Full-Stack Developer | React & Next.js Enthusiast | Passionate about building user-friendly web apps | Ex-Frontend Intern @ Canyfix | CSE Graduate from IIIT Vadodara

Location:
India
Joined:
Jul 29, 2023

🔐 How to Reset Your Forgotten PostgreSQL Password (Windows Guide)

Publish Date: Jun 23
5 0

🔐 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. ❤️

Comments 0 total

    Add comment