用户/角色

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - {name: dbuser_meta     ,password: DBUser.Meta     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: pigsty admin user }
      - {name: dbuser_view     ,password: DBUser.Viewer   ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
      - {name: dbuser_grafana  ,password: DBUser.Grafana  ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for grafana database    }
      - {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for bytebase database   }
      - {name: dbuser_kong     ,password: DBUser.Kong     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for kong api gateway    }
      - {name: dbuser_gitea    ,password: DBUser.Gitea    ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for gitea service       }
      - {name: dbuser_wiki     ,password: DBUser.Wiki     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for wiki.js service     }
      - {name: dbuser_noco     ,password: DBUser.Noco     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for nocodb service      }
      - {name: dbuser_remove   ,state: absent }  # 使用 state: absent 删除用户

- name: dbuser_meta               # 必选，`name` 是用户定义的唯一必选字段
  state: create                   # 可选，用户状态：create（创建，默认）、absent（删除）
  password: DBUser.Meta           # 可选，密码，可以是 scram-sha-256 哈希字符串或明文
  login: true                     # 可选，默认为 true，是否可以登录
  superuser: false                # 可选，默认为 false，是否是超级用户
  createdb: false                 # 可选，默认为 false，是否可以创建数据库
  createrole: false               # 可选，默认为 false，是否可以创建角色
  inherit: true                   # 可选，默认为 true，是否自动继承所属角色权限
  replication: false              # 可选，默认为 false，是否可以发起流复制连接
  bypassrls: false                # 可选，默认为 false，是否可以绕过行级安全
  connlimit: -1                   # 可选，用户连接数限制，默认 -1 不限制
  expire_in: 3650                 # 可选，从创建时起 N 天后过期（优先级比 expire_at 高）
  expire_at: '2030-12-31'         # 可选，过期日期，使用 YYYY-MM-DD 格式（优先级没 expire_in 高）
  comment: pigsty admin user      # 可选，用户备注信息
  roles: [dbrole_admin]           # 可选，所属角色数组
  parameters:                     # 可选，角色级配置参数
    search_path: public
  pgbouncer: true                 # 可选，是否加入连接池用户列表，默认 false
  pool_mode: transaction          # 可选，用户级别的池化模式，默认 transaction
  pool_connlimit: -1              # 可选，用户级别的连接池最大连接数，默认 -1 不限制

- name: dbuser_app         # 标准命名
- name: app_readonly       # 下划线分隔
- name: _internal          # 下划线开头（用于内部角色）

- name: dbuser_app             # state 默认为 create
- name: dbuser_old
  state: absent                # 删除用户

# 明文密码（不推荐，会被记录到配置和日志中）
- name: dbuser_app
  password: MySecretPassword

# SCRAM-SHA-256 哈希（推荐）
- name: dbuser_app
  password: 'SCRAM-SHA-256$4096:xxx$yyy:zzz'

SET log_statement TO 'none';
ALTER USER "dbuser_app" PASSWORD 'xxx';
SET log_statement TO DEFAULT;

# 使用 PostgreSQL 生成（需要先连接到数据库，数据库有 pgcrypto 扩展）
psql -c "SELECT encode(digest('password' || 'username', 'sha256'), 'hex')"

- name: dbuser_app
  comment: '业务应用主账号'

COMMENT ON ROLE "dbuser_app" IS '业务应用主账号';

# 创建可登录用户
- name: dbuser_app
  login: true

# 创建角色（不可登录，用于权限分组）
- name: dbrole_custom
  login: false
  comment: 自定义权限角色

CREATE USER "dbuser_app" LOGIN;
CREATE USER "dbrole_custom" NOLOGIN;

- name: dbuser_admin
  superuser: true            # 危险：拥有全部权限

ALTER USER "dbuser_admin" SUPERUSER;

- name: dbuser_dev
  createdb: true             # 允许创建数据库

ALTER USER "dbuser_dev" CREATEDB;

- name: dbuser_admin
  createrole: true           # 允许管理其他角色

ALTER USER "dbuser_admin" CREATEROLE;

# 自动继承角色权限（默认）
- name: dbuser_app
  inherit: true
  roles: [dbrole_readwrite]

# 需要显式切换角色
- name: dbuser_special
  inherit: false
  roles: [dbrole_admin]

ALTER USER "dbuser_special" NOINHERIT;
-- 用户需要执行 SET ROLE dbrole_admin 才能获得该角色权限（必要但不充分）

- name: replicator
  replication: true          # 允许流复制连接
  roles: [pg_monitor, dbrole_readonly]

ALTER USER "replicator" REPLICATION;

- name: dbuser_myappadmin
  bypassrls: true            # 绕过行级安全策略

ALTER USER "dbuser_myappadmin" BYPASSRLS;

- name: dbuser_app
  connlimit: 100             # 最多 100 个并发连接

- name: dbuser_batch
  connlimit: 10              # 批处理用户限制连接数

ALTER USER "dbuser_app" CONNECTION LIMIT 100;

- name: temp_user
  expire_in: 30              # 30 天后过期

- name: contractor_user
  expire_in: 90              # 90 天后过期

-- expire_in: 30, 假设当前日期为 2025-01-01
ALTER USER "temp_user" VALID UNTIL '2025-01-31';

- name: contractor_user
  expire_at: '2024-12-31'    # 指定日期过期

- name: permanent_user
  expire_at: 'infinity'      # 永不过期

ALTER USER "contractor_user" VALID UNTIL '2024-12-31';
ALTER USER "permanent_user" VALID UNTIL 'infinity';

- name: dbuser_app
  roles:
    - dbrole_readwrite
    - pg_read_all_data

GRANT "dbrole_readwrite" TO "dbuser_app";
GRANT "pg_read_all_data" TO "dbuser_app";

- name: dbuser_app
  roles:
    - dbrole_readwrite                            # 简单字符串：GRANT 角色
    - { name: dbrole_admin, admin: true }         # 带 ADMIN OPTION
    - { name: pg_monitor, set: false }            # PG16+: 不允许 SET ROLE
    - { name: pg_signal_backend, inherit: false } # PG16+: 不自动继承权限
    - { name: old_role, state: absent }           # 撤销角色成员关系

# PostgreSQL 16+ 完整示例
- name: dbuser_app
  roles:
    # 普通成员关系
    - dbrole_readwrite

    # 可以将 dbrole_admin 授予其他用户
    - { name: dbrole_admin, admin: true }

    # 不能 SET ROLE 到 pg_monitor（只能通过继承使用权限）
    - { name: pg_monitor, set: false }

    # 不自动继承 pg_execute_server_program 的权限（需要显式 SET ROLE）
    - { name: pg_execute_server_program, inherit: false }

    # 撤销 old_role 的成员关系
    - { name: old_role, state: absent }

- name: dbuser_analyst
  parameters:
    work_mem: '256MB'
    statement_timeout: '5min'
    search_path: 'analytics,public'
    log_statement: 'all'

ALTER USER "dbuser_analyst" SET "work_mem" = '256MB';
ALTER USER "dbuser_analyst" SET "statement_timeout" = '5min';
ALTER USER "dbuser_analyst" SET "search_path" = 'analytics,public';
ALTER USER "dbuser_analyst" SET "log_statement" = 'all';

- name: dbuser_app
  parameters:
    work_mem: DEFAULT          # 重置为默认值
    statement_timeout: '30s'   # 设置新值

ALTER USER "dbuser_app" SET "work_mem" = DEFAULT;
ALTER USER "dbuser_app" SET "statement_timeout" = '30s';

# 生产用户：需要连接池
- name: dbuser_app
  password: DBUser.App
  pgbouncer: true

# 内部用户：不需要连接池
- name: dbuser_internal
  password: DBUser.Internal
  pgbouncer: false           # 默认值，可省略

# DBA 用户使用 session 模式（可能需要 SET 命令等会话状态）
- name: dbuser_dba
  pgbouncer: true
  pool_mode: session

# 普通业务用户使用 transaction 模式
- name: dbuser_app
  pgbouncer: true
  pool_mode: transaction

dbuser_dba      = pool_mode=session max_user_connections=16
dbuser_monitor  = pool_mode=session max_user_connections=8

- name: dbuser_app
  pgbouncer: true
  pool_connlimit: 50         # 此用户最多使用 50 个连接池连接

# 典型业务用户配置
pg_users:
  - name: dbuser_app
    password: DBUser.App
    pgbouncer: true
    roles: [dbrole_readwrite]    # 生产账号，读写权限

  - name: dbuser_readonly
    password: DBUser.Readonly
    pgbouncer: true
    roles: [dbrole_readonly]     # 只读账号

  - name: dbuser_admin
    password: DBUser.Admin
    pgbouncer: true
    roles: [dbrole_admin]        # 管理员，可执行 DDL

  - name: dbuser_etl
    password: DBUser.ETL
    roles: [dbrole_offline]      # 离线分析账号

"postgres" ""
"dbuser_wiki" "SCRAM-SHA-256$4096:+77dyhrPeFDT/TptHs7/7Q==$KeatuohpKIYzHPCt/tqBu85vI11o9mar/by0hHYM2W8=:X9gig4JtjoS8Y/o1vQsIX/gY1Fns8ynTXkbWOjUfbRQ="
"dbuser_view" "SCRAM-SHA-256$4096:DFoZHU/DXsHL8MJ8regdEw==$gx9sUGgpVpdSM4o6A2R9PKAUkAsRPLhLoBDLBUYtKS0=:MujSgKe6rxcIUMv4GnyXJmV0YNbf39uFRZv724+X1FE="
"dbuser_monitor" "SCRAM-SHA-256$4096:fwU97ZMO/KR0ScHO5+UuBg==$CrNsmGrx1DkIGrtrD1Wjexb/aygzqQdirTO1oBZROPY=:L8+dJ+fqlMQh7y4PmVR/gbAOvYWOr+KINjeMZ8LlFww="
"dbuser_meta" "SCRAM-SHA-256$4096:leB2RQPcw1OIiRnPnOMUEg==$eyC+NIMKeoTxshJu314+BmbMFpCcspzI3UFZ1RYfNyU=:fJgXcykVPvOfro2MWNkl5q38oz21nSl1dTtM65uYR1Q="

dbuser_dba      = pool_mode=session max_user_connections=16
dbuser_monitor  = pool_mode=session max_user_connections=8