Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

After installing postgres, I tried to start psql on my VPS and got an authentication failure

~$ psql
Password: 
psql: FATAL:  password authentication failed for user "michael"

I went back to my install instructions, which had me set my password this way

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'your_password';

I tried to do that again but wasn't able to login:

 sudo -u postgres psql template1
Password: 
psql: FATAL:  password authentication failed for user "postgres"

I'm thinking that I either forgot the password, or, more likely, did this step wrong. The instructions told me, in sudo nano /etc/postgresql/9.1/main/pg_hba.conf, to change

local all postgres

to

local all postgres md5

so in the following code, I replaced both instances of peer with md5

# 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

Is there a way that I can recover or change my password now that I'm locked out?

Update

when trying to login, authentication fails whether I use user postgres or michael

 sudo -u michael psql template
Password: 
psql: FATAL:  password authentication failed for user "michael"
share|improve this question
    
Possible duplicate: serverfault.com/questions/514337/… –  Deer Hunter Jun 8 '13 at 18:36
    
@DeerHunter it's not a duplicate, although there is some overlap. This question is about being locked out, while the other question is about how to configure md5. It might be that faulty configuration of md5 is responsible for me being locked out, which is why I mentioned it here, but the substance of the questions is entirely different (imho). –  Michael Jun 8 '13 at 18:43
1  
The takeaway part is the same, though: you have to stop what you are doing and read the docs. A cavalry charge doesn't always help. –  Deer Hunter Jun 8 '13 at 18:46
2  
@Michael This question is not a duplicate, but you REALLY need to stop and read the docs. ALL of the docs, not just some random tutorials on the internet. You've posted two questions in three hours, both of which you wouldn't be asking if you'd taken the time to read and understand the Postgres documentation. Server Fault is a site for professionals, and professionals read the documentation. –  voretaq7 Jun 9 '13 at 0:01
1  
@Michael There's an introductory PostgreSQL tutorial in the PostgreSQL docs, and the documentation for your PostgreSQL packages contain installation-specific details. In your case since you're on Ubuntu I'd say that'll be help.ubuntu.com/community/PostgreSQL . Start reading there, and in /usr/share/doc/postgres*/README.Debian* where all the install-specific details are recorded. Yes, that could be more obvious, but ... suggestions on how we can make it more obvious given that the PostgreSQL team don't control how Ubuntu package PostgreSQL? –  Craig Ringer Jun 9 '13 at 2:17

1 Answer 1

Have you actually set passwords on these database accounts prior to enabling MD5 password authentication?

If you haven't set a password (or you have but you don't remember them) you can reset the passwords by setting the authentication type to trust in pg_hba.conf, logging in as your Postgres superuser account, and changing the passwords with the ALTER USER command.

share|improve this answer
    
Thanks, I changed the type to trust from md5 but when I run this command sudo -u postgres psql template1 I still get password auth failed for user postgres. According to my instructions, I need to run that command to do alter user to change the password –  Michael Jun 9 '13 at 0:42
1  
@Michael You probably didn't restart PostgreSQL after making the change, or you did something else wrong. read the documentation, or at least the PostgreSQL tutorials. Since you're apparently on Ubuntu, start with Ubuntu's guide on the default Ubuntu install of PostgreSQL: help.ubuntu.com/community/PostgreSQL –  Craig Ringer Jun 9 '13 at 2:14

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.