pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: postgis, schema: public}, {name: timescaledb}]}
      - { name: grafana  ,owner: dbuser_grafana  ,revokeconn: true ,comment: grafana primary database }
      - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
      - { name: kong     ,owner: dbuser_kong     ,revokeconn: true ,comment: kong the api gateway database }
      - { name: gitea    ,owner: dbuser_gitea    ,revokeconn: true ,comment: gitea meta database }
      - { name: wiki     ,owner: dbuser_wiki     ,revokeconn: true ,comment: wiki meta database }
      - { name: noco     ,owner: dbuser_noco     ,revokeconn: true ,comment: nocodb database }

bin/pgsql-db <cls> <dbname>    # 等效于：pgsql-db.yml -l <cls> -e dbname=<dbname>

pg-meta:
  vars:
    pg_databases:
      - name: myapp
        owner: dbuser_myapp
        schemas: [app]
        extensions:
          - { name: pg_trgm }
          - { name: btree_gin }
        comment: my application database

bin/pgsql-db pg-meta myapp

bin/pgsql-db <cls> <dbname>    # 幂等操作，可重复执行

- name: myapp
  owner: dbuser_new_owner     # 修改为新属主

bin/pgsql-db pg-meta myapp

ALTER DATABASE "myapp" OWNER TO "dbuser_new_owner";
GRANT ALL PRIVILEGES ON DATABASE "myapp" TO "dbuser_new_owner";

- name: myapp
  connlimit: 100              # 限制最大 100 个连接

ALTER DATABASE "myapp" CONNECTION LIMIT 100;

- name: myapp
  owner: dbuser_myapp
  revokeconn: true            # 回收 PUBLIC 的 CONNECT 权限

REVOKE CONNECT ON DATABASE "myapp" FROM PUBLIC;
GRANT CONNECT ON DATABASE "myapp" TO "replicator";
GRANT CONNECT ON DATABASE "myapp" TO "dbuser_monitor";
GRANT CONNECT ON DATABASE "myapp" TO "dbuser_dba" WITH GRANT OPTION;
GRANT CONNECT ON DATABASE "myapp" TO "dbuser_myapp" WITH GRANT OPTION;

- name: myapp
  revokeconn: false           # 恢复 PUBLIC 的 CONNECT 权限

GRANT CONNECT ON DATABASE "myapp" TO PUBLIC;

- name: app_template
  is_template: true           # 允许任何有 CREATEDB 权限的用户克隆

ALTER DATABASE "app_template" IS_TEMPLATE true;

- name: myapp
  parameters:
    work_mem: '256MB'
    maintenance_work_mem: '512MB'
    statement_timeout: '30s'
    search_path: 'app,public'

ALTER DATABASE "myapp" SET "work_mem" = '256MB';
ALTER DATABASE "myapp" SET "maintenance_work_mem" = '512MB';
ALTER DATABASE "myapp" SET "statement_timeout" = '30s';
ALTER DATABASE "myapp" SET "search_path" = 'app,public';

- name: myapp
  parameters:
    work_mem: DEFAULT         # 重置为 PostgreSQL 默认值
    statement_timeout: DEFAULT

ALTER DATABASE "myapp" SET "work_mem" = DEFAULT;
ALTER DATABASE "myapp" SET "statement_timeout" = DEFAULT;

- name: myapp
  schemas:
    # 简单形式：只指定模式名
    - app
    - api

    # 完整形式：指定属主
    - { name: core, owner: dbuser_myapp }

CREATE SCHEMA IF NOT EXISTS "app";
CREATE SCHEMA IF NOT EXISTS "api";
CREATE SCHEMA IF NOT EXISTS "core" AUTHORIZATION "dbuser_myapp";

- name: multi_tenant_db
  owner: dbuser_admin
  schemas:
    - { name: tenant_a, owner: dbuser_tenant_a }
    - { name: tenant_b, owner: dbuser_tenant_b }
    - { name: shared, owner: dbuser_admin }

