If you are attempting to connect to a Postgres database and face some issues, there are plenty of forum posts (e.g. StackOverflow) and likely reasons why it does not work.
Some examples are: 1) not setting the listening address to be ‘*’, 2) not setting the right line in the pg_hba.conf, 3) not editing the right pg_hba file, and so on.
However, I faced a very weird issue: I had a RESTful API powered by Nodejs, connecting to a Postgres database with the ‘pg’ node module. For some reason, the ‘password’ and ‘trust’ authentication method worked, but not the MD5 one.
I turned on logging on Postgres and Nodejs and found that the API does reach the database, but it just shows the following message:
FATAL: 28P01: password authentication failed for user ‘demouser’
DETAIL: Connection matched pg_hba.conf line 89: “host all all 0.0.0.0/0 md5”
LOCATION: auth_failed, auth.c:285
Which to me was weird, because
- the pg_hba configuration is very permissive, and
- the password worked for the ‘password’ auth option. The error occurs when I use md5 auth
Some of the options I did try was to:
- Verify if the password hash stored in the database was wrong
- Check if the password has ‘expired’ in Postgres (apparently a bug when using a particular version of pgadmin)
- Check against an old backup instance of the database
Turns out, the problem was simply because the pg node module version I was using did not play well with the Nodejs version I was using. So the solution was to either downgrade my Nodejs version to use v4.x.x, or to reinstall my pg node module. Either way should solve the problem.
Hope it helps someone who is in a similar situation 🙂