Skip to content

Instantly share code, notes, and snippets.

@AtulKsol
Last active November 13, 2024 12:43
Show Gist options
  • Save AtulKsol/4470d377b448e56468baef85af7fd614 to your computer and use it in GitHub Desktop.
Save AtulKsol/4470d377b448e56468baef85af7fd614 to your computer and use it in GitHub Desktop.
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

@1-alex98
Copy link

1-alex98 commented Feb 1, 2020

Can u allow both as well

@hassanannajjar
Copy link

hassanannajjar commented Feb 25, 2020

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

@navichok26
Copy link

navichok26 commented Mar 7, 2020

Thanks a lot! 👍

@jzr-supove
Copy link

Thank you!!!

@Darex1991
Copy link

Thank you! :)

@Lepidopteron
Copy link

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

This helped!

@Mex978
Copy link

Mex978 commented Apr 22, 2020

Thanks a lot!

@LiuXianghai-coder
Copy link

good, it works.
Thank you very much!

@maximianodev
Copy link

Thank you!

@o5shorioush
Copy link

Life saver!!

@saviour123
Copy link

my case i just added -h localhost and it worked

@brian-tung
Copy link

Thank you! This was really helpful! :)

@Tilko
Copy link

Tilko commented Aug 31, 2020

my case i just added -h localhost and it worked

me too 👍

@rabehasy
Copy link

really helpful :) thank you

@tnksss
Copy link

tnksss commented Nov 20, 2020

Thank You!!! SELECT pg_reload_conf();

@Usamaraoo
Copy link

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

@palani-08
Copy link

Thank you.

@aaronahmid
Copy link

Awesome stuff!! Thanks.

@KristinaErdman
Copy link

Thank you so much !!!

@nickie-f
Copy link

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

@jun-jing
Copy link

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

@gokul-kargo
Copy link

my case i just added -h localhost and it worked

worked for me aswell 👍

@CompeanR
Copy link

Thanks a lot!. It helps me a lot!

@EvolveDeveloper
Copy link

Thank you so much! Helped a lot!

@crazyofapple
Copy link

Thanks!

@Vigneshgvs
Copy link

Any way to change ident to md5 - via commands (linux or psql or SQL) ?
i am using ec2, so automatically via commands in .sh file, have to set this line
psql -U postgres -h localhost -p 5432 -d mydb

@aalexren
Copy link

Thank you from the bottom of my heart! ❤️

@mathanprasannakumar
Copy link

Thanks a lot , i wasted a lot of time trying to solve this issue , you are a life saver

@Ruthmy
Copy link

Ruthmy commented Aug 15, 2023

Thanks a lot! ❤️
This was very useful to me.

@uche-inyama
Copy link

my case i just added -h localhost and it worked

worked for me aswell 👍

It worked for me 👍

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