- name: myapp
  schemas:
    - { name: deprecated_schema, state: absent }

DROP SCHEMA IF EXISTS "deprecated_schema" CASCADE;

- name: myapp
  extensions:
    # 简单形式：只指定扩展名
    - postgis
    - pg_trgm

    # 完整形式：指定 schema 和版本
    - { name: vector, schema: public }
    - { name: pg_stat_statements, schema: monitor, version: '1.10' }

CREATE EXTENSION IF NOT EXISTS "postgis" CASCADE;
CREATE EXTENSION IF NOT EXISTS "pg_trgm" CASCADE;
CREATE EXTENSION IF NOT EXISTS "vector" WITH SCHEMA "public" CASCADE;
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "monitor" VERSION '1.10' CASCADE;

- name: myapp
  extensions:
    - { name: pg_trgm, state: absent }    # 卸载扩展
    - { name: postgis }                    # 保留扩展

DROP EXTENSION IF EXISTS "pg_trgm" CASCADE;
CREATE EXTENSION IF NOT EXISTS "postgis" CASCADE;

pg_databases:
  - name: olddb
    state: absent

bin/pgsql-db <cls> olddb

pg_databases:
  - name: testdb
    state: recreate
    owner: dbuser_test
    baseline: test_init.sql    # 重建后执行初始化

bin/pgsql-db <cls> testdb

pg_databases:
  # 1. 首先定义模板数据库
  - name: app_template
    owner: dbuser_app
    schemas: [core, api]
    extensions: [postgis, pg_trgm]
    baseline: app_schema.sql

  # 2. 使用模板创建业务数据库
  - name: app_prod
    template: app_template
    owner: dbuser_app

- name: app_staging
  template: app_template
  strategy: FILE_COPY        # 或 WAL_LOG
  owner: dbuser_app

- name: new_db
  template: existing_db      # 使用现有业务数据库作为模板
  owner: dbuser_app

- name: shared_template
  is_template: true          # 允许任何有 CREATEDB 权限的用户克隆
  owner: dbuser_app

- name: myapp_icu
  template: template0        # 必须使用 template0
  locale_provider: icu
  icu_locale: en-US
  encoding: UTF8

- name: myapp
  pgbouncer: true              # 是否加入连接池（默认 true）
  pool_mode: transaction       # 池化模式：transaction/session/statement
  pool_size: 64                # 默认池大小
  pool_size_min: 0             # 最小池大小
  pool_reserve: 32             # 保留连接数
  pool_connlimit: 100          # 最大数据库连接
  pool_auth_user: dbuser_meta  # 认证查询用户

myapp                       = host=/var/run/postgresql pool_mode=transaction pool_size=64

- name: internal_db
  pgbouncer: false           # 不加入连接池

- name: myapp_icu
  template: template0        # ICU 必须使用 template0
  locale_provider: icu
  icu_locale: en-US          # ICU 本地化规则
  encoding: UTF8

- name: myapp_builtin
  template: template0
  locale_provider: builtin
  builtin_locale: C.UTF-8    # 内置本地化规则
  encoding: UTF8

- name: myapp_custom_icu
  template: template0
  locale_provider: icu
  icu_locale: en-US
  icu_rules: '&V << w <<< W'  # 自定义 ICU 排序规则

# 创建基本数据库
- name: myapp
  owner: dbuser_myapp
  comment: my application database

# 创建带扩展的数据库
- name: geodata
  owner: dbuser_geo
  extensions: [postgis, postgis_topology]

# 限制连接的私有数据库
- name: secure_db
  owner: dbuser_secure
  revokeconn: true
  connlimit: 10

# 设置数据库级参数
- name: analytics
  owner: dbuser_analyst
  parameters:
    work_mem: '512MB'
    statement_timeout: '5min'

# 使用 ICU 本地化
- name: i18n_db
  template: template0
  locale_provider: icu
  icu_locale: zh-Hans
  encoding: UTF8

# 删除数据库
- name: old_db
  state: absent

