如何通过Python连接PostgreSQL Docker容器？认证失败排查

尝试使用PostgreSQL Docker容器进行集成测试，但连接失败。添加密码后仍出现认证失败错误，未添加密码时则会收到要求输入密码的提示。

Python代码 #

import psycopg2

db = psycopg2.connect(dbname='pytest', user='pytest', password='pytest', host='0.0.0.0', port='5432')

报错信息 #

Traceback (most recent call last):
  File "/home/<user_name>/<directory_name>/temp.py", line 26, in <module>
    db = psycopg2.connect(dbname='pytest', user='pytest', password='pytest', host='0.0.0.0', port='5432')
  File "/home/<user_name>/anaconda3/envs/gdg/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "0.0.0.0", port 5432 failed: FATAL:  password authentication failed for user "pytest"
connection to server at "0.0.0.0", port 5432 failed: FATAL:  password authentication failed for user "pytest"

Docker Compose配置文件 #

services:
  psql_db:
    image: postgres
    restart: always
    shm_size: 128mb
    environment:
      POSTGRES_USER: pytest
      POSTGRES_PASSWORD: pytest
      POSTGRES_HOST_AUTH_METHOD: trust

容器内的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

# warning trust is enabled for all connections
# see https://www.postgresql.org/docs/17/auth-trust.html
host all all all trust

解决方法 #

你的配置存在几个关键遗漏，导致连接失败：

1. 未映射容器端口到宿主机 #

容器内的PostgreSQL服务监听5432端口，但你没把这个端口暴露到宿主机，本地Python代码根本无法访问容器里的服务。需要在psql_db服务下添加端口映射：

services:
  psql_db:
    # ... 原有配置 ...
    ports:
      - "5432:5432"

2. 未初始化目标数据库 #

POSTGRES_USER环境变量只会创建指定的数据库用户，但不会自动创建同名数据库。你代码里指定连接dbname='pytest'，这个数据库默认不存在，这会间接导致连接异常（报错显示认证问题是因为PostgreSQL的错误提示优先级问题）。需要添加环境变量初始化数据库：

environment:
  # ... 原有环境变量 ...
  POSTGRES_DB: pytest

3. 连接地址优化 #

本地连接容器内的服务，用localhost或127.0.0.1比0.0.0.0更准确（0.0.0.0是服务监听的地址，不是客户端连接的目标地址），可以调整Python代码的host参数：

db = psycopg2.connect(dbname='pytest', user='pytest', password='pytest', host='localhost', port='5432')

额外验证步骤 #

1. 修改Docker Compose后，重启容器：

   docker-compose down && docker-compose up -d
   

2. 进入容器验证用户和数据库是否正常：

   docker exec -it $(docker-compose ps -q psql_db) psql -U pytest -d pytest
   

   如果能直接进入数据库，说明trust认证模式生效，此时连接甚至可以省略密码参数。

内容的提问来源于stack exchange，提问作者Cogito Ergo Sum