Reset the password of the PostgreSQL superuser

In PostgreSQL, postgres is the superuser. If you have forgotten the password of postgres, you can reset it by the following steps.

1. Locate the configuration file pg_hba.conf for the PostgreSQL database server.

   On Windows, the configuration files for the PostgreSQL database server are located in the data directory of the PostgreSQL installation directory, for example: C:\Program Files\PostgreSQL\14\data.

   On Linux, the configuration file for the PostgreSQL database server is located at /etc/postgresql/14/main/pg_hba.conf.

2. Back up the configuration file before modifying it so that you can restore it later.

   cp pg_hba.conf pg_hba.conf.bak
   

3. Modifying the configuration file to trust local connections does not require a password. Modify scram-sha-256 or md5 in the configuration file to trust as follows:

   local   all             all                                     peer
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            trust
   # IPv6 local connections:
   host    all             all             ::1/128                 trust
   # Allow replication connections from localhost, by a user with the
   # replication privilege.
   local   replication     all                                     peer
   host    replication     all             127.0.0.1/32            trust
   host    replication     all             ::1/128                 trust

4. Restart the PostgreSQL database server.

   On Windows, you can restart PostgreSQL in the Services List window.

   In Linux, you can restart PostgreSQL with the systemctl restart postgresql command.

5. Log in to the PostgreSQL database server.

   psql -U postgres
   

   You do not need to enter a password.

6. Use the following command to modify the postgres user’s password:

   ALTER USER postgres WITH PASSWORD 'new_password';
   

7. Restore the pg_hba.conf configuration file. Overwrite the pg_hba.conf file with the contents of the pg_hba.conf.bak file.

8. Restart the PostgreSQL database server. When you log in, PostgreSQL should prompt you for a password.

Conclusion

This article explains the detailed steps to reset the password of superuser postgres.