FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "postgres", database "prod", SSL off

Question

It has been working fine for last several months; and suddenly started noticing this error in application,

FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "postgres", database "prod", SSL off

pg_hba.conf has,

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5

postgresql.conf has,

listen_addresses = '*'

Both file have not been touched/changed for many months.

Has anybody faced similar issue in a running environment ?

I have gone through several connection related issues on stoackoverflow; but they all point to one of these two files being misconfigured. Thats not the issue in this case.

---

The root cause is found and fixed.

This is what happened (for the benefit of those who might encounter such a strange issue)

• Three mysterious entires were found in pg_hba.conf, right at the top of the file
• These had reject method configured for user postgres, pgsql & pgdbadm
• None of our team members added them
• Because these were right at the top, even before "# PostgreSQL Client Authentication Configuration File...." comment starts, we couldn't notice it.
• I am still not sure, how these appeared there
• It might be some upgrade issue - but we haven't updated Postgres
• It might be a partially successful hacking attempt - still investigating this
• But to be on safer side, we have changed server credentials and looking into other hardening methods.

It just might save someone a sleepless night, if such an issue occurs, in a perfectly running environment.

Answer 1

I got the same issue. It is a hack. The same thing is described here:

https://dba.stackexchange.com/questions/215834/postgres-9-6-10-pg-hba-conf-altered

I got a new admin postgres user "pgdbadm", which could not be deleted because 3 objects in the template1 database depended on it. After restoring the template1 db from template0 I managed to drop the user.

There were two new rules on the top of the pg_hba.conf file:

host all postgres 0.0.0.0/0 reject
host all pgdbadm 0.0.0.0/0 md5

The rest of the file was the same.

I managed to replicate the hack with no shell access at all by doing the following in pgAdmin 4:

-- creating a new table
create table test(a text);

-- inserting the contents of pg_hba.conf into the table
copy test from '/var/lib/pgsql/data/pg_hba.conf';

-- overwriting the pg_hba.conf file with the contents from the table prepended with one random rule (just to test it)
copy (select 'host    all             all             127.0.0.1/32            md5' union all select * from test) TO '/var/lib/pgsql/data/pg_hba.conf';

-- cleanup
drop table a;

-- reloading the server config
select pg_reload_conf();

Of course, this was only possible because postgres was set up on an AWS EC2 instance with all ports open, default postgres user, default port, a stupid super-easy to guess password: "asd123". The security logs in the instance were full of attempts to connect on various ports, with various usernames, etc, so it most probably was a random attack.