pg-meta:hosts:{10.10.10.10:{pg_seq:1, pg_role:primary } }vars:pg_cluster:pg-metapg_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 }
lc_ctype：数据库默认的 LOCALE，默认与实例设置相同，建议不要修改或设置，必须与模板数据库一致。建议配置为 C 或 en_US.UTF8。
allowconn：是否允许连接至数据库，默认为 true，不建议修改。
revokeconn：是否回收连接至数据库的权限？默认为 false。如果为 true，则数据库上的 PUBLIC CONNECT 权限会被回收。只有默认用户（dbsu|monitor|admin|replicator|owner）可以连接。此外，admin|owner 会拥有 GRANT OPTION，可以赋予其他用户连接权限。
tablespace：数据库关联的表空间，默认为 pg_default。
connlimit：数据库连接数限制，默认为 -1，即没有限制。
extensions：对象数组，每一个对象定义了一个数据库中的 扩展，以及其安装的 模式。
parameters：KV 对象，每一个 KV 定义了一个需要针对数据库通过 ALTER DATABASE 修改的参数。
pg_default_hba_rules:# postgres 全局默认的HBA规则，按 order 排序- {user:'${dbsu}',db:all ,addr:local ,auth:ident ,title:'dbsu access via local os user ident' ,order:100}- {user:'${dbsu}',db:replication ,addr:local ,auth:ident ,title:'dbsu replication from local os ident' ,order:150}- {user:'${repl}',db:replication ,addr:localhost ,auth:pwd ,title:'replicator replication from localhost',order:200}- {user:'${repl}',db:replication ,addr:intra ,auth:pwd ,title:'replicator replication from intranet' ,order:250}- {user:'${repl}',db:postgres ,addr:intra ,auth:pwd ,title:'replicator postgres db from intranet' ,order:300}- {user:'${monitor}',db:all ,addr:localhost ,auth:pwd ,title:'monitor from localhost with password' ,order:350}- {user:'${monitor}',db:all ,addr:infra ,auth:pwd ,title:'monitor from infra host with password',order:400}- {user:'${admin}',db:all ,addr:infra ,auth:ssl ,title:'admin @ infra nodes with pwd & ssl' ,order:450}- {user:'${admin}',db:all ,addr:world ,auth:ssl ,title:'admin @ everywhere with ssl & pwd' ,order:500}- {user:'+dbrole_readonly',db:all ,addr:localhost ,auth:pwd ,title:'pgbouncer read/write via local socket',order:550}- {user:'+dbrole_readonly',db:all ,addr:intra ,auth:pwd ,title:'read/write biz user via password' ,order:600}- {user:'+dbrole_offline' ,db:all ,addr:intra ,auth:pwd ,title:'allow etl offline tasks from intranet',order:650}pgb_default_hba_rules:# pgbouncer 全局默认的HBA规则，按 order 排序- {user:'${dbsu}',db:pgbouncer ,addr:local ,auth:peer ,title:'dbsu local admin access with os ident',order:100}- {user:'all' ,db:all ,addr:localhost ,auth:pwd ,title:'allow all user local access with pwd' ,order:150}- {user:'${monitor}',db:pgbouncer ,addr:intra ,auth:pwd ,title:'monitor access via intranet with pwd' ,order:200}- {user:'${monitor}',db:all ,addr:world ,auth:deny ,title:'reject all other monitor access addr' ,order:250}- {user:'${admin}',db:all ,addr:intra ,auth:pwd ,title:'admin access via intranet with pwd' ,order:300}- {user:'${admin}',db:all ,addr:world ,auth:deny ,title:'reject all other admin access addr' ,order:350}- {user:'all' ,db:all ,addr:intra ,auth:pwd ,title:'allow all user intra access with pwd' ,order:400}
#==============================================================## File : pg_hba.conf# Desc : Postgres HBA Rules for pg-meta-1 [primary]# Time : 2023-01-11 15:19# Host : pg-meta-1 @ 10.10.10.10:5432# Path : /pg/data/pg_hba.conf# Note : ANSIBLE MANAGED, DO NOT CHANGE!# Author : Ruohang Feng ([email protected])# License : Apache-2.0#==============================================================## addr alias# local : /var/run/postgresql# admin : 10.10.10.10# infra : 10.10.10.10# intra : 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16# user alias# dbsu : postgres# repl : replicator# monitor : dbuser_monitor# admin : dbuser_dba# dbsu access via local os user ident [default]local all postgres ident# dbsu replication from local os ident [default]local replication postgres ident# replicator replication from localhost [default]local replication replicator scram-sha-256host replication replicator 127.0.0.1/32 scram-sha-256# replicator replication from intranet [default]host replication replicator 10.0.0.0/8 scram-sha-256host replication replicator 172.16.0.0/12 scram-sha-256host replication replicator 192.168.0.0/16 scram-sha-256# replicator postgres db from intranet [default]host postgres replicator 10.0.0.0/8 scram-sha-256host postgres replicator 172.16.0.0/12 scram-sha-256host postgres replicator 192.168.0.0/16 scram-sha-256# monitor from localhost with password [default]local all dbuser_monitor scram-sha-256host all dbuser_monitor 127.0.0.1/32 scram-sha-256# monitor from infra host with password [default]host all dbuser_monitor 10.10.10.10/32 scram-sha-256# admin @ infra nodes with pwd & ssl [default]hostssl all dbuser_dba 10.10.10.10/32 scram-sha-256# admin @ everywhere with ssl & pwd [default]hostssl all dbuser_dba 0.0.0.0/0 scram-sha-256# pgbouncer read/write via local socket [default]local all +dbrole_readonly scram-sha-256host all +dbrole_readonly 127.0.0.1/32 scram-sha-256# read/write biz user via password [default]host all +dbrole_readonly 10.0.0.0/8 scram-sha-256host all +dbrole_readonly 172.16.0.0/12 scram-sha-256host all +dbrole_readonly 192.168.0.0/16 scram-sha-256# allow etl offline tasks from intranet [default]host all +dbrole_offline 10.0.0.0/8 scram-sha-256host all +dbrole_offline 172.16.0.0/12 scram-sha-256host all +dbrole_offline 192.168.0.0/16 scram-sha-256# allow application database intranet access [common] [DISABLED]#host kong dbuser_kong 10.0.0.0/8 md5#host bytebase dbuser_bytebase 10.0.0.0/8 md5#host grafana dbuser_grafana 10.0.0.0/8 md5
示例：渲染 pgb_hba.conf
#==============================================================## File : pgb_hba.conf# Desc : Pgbouncer HBA Rules for pg-meta-1 [primary]# Time : 2023-01-11 15:28# Host : pg-meta-1 @ 10.10.10.10:5432# Path : /etc/pgbouncer/pgb_hba.conf# Note : ANSIBLE MANAGED, DO NOT CHANGE!# Author : Ruohang Feng ([email protected])# License : Apache-2.0#==============================================================## PGBOUNCER HBA RULES FOR pg-meta-1 @ 10.10.10.10:6432# ansible managed: 2023-01-11 14:30:58# addr alias# local : /var/run/postgresql# admin : 10.10.10.10# infra : 10.10.10.10# intra : 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16# user alias# dbsu : postgres# repl : replicator# monitor : dbuser_monitor# admin : dbuser_dba# dbsu local admin access with os ident [default]local pgbouncer postgres peer# allow all user local access with pwd [default]local all all scram-sha-256host all all 127.0.0.1/32 scram-sha-256# monitor access via intranet with pwd [default]host pgbouncer dbuser_monitor 10.0.0.0/8 scram-sha-256host pgbouncer dbuser_monitor 172.16.0.0/12 scram-sha-256host pgbouncer dbuser_monitor 192.168.0.0/16 scram-sha-256# reject all other monitor access addr [default]host all dbuser_monitor 0.0.0.0/0 reject# admin access via intranet with pwd [default]host all dbuser_dba 10.0.0.0/8 scram-sha-256host all dbuser_dba 172.16.0.0/12 scram-sha-256host all dbuser_dba 192.168.0.0/16 scram-sha-256# reject all other admin access addr [default]host all dbuser_dba 0.0.0.0/0 reject# allow all user intra access with pwd [default]host all all 10.0.0.0/8 scram-sha-256host all all 172.16.0.0/12 scram-sha-256host all all 192.168.0.0/16 scram-sha-256
pg_default_hba_rules:# postgres host-based auth rules by default, order by `order`- {user:'${dbsu}',db:all ,addr:local ,auth:ident ,title:'dbsu access via local os user ident' ,order:100}- {user:'${dbsu}',db:replication ,addr:local ,auth:ident ,title:'dbsu replication from local os ident' ,order:150}- {user:'${repl}',db:replication ,addr:localhost ,auth:ssl ,title:'replicator replication from localhost',order:200}- {user:'${repl}',db:replication ,addr:intra ,auth:ssl ,title:'replicator replication from intranet' ,order:250}- {user:'${repl}',db:postgres ,addr:intra ,auth:ssl ,title:'replicator postgres db from intranet' ,order:300}- {user:'${monitor}',db:all ,addr:localhost ,auth:pwd ,title:'monitor from localhost with password' ,order:350}- {user:'${monitor}',db:all ,addr:infra ,auth:ssl ,title:'monitor from infra host with password',order:400}- {user:'${admin}',db:all ,addr:infra ,auth:ssl ,title:'admin @ infra nodes with pwd & ssl' ,order:450}- {user:'${admin}',db:all ,addr:world ,auth:cert ,title:'admin @ everywhere with ssl & cert' ,order:500}- {user:'+dbrole_readonly',db:all ,addr:localhost ,auth:ssl ,title:'pgbouncer read/write via local socket',order:550}- {user:'+dbrole_readonly',db:all ,addr:intra ,auth:ssl ,title:'read/write biz user via password' ,order:600}- {user:'+dbrole_offline' ,db:all ,addr:intra ,auth:ssl ,title:'allow etl offline tasks from intranet',order:650}pgb_default_hba_rules:# pgbouncer host-based authentication rules, order by `order`- {user:'${dbsu}',db:pgbouncer ,addr:local ,auth:peer ,title:'dbsu local admin access with os ident',order:100}- {user:'all' ,db:all ,addr:localhost ,auth:pwd ,title:'allow all user local access with pwd' ,order:150}- {user:'${monitor}',db:pgbouncer ,addr:intra ,auth:ssl ,title:'monitor access via intranet with pwd' ,order:200}- {user:'${monitor}',db:all ,addr:world ,auth:deny ,title:'reject all other monitor access addr' ,order:250}- {user:'${admin}',db:all ,addr:intra ,auth:ssl ,title:'admin access via intranet with pwd' ,order:300}- {user:'${admin}',db:all ,addr:world ,auth:deny ,title:'reject all other admin access addr' ,order:350}- {user:'all' ,db:all ,addr:intra ,auth:ssl ,title:'allow all user intra access with pwd' ,order:400}
