FATAL: password authentication failed for user "postgres" (postgresql 11 with pgAdmin 4)

Question

I recently installed Postgresql 11, during the installation, there's no step to put password and username for Postgres. Now in pgAdmin 4, I wanted to connect the database to server and it's asking me to input password, and I haven't put any in the first place.

Any one knows what's going on?

From the docs: By default, when PostgreSQL is installed, a postgres user is also added. The first question many ask is, “What is the default password for the user postgres?” The answer is easy… there isn't a default password. The default authentication mode for PostgreSQL is set to ident — iLuvLogix, Mar 07 '19 at 08:17
Thank you, can you tell me more about the default authentication mode for PostgreSQL, or any reference I can use. - @iLuvLogix — Michelley, Mar 07 '19 at 08:23
pls see my answer and let me know if you encounter any further issues.. — iLuvLogix, Mar 07 '19 at 08:43
Just to note, the command `CREATE USER sysUSERlive WITH PASSWORD 'password';` actually created my username as `sysuserlive` (_that is in lowercase_). Using this lowercase username in my application.properties, or when connecting to the db via cmd (`psql -h localhost -p 5432 -U sysuserlive`) it then fixed it for me. — Aldahunter, Mar 26 '23 at 23:29

iLuvLogix · Accepted Answer · 2021-03-17T09:47:34.260

102

The default authentication mode for PostgreSQL is set to ident.

You can access your pgpass.conf via pgAdmin -> Files -> open pgpass.conf

That will give you the path of pgpass.conf at the bottom of the window (official documentation).

After knowing the location, you can open this file and edit it to your liking.

If that doesn't work, you can:

Find your pg_hba.conf, usually located under C:\Program Files\PostgreSQL\9.1\data\pg_hba.conf
If necessary, set the permissions on it so that you can modify it. Your user account might not be able to do so until you use the security tab in the properties dialog to give yourself that right by using an admin override.
Alternately, find notepad or notepad++ in your start menu, right click, choose "Run as administrator", then use File->Open to open pg_hba.conf that way.
Edit it to set the "host" line for user "postgres" on host "127.0.0.1/32" to "trust". You can add the line if it isn't there; just insert host all postgres 127.0.0.1/32 trust before any other lines. (You can ignore comments, lines beginning with #).
Restart the PostgreSQL service from the Services control panel (start->run->services.msc)
Connect using psql or pgAdmin4 or whatever you prefer
Run ALTER USER postgres PASSWORD 'fooBarEatsBarFoodBareFoot'
Remove the line you added to pg_hba.conf or change it back
Restart PostgreSQL again to bring the changes to effect.