# 重建数据库
- name: test_db
  state: recreate
  baseline: test_init.sql

# 刷新整个集群的 HBA 规则
bin/pgsql-hba pg-meta

# 刷新特定实例（多个 IP 空格分隔）
bin/pgsql-hba pg-meta 10.10.10.10
bin/pgsql-hba pg-meta 10.10.10.11 10.10.10.12

# 查看脚本帮助
bin/pgsql-hba --help

./pgsql.yml -l <cluster> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload

# 刷新 PostgreSQL HBA 并重载
./pgsql.yml -l pg-meta -t pg_hba,pg_reload

# 刷新 Pgbouncer HBA 并重载
./pgsql.yml -l pg-meta -t pgbouncer_hba,pgbouncer_reload

# 同时刷新两者
./pgsql.yml -l pg-meta -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload

# 使用额外变量强制重载
./pgsql.yml -l pg-meta -e pg_reload=true -t pg_hba,pg_reload

# 使用 psql 查看 PostgreSQL HBA 规则
psql -c "TABLE pg_hba_file_rules"

# 或者直接查看配置文件
cat /pg/data/pg_hba.conf

# 查看 Pgbouncer HBA 规则
cat /etc/pgbouncer/pgb_hba.conf

# PostgreSQL 重载配置（会验证语法）
psql -c "SELECT pg_reload_conf()"

# 如果有语法错误，查看日志
tail -f /pg/log/postgresql-*.log

# 测试特定用户从特定地址的连接
psql -h <host> -p 5432 -U <user> -d <database> -c "SELECT 1"

# 查看连接被哪条 HBA 规则匹配
psql -c "SELECT * FROM pg_hba_file_rules WHERE database @> ARRAY['<dbname>']::text[]"

pg-meta:
  vars:
    pg_hba_rules:
      - {user: new_user, db: new_db, addr: '192.168.1.0/24', auth: pwd, title: 'new app access'}

bin/pgsql-hba pg-meta

pg_hba_rules:
  - {user: all, db: all, addr: '10.1.1.100/32', auth: deny, order: 0, title: 'emergency block'}

bin/pgsql-hba pg-meta

pg_hba_rules:
  # 仅主库允许写入用户
  - {user: writer, db: all, addr: intra, auth: pwd, role: primary, title: 'writer on primary'}

  # 从库允许只读用户
  - {user: reader, db: all, addr: world, auth: ssl, role: replica, title: 'reader on replica'}

# 扩容新实例
./pgsql.yml -l 10.10.10.14

# 刷新所有实例的 HBA（包含新成员 IP）
bin/pgsql-hba pg-meta

# 更新配置文件中的角色后刷新
bin/pgsql-hba pg-meta

psql -c "TABLE pg_hba_file_rules"

psql -c "SELECT pg_reload_conf()"

head -20 /pg/data/pg_hba.conf

# 直接编辑（不推荐）
vi /pg/data/pg_hba.conf

# 重载配置
psql -c "SELECT pg_reload_conf()"
# 或
pg_ctl reload -D /pg/data
# 或
systemctl reload postgresql

# 仅刷新 Pgbouncer HBA
./pgsql.yml -l pg-meta -t pgbouncer_hba,pgbouncer_reload

# 或使用统一脚本（同时刷新 PostgreSQL 和 Pgbouncer）
bin/pgsql-hba pg-meta

cat /etc/pgbouncer/pgb_hba.conf

# 刷新 HBA（推荐）
bin/pgsql-hba <cluster>

# 查看 PostgreSQL HBA
psql -c "TABLE pg_hba_file_rules"
cat /pg/data/pg_hba.conf

# 查看 Pgbouncer HBA
cat /etc/pgbouncer/pgb_hba.conf

# 重载 PostgreSQL 配置
psql -c "SELECT pg_reload_conf()"

# 测试连接
psql -h <host> -U <user> -d <db> -c "SELECT 1"

# 查看认证失败日志
tail -f /pg/log/postgresql-*.log | grep -i auth

