管理 PostgreSQL 业务用户

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users: [{ name: dbuser_app, password: 'DBUser.App', pgbouncer: true }]  # <--- 在这里定义用户列表！

#all.children.pg-meta.vars.pg_users: # 省略上级缩进
  - name: dbuser_app
    password: DBUser.App
    pgbouncer: true
    roles: [dbrole_readwrite]
    comment: application user for myapp

- name: dbuser_app
  password: NewSecretPassword     # 修改密码

- name: dbuser_app
  superuser: false           # 超级用户（谨慎使用！）
  createdb: true             # 允许创建数据库
  createrole: false          # 允许创建角色
  inherit: true              # 自动继承角色权限
  replication: false         # 允许流复制连接
  bypassrls: false           # 绕过行级安全策略
  connlimit: 50              # 限制连接数，-1 不限制

- name: temp_user
  expire_in: 30                   # 30 天后过期（相对时间）

- name: contractor_user
  expire_at: '2024-12-31'         # 指定日期过期（绝对时间）

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

- name: dbuser_app
  roles:
    - dbrole_readwrite                      # 简单形式：授予角色
    - { name: dbrole_admin, admin: true }   # 带 ADMIN OPTION（可以将此角色授予其他用户）
    - { name: pg_monitor, set: false }      # PG16+: 不允许 SET ROLE
    - { name: old_role, state: absent }     # 撤销角色成员关系

- name: dbuser_analyst
  parameters:
    work_mem: '256MB'
    statement_timeout: '5min'
    search_path: 'analytics,public'
    log_statement: DEFAULT        # 重置为默认值

- name: dbuser_app
  pgbouncer: true                 # 添加到连接池
  pool_mode: transaction          # 池化模式
  pool_connlimit: 50              # 用户最大连接数

pg_users:
  - name: dbuser_old
    state: absent

sudo -iu postgres psql -AXtwc "SELECT 1 FROM pg_roles WHERE rolname = 'dbuser_old';"
grep -n '^"dbuser_old"[[:space:]]' /etc/pgbouncer/userlist.txt

# 检查依赖关系（只读操作）
pg-drop-role dbuser_old --check

# 预览删除操作（不实际执行）
pg-drop-role dbuser_old --dry-run -v

# 确认近期备份、精确用户名与继任所有者后，才执行实际删除
pg-drop-role dbuser_old dbuser_new

# 仅当已明确同意终止连接时使用 --force
pg-drop-role dbuser_old dbuser_new --force

- name: dbuser_app
  password: DBUser.App
  pgbouncer: true
  roles: [dbrole_readwrite]
  comment: application user

- name: dbuser_readonly
  password: DBUser.Readonly
  pgbouncer: true
  roles: [dbrole_readonly]

- name: dbuser_admin
  password: DBUser.Admin
  pgbouncer: true
  pool_mode: session
  roles: [dbrole_admin]
  parameters:
    log_statement: 'all'

- name: temp_contractor
  password: TempPassword
  expire_in: 30
  roles: [dbrole_readonly]

- name: custom_role
  login: false
  comment: custom role for special permissions

- name: dbuser_special
  password: DBUser.Special
  pgbouncer: true
  roles:
    - dbrole_readwrite
    - { name: dbrole_admin, admin: true }
    - { name: pg_monitor, set: false }
    - { name: pg_execute_server_program, inherit: false }

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb,
       rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil
FROM pg_roles WHERE rolname NOT LIKE 'pg_%' ORDER BY rolname;

SELECT r.rolname AS member, g.rolname AS role, m.admin_option, m.set_option, m.inherit_option
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.member
JOIN pg_roles g ON g.oid = m.roleid
WHERE r.rolname = 'dbuser_app';

SELECT rolname, setconfig FROM pg_db_role_setting s
JOIN pg_roles r ON r.oid = s.setrole WHERE s.setdatabase = 0;

SELECT rolname, rolvaliduntil, rolvaliduntil - CURRENT_TIMESTAMP AS time_remaining
FROM pg_roles WHERE rolvaliduntil IS NOT NULL
  AND rolvaliduntil < CURRENT_TIMESTAMP + INTERVAL '30 days'
ORDER BY rolvaliduntil;

# 在目标集群当前主库上，交互修改数据库角色密码
sudo -iu postgres psql -d postgres
\password dbuser_dba       # 或 dbuser_monitor / replicator

# 管理员 dbuser_dba：PG 节点 .pgpass、Pgbouncer、Infra 管理端与 pgAdmin 文件
./pgsql.yml -l <cls> -t pg_pass,pgbouncer_user,pgbouncer_reload -e pg_reload=true
./infra.yml -l infra -t env_pgpass,env_pgscv,env_pgadmin

# 监控用户 dbuser_monitor：PG 节点 .pgpass、Pgbouncer、两个 Exporter 与 Grafana 数据源
./pgsql.yml -l <cls> -t pg_pass,pgbouncer_user,pgbouncer_reload,pg_exporter,pgbouncer_exporter,add_ds -e pg_reload=true
./infra.yml -l infra -t env_pgpass

# 复制用户 replicator：Patroni 配置、PG 节点 .pgpass 与 Infra .pgpass
./pgsql.yml -l <cls> -t pg_conf,pg_pass,patroni_reload -e pg_reload=true
./infra.yml -l infra -t env_pgpass

./pgsql.yml -l <cls> -t pg_conf,patroni_reload -e pg_reload=true
./infra.yml -l infra -t env_patroni