Postgres password authentication fails

Question

I tried to login with the postgres user from my windows machine to my server with Pgadmin   But it keeps giving me this error   psql  FATAL   password authentication failed for user  postgres    So then I tried to login from the command line with psql  which gave me the same error  I then resetted the password to  test  using psql  after putting the local entry in pg hba conf to trust  And then I placed the entry back to md5  and tried to login with the password  test     In psql I have used these commands   ALTER ROLE postgres WITH PASSWORD  test   ALTER ROLE postgres PASSWORD  test   ALTER USER postgres WITH PASSWORD  test   ALTER USER postgres PASSWORD  test     And this special psql command   password   Every time  I returned the pg hba conf local entry to md5  and tried to login with psql   psql -U postgres   And then I am asked for a password  After entering  test   psql gives me the same error as I mentioned earlier   And of course  I restarted postgresql after each and every change to the pg hba file  And I m using psql with  su postgres    So  even though I am able to change the password the usual way  it isn t accepted as the password    I hope somebody is able to help me with this   Some info   Postgresql 9 1 Ubuntu 12 04  Pg hba file  as requested   local   all             postgres                                md5  local   all             all                                     md5   IPv4 local connections  host    all             all             127 0 0 1 32            md5   IPv6 local connections  host    all             all               1 128                 md5  host    all             all              lt my-ip-address gt  32        md5   When I wanted to modify the password  I changed the top md5 to trust  I want to mention that this configuration has worked without problems before   The results of  sudo -u postgres psql -x -c  select   from pg user where usename  postgres     Are   usename       postgres usesysid      10 usecreatedb   t usesuper      t usecatupd     t userepl       t passwd                 valuntil      1970-01-01 00 00 00 01 useconfig

User · Answer

Assuming  that you have root access on the box you can do   sudo -u postgres psql   If that fails with a database  postgres  does not exists this block   sudo -u postgres psql template1   Then sudo nano  etc postgresql 11 main pg hba conf file   local   all         postgres                          ident   For newer versions of PostgreSQL ident actually might be peer   Inside the psql shell you can give the DB user postgres a password   ALTER USER postgres PASSWORD  newPassword

User · Answer

I came across this question  and the answers here didn t work for me  i couldn t figure out why i can t login and got the above error    It turns out that postgresql saves usernames lowercase  but during authentication it uses both upper- and lowercase   CREATE USER myNewUser WITH PASSWORD  passWord     will create a user with the username  mynewuser  and password  passWord    This means you have to authenticate with  mynewuser   and not with  myNewUser   For a newbie in pgsql like me  this was confusing  I hope it helps others who run into this problem

User · Answer

pg hba conf entry define login methods by IP addresses  You need to show the relevant portion of pg hba conf in order to get proper help    Change this line   host    all             all              lt my-ip-address gt  32        md5   To reflect your local network settings  So  if your IP is 192 168 16 78  class C  with a mask of 255 255 255 0  then put this   host    all             all             192 168 16 0 24        md5   Make sure your WINDOWS MACHINE is in that network 192 168 16 0 and try again

User · Answer

As shown in the latest edit  the password is valid until 1970  which means it s currently invalid  This explains the error message which is the same as if the password was incorrect   Reset the validity with   ALTER USER postgres VALID UNTIL  infinity     In a recent question  another user had the same problem with user accounts and PG-9 2   PostgreSQL - Password authentication fail after adding group roles  So apparently there is a way to unintentionally set a bogus password validity to the Unix epoch  1st Jan  1970  the minimum possible value for the abstime type   Possibly  there s a bug in PG itself or in some client tool that would create this situation   EDIT  it turns out to be a pgadmin bug  See https   dba stackexchange com questions 36137

[postgresql] Postgres password authentication fails

Examples related to postgresql