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've met this with 4 roles I created:
After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more.
pgAdmin III show error message:

An error has occurred:

Error connecting to the server: FATAL:  password authentication failed for user "sam"
FATAL:  password authentication failed for user "sam"

My system: Postgresql 9.2 on Ubuntu 12.04

Is there any way to fix this?

(1): login with account postgres, right click user in Login Roles, go to tab 'Definition' and enter password

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

It's possible that you're being bitten by this PgAdmin bug (changelog):

2012-11-28 AV 1.16.1 Date picker controls returns a full timestamp by default, which can cause inadvertent date changes on jobs and role validty dates. Ignore the time part.

This bug has been seen to set password expiry dates far in the past, such as 1/1/1970. In this case the error message when trying to connect is no different than with a wrong password.

You can check these expiry dates with:

SELECT usename,valuntil FROM pg_user;

and if they're wrong, reset them with:

ALTER USER username VALID UNTIL 'infinity';

and upgrade pgAdmin.

share|improve this answer
    
Thank you very much! This solved the problem. Every time I reset a user password, pgAdmin set the valid until time to 01-01-1970 so that user cannot log in any more. –  Cao Minh Tu Mar 8 '13 at 0:55
add comment

The simple thing to do is to log in with psql or pgAdmin and

ALTER USER sam WITH PASSWORD 'new_password';

Now, if you cannot log in with a superuser account you can recover by altering the pg_hba.conf settings for this user and reload the config (sometimes I find this requires restarting the server, but am not sure why).

What you can do is add a line that allows you to log in using the ident (peer in 9.2) method (if you can use a local system account of the same name as the user) for local connections for the user, or (if that is not possible) set to "trust" (very temporarily!). If using trust, set back as soon as possible, since this means "trust that the user is who he/she claims!" and consequently this setting is dangerous to leave enabled outside of immediate recovery needs.

Once you have logged in you can reset the password above.

share|improve this answer
    
Shouldn't pgAdmin perform just the same command? –  dezso Mar 7 '13 at 9:06
    
(noting I said psql or pgAdmin. What can I do to make it clearer?) –  Chris Travers Mar 7 '13 at 9:10
    
No-no, I just thought that changing passwords in the GUI does just the same. If it does I can't imagine what could go wrong? –  dezso Mar 7 '13 at 10:35
    
What could go wrong? Typos in the password for starters.... –  Chris Travers Mar 7 '13 at 10:37
    
Can't one simply set the password again once logged in as postgres? –  dezso Mar 7 '13 at 10:38
show 3 more comments

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.