Postgresql – Why can’t user login on Postgres

postgresqlpostgresql-9.3

I have attempted to set up a read-only user in Postgres, but they are unable to log on. I am unsure the next step to take to solve the problem.

When I try and log on as user XXXX I get an error – psql: FATAL: password authentication failed for user "XXXX"

Looking at the log file I see the following lines:

< 2014-05-20 10:22:24.830 NZST >FATAL:  password authentication failed for user "XXXX"
< 2014-05-20 10:22:24.830 NZST >DETAIL:  Connection matched pg_hba.conf line 104: "host DATABASENAME        XXXX           10.0.0.0/8              md5"

I have, of-course, tried resetting the password – multiple times, to no avail.
I have tried changing from md5 to password which did not work. Interestingly enough, setting the authentication mechanism to "trust" did work.

I am using Postgres 9.3.

I am unable to delete this user to reset it – I get an error "ERROR: role "XXXX" cannot be dropped because some objects depend on it
DETAIL: privileges for database DATABASENAME"

If I create a new user, that new user has no problems connecting to the database. (I duplicated the appropriate line in pg_hba.conf and modified the username of-course)

Issuing the command "grant all on DBNAME to XXXX" does not seem to make any difference.

When I do a \ddp command I get

    Default access privileges
  Owner   | Schema |   Type   | Access privileges 
----------+--------+----------+-------------------
 postgres | public | sequence | XXXX=r/postgres
 postgres | public | table    | XXXX=r/postgres

I'm not entirely sure why this should stop me dropping the user altogether and recreating them – or how to [safely] fix this.

Any insite into what is going on or how to further debug the login problems
would be much appreciated.

Best Answer

When getting this error:

psql: FATAL: password authentication failed for user "XXXX"

despite the password being correct, the next thing to check is the validity of the account:

SELECT usename, valuntil FROM pg_user;

See PostgreSQL user can not connect to server after changing password for how a bug in PgAdmin may incorrectly reset this valuntil in the past, making a user unable to connect with the mentioned error message.

If that happens to be your case, you may fix it with:

ALTER USER username VALID UNTIL 'infinity';
Related Topic