Post Mortem: Postgres outage on 2018-08-20

postgres-outage.md

2018-08-20: Postgres failure

What happened

The PostgreSQL container stopped unexpectedly, was automatically restarted, but suddenly didn't accept any connections anymore. Neither from the API service containers nor from the Macbook over the internet.

Error in logs:

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

Cause

Two lines were added at the beginning of the /var/lib/postgresql/data/pg_hba.conf file (automatically by some script of the Postgres docker image?), even before the initial comment block:

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

The first line caused the outage, since it would reject any connection using that user.

Fix

$ docker ps
$ docker exec -it <postgres-container-ID> bash
# In the container:
$ vi /var/lib/postgresql/data/pg_hba.conf

Change first line of pg_hba.conf or (untested:) remove the top two lines:

- host all postgres 0.0.0.0/0 reject
+ host all postgres 0.0.0.0/0 md5

Run (still in the Postgres container):

$ su - postgres
$ pg_ctl reload

That's it. I was now able to connect from the Macbook and the API services worked again.

How to prevent in the future

Not possible to prevent until the cause of the configuration change is known.

Update: Set POSTGRES_PASSWORD or POSTGRES_PASSWORD_FILE to prevent this issue.

I have a POSTGRES_PASSWORD env variable in my docker-compose file but it still keeps on happening. Need an automated deploy script and this one is holding me. Thanks for the support.

Saved my life man!
I dont understand how this happens after some time running the container. It work but some days after give this problem.
What the hell? Nothing would change that.
Thank you very much!

@jeanfbrito
I have this problem now . did you fiend a solution for this?
#help

This solved my problem although I had to install vim to edit the file and restart the docker instance. Thanks

FYI, from some sleuthing, it appears the pgdbadm user is a malicious account, created by hackers on Postgres instances exposed on port 5432 with weak/no password for the default postgres user. Proceed with caution.

Thanks for sharing, @schrockwell!

I wanted to add a little color for others who come here:

1. Postgres defaults do not set password on the main postgres superuser account
2. Postgres does not intend for servers to be publicly accessible from the web without additional configs. If you are putting a Postgres server on a publicly available host, you should at least set a password for the postgres user
3. Only making the modifications in the original post enough is not sufficient. You allow connections to your DB once more but the attacker still has open access to the DB, therefore your data is at risk and likely being downloaded.