psql FATAL Peer authentication failed for user dev

Question

when i create a new user  but it cannot login the database  I do that like this   postgres Aspire  home XXX  createuser dev Shall the new role be a superuser   y n  n Shall the new role be allowed to create databases   y n  y Shall the new role be allowed to create more new roles   y n  y   then create a database     postgres Aspire  home XXX  createdb -O dev test development   after that  I try psql -U dev -W test development to login  but get the error   psql  FATAL   Peer authentication failed for user  dev    I tried to solve the problem but failed

User · Answer

pg dump -h localhost -U postgres -F c -b -v -f mydb backup mydb

User · Answer

In my case I was using different port  Default is 5432  I was using 5433  This worked for me     psql -f update table sql -d db name -U db user name -h 127 0 0 1 -p 5433

User · Answer

Your 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 dev and then login as dev or use sudo -u dev psql test development 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 test development --username dev  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    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 if PostgreSQL is running you ll need to make it re-read the configuration by reloading  pg ctl reload  or restarting  sudo service postgresql restart      The file pg hba conf will most likely be at  etc postgresql 9 x main pg hba conf  Edited  Remarks from  Chloe   JavierEH   Jonas Eicher   fccoelho   Joanis   Uphill What comments incorporated into answer

User · Answer

Try   psql -U role name -d database -h hostname  com -W

User · Answer

I simply had to add -h localhost

User · Answer

While  flaviodesousa s answer would work  it also makes it mandatory for all users  everyone else  to enter a password    Sometime it makes sense to keep peer authentication for everyone else  but make an exception for a service user  In that case you would want to add a line to the pg hba conf that looks like   local   all             some batch user                         md5   I would recommend that you add this line right below the commented header line     TYPE  DATABASE        USER            ADDRESS                 METHOD local   all             some batch user                         md5   You will need to restart PostgreSQL using  sudo service postgresql restart   If you re using 9 3  your pg hba conf would most likely be       etc postgresql 9 3 main pg hba conf

User · Answer

This works for me when I run into it   sudo -u username psql

User · Answer

For people in the future seeing this  postgres is in the  usr lib postgresql 10 bin on my Ubuntu server   I added it to the PATH in my  bashrc file  and add this line at the end  PATH  PATH  usr lib postgresql 10 bin   then on the command line    gt  source    bashrc   I refreshed my bash environment  Now I can use postgres -D  wherever from any directory

User · Answer

The easiest solution   CREATE USER dev WITH PASSWORD  dev   CREATE DATABASE test development  GRANT ALL PRIVILEGES ON DATABASE test development to dev  ALTER ROLE dev CREATEROLE CREATEDB

User · Answer

Peer authentication means that postgres asks the operating system for your login name and uses this for authentication  To login as user  dev  using peer authentication on postgres  you must also be the user  dev  on the operating system   You can find details to the authentication methods in the Postgresql documentation   Hint  If no authentication method works anymore  disconnect the server from the network and use method  trust  for  localhost   and double check that your server is not reachable through the network while method  trust  is enabled

User · Answer

Try   psql -U user name  -h 127 0 0 1 -d db name   where   -U is the database user name -h is the hostname IP of the local server  thus avoiding Unix domain sockets -d is the database name to connect to   This is then evaluated as a  network  connection by Postgresql rather than a Unix domain socket connection  thus not evaluated as a  local  connect as you might see in pg hba conf   local   all             all                                     peer

User · Answer

When you specify   psql -U user   it connects via UNIX Socket  which by default uses peer authentication  unless specified in pg hba conf otherwise   You can specify   host    database             user             127 0 0 1 32       md5 host    database             user               1 128            md5   to get TCP IP connection on loopback interface  both IPv4 and IPv6  for specified database and user   After changes you have to restart postgres or reload it s configuration  Restart that should work in modern RHEL Debian based distros   service postgresql restart   Reload should work in following way   pg ctl reload   but the command may differ depending of PATH configuration - you may have to specify absolute path  which may be different  depending on way the postgres was installed   Then you can use   psql -h localhost -U user -d database   to login with that user to specified database over TCP IP  md5 stands for encrypted password  while you can also specify password for plain text passwords during authorisation  These 2 options shouldn t be of a great matter as long as database server is only locally accessible  with no network access   Important note  Definition order in pg hba conf matters - rules are read from top to bottom  like iptables  so you probably want to add proposed rules above the rule   host    all             all             127 0 0 1 32            ident

[postgresql] psql: FATAL: Peer authentication failed for user "dev"

Examples related to postgresql