psql FATAL Ident authentication failed for user postgres

Question

I have installed PostgreSQL and pgAdminIII on my Ubuntu Karmic box   I am able to use pgAdminIII successfully  i e  connect log on   however when I try to login to the server using the same username pwd on the command line  using psql   I get the error   psql  FATAL   Ident authentication failed for user  postgres    Does anyone now how to resolve this issue

User · Answer

I had the same issuse after following this: PostgreSQL setup for Rails development in Ubuntu 12.04

I tried the other answers but all I had to do was in: "config/database.yml"

development:
  adapter: postgresql
  encoding: unicode
  database: (appname)_development
  pool: 5
  username: (username you granted appname database priviledges to)
  password:

User · Answer

Simply adding the -h localhost bit was all mine required to work

User · Answer

In my case  solution here   for people who concerned  login to postgres   sudo -i -u postgres psql ALTER USER postgres WITH PASSWORD  postgres     type your password here   regards

User · Answer

I had to reinstall pdAdmin to resolve this issue  brew cask reinstall pgadmin4

User · Answer

Out of all the answers above nothing worked for me  I had to manually change the users password in the database and it suddenly worked  psql -U postgres -d postgres -c  quot alter user produser with password  produser   quot   I used the following settings  pg hba conf local   all             all                                     peer   IPv4 local connections  host    all             all             127 0 0 1 32            password     IPv6 local connections  host    all             all               1 128                 password  Connection is successful finally for the following command  psql -U produser -d dbname -h localhost -W

User · Answer

I ve spent more time solving this error that I care to admit   The order of authentication configuration in pg hba conf is relevant in your case I think  The default configuration file includes several lines in a vanilla install  These defaults can match the conditions of your authentication attempts resulting in a failure to authenticate  It fails regardless of additional configuration added at the end of the  conf file   To check which line of configuration is use make sure to look at the default log file for messages  You might see something like this  LOG   could not connect to Ident server at address  127 0 0 1   port 113  Connection refused FATAL   Ident authentication failed for user  acme   DETAIL   Connection matched pg hba conf line 82   host     all             all             127 0 0 1 32            ident    It turns out this default line is causing the rejection   host    all             all             127 0 0 1 32            ident   try commenting it out

User · Answer

You can set the environment variable PGHOST localhost     psql -U db user db name psql  FATAL   Peer authentication failed for user  db user     export PGHOST localhost   psql -U db user db name  Password for user mfonline

User · Answer

The problem is still your pg hba conf file  This line  You can found this file in  etc postgres varion main  local   all             postgres                                peer Should be  local   all             postgres                                md5   These are brief descriptions of both options according to the official PostgreSQL docs on authentication methods   Peer authentication  The peer authentication method works by obtaining the client s operating system user name from the kernel and using it as the allowed database user name  with optional user name mapping   This method is only supported on local connections   Password authentication  The password-based authentication methods are md5 and password  These methods operate similarly except for the way that the password is sent across the connection  namely MD5-hashed and clear-text respectively   If you are at all concerned about password  sniffing  attacks then md5 is preferred  Plain password should always be avoided if possible  However  md5 cannot be used with the db user namespace feature  If the connection is protected by SSL encryption then password can be used safely  though SSL certificate authentication might be a better choice if one is depending on using SSL    After altering this file  don t forget to restart your PostgreSQL server  If you re on Linux  that would be sudo service postgresql restart

User · Answer

The following steps work for a fresh install of postgres 9 1 on Ubuntu 12 04   Worked for postgres 9 3 9 on Ubuntu 14 04 too    By default  postgres creates a user named  postgres   We log in as her  and give her a password     sudo -u postgres psql  password Enter password            Logout of psql by typing  q or ctrl d  Then we connect as  postgres   The -h localhost part is important  it tells the psql client that we wish to connect using a TCP connection  which is configured to use password authentication   and not by a PEER connection  which does not care about the password      psql -U postgres -h localhost

User · Answer

In case none of the above works for you    I ve done quite a few Postgres installations  but was flummoxed today on a RedHat 6 5 system  installing Postgres 9 3    My typical hba conf configuration that Aron shows above didn t work   It turned out that my system was using IPV6  and ignoring the IPV4 configuration   Adding the line   host    all             all               1 128                 password   allowed me to login successfully

