September 18, 2023
Summary: in this tutorial, we will show you step by step how to reset the password of the postgres
user in PostgreSQL.
For some reason, after installing PostgreSQL, you may forget the password of the postgres
user. In this case, you need to know how to reset the password to access to the PostgreSQL server.
PostgreSQL uses the pg_hba.conf
configuration file stored in the database data directory (e.g., C:\Program Files\PostgreSQL\12\data
on Windows) to control the client authentication. The hba
in pg_hba.conf
means host-based authentication.
To reset the password for the postgres
user, you need to modify some parameters in this configuration file, login as postgres
without a password, and reset the password.
The following steps show you how to reset a password for the postgres
user:
Step 1. Backup the pg_hba.conf
file by copying it to a different location or just rename it to pg_hba.conf.bk
Step 2. Edit the pg_hba.conf
file and change all local connections from md5
(or scram-sha-256
in a newer version) to trust
. By doing this, you can 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. Restart the PostgreSQL server. If you are on Windows, you can restart the PostgreSQL from Services:
Or run the following 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. Connect to PostgreSQL database server using any tool such as psql or pgAdmin:
psql -U postgres
PostgreSQL will not require a password to login.
Step 5. Execute the following command to set a new password for the postgres
user.
postgres=# ALTER USER postgres WITH PASSWORD 'new_password';
Step 6. Restore the pg_hba.conf
file, restart the PostgreSQL database server and connect to the PostgreSQL database server with the new password.
In this tutorial, you have learned how to reset the password of the postgres
user.