Customize business users
可以通过 pg_users 定制集群特定的业务用户。该配置项通常用于在数据库集群层面定义业务用户，与 pg_default_roles 采用相同的形式。
样例
一个完整的用户定义由一个JSON/YAML对象构成，如下所示：
# 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
说明
一个用户对象由以下键值构成，只有用户名是必选项，其他参数均为可选，不添加相应键则会使用默认值。
-
name(string)： 用户名称，必选项 -
password(string)： 用户的密码，可以是以md5,sha开头的密文密码。 -
login(bool)：用户是否可以登录，默认为真；如果这里是业务角色，应当将其设置为假。 -
superuser(bool)： 用户是否具有超级用户权限，默认为假 -
createdb(bool)： 用户是否具有创建数据库的权限，默认没有 -
createrole(bool)： 用户是否具有创建新角色的权限，默认没有。 -
inherit(bool)： 用户是否继承其角色的权限？默认继承 -
replication(bool)： 用户是否具有复制权限？默认没有 -
bypassrls(bool)： 用户是否可以绕过行级安全策略？默认不行 -
connlimit(number)： 是否限制用户的连接数量？留空或-1不限，默认不限 -
expire_at(date)： 用户过期时间，默认不过期 -
expire_in(number)： 自创建n天后用户将过期，如果设置将覆盖expire_at -
roles(string[])： 用户所属的角色/用户组 -
pgbouncer(bool)： 是否将用户加入连接池用户列表中？默认不加入，通过连接池访问的生产用户应当显式设置此项为真，交互式个人用户/ETL用户应当设置未假或留空。 -
parameters(dict)： 针对用户修改配置参数，k-v结构 -
comment(string)： 用户备注说明信息
Pigsty建议采用dbuser_ 与 dbrole_ 的前缀区分用户与角色，用户的login选项应当设置为true以允许登录，角色的login选项应当设置为false以拒绝登录。
pg_users 与 pg_default_roles 都是 user 对象构成的数组，两者会依照定义顺序依次创建，因此后创建的用户可以属于先前创建的角色。
实现
pg_default_roles 中的用户会渲染为集群主库上的单个SQL文件：
/pg/tmp/pg-init-roles.sql
pg_users 中的用户会渲染为集群主库上的SQL文件，每个用户一个：
/pg/tmp/pg-db-{{ database.name }}.sql
并依次执行。一个实际渲染的例子如下所示：
----------------------------------------------------------------------
-- File : pg-user-dbuser_meta.sql
-- Path : /pg/tmp/pg-user-dbuser_meta.sql
-- Time : 2021-03-22 22:52
-- Note : managed by ansible, DO NOT CHANGE
-- Desc : creation sql script for user dbuser_meta
----------------------------------------------------------------------
--==================================================================--
-- EXECUTION --
--==================================================================--
-- run as dbsu (postgres by default)
-- createuser -w -p 5432 'dbuser_meta';
-- psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbuser_meta.sql
--==================================================================--
-- CREATE USER --
--==================================================================--
CREATE USER "dbuser_meta" ;
--==================================================================--
-- ALTER USER --
--==================================================================--
-- options
ALTER USER "dbuser_meta" ;
-- password
ALTER USER "dbuser_meta" PASSWORD 'DBUser.Meta';
-- expire
-- expire at 2022-03-22 in 365 days since 2021-03-22
ALTER USER "dbuser_meta" VALID UNTIL '2022-03-22';
-- conn limit
-- remove conn limit
-- ALTER USER "dbuser_meta" CONNECTION LIMIT -1;
-- parameters
ALTER USER "dbuser_meta" SET search_path = public;
-- comment
COMMENT ON ROLE "dbuser_meta" IS 'test user';
--==================================================================--
-- GRANT ROLE --
--==================================================================--
GRANT "dbrole_readwrite" TO "dbuser_meta";
--==================================================================--
-- PGBOUNCER USER --
--==================================================================--
-- user will not be added to pgbouncer user list by default,
-- unless pgbouncer is explicitly set to 'true', which means production user
-- User 'dbuser_meta' will be added to /etc/pgbouncer/userlist.txt via
-- /pg/bin/pgbouncer-create-user 'dbuser_meta' 'DBUser.Meta'
--==================================================================--
连接池
Pgbouncer有自己的用户定义文件，通常是PG用户的一个子集。
在Pigsty中，Pgbouncer的用户定义文件位于：/etc/pgbouncer/userlist.txt
$ cat userlist.txt
"postgres" ""
"dbuser_monitor" "md57bbcca538453edba8be026725c530b05"
只有在该文件中出现的用户，才可以通过PGbouncer访问数据库。
只有pgbouncer选项显式配置为true的用户，会被添加至连接池用户列表中。
修改该配置文件需要reload Pgbouncer方可生效。
导出
以下SQL查询可以使用JSON格式导出数据库中的用户（但需要少量修正）
SELECT row_to_json(u) FROM
(SELECT r.rolname AS name,
a.rolpassword AS password,
r.rolcanlogin AS login,
r.rolsuper AS superuser,
r.rolcreatedb AS createdb,
r.rolcreaterole AS createrole,
r.rolinherit AS inherit,
r.rolreplication AS replication,
r.rolbypassrls AS bypassrls,
r.rolconnlimit AS connlimit,
r.rolvaliduntil AS expire_at,
setconfig AS parameters,
ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as roles,
pg_catalog.shobj_description(r.oid, 'pg_authid') AS comment
FROM pg_catalog.pg_roles r
LEFT JOIN pg_db_role_setting rs ON r.oid = rs.setrole
LEFT JOIN pg_authid a ON r.oid = a.oid
WHERE r.rolname !~ '^pg_'
ORDER BY 1) u;
创建
请尽可能通过声明的方式创建业务用户与业务数据库，而不是在数据库中手工创建。因为业务用户与业务数据库需要同时在数据库与连接池中进行变更。详情请参考：创建业务用户
在运行中的数据库集群中创建新的业务用户，首先应在集群级配置中添加新用户的定义，例如在pg-test.vars.pg_users加入新的用户对象。然后可以使用pgsql-createuser剧本创建用户：
例如，在pg-test 集群中创建或修改名为dbuser_test的用户，可以执行以下命令。
./pgsql-createuser.yml -l <pg_cluster> -e pg_user=dbuser_test
如果dbuser_test的定义不存在，则会在检查阶段报错。