在Postgres容器中用init脚本创建多用户遇角色不存在问题

部署配置（Docker Compose） #

version: "3.9"
services:
  postgres:
    image: postgres
    secrets:
      - pg_user_secret
      - pg_password_secret
    environment:
      POSTGRES_USER_FILE: /run/secrets/pg_user_secret
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_password_secret
    deploy:
      placement:
        constraints: [node.role == manager]
    volumes:
      - ./postgres/init.sql:/docker-entrypoint-initdb.d/init.sql
      - postgres-data:/var/lib/postgresql/data

  adminer:
    image: adminer:latest
    ports:
      - 8080:8080

volumes:
  postgres-data:

secrets:
  pg_user_secret:
    file: './pg_user.txt'
  pg_password_secret:
    file: './pg_password.txt'x

初始化脚本（init.sql） #

CREATE USER authUser WITH PASSWORD 'authPassword';
CREATE USER portfoliosUser WITH PASSWORD 'portfoliosPassword';
CREATE USER ordersUser WITH PASSWORD 'ordersPassword';

CREATE DATABASE auth;
REVOKE CONNECT ON DATABASE auth FROM PUBLIC;
GRANT CONNECT ON DATABASE auth TO authUser;
GRANT ALL PRIVILEGES ON DATABASE auth to authUser;

CREATE DATABASE portfolios;
REVOKE CONNECT ON DATABASE portfolios FROM PUBLIC;
GRANT CONNECT ON DATABASE portfolios TO authUser;
GRANT CONNECT ON DATABASE portfolios TO portfoliosUser;
GRANT ALL PRIVILEGES ON DATABASE portfolios to authUser;
GRANT ALL PRIVILEGES ON DATABASE portfolios to portfoliosUser;

CREATE DATABASE orders;
REVOKE CONNECT ON DATABASE orders FROM PUBLIC;
GRANT CONNECT ON DATABASE orders TO ordersUser;
GRANT ALL PRIVILEGES ON DATABASE orders to ordersUser;

问题现象 #

使用脚本中创建的用户（如authUser）连接PostgreSQL时，收到错误：Role X does not exist.

问题原因 #

PostgreSQL对未用双引号包裹的标识符（包括用户名、数据库名等），默认会自动转换为小写。因此脚本中的authUser实际被创建为authuser，当你用驼峰格式的authUser尝试连接时，PostgreSQL会去查找同名的小写角色，自然找不到，从而抛出角色不存在的错误。

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