Here is an example of the pg_hba.conf file (METHOD is already set 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

NOTE: Remember to change the METHOD back to md5 or other auth-methods listed here after changing your password (as stated above).

edited Mar 17 '21 at 09:47

answered Mar 07 '19 at 08:41

iLuvLogix

5,920
3
26
43

I set all the method to 'trust', because whenever I create a new database, the server won't connect. Do you have any suggestions for added security? – Michelley Mar 07 '19 at 10:56
Anyway, I'll definitely upvote and accept your answer, this will help anyone with the same issue. – Michelley Mar 07 '19 at 10:59
@Michelley What have you done so far? Did you set `host all postgres 127.0.0.1/32 trust` to your `pg_hba.conf`? – iLuvLogix Mar 07 '19 at 13:24
Yes, I've set all to trust. For your reference, this is how my pg_hba looks like: 'host all all 127.0.0.1/32 trust' – Michelley Mar 08 '19 at 01:41
5

I'm running into the same issue on Linux Mint (Ubuntu 18.04 base). I've followed these instructions but it doesn't change anything. Still get the same error if I try to create a new server using postgres and the password I've set up. Any ideas? – Kajsa Oct 22 '19 at 08:04
@Kajsa have you tried it via pgAdmin or manually in the conf files? – iLuvLogix Oct 22 '19 at 08:48
1

I've only done it directly in the files. However I've solved it now. For some reason I thought clicking on the elephant in the panel and choosing *shut down server* would be enough but restarting with `*/postgresql restart` solved it. Thank you for the answer! – Kajsa Oct 22 '19 at 09:03
When I take these steps it still asks me for a password for user postgres, and I still don't have one. Anyone know why that might be? – Danny Santos Sep 05 '20 at 12:51
@DannySantos If you set up the user postgres as described above you should have a password assigned to the db-user. If your db is on a linux system, you could change to user postgres by `su - postgres` followed by `ALTER USER postgres PASSWORD 'someReallyStrongPassword'` That should do the trick, if not let me know and I'll try to guide you through additional steps ;) – iLuvLogix Sep 08 '20 at 09:29
@iLuvLogix I would really appreciate that, if I try and do what you say it asks me for a password. I actually opened a topic specifically for this, if you can help me out at all I would be really grateful, I really can't solve this! Here's the question: https://stackoverflow.com/questions/63754266/postgres-asking-for-forgotten-password-macos – Danny Santos Sep 13 '20 at 12:35
I have followed the steps still I need password to enter. – DevError404 Jul 20 '21 at 11:01
Windows 2012 server – DevError404 Jul 20 '21 at 14:18
@DevError404 So can you tell me exactly what you try to accomplish? Do you want to connect to the SQL-server with the user postgres without a password-prompt or do you want to set a specific password for the client connection with user postgres? – iLuvLogix Jul 20 '21 at 14:45
Actually I m trying to access an existing database but I forgot the password and want to reset it. When I go through the steps as above, I am able to access the DB as I changed md5 to trust but not able to change password it is saying, only superadmins can do – DevError404 Jul 20 '21 at 17:22

score 67 · Answer 2 · answered Jan 16 '21 at 14:29

67

Change the password of default use ALTER USER postgres WITH PASSWORD 'new_password';

answered Jan 16 '21 at 14:29

Gaurav Patil

1,162
10
20

3

When we install first time we need to set password for default `postgres` user – Apr 02 '21 at 11:37
1

For every user that we add, we need to set the password. – Digin Dominic Aug 12 '21 at 22:15

score 59 · Answer 3 · edited Mar 23 '20 at 16:14

59

For Windows variant - I too experienced this nasty bug because of pgAdmin for my Windows x64 install of version 9.2. It left my production paralyzed.

In folder C:\Program Files\PostgreSQL\9.2\data or C:\Program Files (x86)\PostgreSQL\9.x\data, you'll find the pg_hba.conf text file.

Find the following lines:

# 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

and change METHOD md5 to "trust" like this:

# 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

From Windows>Run type "services.msc" and enter find the right PostgreSQL instance and restart it.

Your DB security is now blown wide open! Heed the warning to return it back to md5 after changing the user password expiry time to say year 2099 for all the relevant users.

edited Mar 23 '20 at 16:14

Jason Aller

3,541
28
38
38

answered Mar 23 '20 at 15:40

Pavan Patel

691
5
4

4

What is the significance of md5? – Steffi Keran Rani J Jun 04 '20 at 10:34
1

https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html – Anton Toshik Jul 19 '20 at 20:18
Significance of md5: From https://www.postgresql.org/docs/13/auth-pg-hba-conf.html: `md5: Perform SCRAM-SHA-256 or MD5 authentication to verify the user's password.` See [Section 20.5](https://www.postgresql.org/docs/13/auth-password.html) for details. – questionto42 Sep 07 '21 at 08:44
For Linux users in terminal type: sudo /etc/init.d/postgresql restart – Konstantin F Oct 31 '22 at 04:42

score 23 · Answer 4 · answered Oct 21 '21 at 14:41

23

I have tried all the above mentioned solutions, trust me northing worked! I have resolved the issue by using following commands

psql -U default
\password

Enter new password:

Enter it again:

my username is : default

This worked perfectly for me.

answered Oct 21 '21 at 14:41

Arya Mohanan

577
5
8

1

I'm still getting a `psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "default" ` error message – Shashwat Swain May 15 '23 at 04:25

score 19 · Answer 5 · answered Feb 09 '20 at 18:25

19

Note: CREATE USER is the same as CREATE ROLE except that it implies LOGIN.

$ psql postgres
postgres=# create user postgres with superuser password 'postgres';

answered Feb 09 '20 at 18:25

user1575148

561
6
28

score 18 · Answer 6 · answered Jul 11 '21 at 14:02

After successfully changing the master password

If you get the same error even after following the master password reset steps Open your command prompt and execute

    psql -U postgres

It will ask you for the password, enter the new password which you set now parallelly open SQL shell(psql) and try again with the new password

score 18 · Answer 7 · answered Dec 06 '21 at 01:43

18

For Linux user try this

//CHECK POSTGRES IS WORKING OR NOT
sudo systemctl status postgresql

//THIS WILL ACCEPT PORTS
sudo pg_isready
sudo su postgres

//NAVIGATE TO SQL TERMINAL / BASH
psql

//CREATE A NEW USER WITH PASSWORD
CREATE USER shayon WITH PASSWORD 'shayon';

answered Dec 06 '21 at 01:43

MD SHAYON

7,001
45
38

1

Thank you, this worked out for me when trying to add a new server in pgadmin and getting the message: "Unable to connect to server: FATAL: password authentication failed for user "name_it" – Boswachter_Marc Jan 08 '22 at 09:32

score 11 · Answer 8 · answered May 06 '21 at 12:43

11

try using psql -U postgres if have put password while installing this is command where you have to use that. Thank you :)

answered May 06 '21 at 12:43

VASU TIWARI

638
8
7

1

saved me , but any idea why it isn't working like normal user id , pass scenario – Atif Shafi Sep 06 '21 at 03:51

questionto42 · Answer 9 · 2021-09-19T12:26:36.070

9

Option 1: If you use `trust`

Better change only postgres to trust in the pg_hba.conf, then access your db with postgres super user and add other users and passwords with the power of the postgres super user, then change all other peer to md5.

The steps: In the pg_hba.conf, change

local postgres to trust
do not change local all to trust,
instead change local all from peer to md5 - which means that a right password is enough to login.

See this solution in detail at the second answer of 'Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails'.

Option 2: Use `md5`, no `trust` needed (recommended)

This way is even easier because you will need to change the pg_hba.conf only once:

Change any local user from peer to md5, usually:

Change local postgres from peer to md5
Change local all from peer to md5

Add a postgres pw with the power of your Linux pw only:

 sudo su postgres
 psql (or psql -p <port> if you have more than one PostgreSQL)
 \password
 \q

See the accepted answer and the comments of "Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails".

edited Sep 19 '21 at 12:26

answered Sep 10 '21 at 12:09

questionto42

7,175
4
57
90

1

Option 2: setting the password with \password did it for me. Thanks! – Catalin Oct 23 '21 at 20:04
Thank you for sharing this. I am not sure why this is not easily found. All of the documentation I found online simply instructs us to initially login to psql and issue "alter user postgres with password 'strongpass'" and then update the /var/lib/pgsql/13/data/pg_hba.conf file and change method from peer/trust to md5/scram-sha-256. Doing this DOES NOT WORK. Setting the password with \password is the only thing that works. – waltmagic Mar 14 '22 at 18:34
@waltmagic I also found it after trying and searching around far too long, it was in a comment of the link at the bottom of the answer. Only the comment of @mirek under it showed me this `\password` trick. I also commented and tried to edit the answer but it was refused, I think even twice. Not good. If I had not posted the idea of that comment here, you would not have the solution. And we talk about a post of >1000 upvotes and a ~1 million views Q. The other answer (first of the two links) was also hard to read, I had to edit it strongly to make it more readable, yet it had > 500 upvotes. – questionto42 Mar 14 '22 at 18:56
option2 worked for me too – algone May 25 '23 at 23:49
why the downvote? – questionto42 Jul 05 '23 at 14:49

score 4 · Answer 10 · answered Oct 05 '21 at 09:32

4

I solved this problem by changing peer to trust in the file "pg_hba.conf" at local postgres then I restarted the postgres service with the command:

sudo service postgresql restart

That's it.

answered Oct 05 '21 at 09:32

Younes Belouche

1,183
6
7

score 4 · Answer 11 · answered May 15 '22 at 00:09

This particular situation I'm about to mention probably doesn't come up very often, but I was getting this error as well. After looking into it, it was because I had a local postgres instance listening on port 5433, and I was trying to set up a Kubernetes tunnel to a remote PG instance mapped to local port 5433 as well. It turns out the command I was running was attempting to connect to the local instance rather than the remote instance. When I temporarily stopped the local instance, I was able to connect to the remote instance through the tunnel without changing the psql command I was using.

thankyou! Solved my problem which was, due to having postgress installed locally but trying to access one in a docker container. — AnserGIS, Jul 26 '23 at 12:24

score 2 · Answer 12 · answered Apr 23 '21 at 01:51

2

You can use the "superuser" password for the first time.

After that you can use Object > Create > Login/Group Role to change the password for the "postgres" user.

answered Apr 23 '21 at 01:51

Nilo Guimaraes

21
1

score 2 · Answer 13 · edited May 19 '21 at 02:24

I currently had a headhache solving this case. A friend helped me I decided to post my solution here.

Open pg_hba.conf in any text editor (you can find this file in your postgres instalation folder > data);
Change all the methods fields to trust (meaning you don't need a password for postgre);
Run in your console this comand: "alter user postgres with password '[my password]';" | psql -U postgres (meaning to alter some user password for [my password] for the user as parameter -U postgres);
Et voilà (don't forget to change back the method from trust for the one that should be best for you).

I hope this help someone someday.

score 1 · Answer 14 · answered Dec 30 '19 at 14:33

1

I know this is an old question, but I had the same problem, e.g. no dialog for setting password for Postgres during installation with Postgresql 11.

Instead of doing all the file manipulations suggested in the other answers, I deleted Postgresql 11 and installed Postgresql 12, where I was prompted for setting password during installation.

answered Dec 30 '19 at 14:33

Drublic

660
1
9
23

4

I was asked the password and I set it too while installing Postgres 12 but now the password is not being accepted and I get this error `psql: error: could not connect to server: FATAL: password authentication failed for user "USER"` I am entering the correct password though – Ananta K Roy Mar 08 '20 at 12:29
I'm having the same problem as Ananta K Roy – nk abram Dec 20 '20 at 01:31

score 1 · Answer 15 · answered Jan 16 '21 at 14:17

1

Loggin to PgAdmin4

Go to

Object > Create > Login/Group Role
Create the "username" that was named in the psql terminal
Create password
Give it all the rights
Save
try the password immediately in the psql terminal.

It worked for me.

Hope this works for you.

answered Jan 16 '21 at 14:17

Walter Clayton

141
2
7

Make sure you have PostgreSQL 15 selected in PgAdmin – Bob the Builder Nov 04 '22 at 23:14

JustBeingHelpful · Answer 16 · 2022-04-27T11:39:58.277

For those of you who got this error and NONE of these answers helped, I may not have StackOverflow fish for you, but I'll teach you how to fish!

You likely don't have the correct order of lines in the pg_hba.conf file. If you read this PostgreSQL documentation link below, it says this error can be thrown if "no matching entry is found". However, that is NOT always true! Documentation is written by humans and humans make mistakes.

https://www.postgresql.org/docs/current/client-authentication-problems.html

The truth is that a line further up might take precedence, is qualifying and is forcing you to use a password stored in PostgreSQL rather than delegated authentication or some other method. If you are not specifying a password stored in PostgreSQL, then you do not need the LOGIN role attribute. Put a line at the very top of this list with your specific user, authentication protocol, network details and other criteria. Also, many may think that most computers use IPv4. Try IPv6 and you'll be surprised. Once you know the very specific criteria of your issue and place a line at the top, then you have established the ONLY RELIABLE WAY to troubleshoot these pg_hba.conf issues without source code debugging!

Another helpful trick is to create a crapload of Server entries in pg_admin (SQL IDE for PostgreSQL) with all of your users and authentication protocols for testing. When you test different scenarios, you'll instantly know which ones fail.

Also, whenever you change this file, restart the PostgreSQL service, before testing the user.

You're welcome my friend. :)

score 0 · Answer 17 · edited Aug 28 '23 at 11:16

0

Follow below stepsif you are using pgAdmin4 and facing error in updating password :

Open file "pg_hba.conf" and find "IPv4 local connections"
See the value under "Method" column, it must be set to "md5" becase you selected it while installing.
Make "md5" value blank and save the file. Restart pgAdmin4 application.
Now again set the value back to "md5" and input your password in pgAdmin application.

You should be successfully able to do it.

edited Aug 28 '23 at 11:16

Ahmed Ashour

5,179
10
35
56

answered May 24 '19 at 15:44

Pushkaraj Bhandari

31
2

Unfortunately it didn't work. I wasn't able to input mypassword in pgAdmin application again – Revolucion for Monica Dec 15 '19 at 22:57

score 0 · Answer 18 · answered May 06 '22 at 13:58

windown 11 - postgres 14

open pgAdmin4 - click servers
right-click on your windows user name rule, e.g: MyUserName.
definition tab - enter password, click save.
open/re-open terminal
run: psql "postgres:///"

if you get "MyName database doesn't exist" you're good to go

FATAL: password authentication failed for user "postgres" (postgresql 11 with pgAdmin 4)

18 Answers18

Option 1: If you use `trust`

Option 2: Use `md5`, no `trust` needed (recommended)

windown 11 - postgres 14

Linked

FATAL: password authentication failed for user "postgres" (postgresql 11 with pgAdmin 4)

18 Answers18

Option 1: If you use trust

Option 2: Use md5, no trust needed (recommended)

windown 11 - postgres 14

Linked

Option 1: If you use `trust`

Option 2: Use `md5`, no `trust` needed (recommended)