使用Docker部署Postgres与Prisma时的认证失败问题排查

问题 #

在Ubuntu环境下用Docker部署Postgres数据库和后端服务时，通过.env文件配置环境变量出现数据库认证失败，提示用户myuser不存在；但直接在Docker Compose中写死参数可正常运行。更换端口等方案无效，请求排查原因。

相关配置与报错信息 #

Docker Compose配置

backend:
    build:
      context: ./backend
      dockerfile: Dockerfile
    container_name: backend
    ports:
      - "3000:3000"
    networks:
      backend:
        aliases:
          - back
    env_file: .env
    depends_on:
      - postgres 
postgres:
    container_name: postgres
    image: postgres:15
      #env_file: .env
      #environment:
      #- POSTGRES_DB=transcendance
      #- POSTGRES_USER=myuser
      #- POSTGRES_PASSWORD=mypass
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_DB=${POSTGRES_DB}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
    ports:
      - "5432:5432"
    volumes :
      - db-volume:/var/lib/postgresql/data
    networks:
      backend:
        aliases:
          - db

.env文件内容

POSTGRES_DB=transcendance
POSTGRES_PASSWORD=myuser
POSTGRES_USER=mypass

DATABASE_URL=postgresql://myuser:mypass@db:5432/transcendance?schema=public
#[TYPE_DB]://[USER]:[PASSWORD]@[HOST]:[PORT]/[DB_NAME]

SOCKET_PORT=4242
DB_PORT=5432
BACK_PORT=3000
FRONT_PORT=8080

报错信息

backend     | Environment variables loaded from .env
backend     | Prisma schema loaded from prisma/schema.prisma
backend     | Datasource "db": PostgreSQL database "transcendance", schema "public" at "db:5432"
backend     | 
postgres    | 2023-11-28 08:32:26.923 UTC [33] FATAL:  password authentication failed for user "myuser"
postgres    | 2023-11-28 08:32:26.923 UTC [33] DETAIL:  Role "myuser" does not exist.
postgres    |   Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
backend     | Error: P1000: Authentication failed against database server at `db`, the provided database credentials for `myuser` are not valid.
backend     | 
backend     | Please make sure to provide valid database credentials for the database server at `db`.
backend exited with code 1

解决方法 #

1. 修正.env配置错误
   .env文件中POSTGRES_PASSWORD和POSTGRES_USER的取值写反了：

   • 当前配置创建的Postgres用户是mypass，但后端DATABASE_URL里用的用户名是myuser，导致用户不存在
   • 修正后的对应行应为：

     POSTGRES_PASSWORD=mypass
     POSTGRES_USER=myuser
     

2. 清理Postgres数据卷
   Postgres第一次启动时会初始化数据卷，旧的用户数据会残存在db-volume中，即使修正配置也无法覆盖。执行以下命令清理并重启服务：

   docker-compose down -v
   docker-compose up -d
   

3. 可选：简化Compose配置
   直接给Postgres服务添加env_file: .env，避免手动映射变量出错：

   postgres:
       container_name: postgres
       image: postgres:15
       env_file: .env
       ports:
         - "5432:5432"
       volumes :
         - db-volume:/var/lib/postgresql/data
       networks:
         backend:
           aliases:
             - db
   

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