11
  • I installed PostgreSQL on EC2 machine and now I want to change the password of user postgres
  • I do
$ sudo -u postgres psql
psql (9.1.5)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'newpasswd';
ALTER ROLE
  • Then I exit the shell and try to login with new password
$ psql -U postgres -W
Password for user postgres: 
psql: FATAL:  Peer authentication failed for user "postgres"

My PostgreSQL version is

$ psql --version
psql (PostgreSQL) 9.1.5
contains support for command-line editing

What is that I am doing wrong?

Thank you

UPDATE I made changes in pg_hba.conf and this is how it looks now

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
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

Then I restarted the postgres

$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.1 database server                                                                                                                                               [ OK ] 

I tried logging in again, but failed

$ psql -U postgres -W
Password for user postgres: 
psql: FATAL:  Peer authentication failed for user "postgres"
3
  • Probably the old password is still stored in your ~/.pgpass file.
    – wildplasser
    Sep 22, 2012 at 17:58
  • Nah, there's a password prompt there. I'm thinking the pg_hba.conf is set up in such a way that it's not accepting password authentication.
    – willglynn
    Sep 22, 2012 at 18:10
  • @willglynn, please check my update, it is failing again even after making changes in pg_hba.conf
    – daydreamer
    Sep 22, 2012 at 18:29

5 Answers 5

12

Like willglynn said, it's probably your pg_hba.conf file.

If you have the following line:

local   all    all     peer

then change it to:

local   all    all     md5

That should then let you login with your new password (assuming that you correctly supply it) :)

4
  • Hi @David, I tried as you mentioned, but it still fails, please check my update as what I did, thank you
    – daydreamer
    Sep 22, 2012 at 18:28
  • There's still a peer line that applies to local (UNIX domain sockets) connections from the postgres user. That'll fail unless the far end is running as the OS postgres user. See the pg_hba.conf documentation for a better understanding.
    – willglynn
    Sep 22, 2012 at 18:30
  • Although I was able to run django manage.py syncdb, I will still read about pg_hba.conf documentation you mentioned, thanks a lot for your help
    – daydreamer
    Sep 22, 2012 at 18:45
  • I just needed to add -h localhost
    – Mike
    May 11, 2018 at 3:13
5

I just wanted to add that in addition to to changing pg_hba.conf file from

local   all    all     peer

to

local   all    all     md5

As per the accepted answer, the only way I could log in after doing the same thing the OP was doing was to pass the -h flag when trying to login.

$ psql -U postgres -h localhost

Hopefully, this will help someone in the future. It was driving me nuts!

0

You have this...

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5

change by this:

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
#local   all             all                                     md5

and restart postgresql service by

sudo /etc/init.d/posgresql restart

this work for me

2
  • When changing pg_hba.conf you don't need to restart Postgres. Reloading the configuration is enough. e.g. pg_ctl ... reload or select pg_reload_conf()
    – user1822
    Apr 8, 2017 at 6:51
  • Just reload ... i did not know... thanks Apr 8, 2017 at 12:11
0

In my case that was due to env variable POSTGRES_PASSWORD in docker container.

-1

For the folks, who are going crazy after changing password many times and trying everything mentioned here, remember to check user validity expiration. You can set it to 'never expire' like that in psql 9.x:

ALTER ROLE <username> VALID UNTIL 'infinity';
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.