Docker Compose部署PostgreSQL容器无法创建用户与表的问题

尝试创建包含Node.js服务器和PostgreSQL数据库的devcontainer，数据库无法创建指定用户及数据表，报错如下：

2025-03-18 21:54:00.493 UTC [62] FATAL:  role "postgres" does not exist
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" 
failed: FATAL:  role "postgres" does not exist

数据库已正常启动监听端口：

2025-03-18 21:54:00.813 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

详细配置信息 #

.env文件 #

已被Docker加载的环境变量：

POSTGRES_SUPERPW="super_pw"
POSTGRES_USER="rp_user"
POSTGRES_PASSWORD="rp_pw"
POSTGRES_HOST="localhost"
POSTGRES_PORT=5432
POSTGRES_DB="rp_db"

docker-compose.yml #

version: '3.8'

services:
  api_service:
    build:
      context: ..
      dockerfile: Dockerfile
    image: api_service
    container_name: api_service_container
    volumes:
      - .:/usr/src/app
      - ./../.env:/usr/src/app/.env 
    ports:
      - '5010:5010' # Exposes app port
      - '9229:9229' # Exposes debugging port

    # this will prevent docker compose from starting the service up and let me debug it
    command: sleep infinity
    env_file:
      - ../.env # Load environment variables !!
    depends_on:
      - postgres

  postgres:
    build:
      context: .
      dockerfile: Dockerfile.postgres
    image: postgres:15
    container_name: postgres_container
    restart: always
    env_file:
      - ../.env # Load environment variables !!
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - '5432:5432'

volumes:
  postgres_data:

Dockerfile（Node.js） #

FROM node:18
WORKDIR /usr/src/app
COPY package*.json ./
RUN npm install
COPY . .
RUN npm install --only=development
RUN npm install dotenv
ENV DOCKER_ENV=true
EXPOSE 5010
EXPOSE 9229
CMD ["node", "--inspect=0.0.0.0:9229", "index.js"]

Dockerfile.postgres #

FROM postgres:15
RUN apt-get update && apt-get install -y \
    build-essential \
    git \
    postgresql-server-dev-all \
    && rm -rf /var/lib/apt/lists

# Add custom initialization scripts
COPY init.db.sh /docker-entrypoint-initdb.d/
COPY init.db.users.sh /docker-entrypoint-initdb.d/

# this changes the authentication scheme of how the user can log in (to md5)
COPY pg_hba.conf /docker-entrypoint-initdb.d/pg_hba.conf

# Expose the port the db runs on
EXPOSE 5432

初始化脚本 #

已添加执行权限：

chmod +x init.db.sh
chmod +x init.db.users.sh

init.db.sh

#!/bin/bash
set -e

# Add logging for debugging
echo "Initializing database setup..."

# Ensure the 'postgres' role exists
echo "Checking if the 'postgres' role exists..."
psql -v ON_ERROR_STOP=1 --username "postgres" <<-EOSQL
    DO \$\$ BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'postgres') THEN
            CREATE ROLE postgres WITH LOGIN PASSWORD '$POSTGRES_SUPERPW';
            ALTER ROLE postgres CREATEDB;
            -- Log role creation
            RAISE NOTICE 'Role "postgres" created.';
        ELSE
            -- Log role already exists
            RAISE NOTICE 'Role "postgres" already exists.';
        END IF;
    END \$\$;
EOSQL

# Now, create the user and database as specified
echo "Creating database and user..."
psql -v ON_ERROR_STOP=1 --username "postgres" <<-EOSQL
    CREATE ROLE $POSTGRES_USER WITH LOGIN PASSWORD '$POSTGRES_PASSWORD';
    ALTER ROLE $POSTGRES_USER CREATEDB;
    CREATE DATABASE $POSTGRES_DB OWNER $POSTGRES_USER;
    GRANT ALL PRIVILEGES ON DATABASE $POSTGRES_DB TO $POSTGRES_USER;
    -- Enable the uuid-ossp extension
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    -- Log creation
    RAISE NOTICE 'Database and user created: $POSTGRES_DB, $POSTGRES_USER';
EOSQL

echo "Initialization complete."

init.db.users.sh

#!/bin/bash
set -e

# Use the PostgreSQL environment variables to create the user and database
psql -v ON_ERROR_STOP=1 --username "postgres" <<-EOSQL
    -- Create the users table
    CREATE TABLE IF NOT EXISTS users (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      username VARCHAR(255) NOT NULL,
      password VARCHAR(255) NOT NULL
    );
EOSQL

pg_hba.conf #

# pg_hba.conf - Custom configuration for PostgreSQL authentication

# "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 external connections (md5 authentication)
host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5


# This means that any user connecting remotely is expected to authenticate using the scram-sha-256 password method.
# If devuser has not been configured to use scram-sha-256, or if the password is not correct, the authentication will fail
# host all all all scram-sha-256

