Обсуждение: Postgres dying after many failed logins
Hello all- I have a postgres instance running on an AWS ec2 machine (not RDS ). It is receiving many hits from the hacker address 209.141.53.139. Because this address has been implicated in hacker attempts previously, I have the pg_hba.conf set to explicitlyreject this address ( so I can see how many times it hits). https://www.abuseipdb.com/check/209.141.53.139 Note there are other restrictions on which addresses are allowed to connect, and we have non-default passwords setup on thisdb. I'm finding that after postgres is hit by and rejects many connections, it dies. I haven't been able to find any documentationthat explains failed connections causing the server to die but that is what I'm seeing. In the log file thereare multiple of these messages: 2021-11-04 15:14:46.537 UTC [1513486] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139",user "postgres", database "postgres", SSL on 2021-11-04 15:14:46.709 UTC [1513488] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139",user "postgres", database "postgres", SSL off 2021-11-04 15:14:48.566 UTC [1513494] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139",user "postgres", database "postgres", SSL on 2021-11-04 15:14:48.761 UTC [1513505] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139",user "postgres", database "postgres", SSL off .... 2021-11-05 11:13:49.519 UTC [1834715] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139",user "postgres", database "postgres", SSL on 2021-11-05 11:13:49.702 UTC [1834718] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139",user "postgres", database "postgres", SSL off 2021-11-05 14:35:09.197 UTC [1451469] LOG: received smart shutdown request 2021-11-05 14:35:09.222 UTC [1451660] postgres@breedbase FATAL: terminating connection due to administrator command 2021-11-05 14:35:09.222 UTC [1451662] postgres@breedbase FATAL: terminating connection due to administrator command And after the time span seen here, the log shows a smart shutdown request message shown above. All connections are terminatedand the system is shut down. My question: Is this expected behavior, ie that the server will shutdown after hours of failed attempts? Is there anythingI can do to prevent this, or further secure the database? The hackers are unsuccessful due to the rejected connections,but it is a problem that the database server is continually shut down. Thanks - Lynn
On Mon, 8 Nov 2021 at 18:15, Lynn Carol Johnson <lcj34@cornell.edu> wrote:
Hello all-
I have a postgres instance running on an AWS ec2 machine (not RDS ). It is receiving many hits from the hacker address 209.141.53.139. Because this address has been implicated in hacker attempts previously, I have the pg_hba.conf set to explicitly reject this address ( so I can see how many times it hits). https://www.abuseipdb.com/check/209.141.53.139
Note there are other restrictions on which addresses are allowed to connect, and we have non-default passwords setup on this db.
I'm finding that after postgres is hit by and rejects many connections, it dies. I haven't been able to find any documentation that explains failed connections causing the server to die but that is what I'm seeing. In the log file there are multiple of these messages:
2021-11-04 15:14:46.537 UTC [1513486] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139", user "postgres", database "postgres", SSL on
2021-11-04 15:14:46.709 UTC [1513488] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139", user "postgres", database "postgres", SSL off
2021-11-04 15:14:48.566 UTC [1513494] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139", user "postgres", database "postgres", SSL on
2021-11-04 15:14:48.761 UTC [1513505] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139", user "postgres", database "postgres", SSL off
....
2021-11-05 11:13:49.519 UTC [1834715] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139", user "postgres", database "postgres", SSL on
2021-11-05 11:13:49.702 UTC [1834718] postgres@postgres FATAL: pg_hba.conf rejects connection for host "209.141.53.139", user "postgres", database "postgres", SSL off
2021-11-05 14:35:09.197 UTC [1451469] LOG: received smart shutdown request
2021-11-05 14:35:09.222 UTC [1451660] postgres@breedbase FATAL: terminating connection due to administrator command
2021-11-05 14:35:09.222 UTC [1451662] postgres@breedbase FATAL: terminating connection due to administrator command
And after the time span seen here, the log shows a smart shutdown request message shown above. All connections are terminated and the system is shut down.
My question: Is this expected behavior, ie that the server will shutdown after hours of failed attempts? Is there anything I can do to prevent this, or further secure the database? The hackers are unsuccessful due to the rejected connections, but it is a problem that the database server is continually shut down.
I am not sure pg_hba can handle that attack imho.
you need to have something at the network layer or proxy layer to handle bot attack kind of requests.
i think all cloud providers have ddos protection of some kind like Shield (AWS) etc.
We used akamai for ddos mitigation, and used various rules to tarpit, block ips etc, user agent filtering, location etc depending on the type of attack.
manual management of ips may or may not work. We have seen cases where the moment you reject an ip, they bots learn and start attacking from a new ip etc.
i think having a proxy layer via envoyproxy/haproxy/nginx etc for simple ddos protection would work fine.
but you would need network layer protections as well some ddos mitigation service
for your apps.
Also as a general practice, the database is not to be exposed to the public.
just my opinion. i have doubts postgresql database hba alone can handle ddos.
Lynn Carol Johnson <lcj34@cornell.edu> writes:
> My question: Is this expected behavior, ie that the server will
> shutdown after hours of failed attempts?
There is nothing built-in that would cause that, and frankly I doubt
that there is any direct connection. It could be that the AWS environment
imposes some resource limit (i.e., total number of child processes
spawned) that the postmaster is eventually hitting because of the
hack attempts. However, if that's true, I wouldn't really expect it to
manifest as anything as polite as a SIGTERM signal. So I think there
is something else going on. You need to look for what in your environment
could send the postmaster a SIGTERM. Can you correlate the shutdown with
anything? One thing I'm wondering is if it matches the time of shutdown
of whatever you're launching the postmaster with.
regards, tom lane
On Mon, 8 Nov 2021 at 20:31, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Lynn Carol Johnson <lcj34@cornell.edu> writes:
> My question: Is this expected behavior, ie that the server will
> shutdown after hours of failed attempts?
There is nothing built-in that would cause that, and frankly I doubt
that there is any direct connection. It could be that the AWS environment
imposes some resource limit (i.e., total number of child processes
spawned) that the postmaster is eventually hitting because of the
hack attempts. However, if that's true, I wouldn't really expect it to
manifest as anything as polite as a SIGTERM signal.
saw a similar message on SO just a few days back.
The only thing common is fake login attempts and smart shutdown.
So I think there
is something else going on. You need to look for what in your environment
could send the postmaster a SIGTERM. Can you correlate the shutdown with
anything? One thing I'm wondering is if it matches the time of shutdown
of whatever you're launching the postmaster with.
since its an ec2 instance, maybe /var/log/syslog / messages auth.log and dmesg/kern.log log messages can be of help ?
Thanks,
Vijay
Mumbai, India
On Nov 8, 2021, at 7:45 AM, Lynn Carol Johnson <lcj34@cornell.edu> wrote:2021-11-05 14:35:09.197 UTC [1451469] LOG: received smart shutdown request
2021-11-05 14:35:09.222 UTC [1451660] postgres@breedbase FATAL: terminating connection due to administrator command
That does not look like PostgreSQL crashed but is being shutdown by RDS services due to a DOS attack.
Two things;
1. A firewall should be blocking the incoming rouge traffic to protect the given server; otherwise the system is open to a DOS attack.
2. Why is PostgreSQL accessible on the internet? Normally DB servers are at least 3 deep from the internet and only internal trusted nodes should be connecting to the database.
Thank you for the feedback.
We originally had this setup as a test, and we were connecting to the db from outside. Once the test scenario became something more permanent we neglected to fix access rights. Currently all access should be from a docker GUI which is run on that AWS instance, so I agree it shouldn’t be available.
As you suggest, we’ve removed the postgres port from the AWS instance inbound rules and hoping this takes care of it . Thanks again – I’m new to AWS and postgres setup so am making mistakes, but learning.
From: Rui DeSousa <rui@crazybean.net>
Date: Monday, November 8, 2021 at 12:33 PM
To: Lynn Carol Johnson <lcj34@cornell.edu>
Cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: Postgres dying after many failed logins
On Nov 8, 2021, at 7:45 AM, Lynn Carol Johnson <lcj34@cornell.edu> wrote:
2021-11-05 14:35:09.197 UTC [1451469] LOG: received smart shutdown request
2021-11-05 14:35:09.222 UTC [1451660] postgres@breedbase FATAL: terminating connection due to administrator command
That does not look like PostgreSQL crashed but is being shutdown by RDS services due to a DOS attack.
Two things;
1. A firewall should be blocking the incoming rouge traffic to protect the given server; otherwise the system is open to a DOS attack.
2. Why is PostgreSQL accessible on the internet? Normally DB servers are at least 3 deep from the internet and only internal trusted nodes should be connecting to the database.
On Tue, 9 Nov 2021 at 00:10, Lynn Carol Johnson <lcj34@cornell.edu> wrote:
Thank you for the feedback.
We originally had this setup as a test, and we were connecting to the db from outside. Once the test scenario became something more permanent we neglected to fix access rights. Currently all access should be from a docker GUI which is run on that AWS instance, so I agree it shouldn’t be available.
Can you share logs from /var/log/messages or /var/log/syslog and /var/log/auth.log from around the same time postgresql was shutdown.
around: 2021-11-05 14:35:09.197 UTC i think (this was more than an hour later since the hba reject connection log)
was the db shutdown as a result of vm reboot around the same time. (can check via last reboot)
I think that can help figure out if this was a result of some automated trigger to shutdown the db, or manual.
there seems to be no signs of resource strain like cpu/memory/open file handles/port exhaustion/network interface errors etc. (disconnections were quick)
just wanted to know if there was any kernel panic or other issue that resulted in the server to shutdown itself or some via some other processes etc.
btw, it seems you used postgres(superuser) as your app user. i think you should avoid giving superuser access to app.
A simple model for a role grant/design template can be like this.
Thanks,
Vijay
Mumbai, India