Reset Your PostgreSQL Password in Simple Steps

If you cannot remember your PostgreSQL password, you can replace it with the simple steps below:

Step 1: Access Your PostgreSQL Command Line

You need to switch to the 'postgres' user on your machine. How you do this depends on your operating system.

On Linux:
Open a terminal and run:

sudo -i -u postgres

This command switches you to the 'postgres' user.

For Windows:
You might not need to switch users. Just open your command prompt or PowerShell as an admin.

On macOS:
Similar to Linux, open your Terminal and switch to the 'postgres' user if you’ve set a separate user. It might not be necessary depending on how you installed PostgreSQL.

Step 2: Open PostgreSQL Command Line Tool

Once you're in the correct user or the command prompt, access the PostgreSQL command line interface, 'psql', by simply typing:

psql

If you can’t access psql without a password, you’ll need to alter the PostgreSQL instance to allow password-less entry momentarily. This step is detailed in subsequent steps.

Step 3: Update the Method for Localhost Connections

This step involves editing the 'pg_hba.conf' file to allow connections without a password temporarily.

Find your 'pg_hba.conf' file. The location depends on your Operating System and how you installed PostgreSQL. The usual locations are '/etc/postgresql/14/main/pg_hba.conf' on Linux, or within your PostgreSQL installation directory eg, 'C:\Program Files\PostgreSQL\12\data' on Windows.
  
Open 'pg_hba.conf' in a text editor like nano with root priviledge.

 Look for lines similar to:

local   all             postgres                                md5
  
local   all             all                                     md5

If you want to reset the password as 'postgres' user change the md5 to trust. Otherwise if you are trying to reset the password for a specific username, change the local all all ending part to trust. You can actually change both to trust if for the purpose of resetting your password but don't forget to change them back once you have changed the necessary password.

Here is an example:

local   all             all                                     trust

This 'trust' method allows connections without a password. 

Save the file and restart your PostgreSQL server. For example, on Linux, you might use:

sudo service postgresql restart 

or:

sudo systemctl restart postgresql 

Step 4: Reset the Password

Now, access 'psql' again and run the following command to reset your password:

ALTER USER postgres PASSWORD 'newPassword';

You can replace 'postgres' with a username if you are resetting postgres password for a specific user.

Replace 'newPassword' with your new password.

Step 5: Restore Original Authentication Method

Don’t forget to revert the changes you made in 'pg_hba.conf'. Switch the 'trust' back to its original setting (e.g., 'md5' or 'peer'), save the file, and restart the PostgreSQL server.

Note for Hosted PostgreSQL Databases:

The above method would work on local machine or self hosting service like VPS. If you're using a hosted PostgreSQL database (like on AWS RDS, Heroku, or another cloud provider), you might have to follow a different process. Such services usually offer a way to reset the password through web interface. Check their documentation for instructions.

Resetting a forgotten password can get you back up and running with your PostgreSQL databases. Remember to keep passwords secure and follow best practices for password management.