Обсуждение: PostgreSQL 12 service failing in Ubuntu 20.04 after a few hours

On 1/1/23 12:51, Antonis Christodoulou wrote:
> Hi,
> 
> I have set up PostgreSQL according to this link:
> 
> https://www.cherryservers.com/blog/how-to-install-and-setup-postgresql-server-on-ubuntu-20-04
<https://www.cherryservers.com/blog/how-to-install-and-setup-postgresql-server-on-ubuntu-20-04>
> 
> The issue is that after a few hours of operation, even if I don’t access 
> the database at all, the Postgres process fails:
> 
> *christan@vultr*:*~*$ systemctl status postgresql*
> *●*postgresql.service - PostgreSQL RDBMS
>       Loaded: loaded (/lib/systemd/system/postgresql.service; disabled; 
> vendor preset: enabled)
>       Active: *active (exited)*since Sat 2022-12-31 19:56:56 UTC; 24h ago
>      Process: 3330722 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
>     Main PID: 3330722 (code=exited, status=0/SUCCESS)
> 
> *●*postgresql@12-main.service <mailto:postgresql@12-main.service> - 
> PostgreSQL Cluster 12-main
>       Loaded: loaded (/lib/systemd/system/postgresql@.service; 
> enabled-runtime; vendor preset: enabled)
>       Active: *failed*(Result: exit-code) since Sat 2022-12-31 21:33:52 
> UTC; 23h ago
>      Process: 3330697 ExecStart=/usr/bin/pg_ctlcluster 
> --skip-systemctl-redirect 12-main start (code=exited, status=0/SUCCESS)
>      Process: 3341739 ExecStop=/usr/bin/pg_ctlcluster 
> --skip-systemctl-redirect -m fast 12-main stop *(code=exited, status=2)*
>     Main PID: 3330702 (code=exited, status=0/SUCCESS)
> 
> Moreover, it looks like there is some process stuck into some eternal 
> loop, eating up 4 cores:
> 
> *christan@vultr*:*~*$ ps -ef | grep postgres
> *postgres*3342383       1  0  2022 ?        00:00:00 FzXlkULu
> *postgres*3344758       1 99  2022 ?        3-14:39:11 OElid7Dp
> *postgres*3419125       1 18 13:57 ?        01:17:03 tracepath
> christan 3463982 3462489  0 20:44 pts/0    00:00:00 grep --color=auto 
> *postgres*

The above and the below look weird.

Is this machine open to the world?

> *
> *
> *christan@vultr*:*~*$ top -p 3344758
>      PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ 
> COMMAND
> 3344758 postgres  20   0 2443428   2.3g      4 S 282.3  29.5   5201:46 
> OElid7Dp
> 
> Doing just systemctl postgresql doesn’t restart the process, I have to 
> manually kill all the above processes then do systemctl restart.
> 
> Lastly, in the journalctl I see the following logs:
> 
> Dec 31 21:33:35 vultr.guest sudo[3340093]: *pam_unix(sudo:auth): 
> co**nversation failed*
> Dec 31 21:33:35 vultr.guest sudo[3340093]: *pam_unix(sudo:auth): auth 
> could not identify password for [postgres]*
> Dec 31 21:33:35 vultr.guest sudo[3340093]: *postgres : user NOT in 
> sudoers ; TTY=unknown ; PWD=/var/lib/postgresql/12/main ; USER=root ; 
> COMMAND=/usr/sbin/sysctl kernel.nmi_watchdog=0*
> Dec 31 21:33:52 vultr.guest crontab[3341582]: (postgres) LIST (postgres)
> Dec 31 21:33:52 vultr.guest crontab[3341584]: (postgres) REPLACE (postgres)
> Dec 31 21:33:52 vultr.guest crontab[3341585]: (postgres) LIST (postgres)
> Dec 31 21:33:52 vultr.guest crontab[3341588]: (postgres) LIST (postgres)
> 
> 
> What am I missing here, maybe I have to make sure that postgres user has 
> sudo rights?
> 
> Regards,
> Antonis
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 1/1/23 13:11, Antonis Christodoulou wrote:
> Hello Adrian,
> 
> No it’s not open, but the database itself has very simple credentials (I 
> am just starting with PostgreSQL). What’s weird about the logs?

Not the logs the ps output. I would expect to see something like:

ps -ef | grep postgres
postgres  395382       1  0  2022 ?        00:03:31 
/usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/main -c 
config_file=/etc/postgresql/14/main/postgresql.conf
postgres  395384  395382  0  2022 ?        00:00:01 postgres: 14/main: 
checkpointer
postgres  395385  395382  0  2022 ?        00:00:26 postgres: 14/main: 
background writer
postgres  395386  395382  0  2022 ?        00:00:26 postgres: 14/main: 
walwriter
postgres  395387  395382  0  2022 ?        00:01:45 postgres: 14/main: 
autovacuum launcher
postgres  395388  395382  0  2022 ?        00:00:05 postgres: 14/main: 
archiver last was 0000000100000003000000B9.00000028.backup
postgres  395389  395382  0  2022 ?        00:01:37 postgres: 14/main: 
stats collector
postgres  395390  395382  0  2022 ?        00:02:24 postgres: 14/main: 
pg_cron launcher
postgres  395391  395382  0  2022 ?        00:00:01 postgres: 14/main: 
logical replication launcher

