Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  • 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"
share|improve this question

migrated from stackoverflow.com Sep 23 '12 at 21:19

This question came from our site for professional and enthusiast programmers.

    
Probably the old password is still stored in your ~/.pgpass file. –  wildplasser Sep 22 '12 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 '12 at 18:10
    
@willglynn, please check my update, it is failing again even after making changes in pg_hba.conf –  daydreamer Sep 22 '12 at 18:29

1 Answer 1

up vote 4 down vote accepted

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

share|improve this answer
    
Hi @David, I tried as you mentioned, but it still fails, please check my update as what I did, thank you –  daydreamer Sep 22 '12 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 '12 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 '12 at 18:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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