PostgreSQL 业务用户

业务用户是由CREATE USER/ROLE创建的顶层对象，本文介绍如何定义与创建Pigsty中的业务用户

在PostgreSQL中，用户（User） 指的是数据库集簇中的一个对象，由SQL语句CREATE USER/ROLE所创建。

在PostgreSQL中，用户直接隶属于数据库集簇而非某个具体的数据库。因此在创建业务数据库和业务用户时，应当遵循”先用户，后数据库”的原则。

定义用户

Pigsty通过两个配置参数定义数据库集群中的角色与用户：

前者定义了整套环境中共有的角色，后者定义单个集群中特有的业务角色与用户。二者形式相同，均为用户定义对象数组。 下面是一个用户定义的例子：

  1. - name: dbuser_meta               # required, `name` is the only mandatory field of a user definition
  2.   password: md5d3d10d8cad606308bdb180148bf663e1  # md5 salted password of 'DBUser.Meta'
  3.   # optional, plain text and md5 password are both acceptable (prefixed with `md5`)
  4.   login: true                     # optional, can login, true by default  (new biz ROLE should be false)
  5.   superuser: false                # optional, is superuser? false by default
  6.   createdb: false                 # optional, can create database? false by default
  7.   createrole: false               # optional, can create role? false by default
  8.   inherit: true                   # optional, can this role use inherited privileges? true by default
  9.   replication: false              # optional, can this role do replication? false by default
  10.   bypassrls: false                # optional, can this role bypass row level security? false by default
  11.   pgbouncer: true                 # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
  12.   connlimit: -1                   # optional, user connection limit, default -1 disable limit
  13.   expire_in: 3650                 # optional, now + n days when this role is expired (OVERWRITE expire_at)
  14.   expire_at: '2030-12-31'         # optional, YYYY-MM-DD 'timestamp' when this role is expired  (OVERWRITTEN by expire_in)
  15.   comment: pigsty admin user      # optional, comment string for this user/role
  16.   roles: [dbrole_admin]           # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
  17.   parameters: {}                  # optional, role level parameters with `ALTER ROLE SET`
  18.   # search_path: public         # key value config parameters according to postgresql documentation (e.g: use pigsty as default search_path)
  • name : 每一个用户或角色必须指定 name，唯一的必选参数。
  • password : 是可选项，如果留空则不设置密码，可以使用MD5密文密码。
  • login, superuser, createdb, createrole, inherit, replication, bypassrls : 都是布尔类型标记，用于设置用户属性。如果不设置，则采用系统默认值。 其中pg_default_roles的用户默认不带有login属性，而pg_users默认带有login属性，可通过显式配置覆盖。
  • expire_at与expire_in用于控制用户过期时间，expire_at使用形如YYYY-mm-DD的日期时间戳。expire_in使用从现在开始的过期天数，如果expire_in存在则会覆盖expire_at选项。
  • pgbouncer: true 用于控制是否将新用户加入Pgbouncer用户列表中，该参数必须显式定义为true，相应用户才会被加入到Pgbouncer用户列表。
  • roles 为该角色/用户所属的分组，可以指定多个分组，例如为用户添加默认角色。

创建用户

在创建数据库集群（或主库实例）时，pg_default_roles 与 pg_users 定义的角色和用户会自动依序创建。

在运行中的已有数据库集群上，使用预制剧本 pgsql-createuser.yml 来创建新的业务数据库。

首先，您需要在相应数据库集群配置的 pg_users 配置项中添加该用户的定义。然后，使用以下命令即可在对应集群上创建该用户或角色。

  1. bin/createuser <pg_cluster> <username>    # <pg_cluster> 为集群名称，<user.name> 是新用户名。必须先定义，再执行脚本进行创建
  2. bin/createuser pg-meta dbuser_meta        # 例：在pg-meta集群中创建dbuser_meta用户
  3. ./pgsql-createuser.yml -l <pg_cluster> -e pg_user=<user.name>  # 该脚本实际上调用了以下Ansible剧本完成对应任务

当目标用户已经存在时，Pigsty会修改目标用户的属性使其符合配置。

如果被创建的用户带有pgbouncer: true标记，该剧本会同时修改并重载数据库集群内所有Pgbouncer的配置/etc/pgbouncer/userlist.txt。

务必通过预置剧本或脚本添加新业务用户与业务数据库，否则难以保证连接池配置信息与数据库同步

Pgbouncer中的用户

Pgbouncer的操作系统用户将与数据库超级用户保持一致，都使用{{ pg_dbsu }}，默认为postgres。 Pigsty默认使用Postgres管理用户作为Pgbouncer的管理用户，使用Postgres的监控用户同时作为Pgbouncer的监控用户。

Pgbouncer的用户列表通过/etc/pgbouncer/userlist.txt文件进行控制， Pgbouncer的用户权限通过/etc/pgbouncer/pgb_hba.conf进行控制。

只有显式添加pgbouncer: true配置条目的用户才会被加入到Pgbouncer用户列表中，并通过Pgbouncer访问数据库。 通常生产应用使用的账号应当通过Pgbouncer连接池访问数据库，而个人用户，管理，ETL等则应当直接访问数据库。

正常情况下请使用 pgsql-createuser.yml 剧本管理数据库用户。紧急情况下亦可在数据库实例上以postgres用户执行以下命令来手工添加用户，需要在集群中所有Pgbouncer上执行该命令并重新加载配置。

  1. # 紧急情况下可以使用该命令手工添加用户，用法：pgbouncer-create-user <username> [password]
  2. /pg/bin/pgbouncer-create-user
  4. pgbouncer-create-user dbp_vonng Test.Password # 明文密码         
  5. pgbouncer-create-user dbp_vonng md596bceae83ba2937778af09adf00ae738 # md5密码
  6. pgbouncer-create-user dbp_vonng auto          # 从数据库查询获取密码
  7. pgbouncer-create-user dbp_vonng null          # 使用空密码

最后修改 2022-05-27: init commit (1e3e284)