How to reset the password of postgres user in PostgreSQL

  • KM03603064
  • 26-Feb-2020
  • 26-Feb-2020

Summary

How to reset the password of postgres user in PostgreSQL

Question

This KM should be followed when it is required to restart the postgres user password

Answer

PostgreSQL uses the pg_hba.conf configuration file that is stored in the database data directory to control the client authentication. HBA means host-based authentication. To reset the password for the postgres user, you need to modify some parameters in this configuration file. 

- Edit the pg_dba.conf 

- Change the below line: 

host    all              postgres           127.0.0.1/32            password 

to 

host    all              postgres           127.0.0.1/32            trust 

- Restart PostgreSQL DB service 

- Use psql to connect to PostgreSQL DB: psql -U postgres 

- ALTER USER postgres with encrypted password 'new_password'; 

These steps are required when customer doesn’t know any password of probe DB e.g. mamprobe or postgres.  

Then you’ll need to reset postgres / mamprobe password using the above steps.