6

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.

10

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

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

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.