PG模板

Pigsty中关于定制Postgres模板的相关参数

PG Provision负责拉起一套全新的Postgres集群,而PG Template负责在PG Provision的基础上,在这套全新的数据库集群中创建默认的对象,包括

  • 基本角色:只读角色,读写角色、管理角色
  • 基本用户:复制用户、超级用户、监控用户、管理用户
  • 模板数据库中的默认权限
  • 默认 模式
  • 默认 扩展
  • HBA黑白名单规则

关于定制数据库集群的更多信息,请参考 定制集群

参数概览

名称 类型 层级 说明
pg_init string G/C 自定义PG初始化脚本
pg_replication_username string G PG复制用户
pg_replication_password string G PG复制用户的密码
pg_monitor_username string G PG监控用户
pg_monitor_password string G PG监控用户密码
pg_admin_username string G PG管理用户
pg_admin_password string G PG管理用户密码
pg_default_roles role[] G 默认创建的角色与用户
pg_default_privilegs string[] G 数据库默认权限配置
pg_default_schemas string[] G 默认创建的模式
pg_default_extensions extension[] G 默认安装的扩展
pg_offline_query string I 是否允许离线查询
pg_reload bool A 是否重载数据库配置(HBA)
pg_hba_rules rule[] G 全局HBA规则
pg_hba_rules_extra rule[] C/I 集群/实例特定的HBA规则
pgbouncer_hba_rules rule[] G/C Pgbouncer全局HBA规则
pgbouncer_hba_rules_extra rule[] G/C Pgbounce特定HBA规则
pg_databases database[] C 业务数据库定义
pg_users user[] C 业务用户定义

默认参数

#------------------------------------------------------------------------------
# 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 - #
# chekc http://pigsty.cc/zh/docs/concepts/provision/acl/ for more detail
pg_default_roles:

  # common production readonly user
  - name: dbrole_readonly                 # production read-only roles
    login: false
    comment: role for global readonly access

  # common production read-write user
  - name: dbrole_readwrite                # production read-write roles
    login: false
    roles: [dbrole_readonly]             # read-write includes read-only access
    comment: role for global read-write access

  # offline have same privileges as readonly, but with limited hba access on offline instance only
  # for the purpose of running slow queries, interactive queries and perform ETL tasks
  - name: dbrole_offline
    login: false
    comment: role for restricted read-only access (offline instance)

  # admin have the privileges to issue DDL changes
  - name: dbrole_admin
    login: false
    bypassrls: true
    comment: role for object creation
    roles: [dbrole_readwrite,pg_monitor,pg_signal_backend]

  # dbsu, name is designated by `pg_dbsu`. It's not recommend to set password for dbsu
  - name: postgres
    superuser: true
    comment: system superuser

  # default replication user, name is designated by `pg_replication_username`, and password is set by `pg_replication_password`
  - name: replicator
    replication: true
    roles: [pg_monitor, dbrole_readonly]
    comment: system replicator

  # default replication user, name is designated by `pg_monitor_username`, and password is set by `pg_monitor_password`
  - name: dbuser_monitor
    connlimit: 16
    comment: system monitor user
    roles: [pg_monitor, dbrole_readonly]

  # default admin user, name is designated by `pg_admin_username`, and password is set by `pg_admin_password`
  - name: dbuser_admin
    bypassrls: true
    comment: system admin user
    roles: [dbrole_admin]

  # default stats user, for ETL and slow queries
  - name: dbuser_stats
    password: DBUser.Stats
    comment: business offline user for offline queries and ETL
    roles: [dbrole_offline]


# - privileges - #
# object created by dbsu and admin will have their privileges properly set
pg_default_privileges:
  - 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 USAGE                         ON SCHEMAS   TO dbrole_offline
  - GRANT SELECT                        ON TABLES    TO dbrole_offline
  - GRANT SELECT                        ON SEQUENCES TO dbrole_offline
  - GRANT EXECUTE                       ON FUNCTIONS TO dbrole_offline
  - 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

# - schemas - #
pg_default_schemas: [monitor]                 # default schemas to be created