User · Answer

You re getting this error because you re failing client authentication  Based on the error message  you probably have the default postgres configuration  which sets client authentication method to  IDENT  for all PostgreSQL connections   You should definitely read section 19 1 Client Authentication in the PostgreSQL manual to better understand the authentication settings available  for each record in pg hba conf   but here is the relevant snippet to help with the problem you re having  from the version 9 5 manual       trust      Allow the connection unconditionally  This method allows anyone that   can connect to the PostgreSQL database server to login as any   PostgreSQL user they wish  without the need for a password or any   other authentication  See Section 19 3 1 for details       reject      Reject the connection unconditionally  This is useful for  filtering   out  certain hosts from a group  for example a reject line could block   a specific host from connecting  while a later line allows the   remaining hosts in a specific network to connect       md5      Require the client to supply a double-MD5-hashed password for   authentication  See Section 19 3 2 for details       password      Require the client to supply an unencrypted password for   authentication  Since the password is sent in clear text over the   network  this should not be used on untrusted networks  See Section   19 3 2 for details       gss      Use GSSAPI to authenticate the user  This is only available for TCP IP   connections  See Section 19 3 3 for details       sspi      Use SSPI to authenticate the user  This is only available on Windows    See Section 19 3 4 for details       ident      Obtain the operating system user name of the client by contacting the   ident server on the client and check if it matches the requested   database user name  Ident authentication can only be used on TCP IP   connections  When specified for local connections  peer authentication   will be used instead  See Section 19 3 5 for details       peer      Obtain the client s operating system user name from the operating   system and check if it matches the requested database user name  This   is only available for local connections  See Section 19 3 6 for   details       ldap      Authenticate using an LDAP server  See Section 19 3 7 for details       radius      Authenticate using a RADIUS server  See Section 19 3 8 for details       cert      Authenticate using SSL client certificates  See Section 19 3 9 for   details       pam      Authenticate using the Pluggable Authentication Modules  PAM  service   provided by the operating system  See Section 19 3 10 for details    So     to solve the problem you re experiencing  you could do one of the following    Change the authentication method s  defined in your pg hba conf file to trust  md5  or password  depending on your security and simplicity needs  for the local connection records you have defined in there  Update pg ident conf to map your operating system users to PostgreSQL users and grant them the corresponding access privileges  depending on your needs  Leave the IDENT settings alone and create users in your database for each operating system user that you want to grant access to  If a user is already authenticated by the OS and logged in  PostgreSQL won t require further authentication and will grant access to that user based on whatever privileges  roles  are assigned to it in the database  This is the default configuration    Note  The location of pg hba conf and pg ident conf is OS dependent

User · Answer

If you are using it on CentOS you may need to reload postgres after making the above solutions   systemctl restart postgresql-9 3 service

User · Answer

I found that I had to install an identity server  that listens on port 113   sudo apt-get install pidentd sudo service postgresql restart   And then ident worked

User · Answer

