In this article, we will look into the step-by-step process of resetting the Postgres user password in case the user forgets it.
PostgreSQL uses the has a pg_hba.conf configuration file stored in the database data directory (e.g., C:\Program Files\PostgreSQL\12\data on Windows) and is used to handle user authentication. The hba in pg_hba.conf means host-based authentication.
As resetting password requires modification in the pg_hba.conf file, you will need to login to the Postgres role without any password.
Follow the below steps to reset a password for the postgres user:
- Step 1: Create a backup of thepg_hba.conf file by copying it to a different location or just rename it to pg_hba.conf.bk
- Step 2: Now change the pg_dba.conf file by making all local connections from md5 to trust.This will help you to log in to the PostgreSQL database server without using a password.
# TYPE DATABASE USER ADDRESS METHOD # 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. host replication all 127.0.0.1/32 trust host replication all ::1/128 trust
- Step 3: Now restart the PostgreSQL server. On a Windows machine, you can restart the PostgreSQL from Services.
Or use thebelow command from the window terminal:
pg_ctl -D "C:\Program Files\PostgreSQL\12\data" restart
The “C:\Program Files\PostgreSQL\12\data” is the data directory.
- Step 4: Finally connect to PostgreSQL database server using any tool such as psql or pgAdmin:
psql -U postgres
At this stage you will not be asked for any authentication.
- Step 5:Use the below command to set a new password for the postgres user.
ALTER USER postgres WITH PASSWORD 'new_password';
This will change the users password as shown below:
- Step 6:Now restore the pg_dba.conf file and restart the PostgreSQL database server., At this stage you can connect to the PostgreSQL database server with the new password.
Follow the above steps to successfully reset the Postgres password and do not forget to restore the pg_dba.conf file after the reset to successfully store the credentials for future verification.