bin/pgsql-add   <cls>                   # 创建 pgsql 集群 <cls>
bin/pgsql-user  <cls> <username>        # 在 <cls> 上创建 pg 用户 <username>
bin/pgsql-db    <cls> <dbname>          # 在 <cls> 上创建 pg 数据库 <dbname>
bin/pgsql-svc   <cls> [...ip]           # 重新加载集群 <cls> 的 pg 服务
bin/pgsql-hba   <cls> [...ip]           # 重新加载集群 <cls> 的 postgres/pgbouncer HBA 规则
bin/pgsql-add   <cls> [...ip]           # 为集群 <cls> 添加从库副本
bin/pgsql-rm    <cls> [...ip]           # 从集群 <cls> 移除实例
bin/pgsql-rm    <cls>                   # 删除 pgsql 集群 <cls>

pg list        <cls>                    # 打印集群信息
pg edit-config <cls>                    # 编辑集群配置
pg reload      <cls> [ins]              # 重新加载集群配置
pg restart     <cls> [ins]              # 重启 PostgreSQL 集群
pg reinit      <cls> [ins]              # 重新初始化集群成员
pg pause       <cls>                    # 进入维护模式（自动故障转移暂停）
pg resume      <cls>                    # 退出维护模式
pg switchover  <cls>                    # 在集群 <cls> 上进行主动主从切换（主库健康）
pg failover    <cls>                    # 在集群 <cls> 上进行故障转移（主库故障）

pb info                                 # 打印 pgbackrest 备份仓库信息
pg-backup                               # 进行备份，默认进行增量备份，如果没有完整备份过就做全量备份
pg-backup full                          # 进行全量备份
pg-backup diff                          # 进行差异备份
pg-backup incr                          # 进行增量备份
pg-pitr -i                              # 恢复到最近备份完成的时间（不常用）
pg-pitr --time="2022-12-30 14:44:44+08" # 恢复到特定时间点（如在删除数据库或表的情况下）
pg-pitr --name="my-restore-point"       # 恢复到由 pg_create_restore_point 创建的命名还原点
pg-pitr --lsn="0/7C82CB8" -X            # 恢复到 LSN 之前
pg-pitr --xid="1234567" -X -P           # 恢复到特定的事务ID之前，然后将其提升为主库
pg-pitr --backup=latest                 # 恢复到最新的备份集
pg-pitr --backup=20221108-105325        # 恢复到特定的备份集，使用名称指定，可以使用 pgbackrest info 进行检查

systemctl stop patroni                  # 启动 停止 重启 重载
systemctl stop pgbouncer                # 启动 停止 重启 重载
systemctl stop pg_exporter              # 启动 停止 重启 重载
systemctl stop pgbouncer_exporter       # 启动 停止 重启 重载
systemctl stop node_exporter            # 启动 停止 重启
systemctl stop haproxy                  # 启动 停止 重启 重载
systemctl stop vip-manager              # 启动 停止 重启 重载
systemctl stop postgres                 # 仅当 patroni_mode == 'remove' 时使用这个服务

bin/node-add <cls>                # 为集群 <cls> 初始化节点                  # ./node.yml  -l <cls> 
bin/pgsql-add <cls>               # 初始化集群 <cls> 的pgsql实例             # ./pgsql.yml -l <cls>

bin/pgsql-user <cls> <username>   # ./pgsql-user.yml -l <cls> -e username=<username>

bin/pgsql-user <cls> <username>   # ./pgsql-user.yml -l <cls> -e username=<username>

bin/pgsql-db <cls> <dbname>       # ./pgsql-db.yml -l <cls> -e dbname=<dbname>

bin/pgsql-svc <cls>               # pgsql.yml -l <cls> -t pg_service -e pg_reload=true
bin/pgsql-svc <cls> [ip...]       # pgsql.yml -l ip... -t pg_service -e pg_reload=true

bin/pgsql-hba <cls>               # pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true
bin/pgsql-hba <cls> [ip...]       # pgsql.yml -l ip... -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true

