11

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

6 Answers 6

16

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.

5
  • 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. Mar 8, 2013 at 0:55
  • you got it! damn bugs Jan 2, 2015 at 16:04
  • How exactly am I supposed to log in to psql??? That's the role I just updated. Apr 20, 2016 at 21:34
  • 1
    @ericpeters0n: temporarily switch the authentication method to trust or peer in the pg_hba.conf file for this account. Apr 21, 2016 at 0:30
  • Thanks, got it sorted. For those that come later, the "trust" means that: Once you've restarted postgres, you can run psql without password authentication iff you're a user of the same name as a privileged user (e.g. username 'postgres')... So, 'su - postgres psql' will allow you to log in and correct the password or valid date. Apr 21, 2016 at 7:15
3

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.

8
  • Shouldn't pgAdmin perform just the same command?
    – dezso
    Mar 7, 2013 at 9:06
  • (noting I said psql or pgAdmin. What can I do to make it clearer?) Mar 7, 2013 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, 2013 at 10:35
  • What could go wrong? Typos in the password for starters.... Mar 7, 2013 at 10:37
  • Can't one simply set the password again once logged in as postgres?
    – dezso
    Mar 7, 2013 at 10:38
3

For Windows variant - I too experienced this nasty bug because of pgAdmin for my Windows x64 install of version 9.2. It left my production paralyzed.

In folder C:\Program Files\PostgreSQL\9.2\data or C:\Program Files (x86)\PostgreSQL\9.**x**\data, you'll find the pg_hba.conf text file.

Find the following lines:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

and change METHOD md5 to "trust" like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

From Windows>Run type "services.msc" and [enter] find the right PostgreSQL instance and restart it.

Your DB security is now blown wide open! Heed the warning to return it back to md5 after changing the user password expiry time to say year 2099 for all the relevant users.

1

If you have not tried this already, review your pg_hba.conf file. It will be named something like /var/lib/pgsql/9.3/data/pg_hba.conf (Fedora 20); you may have to use 'find / -name pg_hba.conf' to locate it.

At the bottom of the file, change the 'METHOD' values to 'trust' for local testing (see postgres docs for full information). Reboot the machine to ensure everything is started clean and the new params are read.

Hopefully this will cure your woes. It solved my problems on Fedora 20 with PostgreSQL 9.3.

UPDATE 2016-10-14:

On Ubuntu, the needed filename is /etc/postgresql/9.5/main/pg_hba.conf. For local testing only, modify it to look like 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                                     trust
# IPv4 local connections:
# host    all             all             127.0.0.1/32            md5
  host    all             all             127.0.0.1/32            trust

The two lines with METHOD "trust" are new. They allow you to connect without a username/password.

When complete, you will need to restart the server via:

sudo systemctl restart postgresql 
1
  • For the pg_hba.conf to take effect, you need only a reload, not a restart. Furthermore, your suggestion looks incomplete as it is not clear how it would solve the issue in the end.
    – dezso
    Jul 26, 2014 at 5:32
1

I just had this same problem and it turned out that I had multiple users with the same name (differing cases). Once I merged the ownership and removed one, it was at least clear. Depending on the method of connection, the case was not necessarily transferred for authentication.

1

FATAL: password authentication failed can occur when you changed the user password remotely but the password authentication methods differs to the local one.

In this case the resolution is to edit the pg_hba.conf file and ensure consistent password authentication. For instance, 127.0.0.1 uses scram-sha-256 and 192.168.1.2 uses md5 then setting 127.0.0.1 to md5 allows you to login again (after restarting the service).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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