PG模板参数
Pigsty中关于定制Postgres模板的相关参数
PG Provision负责拉起一套全新的Postgres集群，而PG Template就负责在PG Provision的基础上，在这套全新的数据库集群中创建默认的对象，包括
- 基本角色：只读角色，读写角色、管理角色
- 基本用户：复制用户、超级用户、监控用户、管理用户
- 模板数据库中的默认权限
- 默认 模式
- 默认 扩展
- HBA黑白名单规则
参数概览
#------------------------------------------------------------------------------
# POSTGRES TEMPLATE
#------------------------------------------------------------------------------
pg_init
pg_replication_username
pg_replication_password
pg_monitor_username
pg_monitor_password
pg_admin_username
pg_admin_password
pg_default_roles
pg_default_privilegs
pg_default_schemas
pg_default_extensions
pg_offline_query
pg_hba_rules
pg_hba_rules_extra
pgbouncer_hba_rules
pgbouncer_hba_rules_extra
pg_users
pg_databases
默认参数
#------------------------------------------------------------------------------
# POSTGRES TEMPLATE
#------------------------------------------------------------------------------
# - template - #
pg_init: pg-init # init script for cluster template
# - system roles - #
pg_replication_username: replicator # system replication user
pg_replication_password: DBUser.Replicator # system replication password
pg_monitor_username: dbuser_monitor # system monitor user
pg_monitor_password: DBUser.Monitor # system monitor password
pg_admin_username: dbuser_admin # system admin user
pg_admin_password: DBUser.Admin # system admin password
# - default roles - #
pg_default_roles:
- username: dbrole_readonly # sample user:
options: NOLOGIN # role can not login
comment: role for readonly access # comment string
- username: dbrole_readwrite # sample user: one object for each user
options: NOLOGIN
comment: role for read-write access
groups: [ dbrole_readonly ] # read-write includes read-only access
- username: dbrole_admin # sample user: one object for each user
options: NOLOGIN BYPASSRLS # admin can bypass row level security
comment: role for object creation
groups: [dbrole_readwrite,pg_monitor,pg_signal_backend]
# NOTE: replicator, monitor, admin password are overwritten by separated config entry
- username: postgres # reset dbsu password to NULL (if dbsu is not postgres)
options: SUPERUSER LOGIN
comment: system superuser
- username: replicator
options: REPLICATION LOGIN
groups: [pg_monitor, dbrole_readonly]
comment: system replicator
- username: dbuser_monitor
options: LOGIN CONNECTION LIMIT 10
comment: system monitor user
groups: [pg_monitor, dbrole_readonly]
- username: dbuser_admin
options: LOGIN BYPASSRLS
comment: system admin user
groups: [dbrole_admin]
- username: dbuser_stats
password: DBUser.Stats
options: LOGIN
comment: business read-only user for statistics
groups: [dbrole_readonly]
# object created by dbsu and admin will have their privileges properly set
pg_default_privilegs:
- GRANT USAGE ON SCHEMAS TO dbrole_readonly
- GRANT SELECT ON TABLES TO dbrole_readonly
- GRANT SELECT ON SEQUENCES TO dbrole_readonly
- GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly
- GRANT INSERT, UPDATE, DELETE ON TABLES TO dbrole_readwrite
- GRANT USAGE, UPDATE ON SEQUENCES TO dbrole_readwrite
- GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dbrole_admin
- GRANT CREATE ON SCHEMAS TO dbrole_admin
- GRANT USAGE ON TYPES TO dbrole_admin
# schemas
pg_default_schemas: [monitor]
# extension
pg_default_extensions:
- { name: 'pg_stat_statements', schema: 'monitor' }
- { name: 'pgstattuple', schema: 'monitor' }
- { name: 'pg_qualstats', schema: 'monitor' }
- { name: 'pg_buffercache', schema: 'monitor' }
- { name: 'pageinspect', schema: 'monitor' }
- { name: 'pg_prewarm', schema: 'monitor' }
- { name: 'pg_visibility', schema: 'monitor' }
- { name: 'pg_freespacemap', schema: 'monitor' }
- { name: 'pg_repack', schema: 'monitor' }
- name: postgres_fdw
- name: file_fdw
- name: btree_gist
- name: btree_gin
- name: pg_trgm
- name: intagg
- name: intarray
# postgres host-based authentication rules
pg_hba_rules:
- title: allow meta node password access
role: common
rules:
- host all all 10.10.10.10/32 md5
- title: allow intranet admin password access
role: common
rules:
- host all +dbrole_admin 10.0.0.0/8 md5
- host all +dbrole_admin 172.16.0.0/12 md5
- host all +dbrole_admin 192.168.0.0/16 md5
- title: allow intranet password access
role: common
rules:
- host all all 10.0.0.0/8 md5
- host all all 172.16.0.0/12 md5
- host all all 192.168.0.0/16 md5
- title: allow local read-write access (local production user via pgbouncer)
role: common
rules:
- local all +dbrole_readwrite md5
- host all +dbrole_readwrite 127.0.0.1/32 md5
- title: allow read-only user (stats, personal) password directly access
role: replica
rules:
- local all +dbrole_readonly md5
- host all +dbrole_readonly 127.0.0.1/32 md5
pg_hba_rules_extra: []
# pgbouncer host-based authentication rules
pgbouncer_hba_rules:
- title: local password access
role: common
rules:
- local all all md5
- host all all 127.0.0.1/32 md5
- title: intranet password access
role: common
rules:
- host all all 10.0.0.0/8 md5
- host all all 172.16.0.0/12 md5
- host all all 192.168.0.0/16 md5
pgbouncer_hba_rules_extra: []
#------------------------------------------------------------------------------
# BUSINESS TEMPLATE
#------------------------------------------------------------------------------
# - business - #
# users that are ad hoc to each cluster
pg_users:
- username: dbuser_test
password: DBUser.Test
options: LOGIN NOINHERIT
comment: business read-write user
groups: [dbrole_readwrite]
pg_databases: # additional business database
- name: test # one object for each database
owner: dbuser_test
schemas: [monitor, public]
extensions: [{name: "postgis", schema: "public"}]
parameters:
search_path: 'yay,public,monitor' # set default search path
参数详解
pg_init
用于初始化数据库模板的Shell脚本位置
默认为pg-init，该脚本会被拷贝至/pg/bin/pg-init后执行
默认的pg-init非常简单，根据其他模板参数渲染好的/pg/tmp/pg-init-template.sql 会被应用至模板数据库template1与管理数据库postgres。您可以在这里添加或覆盖模板初始化逻辑。
# system default template
psql template1 -qAXwtf /pg/tmp/pg-init-template.sql
# make postgres same as templated database (optional)
psql postgres -qAXwtf /pg/tmp/pg-init-template.sql
pg_replication_username
用于执行PostgreSQL流复制的数据库用户名
默认为replicator
pg_replication_password
用于执行PostgreSQL流复制的数据库用户密码，必须使用明文
默认为DBUser.Replicator强烈建议修改
pg_monitor_username
用于执行PostgreSQL与Pgbouncer监控任务的数据库用户名
默认为dbuser_monitor
pg_monitor_password
用于执行PostgreSQL与Pgbouncer监控任务的数据库用户密码，必须使用明文
默认为DBUser.Monitor，强烈建议修改
pg_admin_username
用于执行PostgreSQL数据库管理任务（DDL变更）的数据库用户名
默认为dbuser_admin
pg_admin_password
用于执行PostgreSQL数据库管理任务（DDL变更）的数据库用户密码，必须使用明文
默认为DBUser.Admin，强烈建议修改
pg_default_roles
定义了PostgreSQL中默认的角色与用户
对象数组，每一个对象定义一个用户或角色。
密码是可选项，如果留空则不设置密码，可以使用MD5密文密码。
例如，以下代码定义了一个名为dbrole_admin的角色，属于几个权限组。
- username: dbrole_admin # sample user: one object for each user
options: NOLOGIN BYPASSRLS # admin can bypass row level security
comment: role for object creation
groups: [dbrole_readwrite,pg_monitor,pg_signal_backend]
Pigsty定义了由三个默认角色与四个默认用户组成的基本访问控制系统，更多内容，请参考访问控制。
pg_default_privilegs
定义数据库模板中的默认权限。
Pigsty的默认情况下，任何由{{ dbsu」}}与{{ pg_admin_username }}创建的对象都会具有以下默认权限：
pg_default_privilegs:
- GRANT USAGE ON SCHEMAS TO dbrole_readonly
- GRANT SELECT ON TABLES TO dbrole_readonly
- GRANT SELECT ON SEQUENCES TO dbrole_readonly
- GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly
- GRANT INSERT, UPDATE, DELETE ON TABLES TO dbrole_readwrite
- GRANT USAGE, UPDATE ON SEQUENCES TO dbrole_readwrite
- GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dbrole_admin
- GRANT CREATE ON SCHEMAS TO dbrole_admin
- GRANT USAGE ON TYPES TO dbrole_admin
更多内容，请参考访问控制
pg_default_schemas
创建于模版数据库的默认模式
Pigsty默认会创建名为monitor的模式用于安装监控扩展。
pg_default_extensions
默认安装于模板数据库的扩展，对象数组。
如果没有指定schema字段，扩展会根据当前的search_path安装至对应模式中。
pg_default_extensions:
- { name: 'pg_stat_statements', schema: 'monitor' }
- { name: 'pgstattuple', schema: 'monitor' }
- { name: 'pg_qualstats', schema: 'monitor' }
- { name: 'pg_buffercache', schema: 'monitor' }
- { name: 'pageinspect', schema: 'monitor' }
- { name: 'pg_prewarm', schema: 'monitor' }
- { name: 'pg_visibility', schema: 'monitor' }
- { name: 'pg_freespacemap', schema: 'monitor' }
- { name: 'pg_repack', schema: 'monitor' }
- name: postgres_fdw
- name: file_fdw
- name: btree_gist
- name: btree_gin
- name: pg_trgm
- name: intagg
- name: intarray
pg_offline_query
实例级变量，布尔类型，默认为false。
设置为true时，无论当前实例的角色为何，用户组dbrole_offline都可以连接至该实例并执行离线查询。
对于实例数量较少（例如一主一从）的情况较为实用，用户可以将唯一的从库标记为pg_offline_query = true，从而接受ETL，慢查询与交互式访问。详情请参考访问控制模型中，离线用户一节。
pg_hba_rules
设置数据库的客户端IP黑白名单规则。对象数组，每一个对象都代表一条规则。
每一条规则由三部分组成：
title，规则标题，会转换为HBA文件中的注释role，应用角色，common代表应用至所有实例，其他取值（如replica,offline）则仅会安装至匹配的角色上。例如role='replica'代表这条规则只会应用到pg_role == 'replica'的实例上。rules，字符串数组，每一条记录代表一条最终写入pg_hba.conf的规则。
pg_hba_rules:
- title: allow meta node password access
role: common
rules:
- host all all 10.10.10.10/32 md5
- title: allow intranet admin password access
role: common
rules:
- host all +dbrole_admin 10.0.0.0/8 md5
- host all +dbrole_admin 172.16.0.0/12 md5
- host all +dbrole_admin 192.168.0.0/16 md5
- title: allow intranet password access
role: common
rules:
- host all all 10.0.0.0/8 md5
- host all all 172.16.0.0/12 md5
- host all all 192.168.0.0/16 md5
- title: allow local read-write access (local production user via pgbouncer)
role: common
rules:
- local all +dbrole_readwrite md5
- host all +dbrole_readwrite 127.0.0.1/32 md5
- title: allow read-only user (stats, personal) password directly access
role: replica
rules:
- local all +dbrole_readonly md5
- host all +dbrole_readonly 127.0.0.1/32 md5
建议在全局配置统一的pg_hba_rules，针对特定集群使用pg_hba_rules_extra进行额外定制。
pg_hba_rules_extra
与pg_hba_rules类似，但通常用于集群层面的HBA规则设置。
pg_hba_rules_extra 会以同样的方式追加至pg_hba.conf中。
如果您需要彻底复写集群的HBA规则，即，不想继承全局HBA配置，则应当在集群层面配置pg_hba_rules并覆盖全局配置。
pgbouncer_hba_rules
与pg_hba_rules类似，用于Pgbouncer的HBA规则设置。
默认的Pgbouncer HBA规则很简单，您可以按照自己的需求进行定制。
- 允许从本地使用密码登陆
- 允许从内网网断使用密码登陆
pgbouncer_hba_rules:
- title: local password access
role: common
rules:
- local all all md5
- host all all 127.0.0.1/32 md5
- title: intranet password access
role: common
rules:
- host all all 10.0.0.0/8 md5
- host all all 172.16.0.0/12 md5
- host all all 192.168.0.0/16 md5
pgbouncer_hba_rules_extra
与pg_hba_rules_extras类似，用于在集群层次对Pgbouncer的HBA规则进行额外配置。
业务模板
以下两个参数属于业务模板，用户应当在这里定义所需的业务用户与业务数据库。
在这里定义的用户与数据库，会在以下两个步骤中完成应用，不仅仅包括数据库中的用户与DB，还有Pgbouncer连接池中的对应配置。
./pgsql.yml --tags=pg_biz_init,pg_biz_pgbouncer
pg_users
对象数组，每个对象定义一个业务用户。
密码可以使用MD5密文密码
普通生产账号建议使用dbrole_readwrite角色，具有所有表的读写权限。
如果希望用户具有DDL变更权限，可以使用默认的dbrole_admin，默认具有创建模式，执行DDL变更的能力。
pg_users:
- username: dbuser_test
password: DBUser.Test
options: LOGIN NOINHERIT
comment: business read-write user
groups: [dbrole_readwrite]
pg_databases
对象数组，每个对象定义一个业务数据库。
-
name：数据库的名称
-
owner：数据库的属主用户，如果留空则为
postgres，且所有用户都可连接。如果指定了非空属主，则数据库属主将被配置为该用户。
所有用户对该数据库的
CONNECT权限都会被回收，数据库属主将被显式赋予数据库CONNECT权限并带有GRANT选项，可转授他人。 -
schemas：需要在该数据库中创建的默认模式列表
-
extensions：同
pg_default_extensions，仅在该数据库中创建的扩展。 -
parameters：针对数据库进行的参数修改，通过
ALTER DATABASE执行。 -
baseline：一个本地SQL文件的路径，如果存在，将被拷贝至
/pg/tmp/pg-init-database-{{ name }}并用作业务数据库的基线定义。
pg_databases: # additional business database
- name: test # one object for each database
owner: dbuser_test
schemas: [monitor, public]
extensions: [{name: "postgis", schema: "public"}]
baseline: '/path/to/baseline.sql'
parameters:
search_path: 'yay,public,monitor' # set default search path
最后修改 2021-02-20: update config doc to v0.6 (835a0cf)