pg edit-config <cls>              # interactive config a cluster with patronictl

bin/node-add <ip>                 # 将节点 <ip> 纳入 Pigsty 管理                
bin/pgsql-add <cls> <ip>          # 初始化 <ip> ，作为集群 <cls> 的新从库

bin/pgsql-rm <cls> <ip...>        # ./pgsql-rm.yml -l <ip>

bin/pgsql-rm <cls>                # ./pgsql-rm.yml -l <cls>

pg switchover <cls>   # 交互模式，您可以使用下面的参数组合直接跳过此交互向导
pg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test

bin/pgsql-svc <cls>
bin/pgsql-hba <cls>

pg-backup       # 执行备份，如有必要，执行增量或全量备份
pg-backup full  # 执行全量备份
pg-backup diff  # 执行差异备份
pg-backup incr  # 执行增量备份
pb info         # 打印备份信息 （pgbackrest info）

pg-pitr -i                              # 恢复到最近备份完成的时间（不常用）
pg-pitr --time="2022-12-30 14:44:44+08" # 恢复到指定的时间点（在删除数据库或表的情况下使用）
pg-pitr --name="my-restore-point"       # 恢复到使用 pg_create_restore_point 创建的命名恢复点
pg-pitr --lsn="0/7C82CB8" -X            # 在LSN之前立即恢复
pg-pitr --xid="1234567" -X -P           # 在指定的事务ID之前立即恢复，然后将集群直接提升为主库
pg-pitr --backup=latest                 # 恢复到最新的备份集
pg-pitr --backup=20221108-105325        # 恢复到特定备份集，备份集可以使用 pgbackrest info 列出

ansible pg-test -b -m package -a "name=pg_cron_15,topn_15,pg_stat_monitor_15*"  # 使用 ansible 安装一些包

./pgsql.yml -t pg_extension     # 安装扩展

ansible <cls> -b -a "yum upgrade/downgrade -y <pkg>"    # 升级/降级软件包
pg restart --force <cls>                                # 重启集群

./pgsql.yml -t pg_pkg -e pg_version=15                         # 安装pg 15的包
sudo su - postgres; mkdir -p /data/postgres/pg-meta-15/data/   # 为15准备目录
pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ -v -c # 预检
pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ --link -j8 -v -c
rm -rf /usr/pgsql; ln -s /usr/pgsql-15 /usr/pgsql;             # 修复二进制链接
mv /data/postgres/pg-meta-14 /data/postgres/pg-meta-15         # 重命名数据目录
rm -rf /pg; ln -s /data/postgres/pg-meta-15 /pg                # 修复数据目录链接

bin/node-add <cls>                # 为集群 <cls> 初始化节点                  # ./node.yml  -l <cls> 
bin/pgsql-add <cls>               # 初始化集群 <cls> 的pgsql实例             # ./pgsql.yml -l <cls>

bin/pgsql-user <cls> <username>   # ./pgsql-user.yml -l <cls> -e username=<username>

bin/pgsql-db <cls> <dbname>       # ./pgsql-db.yml -l <cls> -e dbname=<dbname>

bin/pgsql-svc <cls>               # pgsql.yml -l <cls> -t pg_service -e pg_reload=true
bin/pgsql-svc <cls> [ip...]       # pgsql.yml -l ip... -t pg_service -e pg_reload=true

bin/pgsql-hba <cls>               # pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true
bin/pgsql-hba <cls> [ip...]       # pgsql.yml -l ip... -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true

pg edit-config <cls>              # interactive config a cluster with patronictl

bin/node-add <ip>                 # 将节点 <ip> 纳入 Pigsty 管理                
bin/pgsql-add <cls> <ip>          # 初始化 <ip> ，作为集群 <cls> 的新从库

bin/pgsql-rm <cls> <ip...>        # ./pgsql-rm.yml -l <ip>

