PostgreSQL – Reset Password For Postgres

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 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 the 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 the pg_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_hba.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 the below 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 the PostgreSQL database server using any tool such as psql or pgAdmin(In pgAdmin, press ok while it prompts you to enter the password without entering anything in the field.):

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 user’s password as shown below:

• Step 6:Now 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_hba.conf file after the reset to successfully store the credentials for future verification.