CREATE ROLE权限用户无法修改同样属性的用户的密码

在日常的工作中遇到一个问题：客户在使用管理数据库账号的用户下，修改其他账号的密码失败。这边优先检查了这个用户的权限，发现没有问题，并新建用户并修改其密码发现没有问题。最终发现是需要修改的用户也具有CREATE ROLE的权限，在回收其权限后果然没有问题了。

1.在测试前先确认现有用户

postgres=# \du
                                                                 角色列表
       角色名称       |                                                  属性                                                  | 成员属于 
----------------------+--------------------------------------------------------------------------------------------------------+----------
 aiuap                |                                                                                                        | {}
 omm                  | 系统管理员, 建立角色, 建立 DB, 复制, 安全管理员, 审计管理员, 监控管理员, 操作管理员, 策略管理员, UseFT | {}
 pw_read_all_settings | 无法登录, 监控管理员, 操作管理员, 策略管理员                                                           | {}
 pwadmin              | 系统管理员, 建立角色, 建立 DB, 复制, UseFT                                                             | {}
 pwaudit              | 建立角色, 审计管理员, UseFT                                                                            | {}
 pwsso                | 建立角色, 安全管理员                                                                                   | {}
 test_user            |                                                                                                        | {aiuap}

2.创建管理其他用户的角色

postgres=# create user test_cr with createrole password '9************';
CREATE ROLE

3.创建一个与其同权限的用户

postgres=#  create user test_1 with createrole password '9************';
CREATE ROLE

4.查看现有用户

postgres=# \du
                                                                 角色列表
       角色名称       |                                                  属性                                                  | 成员属于 
----------------------+--------------------------------------------------------------------------------------------------------+----------
 aiuap                |                                                                                                        | {}
 omm                  | 系统管理员, 建立角色, 建立 DB, 复制, 安全管理员, 审计管理员, 监控管理员, 操作管理员, 策略管理员, UseFT | {}
 pw_read_all_settings | 无法登录, 监控管理员, 操作管理员, 策略管理员                                                           | {}
 pwadmin              | 系统管理员, 建立角色, 建立 DB, 复制, UseFT                                                             | {}
 pwaudit              | 建立角色, 审计管理员, UseFT                                                                            | {}
 pwsso                | 建立角色, 安全管理员                                                                                   | {}
 test_1               | 建立角色                                                                                               | {}
 test_cr              | 建立角色                                                                                               | {}
 test_user            |                                                                                                        | {aiuap}

5.尝试使用管理用户test_cr修改test_1的密码

postgres=>  ALTER USER test_1 WITH PASSWORD '8************';
ERROR:  Permission denied.

可以看到会提示权限不允许，我们去数据库日志中看同样可以发现

2024-04-28 10:50:38.776 test_cr postgres [local] 47231547082496 0[0:0#0]  3377699720528179 [BACKEND] ERROR:  Permission denied.
2024-04-28 10:50:38.776 test_cr postgres [local] 47231547082496 0[0:0#0]  3377699720528179 [BACKEND] STATEMENT:  ALTER USER test_1 WITH PASSWORD '********';

6.切换到超户回收其CREATE ROLE权限

查看alter user 的语法

postgres=# \h alter user
命令：       ALTER USER
描述：       变更数据库角色
语法：
ALTER USER [ IF EXISTS ] user_name [ [ WITH ] option [ ... ] ];
ALTER USER user_name
    RENAME TO new_name;
ALTER USER user_name [ IN DATABASE database_name ]
    SET configuration_parameter {{ TO | = } { value | DEFAULT }|FROM CURRENT};
ALTER USER user_name
    [ IN DATABASE database_name ] RESET {configuration_parameter|ALL};

where option can be:
{CREATEDB | NOCREATEDB}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {AUDITADMIN | NOAUDITADMIN}
    | {SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {USEFT | NOUSEFT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [ EXPIRED ] | DISABLE | EXPIRED }
    | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | RESERVE SPACE 'reservespacelimit'
    | NODE GROUP logic_cluster_name
    | ACCOUNT { LOCK | UNLOCK }
    | PGUSER

回收其权限

postgres=# ALTER USER test_1  NOCREATEROLE;
ALTER ROLE

7.再次尝试使用test_cr用户修改其密码，可见成功

postgres=# \c postgres test_cr
用户 test_cr 的密码：
非SSL连接（安全性要求高时，建议使用SSL连接）
您现在已经连线到数据库 "postgres",用户 "test_cr".
postgres=> ALTER USER test_1 WITH PASSWORD 'Qwer1234www';
ALTER ROLE