I forgot the password I entered during PostgreSQL installation

Asked 1/6, 2012 at 7:14 Answered 7/2 at 2:24

Solved postgresql postgresql-9.1 forgot-password

296

I either forgot or mistyped (during the installation) the password to the default user of PostgreSQL. I can't seem to be able to run it, and I get the following error:

psql: FATAL:  password authentication failed for user "hisham"
hisham-agil: hisham$ psql

Is there a way to reset the password or how do I create a new user with superuser privileges?

I am new to PostgreSQL and just installed it for the first time. I am trying to use it with Ruby on Rails and I am running Mac OS X v10.7 (Lion).

Blinker answered 1/6, 2012 at 7:14 Comment(1)

I cannot comment on answers yet, so have to do it this way. I did what SaiyanGirl said, however I still needed to enter a password, which was 'postgres' to log in, then I could reset the password – Archon 10/12, 2016 at 8:28

462

Find the file pg_hba.conf. It may be located, for example, in /etc/postgresql-9.1/pg_hba.conf.

cd /etc/postgresql-9.1/
Back it up

cp pg_hba.conf pg_hba.conf-backup
Place the following line (as either the first uncommented line, or as the only one):

For all occurrence of below (local and host) , except replication section if you don't have any it has to be changed as follow ,no MD5 or Peer authentication should be present.

local all all trust
Restart your PostgreSQL server (e.g., on Linux:)

sudo /etc/init.d/postgresql restart

If the service (daemon) doesn't start reporting in log file:

local connections are not supported by this build

you should change

local all all trust

to

host all all 127.0.0.1/32 trust
You can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)

psql -U postgres

or

psql -h 127.0.0.1 -U postgres

