Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Solution of psql: FATAL: Peer authentication failed for user “postgres” (or any user)

psql: FATAL: Peer authentication failed for user “postgres” (or any user)

The connection failed because by default psql connects over UNIX sockets using peer authentication, that requires the current UNIX user to have the same user name as psql. So you will have to create the UNIX user postgres and then login as postgres or use sudo -u postgres psql database-name for accessing the database (and psql should not ask for a password).

If you cannot or do not want to create the UNIX user, like if you just want to connect to your database for ad hoc queries, forcing a socket connection using psql --host=localhost --dbname=database-name --username=postgres (as pointed out by @meyerson answer) will solve your immediate problem.

But if you intend to force password authentication over Unix sockets instead of the peer method, try changing the following pg_hba.conf* line:

from

# TYPE DATABASE USER ADDRESS METHOD
local  all      all          peer

to

# TYPE DATABASE USER ADDRESS METHOD
local  all      all          md5
  • peer means it will trust the identity (authenticity) of UNIX user. So not asking for a password.

  • md5 means it will always ask for a password, and validate it after hashing with MD5.

  • trust means it will never ask for a password, and always trust any connection.

You can, of course, also create more specific rules for a specific database or user, with some users having peer and others requiring passwords.

After changing pg_hba.conf you'll need to restart PostgreSQL if it's running. E.g. sudo service postgresql restart

Steps to change/create default postgres user's password:
  1. trust connection by adding in pg_hba.conf file
  • local all postgres trust
  1. Restart postgresql service
  • sudo service postgresql restart
  1. psql -U postgres

  2. At the postgres=# prompt, change the user name postgres password:

  • ALTER USER postgres with password ‘new-password’;
  1. Revert the changes in pg_hba.conf file from trust to md5 and restart postgresql.
pg_hba.conf file location

The file pg_hba.conf will most likely be at /etc/postgresql/9.x/main/pg_hba.conf To check location of pg_hba.conf connect to postgres db using psql then type SHOW hba_file; command.

After change pg_hba.conf file, you can execute SELECT pg_reload_conf(); or pg_ctl reload with superuser instead of restart postgresql service.

* Source

@atul91khanduri

This comment has been minimized.

Copy link

@atul91khanduri atul91khanduri commented Jul 10, 2017

To check location of pg_hba.conf connect to postgres db using psql then type SHOW hba_file; command.

@yousefessam

This comment has been minimized.

Copy link

@yousefessam yousefessam commented May 16, 2019

Thank you its very useful

@lintingzhen

This comment has been minimized.

Copy link

@lintingzhen lintingzhen commented Jun 21, 2019

After change pg_hba.conf file, you can execute SELECT pg_reload_conf(); or pg_ctl reload with superuser instead of restart postgresql service.

@dakotadelnorte

This comment has been minimized.

Copy link

@dakotadelnorte dakotadelnorte commented Aug 22, 2019

Thanks a lot! You clear my mind about it :-)

@Mvrs

This comment has been minimized.

Copy link

@Mvrs Mvrs commented Nov 7, 2019

Thank you brodie 🔥

@jdc-cunningham

This comment has been minimized.

Copy link

@jdc-cunningham jdc-cunningham commented Nov 28, 2019

Thanks came here for Raspberry Pi use
I used this link from octoprint about creating a user/database then imported my table, had to use $psql db-name --username=username and was able to get into the table, md5 uses same pass I set when creating user as mentioned above

edit: as of this time though, I still am dealing with some unknown issue(probably permission) when using systemd I ended up using MySQL in the mean time.

@onemidzuk

This comment has been minimized.

Copy link

@onemidzuk onemidzuk commented Dec 25, 2019

Thanks! It works!

@Abdelsalam-Megahed

This comment has been minimized.

Copy link

@Abdelsalam-Megahed Abdelsalam-Megahed commented Jan 7, 2020

If you're using Docker.

