PostgreSQL数据库遭异常连接尝试的原因及解决方案咨询

问题背景 #

我在Google Cloud上通过Kubernetes部署了PostgreSQL数据库，仅使用DataGrip执行SQL脚本及测试，未运行其他额外服务。闲置一天后，数据库日志出现大量认证失败记录，此前在DigitalOcean上通过Docker部署该数据库时也出现过相同情况。目前自身连接数据库正常，数据完整保存。

异常日志 #

2024-02-25 12:08:42.990 UTC [14890] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:08:48.270 UTC [14892] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:08:48.270 UTC [14892] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:08:51.549 UTC [14894] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:08:51.549 UTC [14894] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:08:54.498 UTC [14896] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:08:54.498 UTC [14896] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:08:59.547 UTC [14897] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:08:59.547 UTC [14897] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:09:03.284 UTC [14898] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:09:03.284 UTC [14898] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:09:07.893 UTC [14899] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:09:07.893 UTC [14899] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:09:14.411 UTC [14901] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:09:14.411 UTC [14901] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:09:18.038 UTC [14902] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:09:18.038 UTC [14902] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:09:23.023 UTC [14903] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:09:23.023 UTC [14903] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:09:24.523 UTC [14904] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:09:24.523 UTC [14904] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:09:28.164 UTC [14905] FATAL:  password authentication failed for user "postgres"
2024-02-25 12:09:28.164 UTC [14905] DETAIL:  Connection matched file "/var/lib/postgresql/data/pgdata/pg_hba.conf" line 128: "host all all all scram-sha-256"
2024-02-25 12:09:32.411 UTC [14907] FATAL:  password authentication failed for user "postgres"

Kubernetes配置文件 #

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: database-sfs
spec:
  serviceName: database-srv
  replicas: 1
  selector:
    matchLabels:
      app: database
  template:
    metadata:
      labels:
        app: database
    spec:
      containers:
        - name: postgres
          image: postgres
          ports:
            - containerPort: 5432
              name: postgres-port
          env:
            - name: POSTGRES_PASSWORD
              value: <my-password-here>
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
          volumeMounts:
            - name: postgres-data
              mountPath: /var/lib/postgresql/data
  volumeClaimTemplates:
    - metadata:
        name: postgres-data
      spec:
        storageClassName: manual
        accessModes:
          - ReadWriteMany
        resources:
          requests:
            storage: 1Gi

---
apiVersion: v1
kind: Service
metadata:
  name: database-srv
spec:
  selector:
    app: database
  type: LoadBalancer
  ports:
  - name: database
    protocol: TCP
    port: 5432
    targetPort: 5432

原因分析 #

这是典型的暴力破解攻击，核心原因如下：

1. 数据库通过LoadBalancer类型的Service直接暴露到公网，互联网上的端口扫描工具能轻易探测到5432端口开放。
2. 攻击脚本会默认针对postgres这个通用超级用户账号，尝试暴力枚举密码，这就是日志中反复出现认证失败的原因。
3. 之前DigitalOcean的Docker部署出现同样问题，说明当时容器也直接暴露了公网端口，攻击逻辑一致。

解决办法 #

1. 禁止数据库直接暴露公网 #

将Service的type从LoadBalancer改为ClusterIP，限制数据库仅在Kubernetes集群内部访问：

apiVersion: v1
kind: Service
metadata:
  name: database-srv
spec:
  selector:
    app: database
  type: ClusterIP  # 修改此处
  ports:
  - name: database
    protocol: TCP
    port: 5432
    targetPort: 5432

如果需要本地连接，使用端口转发命令：

kubectl port-forward service/database-srv 5432:5432

之后在DataGrip中连接localhost:5432即可。

2. 限制pg_hba.conf访问范围 #

修改PostgreSQL的pg_hba.conf文件，仅允许可信IP段访问，比如集群内部IP或你的本地公网IP：

# 替换原有的"host all all all scram-sha-256"
host all all 192.168.0.0/16 scram-sha-256  # 集群内部IP段
host all all 你的公网IP/32 scram-sha-256   # 你的本地IP

在Kubernetes中，可通过ConfigMap挂载自定义pg_hba.conf，或在容器启动时通过POSTGRES_INITDB_ARGS环境变量设置初始规则。

3. 禁用postgres默认超级用户 #

创建新的超级用户后，删除或禁用默认的postgres账号：

CREATE USER myadmin WITH SUPERUSER PASSWORD '强复杂度密码';
DROP USER postgres;

切断攻击脚本对默认账号的尝试路径。

4. 使用强复杂度密码 #

确保数据库密码包含大小写字母、数字和特殊字符，避免使用简单易猜的组合。

5. 启用日志审计（可选） #

配置PostgreSQL记录所有连接请求的源IP，便于追踪攻击来源：

ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_hostname = on;
SELECT pg_reload_conf();

之后可通过日志获取恶意IP，用防火墙规则封禁。

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