Docker部署PostgreSQL容器频繁出现FATAL认证错误求助

问题背景 #

在DigitalOcean Ubuntu Droplet上用docker-compose部署PostgreSQL已一周，近期执行docker compose up后容器持续输出大量认证失败错误，但业务使用的自定义用户（通过pgAdmin远程连接）功能完全正常。

错误日志 #

未创建postgres用户时 #

database  | PostgreSQL Database directory appears to contain a database; Skipping initialization
database  | 
database  | 2024-03-09 05:05:35.510 UTC [1] LOG:  starting PostgreSQL 16.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
database  | 2024-03-09 05:05:35.510 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
database  | 2024-03-09 05:05:35.511 UTC [1] LOG:  listening on IPv6 address "::", port 5432
database  | 2024-03-09 05:05:35.536 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
database  | 2024-03-09 05:05:35.570 UTC [25] LOG:  database system was shut down at 2024-03-09 05:02:46 UTC
database  | 2024-03-09 05:05:35.619 UTC [1] LOG:  database system is ready to accept connections
database  | 2024-03-09 05:05:37.901 UTC [29] FATAL:  password authentication failed for user "postgres"
database  | 2024-03-09 05:05:37.901 UTC [29] DETAIL:  Role "postgres" does not exist.
database  |     Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"
database  | 2024-03-09 05:05:39.028 UTC [30] FATAL:  password authentication failed for user "postgres"
database  | 2024-03-09 05:05:39.028 UTC [30] DETAIL:  Role "postgres" does not exist.
database  |     Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"
database  | 2024-03-09 05:05:40.207 UTC [31] FATAL:  password authentication failed for user "postgres"
database  | 2024-03-09 05:05:40.207 UTC [31] DETAIL:  Role "postgres" does not exist.
database  |     Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"
database  | 2024-03-09 05:05:41.332 UTC [32] FATAL:  password authentication failed for user "postgres"
database  | 2024-03-09 05:05:41.332 UTC [32] DETAIL:  Role "postgres" does not exist.
database  |     Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"

创建无密码postgres用户后 #

database  | 2024-03-09 05:14:54.013 UTC [73] FATAL:  password authentication failed for user "postgres"
database  | 2024-03-09 05:14:54.013 UTC [73] DETAIL:  User "postgres" has no password assigned.

设置postgres用户密码后 #

database  | 2024-03-09 05:15:02.139 UTC [80] FATAL:  password authentication failed for user "postgres"
database  | 2024-03-09 05:15:02.139 UTC [80] DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"

docker-compose.yml配置 #

version: "3.4"

services:
    database:
      image: postgres
      container_name: ${DATABASE_HOST}
      ports:
        - ${DATABASE_PORT}:${DATABASE_PORT}
      environment:
        POSTGRES_USER: ${DATABASE_USER}
        POSTGRES_PASSWORD: ${DATABASE_PASSWORD}
        POSTGRES_DB: ${DATABASE_NAME}
      volumes:
        - ~/apps/postgres:/var/lib/postgresql/data

（同目录存在对应.env文件，自定义用户凭据可正常通过pgAdmin远程连接）

已完成的排查 #

1. 容器内pg_hba.conf配置：

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host all all all scram-sha-256

2. 远程主机曾安装的PostgreSQL已卸载（dpkg -l | grep postgres无结果，postgres系统用户已删除）
3. 手动创建postgres用户并设置密码后，可在容器内通过psql正常连接，但错误日志仍持续输出

解决方案 #

1. 定位连接请求来源 #

先找到发起postgres用户连接的主体：

• 进入容器执行命令，查看活跃连接记录：

  psql -U ${DATABASE_USER} -c "SELECT * FROM pg_stat_activity WHERE usename = 'postgres';"
  

• 如果无结果，开启PostgreSQL详细日志：修改容器内postgresql.conf，添加/修改以下配置：

  log_connections = on
  log_disconnections = on
  log_line_prefix = '%t [%p]: [%c-%l] user=%u,db=%d,app=%a,client=%h '
  

  重启容器后，日志会显示请求的客户端IP、应用名称，直接定位来源。

2. 针对性处理 #

• 远程主机残留进程：用以下命令排查主机上连接5432端口的进程：

  ss -tulnp | grep :5432
  

  找到对应进程后停止并清理。
• 外部恶意扫描：在DigitalOcean控制面板配置防火墙，仅允许信任IP（如pgAdmin所在IP）访问5432端口；或修改pg_hba.conf，替换最后一行的all为具体信任IP段：

  host all all 你的信任IP/32 scram-sha-256
  

• 不需要postgres用户：直接在pg_hba.conf中拒绝postgres用户的外部连接，添加一行在最后一行之前：

  host all postgres all reject
  

  日志会变为拒绝连接的提示，数量会大幅减少。

3. 卷配置验证 #

你的卷配置无问题：PostgreSQL初始化时会用POSTGRES_USER创建超级用户，不会自动生成postgres用户，这是正常行为，和卷配置无关。

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