# - extension - #
pg_default_extensions:                        # default extensions to be created
  - { 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

# - hba - #
pg_offline_query: false                       # set to true to enable offline query on instance
pg_hba_rules:                                 # postgres host-based authentication 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 (local production user via pgbouncer)
    role: common
    rules:
      - local   all     +dbrole_readonly                                md5
      - host    all     +dbrole_readonly           127.0.0.1/32         md5

  - title: allow offline query (ETL,SAGA,Interactive) on offline instance
    role: offline
    rules:
      - host    all     +dbrole_offline               10.0.0.0/8        md5
      - host    all     +dbrole_offline               172.16.0.0/12     md5
      - host    all     +dbrole_offline               192.168.0.0/16    md5

pg_hba_rules_extra: []                        # extra hba rules (for cluster/instance overwrite)

pgbouncer_hba_rules:                          # pgbouncer host-based authentication 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: []                 # extra pgbouncer hba rules (for cluster/instance overwrite)

参数详解

pg_init

用于初始化数据库模板的Shell脚本位置,默认为pg-init,该脚本会被拷贝至/pg/bin/pg-init后执行。

默认的pg-init 只是预渲染SQL命令的包装:

# system default roles
psql postgres -qAXwtf /pg/tmp/pg-init-roles.sql

# 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-init脚本中添加自己的集群初始化逻辑。

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中默认的角色与用户,形式为对象数组,每一个对象定义一个用户或角色。

每一个用户或角色必须指定 name ,其余字段均为可选项。

  • password是可选项,如果留空则不设置密码,可以使用MD5密文密码。

  • login, superuser, createdb, createrole, inherit, replication, bypassrls 都是布尔类型,用于设置用户属性。如果不设置,则采用系统默认值。

  • 用户通过CREATE USER创建,所以默认具有login属性,如果创建的是角色,需要指定login: false

  • expire_atexpire_in用于控制用户过期时间,expire_at使用形如YYYY-mm-DD的日期时间戳。expire_in使用从现在开始的过期天数,如果expire_in存在则会覆盖expire_at选项。

  • 新用户默认不会添加至Pgbouncer用户列表中,必须显式定义pgbouncer: true,该用户才会被加入到Pgbouncer用户列表。

  • 用户/角色会按顺序创建,后面定义的用户可以属于前面定义的角色。

pg_users:
  # complete example of user/role definition for production user
  - name: dbuser_meta               # example production user have read-write access
    password: DBUser.Meta           # example user's password, can be encrypted
    login: true                     # can login, true by default (should be false for role)
    superuser: false                # is superuser? false by default
    createdb: false                 # can create database? false by default
    createrole: false               # can create role? false by default
    inherit: true                   # can this role use inherited privileges?
    replication: false              # can this role do replication? false by default
    bypassrls: false                # can this role bypass row level security? false by default
    connlimit: -1                   # connection limit, -1 disable limit
    expire_at: '2030-12-31'         # 'timestamp' when this role is expired
    expire_in: 365                  # now + n days when this role is expired (OVERWRITE expire_at)
    roles: [dbrole_readwrite]       # dborole_admin|dbrole_readwrite|dbrole_readonly|dbrole_offline
    pgbouncer: true                 # add this user to pgbouncer? false by default (true for production user)
    parameters:                     # user's default search path
      search_path: public
    comment: test user

Pigsty定义了由四个默认角色与四个默认用户组成的基本访问控制系统,详细信息请参考 访问控制

pg_default_privileges

定义数据库模板中的默认权限。

任何由{{ dbsu」}}{{ pg_admin_username }}创建的对象都会具有以下默认权限:

pg_default_privileges:
  - 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 USAGE                         ON SCHEMAS   TO dbrole_offline
  - GRANT SELECT                        ON TABLES    TO dbrole_offline
  - GRANT SELECT                        ON SEQUENCES TO dbrole_offline
  - GRANT EXECUTE                       ON FUNCTIONS TO dbrole_offline
  - 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

详细信息请参考 访问控制

pg_default_schemas

创建于模版数据库的默认模式

Pigsty默认会创建名为monitor的模式用于安装监控扩展。

pg_default_schemas: [monitor]                 # default schemas to be created

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_reload

命令行参数,布尔类型,默认为true

设置为true时,Pigsty会在生成HBA规则后立刻执行pg_ctl reload应用。

当您希望生成pg_hba.conf文件,并手工比较后再应用生效时,可以指定-e pg_reload=false来禁用它。

pg_hba_rules

设置数据库的客户端IP黑白名单规则。对象数组,每一个对象都代表一条规则。

每一条规则由三部分组成:

  • title,规则标题,会转换为HBA文件中的注释
  • role,应用角色,common代表应用至所有实例,其他取值(如replica, offline)则仅会安装至匹配的角色上。例如role='replica'代表这条规则只会应用到pg_role == 'replica' 的实例上。
  • rules,字符串数组,每一条记录代表一条最终写入pg_hba.conf的规则。

作为一个特例,role == 'offline' 的HBA规则,还会额外安装至 pg_offline_query == true 的实例上。

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规则较为宽松:

  1. 允许从本地使用密码登陆
  2. 允许从内网网断使用密码登陆
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

通常用于在数据库集群层面定义业务用户,与 pg_default_roles 采用相同的形式。

对象数组,每个对象定义一个业务用户。用户名name字段为必选项,密码可以使用MD5密文密码

用户可以通过roles字段为业务用户添加默认权限组:

  • dbrole_readonly:默认生产只读用户,具有全局只读权限。(只读生产访问)
  • dbrole_offline:默认离线只读用户,在特定实例上具有只读权限。(离线查询,个人账号,ETL)
  • dbrole_readwrite:默认生产读写用户,具有全局CRUD权限。(常规生产使用)
  • dbrole_admin:默认生产管理用户,具有执行DDL变更的权限。(管理员)

应当为生产账号配置 pgbouncer: true,允许其通过连接池访问,普通用户不应当通过连接池访问数据库。

下面是一个创建业务账号的例子:

pg_users:
  # complete example of user/role definition for production user
  - name: dbuser_meta               # example production user have read-write access
    password: DBUser.Meta           # example user's password, can be encrypted
    login: true                     # can login, true by default (should be false for role)
    superuser: false                # is superuser? false by default
    createdb: false                 # can create database? false by default
    createrole: false               # can create role? false by default
    inherit: true                   # can this role use inherited privileges?
    replication: false              # can this role do replication? false by default
    bypassrls: false                # can this role bypass row level security? false by default
    connlimit: -1                   # connection limit, -1 disable limit
    expire_at: '2030-12-31'         # 'timestamp' when this role is expired
    expire_in: 365                  # now + n days when this role is expired (OVERWRITE expire_at)
    roles: [dbrole_readwrite]       # dborole_admin|dbrole_readwrite|dbrole_readonly
    pgbouncer: true                 # add this user to pgbouncer? false by default (true for production user)
    parameters:                     # user's default search path
      search_path: public
    comment: test user

  # simple example for personal user definition
  - name: dbuser_vonng2              # personal user example which only have limited access to offline instance
    password: DBUser.Vonng          # or instance with explict mark `pg_offline_query = true`
    roles: [dbrole_offline]         # personal/stats/ETL user should be grant with dbrole_offline
    expire_in: 365                  # expire in 365 days since creation
    pgbouncer: false                # personal user should NOT be allowed to login with pgbouncer
    comment: example personal user for interactive queries

pg_databases

对象数组,每个对象定义一个业务数据库。每个数据库定义中,数据库名称 name 为必选项,其余均为可选项。

  • name:数据库名称,必选项
  • owner:数据库属主,默认为postgres
  • template:数据库创建时使用的模板,默认为template1
  • encoding:数据库默认字符编码,默认为UTF8,默认与实例保持一致。建议不要配置与修改。
  • locale:数据库默认的本地化规则,默认为C,建议不要配置,与实例保持一致。
  • lc_collate:数据库默认的本地化字符串排序规则,默认与实例设置相同,建议不要修改,必须与模板数据库一致。强烈建议不要配置,或配置为C
  • 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修改的参数。
  • pgbouncer:布尔选项,是否将该数据库加入到Pgbouncer中。所有数据库都会加入至Pgbouncer,除非显式指定pgbouncer: false
  • comment:数据库备注信息。
pg_databases:
  - name: meta                      # name is the only required field for a database
    owner: postgres                 # optional, database owner
    template: template1             # optional, template1 by default
    encoding: UTF8                  # optional, UTF8 by default
    locale: C                       # optional, C by default
    lc_collate: C                   # optional, C by default , must same as template database, leave blank to set to db default
    lc_ctype: C                     # optional, C by default , must same as template database, leave blank to set to db default
    allowconn: true                 # optional, true by default, false disable connect at all
    revokeconn: false               # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
    tablespace: pg_default          # optional, 'pg_default' is the default tablespace
    connlimit: -1                   # optional, connection limit, -1 or none disable limit (default)
    extensions:                     # optional, extension name and where to create
      - {name: postgis, schema: public}
    parameters:                     # optional, extra parameters with ALTER DATABASE
      enable_partitionwise_join: true
    pgbouncer: true                 # optional, add this database to pgbouncer list? true by default
    comment: pigsty meta database   # optional, comment string for database
最后修改 2021-03-18: update docs (b47aa4b)