bin/pgsql-rm <cls>                # ./pgsql-rm.yml -l <cls>

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: postgis, schema: public}, {name: timescaledb}]}
      - { name: grafana  ,owner: dbuser_grafana  ,revokeconn: true ,comment: grafana primary database }
      - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }

bin/pgsql-user <cls> <username>    # 等效于：pgsql-user.yml -l <cls> -e username=<username>

pg-meta:
  vars:
    pg_users:
      - name: dbuser_app
        password: DBUser.App
        pgbouncer: true
        roles: [dbrole_readwrite]
        comment: application user for myapp

bin/pgsql-user pg-meta dbuser_app

bin/pgsql-user <cls> <username>    # 幂等操作，可重复执行

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

bin/pgsql-user pg-meta dbuser_app

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

- name: dbuser_app
  createdb: true                  # 允许创建数据库
  connlimit: 50                   # 限制连接数

ALTER USER "dbuser_app" CREATEDB;
ALTER USER "dbuser_app" CONNECTION LIMIT 50;

- name: dbuser_app
  comment: '应用主账号 - 已更新'

COMMENT ON ROLE "dbuser_app" IS '应用主账号 - 已更新';

pg_users:
  - name: dbuser_old
    state: absent

bin/pgsql-user <cls> dbuser_old

pg-drop-role <role_name> [successor_role] [options]

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

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

# 删除用户，转移对象给 postgres
pg-drop-role dbuser_old

# 删除用户，转移对象给指定用户
pg-drop-role dbuser_old dbuser_new

# 强制删除（终止活跃连接）
pg-drop-role dbuser_old --force

# 登录到数据库主机
ssh <host>

# 检查依赖关系
sudo -u postgres pg-drop-role dbuser_old --check

# 执行删除
sudo -u postgres pg-drop-role dbuser_old --force

-- 查看用户拥有的对象
SELECT n.nspname as schema, c.relname as name, c.relkind as type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_roles r ON r.oid = c.relowner
WHERE r.rolname = 'dbuser_old';

-- 转移对象所有权
REASSIGN OWNED BY dbuser_old TO postgres;

-- 删除用户拥有的权限
DROP OWNED BY dbuser_old;

-- 删除用户
DROP ROLE dbuser_old;

- 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_admin
  roles:
    # 普通授予
    - dbrole_readwrite

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

    # PG16+: 不能 SET ROLE 到此角色
    - { name: pg_monitor, set: false }

    # PG16+: 不自动继承此角色的权限
    - { name: pg_execute_server_program, inherit: false }

GRANT "dbrole_readwrite" TO "dbuser_admin";
GRANT "dbrole_admin" TO "dbuser_admin" WITH ADMIN TRUE;
GRANT "pg_monitor" TO "dbuser_admin";
REVOKE SET OPTION FOR "pg_monitor" FROM "dbuser_admin";
GRANT "pg_execute_server_program" TO "dbuser_admin";
REVOKE INHERIT OPTION FOR "pg_execute_server_program" FROM "dbuser_admin";

- name: dbuser_app
  roles:
    - dbrole_readwrite                      # 保留此角色
    - { name: old_role, state: absent }     # 撤销此角色

GRANT "dbrole_readwrite" TO "dbuser_app";
REVOKE "old_role" FROM "dbuser_app";

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

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';

- name: dbuser_app
  parameters:
    work_mem: DEFAULT             # 重置为默认值
    statement_timeout: DEFAULT

ALTER USER "dbuser_app" SET "work_mem" = DEFAULT;
ALTER USER "dbuser_app" SET "statement_timeout" = DEFAULT;

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

ALTER USER "temp_user" VALID UNTIL '2024-02-14';

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

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

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

ALTER USER "permanent_user" VALID UNTIL 'infinity';

SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolvaliduntil IS NOT NULL
ORDER BY rolvaliduntil;

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

"postgres" ""
"dbuser_app" "SCRAM-SHA-256$4096:xxx$yyy:zzz"
"dbuser_view" "SCRAM-SHA-256$4096:xxx$yyy:zzz"

