pg_libs: 'passwordcheck, pg_stat_statements, auto_explain'

pg_hba_rules:
  - {user: all, db: all, addr: world, auth: ssl}

pg_hba_rules:
  - {user: secure_user, db: all, addr: world, auth: cert}

patroni_ssl_enabled: true

pgbouncer_sslmode: require

pg_hba_rules:
  - user: '<用户>'       # 用户名或角色，支持 + 前缀表示角色成员
    db: '<数据库>'       # 数据库名，all 表示所有
    addr: '<地址>'       # 来源地址，支持别名
    auth: '<方法>'       # 认证方法
    title: '<说明>'      # 规则说明（可选）

pg_default_hba_rules:
  # dbsu 本地免密访问
  - {user: '${dbsu}', db: all, addr: local, auth: ident}
  - {user: '${dbsu}', db: replication, addr: local, auth: ident}

  # 复制用户
  - {user: '${repl}', db: replication, addr: localhost, auth: pwd}
  - {user: '${repl}', db: replication, addr: intra, auth: pwd}

  # 监控用户
  - {user: '${monitor}', db: all, addr: localhost, auth: pwd}
  - {user: '${monitor}', db: all, addr: infra, auth: pwd}

  # 管理员（强制 SSL）
  - {user: '${admin}', db: all, addr: infra, auth: ssl}
  - {user: '${admin}', db: all, addr: world, auth: ssl}

  # 业务用户
  - {user: '+dbrole_readonly', db: all, addr: localhost, auth: pwd}
  - {user: '+dbrole_readonly', db: all, addr: intra, auth: pwd}
  - {user: '+dbrole_offline', db: all, addr: intra, auth: pwd}

pg_pwd_enc: scram-sha-256

pg_users:
  # 明文密码（部署时自动加密）
  - name: dbuser_app
    password: 'MySecurePassword123!'

  # 已加密的哈希值
  - name: dbuser_app
    password: 'SCRAM-SHA-256$4096:xxx...'

pg_libs: 'passwordcheck, pg_stat_statements, auto_explain'

pg_extensions:
  - credcheck

pg_users:
  - name: temp_user
    password: 'xxx'
    expire_in: 30      # 30 天后过期

  - name: contract_user
    password: 'xxx'
    expire_at: '2024-12-31'  # 指定日期过期

# 使用 Pigsty 提供的脚本签发证书
./cert.yml -e cn=dbuser_app

pg_hba_rules:
  - user: dbuser_app
    db: all
    addr: world
    auth: cert
    title: 'App user with certificate'

psql "host=pg-test port=5432 dbname=app \
  sslmode=verify-full \
  sslcert=dbuser_app.crt \
  sslkey=dbuser_app.key \
  sslrootcert=ca.crt"

pg_hba_rules:
  # 需要证书 + 密码
  - user: secure_user
    db: all
    addr: world
    auth: cert
    # 在 PostgreSQL 中还需要配置 clientcert=verify-full

# /etc/fail2ban/jail.local
[postgresql]
enabled = true
filter = postgresql
action = iptables[name=PostgreSQL, port=5432, protocol=tcp]
logpath = /pg/log/postgres/postgresql-*.log
maxretry = 5
bantime = 3600

# 查看当前 HBA 规则
psql -c "SELECT * FROM pg_hba_file_rules;"

# 查看用户信息
psql -c "SELECT usename, valuntil FROM pg_user;"

# 测试连接
psql "host=xxx port=5432 dbname=xxx user=xxx" -c "SELECT 1;"

pg_users:
  - name: dbuser_app
    password: 'AppUserPassword123!'
    roles: [dbrole_readwrite]  # 继承读写权限
    pgbouncer: true             # 添加到连接池
    comment: 'Application user'

pg_users:
  - name: dbuser_readonly
    password: 'ReadOnlyPassword123!'
    roles: [dbrole_readonly]
    pgbouncer: true
    comment: 'Read-only user for reporting'

pg_users:
  - name: dbuser_admin
    password: 'AdminPassword123!'
    roles: [dbrole_admin]
    pgbouncer: true
    comment: 'Business admin user'

pg_databases:
  - name: secret_db
    owner: dbuser_secret
    revokeconn: true  # 只有 owner 和管理员可连接

pg_databases:
  - name: app1_db
    owner: dbuser_app1
    revokeconn: true

  - name: app2_db
    owner: dbuser_app2
    revokeconn: true

pg_users:
  - name: dbuser_app1
    roles: [dbrole_admin]

  - name: dbuser_app2
    roles: [dbrole_admin]

pg_users:
  # 业务角色（不可登录）
  - name: role_app1
    login: false
    roles: [dbrole_readwrite]

  # 实际用户继承角色
  - name: user_app1_prod
    roles: [role_app1]
    pgbouncer: true

  - name: user_app1_dev
    roles: [role_app1]
    pgbouncer: true

-- 创建表
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT,
  amount DECIMAL
);

-- 启用 RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 创建策略：用户只能看到自己的订单
CREATE POLICY user_orders ON orders
  FOR ALL
  USING (user_id = current_setting('app.user_id')::INT);

pg_users:
  - name: app_user
    bypassrls: false  # 不允许绕过 RLS
    roles: [dbrole_readwrite]

# 将您的 CA 文件放入指定目录
cp your-ca.crt files/pki/ca/ca.crt
cp your-ca.key files/pki/ca/ca.key

# 设置参数防止覆盖
# pigsty.yml
ca_method: copy

ssl: 'on'
ssl_cert_file: '/pg/cert/server.crt'
ssl_key_file: '/pg/cert/server.key'
ssl_ca_file: '/pg/cert/ca.crt'

# 使用 SSL 连接
psql "host=pg-test port=5432 dbname=test sslmode=require"

# 验证服务器证书
psql "host=pg-test port=5432 dbname=test sslmode=verify-full sslrootcert=ca.crt"

patroni_ssl_enabled: true

pgbouncer_sslmode: require

# 查看 CA 证书
openssl x509 -text -noout -in /etc/pki/ca.crt

# 查看服务器证书
openssl x509 -text -noout -in /pg/cert/server.crt

cert_validity: 3650d  # 证书有效期（10年）

# 签发证书，CN 设为用户名
./cert.yml -e cn=dbuser_app

# 更新 PostgreSQL 集群证书
./pgsql.yml -t pg_cert

# 重启 PostgreSQL 加载新证书
pg_ctl reload -D /pg/data

# Linux/macOS
psql "... sslrootcert=/path/to/ca.crt"

# 或设置环境变量
export PGSSLROOTCERT=/path/to/ca.crt

# 密码策略
pg_libs: 'passwordcheck, pg_stat_statements, auto_explain'

# 强制 SSL
pg_default_hba_rules:
  - {user: all, db: all, addr: world, auth: ssl}

# 用户有效期
pg_users:
  - name: dbuser_app
    password: 'xxx'
    expire_in: 90  # 90天后过期

# 审计日志
pg_parameters:
  log_statement: all
  log_connections: 'on'
  log_disconnections: 'on'

# 启用 pgaudit
pg_extensions:
  - pgaudit

# 同步复制（零数据丢失）
pg_conf: crit.yml
pg_rpo: 0

# 备份加密
pgbackrest_method: minio
pgbackrest_repo:
  minio:
    cipher_type: aes-256-cbc
    cipher_pass: 'YourSecurePassword!'

# Patroni SSL
patroni_ssl_enabled: true

# Pgbouncer SSL
pgbouncer_sslmode: require

# 检查 SSL 配置
psql -c "SHOW ssl;"

# 检查密码加密方式
psql -c "SHOW password_encryption;"

# 检查 HBA 规则
psql -c "SELECT * FROM pg_hba_file_rules;"

# 检查用户过期时间
psql -c "SELECT usename, valuntil FROM pg_user WHERE valuntil IS NOT NULL;"

# 检查数据校验和
psql -c "SHOW data_checksums;"