How to Reset the Password for the Postgres User in PostgreSQL

Step 1: Log in to the Server

Ensure you have access to the server where the PostgreSQL database is hosted.

Step 2: Modify the PostgreSQL Configuration

Edit the PostgreSQL configuration file pg_hba.conf to temporarily allow password-free login. This file is often located in /etc/postgresql/[version]/main/ or /var/lib/postgresql/[version]/data/.

# Example path to edit pg_hba.conf
sudo vim /etc/postgresql/[version]/main/pg_hba.conf
    

Change the authentication method from md5 or scram-sha-256 to trust for local connections:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                trust
host    all             postgres        127.0.0.1/32            trust
    

Step 3: Reload PostgreSQL Configuration

Reload the PostgreSQL service to apply these changes:

sudo systemctl reload postgresql
    

Step 4: Reset the Password

Log in without a password and set a new one:

psql -h localhost -U postgres
    

Once logged in, execute:

ALTER USER postgres WITH PASSWORD 'new_password';
    

Replace 'new_password' with a secure password of your choice.

Step 5: Restore the Original Authentication Method

Revert your changes in the pg_hba.conf file from trust back to md5 or scram-sha-256.

sudo vim /etc/postgresql/[version]/main/pg_hba.conf
    

Step 6: Reload the Configuration Again

Reload the PostgreSQL server to enforce the new password settings:

sudo systemctl reload postgresql
    

After completing these steps, you should be able to log in to the PostgreSQL server with the postgres user using the new password.