dbuser_dba                  = pool_mode=session max_user_connections=16
dbuser_monitor              = pool_mode=session max_user_connections=8
dbuser_app                  = pool_mode=transaction max_user_connections=50

- name: dbuser_old
  state: absent                   # 删除用户时自动从连接池移除

- name: dbuser_internal
  pgbouncer: false                # 仅从连接池移除，保留用户

# 创建基本业务用户
- 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]

# 创建 DBA 用户（使用 session 模式）
- name: dbuser_dba
  password: DBUser.DBA
  pgbouncer: true
  pool_mode: session
  roles: [dbrole_admin]
  parameters:
    log_statement: 'all'

# 创建临时用户（30天后过期）
- name: temp_contractor
  password: TempPassword
  expire_in: 30
  roles: [dbrole_readonly]

# 创建角色（不可登录）
- name: custom_role
  login: false
  comment: custom role for special permissions

# 创建带高级角色选项的用户（PG16+）
- name: dbuser_admin
  password: DBUser.Admin
  pgbouncer: true
  roles:
    - dbrole_readwrite
    - { name: dbrole_admin, admin: true }
    - { name: pg_monitor, set: false }

# 删除用户
- name: dbuser_old
  state: absent

-- 查看所有用户
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;  -- 0 表示用户级设置

-- 查看用户拥有的对象
SELECT n.nspname AS schema, c.relname AS name,
       CASE c.relkind
         WHEN 'r' THEN 'table'
         WHEN 'v' THEN 'view'
         WHEN 'm' THEN 'materialized view'
         WHEN 'i' THEN 'index'
         WHEN 'S' THEN 'sequence'
         WHEN 'f' THEN 'foreign table'
       END AS type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_roles r ON r.oid = c.relowner
WHERE r.rolname = 'dbuser_app'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- 查看即将过期的用户
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;

{% raw %}
{% if pg_max_conn != 'auto' and pg_max_conn|int >= 20 %}{% set pg_max_connections = pg_max_conn|int %}{% else %}{% if pg_default_service_dest|default('postgres') == 'pgbouncer' %}{% set pg_max_connections = 500 %}{% else %}{% set pg_max_connections = 1000 %}{% endif %}{% endif %}
{% set pg_max_prepared_transactions = pg_max_connections if 'citus' in pg_libs else 0 %}
{% set pg_max_locks_per_transaction = (2 * pg_max_connections)|int if 'citus' in pg_libs or 'timescaledb' in pg_libs else pg_max_connections %}
{% set pg_shared_buffers = (node_mem_mb|int * pg_shared_buffer_ratio|float) | round(0, 'ceil') | int %}
{% set pg_maintenance_mem = (pg_shared_buffers|int * 0.25)|round(0, 'ceil')|int %}
{% set pg_effective_cache_size = node_mem_mb|int - pg_shared_buffers|int  %}
{% set pg_workmem =  ([ ([ (pg_shared_buffers / pg_max_connections)|round(0,'floor')|int , 64 ])|max|int , 1024])|min|int %}
{% endraw %}

parallel_setup_cost: 2000           # double from 100 to increase parallel cost
parallel_tuple_cost: 0.2            # double from 0.1 to increase parallel cost
min_parallel_table_scan_size: 16MB  # double from 8MB to increase parallel cost
min_parallel_index_scan_size: 1024  # double from 512 to increase parallel cost

{% raw %}
min_wal_size: {{ ([pg_size_twentieth, 200])|min }}GB                  # 1/20 disk size, max 200GB
max_wal_size: {{ ([pg_size_twentieth * 4, 2000])|min }}GB             # 2/10 disk size, max 2000GB
max_slot_wal_keep_size: {{ ([pg_size_twentieth * 6, 3000])|min }}GB   # 3/10 disk size, max 3000GB
temp_file_limit: {{ ([pg_size_twentieth, 200])|min }}GB               # 1/20 of disk size, max 200GB
{% endraw %}