Skip to content
Related Articles

Related Articles

PostgreSQL – Reset Password For Postgres
  • Last Updated : 08 Feb, 2021

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.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :