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. Thank you!

Answer 1

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

Answer 2

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.

Answer 3

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

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

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

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

1] Open file "pg_hba.conf" and find "IPv4 local connections"

2] See the value under "Method" column, it must be set to "md5" becase you selected it while installing.

3] Make "md5" value blank and save the file. Restart pgAdmin4 application.

4] Now again set the value back to "md5" and input your password in pgAdmin application.

You should be successfully able to do it.

Answer 8

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

Answer 9

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.

Answer 10

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

Answer 11

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

1. Open pg_hba.conf in any text editor (you can find this file in your postgres instalation folder > data);
2. Change all the methods fields to trust (meaning you don't need a password for postgre);
3. 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);
4. 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.