(note that with the first command you will not always be connected with local host)
Reset the password ('replace my_user_name with postgres since you are resetting the postgres user)

ALTER USER my_user_name with password 'my_secure_password';
Restore the old pg_hba.conf file as it is very dangerous to keep around

cp pg_hba.conf-backup pg_hba.conf
Restart the server, in order to run with the safe pg_hba.conf file

sudo /etc/init.d/postgresql restart

Further reading about that pg_hba file: 19.1. The pg_hba.conf File (official documentation)

Hailstone answered 1/6, 2012 at 7:42 Comment(20)

That's pretty much it. If possible, do this at the console and disconnect your machine from the network while you do this to avoid someone hacking it while you work. – Todtoday 1/6, 2012 at 11:47

And then start psql? I'm still getting prompted for a password, and I do not know it – Pitiful 24/8, 2014 at 23:1

Worked great thanks! Note: Keep pgAdmin closed while following these steps. – Multicellular 19/7, 2016 at 6:35

this answer did NOT work for me, after I do step 3, the service won´t start, I don´t know, maybe it´s the OS(W8), it just won´t. – Prophet 12/9, 2016 at 1:16

anyone for Windows? – Onestep 14/11, 2016 at 8:2

@Mahesha999: it's the same for Windows. Except that you need net stop ... and net start ... or the Control Panel to stop and start the service (and you can not use local connections in Windows) – Caplan 10/12, 2016 at 9:49

this command is not working for me local all all trust – Poach 8/3, 2017 at 16:5

Keep in mind order of entries in pg_hba.conf is important. If you add "local all all trust" to the end of the file, it'll not work as you expect, as previous records will be matched first. So put that at the top of the file to have what you expect. – Tatiania 14/3, 2017 at 5:46

If you're on windows with this error, keep scrolling to @SaiyanGirl 's answer. Just modfiy the METHOD columns of the existing entries to 'trust', then change it back when you're done – Reinertson 28/3, 2017 at 19:2

psql -U postgres will simply log in if not then you can use password that you have provided at installation time. – Peloria 21/6, 2017 at 16:39

I also had to add local all postgres trust to /etc/postgresql/9.5/mainpg_hba.conf. – Gangrel 25/9, 2017 at 8:56

You should comment all other lines or replace all md5 with trust. – Limitation 20/3, 2018 at 9:59

This failed for me until I finally set the IPv6 from md5 to trust. I had thought it was ignoring that and only using IPv4. Apparently not. Perhaps removing the IPv6 line entirely would also have worked. – Riccio 29/4, 2019 at 17:32

I found the pg_hba.conf file in /Library/PostgreSQL/11/data (MacOS v.10 and postgress v.11) – Solarium 9/7, 2019 at 17:23

I found that the easiest way to find the file for mac is find / -name 'pg_hba.conf' 2>/dev/null. – Erring 28/7, 2019 at 2:50

I'm being asked for postgres password at step 2, same password ive forgotten, how do I go about it now? – Baywood 29/4, 2020 at 17:42

LOL, I've just spent an extra 30 minutes on this because I forgot to add a semicolon to the end of the SQL query – Oestrin 12/8, 2021 at 9:32

Just instead of md5 need set scram-sha-256 - for Postgres 14 / Ubuntu 22 – Confined 8/9, 2022 at 20:19

@Pitiful and anyone who is still prompted to enter password, I have solved it just by closing putty (I am accessing ubuntu using putty) and reconnecting. – Postmaster 25/12, 2022 at 9:37

In my case it worked with ::1/128 instead of 127.0.0.1/32 – Barbra 22/4 at 15:41

157

When connecting to PostgreSQL from the command line, don't forget to add -h localhost as a command line parameter. If not, PostgreSQL will try to connect using PEER authentication mode.

The below shows a reset of the password, a failed login with PEER authentication and a successful login using a TCP connection.

# sudo -u postgres psql

could not change directory to "/root"
psql (9.1.11)
Type "help" for help.

postgres=# \password
Enter new password:
Enter it again:
postgres=# \q

Failing:

# psql -U postgres -W

Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"

Working with -h localhost:

# psql -U postgres -W  -h localhost

Password for user postgres:
psql (9.1.11)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#

Veratridine answered 2/2, 2014 at 10:21 Comment(1)

Unneeded on Windows cmd.exe – Lecialecithin 12/7, 2018 at 6:13

The pg_hba.conf (C:\Program Files\PostgreSQL\9.3\data) file has changed since these answers were given. What worked for me, in Windows, was to open the file and change the METHOD from md5 to trust:

# 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

Then, using pgAdmin III, I logged in without using a password and changed user postgres's password by going to menu File → Change Password.

Diorama answered 19/9, 2014 at 22:26 Comment(6)

This doesn't work because PgAdmin still asks for the current password... I reset to 'trust' and restarted PgAdmin. Still can't reset without typing in the CURRENT password... – Niacin 15/11, 2017 at 15:11

Once you change the method to trust you can follow this and alter password in cmd – Septuagenarian 17/2, 2018 at 8:38

Interesting note: Running PostgreSQL Server 13 on Windows, even after making the aforementioned changes, the server will still prompt for a password when trying to connect (when using pgAdmin). You can just hit "OK" (w/o specifying a password) and the server will let you in. I did not anticipate being prompted. – Thump 1/9, 2020 at 13:57

Just to add to the above interesting note, I had to click "Cancel"; clicking "Okay" just got another demand for a password. And whenever I tried to actually do anything, I got a further demand, so it was not much help. – Churchy 22/6, 2021 at 8:49

In my case the file was in C:\Users\user\Documents\PostgreSQL\13\data. – Octachord 24/3, 2022 at 0:21

Thanks this worked for me, windows 11 user here. I logged into PgAdmin and selected the database – Voltmer 22/4, 2022 at 0:15

For Windows (what has helped me):

This is the document I am referring to: How can I reset a PostgreSQL password?

Open your cmd and go to C:\Program Files\PostgreSQL\12\data. This is usually the right path. You might have it stored somewhere else. Note that, if you have a different PostgreSQL version, there will be a different number. That doesn't matter.
Find a pg_hba.conf file and copy it to somewhere else (that way you will have an unmodified version of this file, so you will be able to look at it after we make some changes)
Open the pg_hba.conf file (not the backup, but the original)

Find the multiple lines that start with host near the bottom of the file:

host    all             all             127.0.0.1/32            md5

host    all             all             ::1/128                 md5

host    replication     all             127.0.0.1/32            md5

host    replication     all             ::1/128                 md5

Replace md5 with trust:

host    all             all             127.0.0.1/32            trust

host    all             all             ::1/128                 trust

host    replication     all             127.0.0.1/32            trust

host    replication     all             ::1/128                 trust

Close this file
Go to your search bar on windows and open Services app. Find postgres and restart it.

Picture of services app
Write cd.. in cmd and then cd bin. Your path should be C:\Program Files\PostgreSQL\12\bin
Enter: psql -U postgres -h localhost
Enter: ALTER USER postgres with password '<your new password>';Make sure that you include ; at the end “ALTER ROLE” should be displayed as an indication that the previous line was executed successfully
Open original pg_hba.conf file and change back from trust to md5
Restart the server with Services app as before

Sooksoon answered 15/11, 2020 at 22:14 Comment(1)

psql -U postgres -h localhost -> .\psql.exe -U postgres -h localhost – Forget 15/2, 2022 at 4:30

Edit the file /etc/postgresql/<version>/main/pg_hba.conf and find the following line:
```
local  all  postgres  md5
```
Edit the line and change md5 at the end to trust and save the file
Reload the postgresql service
```
sudo service postgresql reload
```
This will load the configuration files. Now you can modify the postgres user by logging into the psql shell
```
psql -U postgres
```

Update the postgres user's password

alter user postgres with password 'secure-passwd-here';

Edit the file /etc/postgresql/<version>/main/pg_hba.conf and change trust back to md5 and save the file
Reload the postgresql service
```
sudo service postgresql reload
```
Verify that the password change is working
```
psql -U postgres -W
```

Earmuff answered 17/8, 2017 at 2:55 Comment(2)

Is it so hard to give a Windows solution? Not everyone uses Linux with sudo. How would I do it on Windows?? – Niacin 15/11, 2017 at 15:9

Just instead of md5 need set scram-sha-256 - for Postgres 14 / Ubuntu 22 – Confined 8/9, 2022 at 20:18

I was just having this problem on Windows 10 and the issue in my case was that I was just running psql and it was defaulting to trying to log in with my Windows username ("Nathan"), but there was no PostgreSQL user with that name, and it wasn't telling me that.

So the solution was to run psql -U postgres rather than just psql, and then the password I entered at installation worked.

Ostyak answered 26/6, 2019 at 21:29 Comment(0)

Just a note: On Linux, you can simply run sudo su - postgres to become the postgres user and from there change what is required using psql.

Darlenedarline answered 12/3, 2018 at 12:58 Comment(1)

you still need the password after running psql – Xenolith 27/8, 2019 at 13:12

For a Windows user for the latest PostgreSQL version (greater than 10):

Go to your PostgreSQL installation location, and search for pg_hba.conf, you will find it in ..\postgres\data\pg_hba.conf.

Open that file with Notepad, and find this line:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
#..

Change the method from *md5* to *trust*:

# 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
# ...

Now go to your SQL shell (PSQL) and leave everything blank,

Server [localhost]:
Database [postgres]:
Port [8000]:
Username [postgres]:

It will not ask for a password this time, and you will be logged in,

Now run this line:

  `ALTER USER yourusername WITH SUPERUSER`

Now you can leave the shell with \q.

Again, go to the file pg_hba.conf and change METHOD from trust to md5 again, and save it.

Now log in with your new user and password, and you can check \du for its attributes.

Lacrosse answered 3/2, 2019 at 13:40 Comment(0)

If you are running PostgreSQL on macOS, try these:

1. Edit the pg_hba.conf file

sudo vi /Library/PostgreSQL/9.2/data/pg_hba.conf and Change the "md5" method for all users to "trust" near the bottom of the file

2. Find the name of the postgres service

ls /Library/LaunchDaemons Look for postgresql

3. Restart the postgresql service

sudo launchctl stop com.edb.launchd.postgresql-9.2

sudo launchctl start com.edb.launchd.postgresql-9.2 (com.edb.launchd.postgresql-9.2 should be name postgresql service from step 3)

4. Start a psql session as postgres psql -U postgres (shouldn't ask for password because of 'trust' setting)

5. Reset password in the psql session by typing:

    ALTER USER postgres with password 'secure-new-password';

6. Edit the pg_hba.conf file Switch it back to 'md5'

8. Restart services again

Hanshaw answered 16/3, 2020 at 15:22 Comment(0)

For a Windows installation, a Windows user is created. And "psql" uses this user for connection to the port. If you change the PostgreSQL user's password, it won't change the Windows one. The command line just below works only if you have access to the command line.

Instead, you could use the Windows GUI application "c:\Windows\system32\lusrmgr.exe". This application manages users created by Windows. So you can now modify the password.

Parthen answered 21/3, 2017 at 15:31 Comment(2)

But not for Windows 10 Home? – Bashan 9/1, 2023 at 17:21

What is the name of the application? Local User And Group Management? – Bashan 9/1, 2023 at 17:22

I did this to resolve the same problem:

Open the pg_hba.conf file with the gedit editor from the terminal:

sudo gedit /etc/postgresql/9.5/main/pg_hba.conf

It will ask for a password. Enter your admin login password. This will open gedit with the file. Paste the following line:

host  all   all  127.0.0.1/32  trust
just below -

# Database administrative login by Unix domain socket

Save and close it.

Close the terminal, open it again and run this command:

psql -U postgres

You will now enter the psql console.

Now change the password by entering this:

ALTER USER [your preferred user name] with password '[desired password]';

If it says the user does not exist then instead of ALTER, use CREATE.

Lastly, remove that certain line you pasted in pg_hba and save it.

Virnelli answered 14/11, 2017 at 12:8 Comment(0)

sudo -u postgres psql

ALTER USER user_name WITH PASSWORD 'new_password';

Takeo answered 21/5, 2023 at 11:20 Comment(1)

Please read "How to Answer" and "Explaining entirely code-based answers". It helps more if you supply an explanation why this is the preferred solution and explain how it works. We want to educate, not just provide code. – Conscientious 24/5, 2023 at 5:6

If you are on Windows you can just run

net user postgres postgres

And log in in PostgreSQL with postgres/postgres as the user/password.

Len answered 7/6, 2016 at 16:36 Comment(0)

Follow step 1 on the best answer.

Here is my addition if you use the Windows operating system. Follow only step 1, and then open pgAdmin or postgres on web and click on file on the top nav. Click on reset layout, and finally reload the application. Whatever password you put should work. I used 1234.

Slub answered 27/6, 2022 at 11:12 Comment(0)

I didn't manage to find the file pg_hba.conf in the folder C:\Program Files\PostgreSQL\14\data, because there is not a folder data at all.

I solved the problem by creating a new user using pgAdmin and gave it super system administrator rights.

Montsaintmichel answered 23/11, 2022 at 15:31 Comment(0)

Go to your PostgreSQL installation location, and search for pg_hba.conf, you will find it in ..\postgres\data\pg_hba.conf.
open your notepad
Now go to your SQL shell (PSQL) and leave everything blank
It will not ask for a password this time, and you will be logged in and now run this line, ALTER USER yourusername WITH SUPERUSER
go to the file pg_hba.conf and change METHOD from trust to md5 again, and save it which was open your notepad.

Agneta answered 13/11, 2023 at 17:16 Comment(0)

If your postgres is running in a docker container, try the following line instead:

host    all             all             172.17.0.1/32            trust

172.17.0.1 is the gateway address for the docker bridge network.

Birdhouse answered 7/2 at 2:24 Comment(0)

-1

Add the below line to your pg_hba.conf file. Which will be present in the installation directory of PostgreSQL

hostnossl    all          all            0.0.0.0/0  trust

It will start working.

Harness answered 26/2, 2021 at 10:47 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

For Windows (what has helped me):

Recommended topics

Hot tags