pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres dying after many failed logins
7+ messages / 4 participants
[nested] [flat]
* Postgres dying after many failed logins
@ 2021-11-08 12:45 Lynn Carol Johnson <lcj34@cornell.edu>
0 siblings, 3 replies; 7+ messages in thread
From: Lynn Carol Johnson @ 2021-11-08 12:45 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
Thanks - Lynn
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres dying after many failed logins
@ 2021-11-08 13:07 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Lynn Carol Johnson <lcj34@cornell.edu>
2 siblings, 0 replies; 7+ messages in thread
From: Vijaykumar Jain @ 2021-11-08 13:07 UTC (permalink / raw)
To: Lynn Carol Johnson <lcj34@cornell.edu>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
Application-Layer DDoS Attack Protection with HAProxy - HAProxy Technologies
<https://www.haproxy.com/blog/application-layer-ddos-attack-protection-with-haproxy/;
Also as a general practice, the database is not to be exposed to the public.
AWS Shield - Amazon Web Services (AWS) <https://aws.amazon.com/shield/;
just my opinion. i have doubts postgresql database hba alone can handle
ddos.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres dying after many failed logins
@ 2021-11-08 15:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Lynn Carol Johnson <lcj34@cornell.edu>
2 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-11-08 15:00 UTC (permalink / raw)
To: Lynn Carol Johnson <lcj34@cornell.edu>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres dying after many failed logins
@ 2021-11-08 16:15 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Vijaykumar Jain @ 2021-11-08 16:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Lynn Carol Johnson <lcj34@cornell.edu>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
Postgresql shutdown by itself - Stack Overflow
<https://stackoverflow.com/questions/69834413/postgresql-shutdown-by-itself;
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres dying after many failed logins
@ 2021-11-08 17:33 Rui DeSousa <rui@crazybean.net>
parent: Lynn Carol Johnson <lcj34@cornell.edu>
2 siblings, 1 reply; 7+ messages in thread
From: Rui DeSousa @ 2021-11-08 17:33 UTC (permalink / raw)
To: Lynn Carol Johnson <lcj34@cornell.edu>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres dying after many failed logins
@ 2021-11-08 18:40 Lynn Carol Johnson <lcj34@cornell.edu>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 7+ messages in thread
From: Lynn Carol Johnson @ 2021-11-08 18:40 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto: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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres dying after many failed logins
@ 2021-11-08 19:34 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Lynn Carol Johnson <lcj34@cornell.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Vijaykumar Jain @ 2021-11-08 19:34 UTC (permalink / raw)
To: Lynn Carol Johnson <lcj34@cornell.edu>; +Cc: Rui DeSousa <rui@crazybean.net>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
My GOTO Postgres Configuration for Web Services (tightlycoupled.io)
<https://tightlycoupled.io/my-goto-postgres-configuration-for-web-services/;
--
Thanks,
Vijay
Mumbai, India
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-11-08 19:34 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-08 12:45 Postgres dying after many failed logins Lynn Carol Johnson <lcj34@cornell.edu>
2021-11-08 13:07 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-11-08 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-11-08 16:15 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-11-08 17:33 ` Rui DeSousa <rui@crazybean.net>
2021-11-08 18:40 ` Lynn Carol Johnson <lcj34@cornell.edu>
2021-11-08 19:34 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox