Why can't user login on Postgres

Question

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.

Answer 1

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';

Answer 2

I have struggled with this a few times, and have come up with the following method.

1) Modify pg_hba.conf, and set the authentication mode to "trust" for both "local" and "host" entries:

local all all trust

host all all 127.0.0.1/32 trust

2) Restart the database: postgres$ pg_ctl restart

3) Go into the database and set the postgres password:

postgres$ psql

postgres=# alter user postgres password 'mypassword';

ALTER ROLE

postgres=# \q

4) Set the authentication mechanism back to password in pg_hba.conf:

local all all password

host all all 127.0.0.1/32 password

(I presume this would also work for md5 authentication, but have not tried)

5) Restart the database one last time: postgres$ pg_ctl restart