Hmmm      If you can connect with the username and password in pgAdminIII but you can t connect with psql then those two programs are probably connecting to the  database differently    If you re connecting to different databases  first try connecting to the same database  See below    From PostgreSQL  Documentation  9 3  psql      If you omit the host name  psql will connect via a Unix-domain socket to a server on the local host  or via TCP IP to localhost on machines that don t have Unix-domain sockets    If you re not running something like psql     -h host name      and you re running Ubuntu  psql should be connecting via a Unix-domain socket  so PostgreSQL probably isn t configured to allow one of the password authentication methods for the postgres user   You can test this by running   sudo -u postgres psql  If the above works  your server is probably configured to use peer authentication for local connections by the postgres user  i e  asking the OS for your user name to confirm that you re postgres   So It s Probably Your pg hba conf File  The full path of the file will be something like  etc postgresql 9 3 main pg hba conf  You can view it by  e g  sudo cat  etc postgresql 9 3 main pg hba conf   more   If you re omitting the host name in your psql command  you should be able to connect if you add the following entry to your pg hba conf file     Connection type   Database   User       IP addresses   Method local               all        postgres                  md5    Commented lines in the pg hba conf file start with      If you are including the host name in your psql command  add this entry instead     Connection type   Database   User       IP addresses   Method host                all        postgres   127 0 0 1 32   md5   You need to put the entry before any other entries are matched for your connection via psql  If in doubt about where to put it  just put it before the first un-commented line    More about pg hba conf  From PostgreSQL  Documentation  9 3  The pg hba conf File  bold emphasis mine       The first record with a matching connection type  client address  requested database  and user name is used to perform authentication  There is no  fall-through  or  backup   if one record is chosen and the authentication fails  subsequent records are not considered  If no record matches  access is denied    Note that records are not matched on authentication method  So  if your pg hba conf file contains the following entry     Connection type   Database   User       IP addresses   Method local               all        postgres                  peer   Then you won t be able to connect via   psql -u postgres  Unless one of these entries is in your pg hba conf file above the former entry     Connection type   Database   User       IP addresses   Method local               all        postgres                  md5 local               all        postgres                  password     Unencrypted  local               all        all                       md5 local               all        all                       password     Unencrypted

User · Answer

I had similar problem and I fixed it in pg hba conf when removing all ident methods even for IP6 address  in spite I have only IP4 on machine    host all all 127 0 0 1 32 password host all all   1 128 password  for pgAdmin running at local network host all all 192 168 0 0 24 md5

User · Answer

For Windows if you dont want to edit pb gba conf ie leave the method to MD5 default   create a new user  by running this query in Query tool in PGadmin   CREATE USER admin WITH PASSWORD  secret    then in cmd  psql  dbname Main db host 127 0 0 1 user admin password secret port 5432   where dbname is your db in postgresql

User · Answer

This worked for me   http   tecadmin net fatal-ident-authentication-failed-for-user-postgres    local   all             postgres                                trust local   all             myapp usr                               trust   IPv4 local connections  host    all             all             127 0 0 1 32            trust   IPv6 local connections   host    all             all               1 128                 trust

User · Answer

If you ve done all this and it still doesn t work  check the expiry for that user   Postgres password authentication fails

User · Answer

Did you set the proper settings in pg hba conf   See https   help ubuntu com stable serverguide postgresql html how to do it

User · Answer

my solution on PostgreSQL 9 3 on Mac OSX in bash shell was to use sudo to go into the data folder  and then append the necessary lines to the pg hba conf file to allow for all users to be trusted and be able to log in   This is what I did     in bash profile edit PGDATA environmental variable open    bash profile    append this line to bash profile export PGDATA   Library PostgreSQL 9 3 data     reload bash profile source    bash profile    open pg hba conf in vim sudo vi  Library PostgreSQL 9 3 data pg hba conf    append these two lines to the end of the pg hba conf file local   all   all                  trust host    all   all   127 0 0 1 32   trust    can now login as user in bash psql -d  lt db name gt  -U  lt user name gt  -W

User · Answer

For fedora26 and postgres9 6    First  log as user root then enter to psql by the following commands      su postgres     then    psql   in psql find location of hba file     means pg hba conf  postgres   show hba file     hba file   --------------------------------------      etc postgresql 9 6 main pg hba conf    1 row      in file pg hba conf change user access to this    host all all 127 0 0 1 32 md5

User · Answer

Edit the file  etc postgresql 8 4 main pg hba conf and replace ident or peer by either md5 or trust  depending on whether you want it to ask for a password on your own computer or not  Then reload the configuration file with    etc init d postgresql reload

User · Answer

One hack around this is to edit pg hba conf  sudo vi  etc postgresql 9 3 main pg hba conf   To temporarily     Database administrative login by Unix domain socket local   all             postgres                                   trust   At this point you are done  For security  then go and   sudo -u postgres psql template1 ALTER USER postgres with encrypted password  your password     then go back and set pg hba conf back to     Database administrative login by Unix domain socket local   all             postgres                                   md5

[postgresql] psql: FATAL: Ident authentication failed for user "postgres"

Examples related to postgresql