PostgreSQL Tutorial: Reset Forgotten Password For postgres User

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.