Try to check if your local DB is active because mostly it's conflicting with Docker, if so, you can deactivate it or uninstall it.

@SewookJung

This comment has been minimized.

Copy link

@SewookJung SewookJung commented Jan 21, 2020

If 'md5' didn't working
to change 'trust'
then will be working

@1-alex98

This comment has been minimized.

Copy link

@1-alex98 1-alex98 commented Feb 1, 2020

Can u allow both as well

@hassanannajjar

This comment has been minimized.

Copy link

@hassanannajjar hassanannajjar commented Feb 25, 2020

Thanks a lot! 🥰🥰
if this didn't work with you,
you can use this way

@navichok26

This comment has been minimized.

Copy link

@navichok26 navichok26 commented Mar 7, 2020

Thanks a lot! 👍

@GooDeeJaY

This comment has been minimized.

Copy link

@GooDeeJaY GooDeeJaY commented Mar 28, 2020

Thank you!!!

@Darex1991

This comment has been minimized.

Copy link

@Darex1991 Darex1991 commented Apr 4, 2020

Thank you! :)

@Lepidopteron

This comment has been minimized.

Copy link

@Lepidopteron Lepidopteron commented Apr 6, 2020

If 'md5' didn't working
to change 'trust'
then will be working

This helped!

@Mex978

This comment has been minimized.

Copy link

@Mex978 Mex978 commented Apr 22, 2020

Thanks a lot!

@LiuXianghai-coder

This comment has been minimized.

Copy link

@LiuXianghai-coder LiuXianghai-coder commented Apr 25, 2020

good, it works.
Thank you very much!

@sr-max

This comment has been minimized.

Copy link

@sr-max sr-max commented May 3, 2020

Thank you!

@o5shorioush

This comment has been minimized.

Copy link

@o5shorioush o5shorioush commented Jul 2, 2020

Life saver!!

@saviour123

This comment has been minimized.

Copy link

@saviour123 saviour123 commented Jul 16, 2020

my case i just added -h localhost and it worked

@brian-tung

This comment has been minimized.

Copy link

@brian-tung brian-tung commented Aug 13, 2020

Thank you! This was really helpful! :)

@Tilko

This comment has been minimized.

Copy link

@Tilko Tilko commented Aug 31, 2020

my case i just added -h localhost and it worked

me too 👍

@rabehasy

This comment has been minimized.

Copy link

@rabehasy rabehasy commented Sep 12, 2020

really helpful :) thank you

@tnksss

This comment has been minimized.

Copy link

@tnksss tnksss commented Nov 20, 2020

Thank You!!! SELECT pg_reload_conf();

@Usamaraoo

This comment has been minimized.

Copy link

@Usamaraoo Usamaraoo commented Nov 23, 2020

is this error ubuntu related cause my db running fine on windows

@palani-08

This comment has been minimized.

Copy link

@palani-08 palani-08 commented Jun 1, 2021

Thank you.

@AmidBidee

This comment has been minimized.

Copy link

@AmidBidee AmidBidee commented Jun 26, 2021

Awesome stuff!! Thanks.

@KristinaErdman

This comment has been minimized.

Copy link

@KristinaErdman KristinaErdman commented Jul 2, 2021

Thank you so much !!!

@nickie-f

This comment has been minimized.

Copy link

@nickie-f nickie-f commented Aug 21, 2021

I'm sorry, but my file is just a blank one. How can I do?

@jun-jing

This comment has been minimized.

Copy link

@jun-jing jun-jing commented Sep 14, 2021

It really helps, thanks for the solution and detailed explanation.

@gokul-kargo

This comment has been minimized.

Copy link

@gokul-kargo gokul-kargo commented Oct 8, 2021

my case i just added -h localhost and it worked

worked for me aswell 👍

@CompeanR

This comment has been minimized.

Copy link

@CompeanR CompeanR commented Oct 22, 2021

Thanks a lot!. It helps me a lot!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment