磐维数据库用户与权限管理篇（二）

引言

在磐维数据库用户与权限管理篇（一）中，我们了解了用户和角色的区别、权限的分类以及通过用户进行基本的权限管理，本文将围绕用户管理与系统权限深入展开。

用户创建

语法：

CREATE user user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [ EXPIRED ] | DISABLE };


在新建用户时，可选地赋予该用户系统权限以及相关属性：

where option can be:
{SYSADMIN | NOSYSADMIN}
| {MONADMIN | NOMONADMIN}
| {OPRADMIN | NOOPRADMIN}
| {POLADMIN | NOPOLADMIN}
| {AUDITADMIN | NOAUDITADMIN}
| {CREATEDB | NOCREATEDB}
| {USEFT | NOUSEFT}
| {CREATEROLE | NOCREATEROLE}
| {INHERIT | NOINHERIT}
| {LOGIN | NOLOGIN}
| {REPLICATION | NOREPLICATION}
| {INDEPENDENT | NOINDEPENDENT}
| {VCADMIN | NOVCADMIN}
| {PERSISTENCE | NOPERSISTENCE}
| CONNECTION LIMIT connlimit
| VALID BEGIN 'timestamp'
| VALID UNTIL 'timestamp'
| RESOURCE POOL 'respool'
| USER GROUP 'groupuser'
| PERM SPACE 'spacelimit'
| TEMP SPACE 'tmpspacelimit'
| SPILL SPACE 'spillspacelimit'
| NODE GROUP logic_cluster_name
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
| DEFAULT TABLESPACE tablespace_name
| PROFILE DEFAULT
| PROFILE profile_name
| PGUSER


参数	解释	备注
user_name	用户名称	字符串，要符合标识符的命名规范。且最大长度不超过63个字符
password	登录密码	当开启密码复杂度检测参数password_policy后，设置的密码需要满足复杂性要求，具体的要求可人为设定相关参数进行控制，如：password_min_length、password_min_special、password_min_digital
EXPIRED	使密码过期	只有在修改密码后才可正常执行相关sql语句
DISABLE	禁用用户密码	禁用某个用户的密码后，将从系统中删除该密码，无法连接数据库
[un]ENCRYPTED	密码加密存储	按照产品安全要求，密码必须加密存储，所以，UNENCRYPTED在内核层面禁止使用
[no]SYSADMIN	系统管理员权限	默认安装情况下具有与对象所有者相同的权限，但不包括dbe_perf模式的对象权限和使用Roach工具执行备份恢复的权限。三权分立关闭时，具有SYSADMIN属性的用户有权限创建具有SYSADMIN、REPLICATION、CREATEROLE、AUDITADMIN、MONADMIN、POLADMIN、CREATEDB属性的用户和普通用户。三权分立打开时，具有SYSADMIN属性的用户无权创建用户
[no]MONADMIN	监控管理员	具有查看dbe_perf模式下视图和函数的权限，亦可以对dbe_perf模式的对象权限进行授予或收回。
[no]OPRADMIN	运维管理员	具有使用Roach工具执行备份恢复的权限
[no]POLADMIN	安全策略管理员	具有创建资源标签、脱敏策略和统一审计策略的权限。
[no]AUDITADMIN	审计管理员	具有查看和删除审计日志的权限
[no]CREATEROLE	安全管理员	具有创建、修改、删除用户或角色的权限，有权限创建具有CREATEROLE、AUDITADMIN、MONADMIN、POLADMIN、CREATEDB属性的用户和普通用户
[no]CREATEDB	创建数据库权限	-
[no]LOGIN	连接或登录数据库的权限	具有LOGIN属性的角色才可以登录数据库
[no]REPLICATION	允许流复制或设置系统为备份模式	仅用于复制
[no]INDEPENDENT	私有角色	系统管理员不再拥有该角色创建对象的相关权限，必须通过该角色授权
PERSISTENCE	永久用户	-
CONNECTION LIMIT	该角色可以使用的并发连接数量	系统管理员不受此参数的限制
VALID BEGIN	角色生效的时间戳	-
VALID UNTIL	角色失效的时间戳	-
RESOURCE POOL	角色使用的resource pool名称	该名称属于系统表：pg_resource_pool
PERM SPACE	设置用户使用空间的大小	-
TEMP SPACE	设置用户临时表存储空间限额	-
SPILL SPACE	设置用户算子落盘空间限额	-
ROLE	指定子成员	拥有新角色所有的权限
ADMIN	指定子成员	拥有新角色所有的权限且可把新角色的权限赋给其他角色

tip: 上述表格未列举出来的其他参数实用性不强或者被弃用，无须特别关注。

三权分立

从前面的介绍可以看出，默认情况下拥有SYSADMIN属性的系统管理员，具备系统最高权限。在实际业务管理中为了避免权限过度集中带来的风险，对SYSADMIN权限进行拆分到三种管理员角色中，这就是三权分立（设置参数enableSeparationOfDuty为on开启，但不影响数据库初始sysadmin用户）：

