Passwords play a very crucial role in our lives. Passwords protect the data and prevent a database from unauthorized access. In database management systems, like PostgreSQL, passwords are considered the primary protection parameter against cybercrime.
Try the new PgManage (Open Source) and get rid of PgAdmin!
While installing Postgres, users specify a superuser password that must be remembered for later use. The superuser password is required every time a user logs into the Postgres server. But what if a Postgres user forgets the password? How to reset the forgotten passwords in Postgres?
Well! Nothing to worry about! This post will present step-by-step instructions on how to reset the forgotten password for the “postgres” user.
How Do I Reset the Password for postgres User?
Postgres utilizes a configuration file named “pg_hba.conf” to address the client authentication. Here, the term “hba” stands for “host-based authentication”. The stated file is placed in the data directory of Postgres, i.e., “C:\Program Files\PostgreSQL\15\data”. To reset a password, you must change the parameters in the “hba.config” file. Changing the configuration parameters will allow a user to log in without a password.
The below-provided steps will guide you on how to reset a password in Postgres.
Step 1: Locate the “pg_hba.config” File
Open the “C” drive > Program Files > PostgreSQL > 15 > and finally the Data directory. In the Data director, scroll down to locate the pg_hba.config file:
Step 2: Open the “pg_hba.config” File
Firstly, copy the stated file into some other location, or rename the file like “pg_hba.conf.bk” to keep the backup of the file. Next, double-click on the selected file to open it:
In the “pg_hba.config” file, replace the local connections with “trust”, as demonstrated in the following snippet:
Resetting the local connections to “trust” will allow you to log into Postgres without providing the superuser password.
Step 3: Restart Postgres
Press “win + S” to open the Windows search bar, type “services”, and click on the “services” app to open it:
In the “Services” window, find the “Postgresql-x64-15”, select the service, and click on the “restart” button to restart a Postgres server:
Step 4: Open Postgres
Now connect to Postgres using SQL Shell or pgAdmin:
The above snippet proves that we are successfully logged in as a “postgres” user.
Step 5: Reset the Password
Now execute the “ALTER USER” or “ALTER ROLE” command with the “PASSWORD” attribute to reset the password for the “postgres” user:
ALTER USER postgres WITH PASSWORD 'my_modified_password';
The output proves that the password for the “postgres” user has been reset successfully.
Conclusion
To reset a forgotten password for a “postgres” user > open the “pg_hba.config” file located at “C:\Program Files\PostgreSQL\15\data”, and replace the local connections with “trust”. After that, open the Services manager, select the “Postgresql-x64-15” service, and click on the “restart” button to restart the Postgres server. Finally, connect to postgres, and execute the “ALTER USER” command with the “PASSWORD” attribute to reset the password for the “postgres” user. This post presented a detailed guide on resetting the forgotten password for a “postgres” user.
