Home / server / Questions / 597076
Accepted
davidgo
Asked: 2014-05-20 14:33:03 +0800 CST

Why can't user login on Postgres

  • 772

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.

postgresql

2 Answers

  1. 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';
    • 14

  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

    • 5