Reset Forgotten PostgreSQL Password

If you have forgotten PostgreSQL passwords like me, this post is going to help you in resetting them. I tested it on windows and it worked , you can try doing the similar process on Linux and Mac as well and it should work.

You will get error like below, when you enter wrong password.

C:\Users\codingissimple>psql
Password for user codingissimple: ********
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "codingissimple"

Now lets try to resolve this problem by resetting the forgotten password.

First stop PostgreSQL service

Stoping PostgreSQL on Linux:

sudo service postgresql stop

Stopping PostgreSQL on macOS:

brew services stop postgresql

Stopping PostgreSQL on windows:

On windows you can you can search for “services” in windows search and open it. Find service related to PostgreSQL, select the postgresql service and on top tool bar you will see an option to stop the service.

Or you can save the time by directly running the command below to stop the PostgreSQL service.

pg_ctl -D "C:\Program Files\PostgreSQL\15\data" stop

Make Changes to pg_hbe.conf file

Go to this folder C:\Program Files\PostgreSQL\<version>\data, where <version> is the version of your PostgreSQL installation. You can check the version using psql --version. Mine came 15.3, file path for me was C:\Program Files\PostgreSQL\15\data. Now, find the file pg_hba.conf inside data folder. You don’t need to know the version to find the file, just try to manually find it. Now, in this file you will find something like below,

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

First create back up of the file somewhere, we need it to restore original file from the backup later. Now replace scram-sha-256 with trust as shown below. This will allow you to log in as user postgres without needing password.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# 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.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

After making above changes, save the pg_hba.conf file and restart the PostgreSQL service:

Starting the PostgreSQL Service

Starting PostgreSQL on Linux:

sudo service postgresql start

Starting PostgreSQL on macOS:

brew services start postgresql

Starting PostgreSQL On windows

pg_ctl -D "C:\Program Files\PostgreSQL\15\data" start

You can also start from services as we discussed steps for stopping previously. You just need to start this time instead of stopping.

Now, open command prompt as administrator. Log into PostgreSQL using following command psql -U postgres. You will be able to login without password

C:\Users\codingissimple>psql -U postgres
psql (15.3)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

To List all users in PostgreSQL

postgres=# SELECT usename FROM pg_user;
 usename
----------
 postgres
(1 row)

Change password for any username

ALTER USER postgres PASSWORD 'new_password';

Here we have changed password for user postgres. You can change password for any other user as well. Just replace postgres with username for which you want to change password for.

Create new user and set password

CREATE ROLE new_username WITH LOGIN PASSWORD 'new_password';

--To create username with superuser ability run below command.
CREATE ROLE new_username WITH LOGIN PASSWORD 'new_password' SUPERUSER;

--To give permission to create database
ALTER ROLE new_username CREATEDB;

--to quit 
\q  

---To test the new user login with new_username account
psql -U new_username -d your_database

--see all the users again after creating new user
SELECT usename FROM pg_user;

Once you reset the password, revert back the changes made to pg_hba.conf ( you can restore from backup file of it) . To not forget it again, save the usernames and password somewhere.