• sysadmin：系统管理员，仅仅具有对owner为自身或者public模式下数据库对象的所有权
• auditadmin：审计管理员，具有查看和删除审计日志的权限
• createrole：安全管理员，具有创建、修改、删除用户或角色的权限

行级访问控制

通过行级访问控制（Row Level Security）策略，使不同用户执行相同的SQL查询操作，读取到的结果是不同的

# 开启表的行级访问控制
ALTER TABLE test1 ENABLE ROW LEVEL SECURITY;
# 设置行级访问策略
# 用户u1和u2执行相同的查询sql，只能读取到当前用户插入的行
CREATE ROW LEVEL SECURITY POLICY test1_rls ON test1 USING(role = CURRENT_USER);
select * from test1;


行级访问控制的目的是控制表中行级数据可见性，通过在数据表上预定义Filter，在查询优化阶段将满足条件的表达式应用到执行计划上，影响最终的执行结果。当前受影响的SQL语句包括SELECT、UPDATE、DELETE。

用户锁定与解锁

当用户连接数据库输入密码错误次数的次数达到指定的值后，用户进入lock状态，不允许继续连接数据库，相关的参数：

• failed_login_attempts：默认值为10，如果用户输入密码错误次数超过该值就会被锁定。
• password_lock_time：用户被锁定后自动解锁的时间，默认值为1天。

用户锁定与解锁参考：

ALTER USER u1 ACCOUNT LOCK;
ALTER USER u1 ACCOUNT UNLOCK;


用户账号有效期

在新建用户时，可以通过参数“valid begin”和“valid until”参数来控制用户账号有效期，如果到期可以对账号有效期调整：

create user u1 with valid begin '2024-06-26 16:00:00' valid until '2024-06-26 17:00:00' password 'Panwei@123';
alter user u1 with valid begin '2024-06-26 17:00:00' valid until '2025-06-26 17:00:00';


用户密码有效期

用户密码有效期是通过参数password_effect_time（单位为天）来控制，可通过如下sql查看用户密码有效期范围：

select pg_get_userbyid(roloid),max(passwordtime),max(passwordtime)+(select setting from pg_settings where name='password_effect_time')::interval as passwordexpiredtime from pg_catalog.pg_auth_history group by roloid order by 3;


对于密码即将过期的用户，需要及时地修改密码，以防业务中断等安全事故。
用户密码修改可通过高权角色操作也可当前用户操作：

# sysadmin、createrole
alter user u1 password 'XXXXXXXX';
# 当前用户
ALTER USER u1 IDENTIFIED BY "$$$$$$$$" REPLACE "XXXXXXXX";


用户密码安全策略

修改的密码时一般要求进行密码复杂度检测以及密码重用检测，通过以下参数控制：

• password_policy：是否开启密码复杂度检测
• password_min_length：密码长度下限
• password_max_length：密码长度上限
• password_min_special：密码中涉及的最少字符种类
• password_min_digital：密码中数字的最小位数
• password_min_lowercase：密码中小写字母的最小位数
• password_min_uppercase：密码中大写字母的最小位数
• password_reuse_max：密码解除重用限制的次数
• password_reuse_time：密码解除重用限制的时间

用户删除

删除用户并不是一个很困难的事情，但是如何合理安全地删除却是一个值得深究的事情。经过上述介绍，了解了用户往往承载着一系列的权限和owner为该角色的对象，在删除前需要考虑：

• 该用户是否曾级联授权给其他用户，是否会影响其他用户的权限
• 如果有owner为该角色的对象，是否能够级联删除（生产一般不允许），如何在不删除相关对象的情况下删除角色
• 业务是否仍在使用该用户，是否存在潜在安全隐患等

所以在生产环境中，删除用户属于高危操作，一定要谨慎对待，严禁使用“drop user xx cascade”，可分步处理owner为该角色的对象以及回收该用户的权限。

注意，如果一个用户在多个database中涉及了相关权限和owner为该角色的对象，cascade也无法删除，这里提供一种安全删除用户的步骤：

1. 通过pg_stat_activity视图查询当前是否存在使用该用户的业务，通过alter user xx password disable 禁用掉该用户，与业务沟通是否受到影响
2. 判断各个业务库下是否存在owner为该角色的对象（替换下面sql中的username为实际用户）

# 各个业务库都要执行
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 'L' THEN 'large sequence' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','L','f','e','o','')
AND pg_catalog.pg_get_userbyid(c.relowner) in ('username')
ORDER BY 1,2;


3. 如果存在owner为该角色的对象，可批量修改owner为其他用户

# 各个业务库都要执行
reassign owned by old_role to new_role;


4. 对象处理完成后回收权限

# 各个业务库都要执行
drop owned by old_role；


5. 最后删除用户

drop user old_role;