This:

postgres 3342383       1  0  2022 ?        00:00:00 FzXlkULu
postgres 3344758       1 99  2022 ?        3-14:39:11 OElid7Dp
postgres 3419125       1 18 13:57 ?        01:17:03 tracepath

just does not look right.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 1/1/23 13:11, Antonis Christodoulou wrote:

Hello Adrian,

No it’s not open, but the database itself has very simple credentials (I am just starting with PostgreSQL). What’s weird about the logs?

Not the logs the ps output. I would expect to see something like:

ps -ef | grep postgres
postgres  395382       1  0  2022 ?        00:03:31 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/main -c config_file=/etc/postgresql/14/main/postgresql.conf
postgres  395384  395382  0  2022 ?        00:00:01 postgres: 14/main: checkpointer
postgres  395385  395382  0  2022 ?        00:00:26 postgres: 14/main: background writer
postgres  395386  395382  0  2022 ?        00:00:26 postgres: 14/main: walwriter
postgres  395387  395382  0  2022 ?        00:01:45 postgres: 14/main: autovacuum launcher
postgres  395388  395382  0  2022 ?        00:00:05 postgres: 14/main: archiver last was 0000000100000003000000B9.00000028.backup
postgres  395389  395382  0  2022 ?        00:01:37 postgres: 14/main: stats collector
postgres  395390  395382  0  2022 ?        00:02:24 postgres: 14/main: pg_cron launcher
postgres  395391  395382  0  2022 ?        00:00:01 postgres: 14/main: logical replication launcher

This:

postgres 3342383       1  0  2022 ?        00:00:00 FzXlkULu
postgres 3344758       1 99  2022 ?        3-14:39:11 OElid7Dp
postgres 3419125       1 18 13:57 ?        01:17:03 tracepath

just does not look right.

Antonis Christodoulou <christan305@hotmail.com>
<VI1P193MB051005C8BE974502A0D4A315E1F79@VI1P193MB0510.EURP193.PROD.OUTLOOK.COM>writes: 
> This is a machine in the cloud, I can’t disconnect it.

In that case, you need to be taking nonzero security precautions.

> And yes the ps looks like this precisely when I do a fresh restart. I kill all postgres processes and restart:
> Then this is the output of me ps:

That looks fine ... but this doesn't:

>>> postgres 3342383       1  0  2022 ?        00:00:00 FzXlkULu
>>> postgres 3344758       1 99  2022 ?        3-14:39:11 OElid7Dp
>>> postgres 3419125       1 18 13:57 ?        01:17:03 tracepath

Somebody is hacking into your system and commandeering it to run
something resource-intensive, possibly a bitcoin miner.  Whatever
it is, it's trying to obscure its process name which is hardly
a sign of good intentions.

I'd counsel taking a hard look at your pg_hba.conf to be sure
it's not allowing non-credentialed logins from anywhere.  And
for pete's sake don't use a guessable password.

            regards, tom lane

El día lunes, enero 02, 2023 a las 08:53:32a. m. +0200, Antonis Christodoulou escribió:

> And for the record, Ahmet, here’s a weird cron job:
>
> christan@vultr:~$ sudo crontab -l -u postgres
> 13 * * * * /var/lib/postgresql/.systemd-private-x8C8W8llVk0Rzccy9N0ggCOI2VBAc.sh > /dev/null 2>&1 &
>
> Had no idea somebody can add something like this externally...

Please post the content of this script.

    matthias


--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

El día Montag, Januar 02, 2023 a las 11:54:53 +0300, Ebubekir Büyüktosun escribió:

>    Hey Antonis,
> 
> 
> 
>    If you decode the below Base64 code, you will see the following bash
>    script that is tried to execute on your machine;
> 
> ...

Without analyzing the shell code in detail, this is clear evidence of an
attack. You must purge the full operating system and reinstall it from
scratch with better credentials of Linux and later PostgreSQL.

    matthias
-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

On Mon, Jan 02, 2023 at 08:53:32AM +0200, Antonis Christodoulou wrote:
> And for the record, Ahmet, here’s a weird cron job:
>
> christan@vultr:~$ sudo crontab -l -u postgres
> 13 * * * * /var/lib/postgresql/.systemd-private-x8C8W8llVk0Rzccy9N0ggCOI2VBAc.sh > /dev/null 2>&1 &
>
> Had no idea somebody can add something like this externally...

Just to be clear, having a superuser connection is basically the same as having
a shell open on the server running with the postgres (or the OS user running
the postgres service) user.  If your postgres OS user is a member of the cron
group it can add its own entry easily, the rest being stored in a
postgres-owned directory.