如何借助SQL脚本与环境变量在Postgres中安全创建数据库角色

核心原则是绝对不要把密码明文硬编码在SQL脚本、拼接在命令行参数中——前者会随脚本存储/流转造成凭据泄露，后者会被同服务器用户通过进程列表、命令历史直接拿到密码。以下是生产环境验证过的安全方案，按推荐优先级排序：

方案1：psql变量插值 + 环境变量传参（通用场景首选） #

这个方案既支持手动执行也支持自动化场景，密码不会以明文形式出现在命令、脚本、进程快照中，psql会自动处理特殊字符转义，避免SQL注入风险。

• 先在当前shell会话存入密码，操作前先关闭命令历史记录，避免密码写入bash/zsh历史文件：

  set +o history
  export NEW_ROLE_PASSWORD='你的强密码(包含大小写、数字、特殊字符)'
  set -o history
  

• 编写创建角色的SQL脚本（例：create_app_role.sql），用psql的变量占位符接收密码，注意用:'变量名'的写法，会自动做SQL字符串转义：

  -- 创建可登录的业务角色
  CREATE ROLE app_biz WITH LOGIN PASSWORD :'new_role_pwd';
  -- 按需追加授权逻辑
  GRANT CONNECT ON DATABASE biz_db TO app_biz;
  GRANT USAGE, CREATE ON SCHEMA public TO app_biz;
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_biz;
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_biz;
  

• 执行脚本时，从环境变量读取密码传入psql变量，不要直接在命令行写死密码：

  psql -h 127.0.0.1 -p 5432 -U db_admin -d biz_db -v new_role_pwd="$NEW_ROLE_PASSWORD" -f create_app_role.sql
  

• 执行完成后清理当前会话的环境变量：

  unset NEW_ROLE_PASSWORD
  

方案2：createuser工具交互式输入（手动操作最简方案） #

PostgreSQL自带的createuser客户端是封装好的角色创建工具，手动操作时用交互式密码输入最省心，完全不会留凭据痕迹：

createuser -h 127.0.0.1 -p 5432 -U db_admin -d biz_db --login --pwprompt app_biz

执行后命令行会提示输入两次密码，输入过程不会回显，密码不会写入命令历史、进程列表。

方案3：.pgpass密码文件（自动化流水线/定时任务场景） #

如果是CI/CD流水线、定时运维任务这类非交互场景，可以用PostgreSQL官方支持的密码文件传参，注意必须严格设置文件权限，否则PostgreSQL会主动拒绝读取该文件：

• 动态生成.pgpass文件，格式为主机地址:端口:数据库名:用户名:密码，权限设为仅当前用户可读可写：

  cat > ~/.pgpass <<EOF
  127.0.0.1:5432:biz_db:app_biz:你的强密码
  EOF
  chmod 0600 ~/.pgpass
  

• 正常执行创建角色的SQL脚本即可，libpq驱动会自动读取.pgpass中的凭据完成认证、角色创建，不需要在命令中传密码
• 脚本执行完成后立刻删除临时生成的.pgpass文件，避免凭据残留

必须避开的安全坑 #

• 不要用echo "CREATE ROLE xxx PASSWORD '$pwd'" | psql这类shell拼接写法，密码会暴露在进程列表、命令历史中，特殊字符还容易触发SQL注入
• 生产环境不要把log_statement参数设为all，该配置会记录所有执行的SQL原文，会把密码明文打印到数据库日志中，生产环境建议设为ddl或none
• 不要把包含密码的SQL脚本、.pgpass文件提交到代码仓库，哪怕是私有仓库也存在凭据泄露风险
• 不要用数据库超级用户运行业务逻辑，创建的业务角色只给最小必要权限

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