PostgreSQL: changing password for a user is not working

Question

• 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"

Answer 1

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) :)

Answer 2

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!

Answer 3

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

Answer 4

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

Answer 5

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';