Обсуждение: 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
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.
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
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.
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
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.
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.