# This will allow clients to connect using md5-encrypted passwords
host all all all md5

# end of file

日志信息 #

PostgreSQL容器状态：

769e52b3dfa3   postgres:15   "docker-entrypoint.s…"   19 seconds ago   Up 17 seconds   0.0.0.0:5432->5432/tcp                           postgres_container

容器日志显示初始化脚本执行报错：

CREATE DATABASE

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.db.sh
Initializing database setup...
Checking if the 'postgres' role exists...
2025-03-18 21:54:00.493 UTC [62] FATAL:  role "postgres" does not exist
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

PostgreSQL Database directory appears to contain a database; Skipping initialization

随后数据库自动恢复：

2025-03-18 21:54:00.806 UTC [1] LOG:  starting PostgreSQL 15.12 (Debian 15.12-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2025-03-18 21:54:00.809 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-03-18 21:54:00.809 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2025-03-18 21:54:00.813 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-03-18 21:54:00.816 UTC [28] LOG:  database system was interrupted; last known up at 2025-03-18 21:54:00 UTC
2025-03-18 21:54:00.895 UTC [28] LOG:  database system was not properly shut down; automatic recovery in progress
2025-03-18 21:54:00.896 UTC [28] LOG:  redo starts at 0/1501470
2025-03-18 21:54:00.911 UTC [28] LOG:  invalid record length at 0/1920C08: wanted 24, got 0
2025-03-18 21:54:00.911 UTC [28] LOG:  redo done at 0/1920BC0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2025-03-18 21:54:00.915 UTC [26] LOG:  checkpoint starting: end-of-recovery immediate wait
2025-03-18 21:54:00.954 UTC [26] LOG:  checkpoint complete: wrote 918 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.012 s, sync=0.020 s, total=0.040 s; sync files=301, longest=0.008 s, average=0.001 s; distance=4222 kB, estimate=4222 kB
2025-03-18 21:54:00.957 UTC [1] LOG:  database system is ready to accept connections

已删除所有Docker容器、镜像及卷并重新构建，问题依旧，求排查原因。

核心问题 #

1. 环境变量冲突：Postgres官方镜像会自动使用POSTGRES_USER环境变量创建初始超级用户，你设置了POSTGRES_USER=rp_user，所以初始超级用户是rp_user而非默认的postgres，导致初始化脚本用--username "postgres"连接时失败。
2. 初始化脚本逻辑错误：脚本试图创建postgres角色，但此时数据库还未完成初始化，无法通过psql连接；且即便能连接，初始用户是rp_user，没有权限创建超级角色postgres。
3. pg_hba.conf未正确替换：你将pg_hba.conf复制到/docker-entrypoint-initdb.d/目录，该目录只执行脚本文件，不会自动替换Postgres的配置文件，导致自定义认证规则未生效。

修复步骤 #

1. 调整环境变量与初始化脚本逻辑 #

修改.env文件，添加默认认证方式配置（可选）：

# .env新增
POSTGRES_INITDB_ARGS="--auth-host=md5"

修改init.db.sh，去掉创建postgres角色的逻辑，直接用$POSTGRES_USER连接：

#!/bin/bash
set -e

echo "Initializing database setup..."

# 使用Postgres镜像自动创建的超级用户rp_user连接
echo "Creating uuid-ossp extension..."
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
EOSQL

echo "Initialization complete."

修改init.db.users.sh，同样使用$POSTGRES_USER连接：

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE TABLE IF NOT EXISTS users (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      username VARCHAR(255) NOT NULL,
      password VARCHAR(255) NOT NULL
    );
EOSQL

2. 正确替换pg_hba.conf #

修改Dockerfile.postgres，将pg_hba.conf复制到Postgres的配置目录并修正权限：

FROM postgres:15
RUN apt-get update && apt-get install -y \
    build-essential \
    git \
    postgresql-server-dev-all \
    && rm -rf /var/lib/apt/lists

# Add custom initialization scripts
COPY init.db.sh /docker-entrypoint-initdb.d/
COPY init.db.users.sh /docker-entrypoint-initdb.d/

# 正确替换pg_hba.conf到Postgres配置目录
COPY pg_hba.conf /var/lib/postgresql/data/pg_hba.conf
RUN chown postgres:postgres /var/lib/postgresql/data/pg_hba.conf

EXPOSE 5432

3. 重新构建并启动 #

执行以下命令彻底清理并重建：

docker-compose down -v
docker-compose build --no-cache
docker-compose up

验证 #

启动后进入Postgres容器，执行以下命令验证：

docker exec -it postgres_container psql -U rp_user -d rp_db
# 验证用户表是否存在
SELECT * FROM users;
# 验证uuid-ossp扩展是否启用
SELECT uuid_generate_v4();

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