磐维数据库pg_get_userbyid函数使用测试

函数介绍

• pg_get_userbyid(oid)
• 描述：获取给定 OID 的角色名。
• 返回类型：name
• 备注：pg_get_userbyid 通过角色的 OID 抽取对应的用户名。
• 查看函数定义

postgres=# \sf pg_get_userbyid
CREATE  OR REPLACE DEFINER = omm FUNCTION pg_catalog.pg_get_userbyid(oid)
 RETURNS name
 LANGUAGE internal
 STABLE PARALLEL SAFE STRICT AUTHID CURRENT_USER NOT FENCED NOT SHIPPABLE
AS $function$pg_get_userbyid$function$;

查看用户密码有效期

• 使用 omm用户可以查询出3条记录

postgres=# select pg_get_userbyid(roloid),max(passwordtime),max(passwordtime)+90::interval as passwordexpiredtime,max(passwordtime)+90::interval-now() as duar from pg_catalog.pg_auth_history where pg_get_userbyid(roloid) not like 'pw%' group by roloid order by 3;
 pg_get_userbyid |              max              |      passwordexpiredtime      |           duar            
-----------------+-------------------------------+-------------------------------+---------------------------
 omm             | 2025-01-24 15:35:43.384728+08 | 2025-04-24 15:35:43.384728+08 | -28 days -01:20:38.773678
 repl            | 2025-04-24 17:48:37.234443+08 | 2025-07-23 17:48:37.234443+08 | 62 days 00:52:15.076037
 u1              | 2025-05-22 15:28:49.913557+08 | 2025-08-20 15:28:49.913557+08 | 89 days 22:32:27.755151
(3 rows)

• 使用u1用户，只能查询出1条记录

You are now connected to database "postgres" as user "u1".
postgres=> select pg_get_userbyid(roloid),max(passwordtime),max(passwordtime)+90::interval as passwordexpiredtime,max(passwordtime)+90::interval-now() as duar from pg_catalog.pg_auth_history where pg_get_userbyid(roloid) not like 'pw%' group by roloid order by 3;
 pg_get_userbyid |              max              |      passwordexpiredtime      |          duar           
-----------------+-------------------------------+-------------------------------+-------------------------
 u1              | 2025-05-22 15:28:49.913557+08 | 2025-08-20 15:28:49.913557+08 | 89 days 22:11:44.038743
(1 row)

• 经过排查，是u1用户权限不足，无法查询到其他用户信息。

postgres=> select pg_get_userbyid(roloid) from pg_catalog.pg_auth_history ;
 pg_get_userbyid 
-----------------
 
 
 
 
 
 
 u1
(7 rows)

• 查看omm和u1用户具备的权限

postgres=# \du u1
            List of roles
 Role name |  Attributes  | Member of 
-----------+--------------+-----------
 u1        | Monitoradmin | {}

postgres=# \du omm
                                                                      List of roles
 Role name |                                                            Attributes                                                            | Member of 
-----------+----------------------------------------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer sso, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

• 授予权限，显示内置函数是硬编码，不可以授权

postgres=# grant execute on function pg_catalog.pg_get_userbyid(oid) to u1;
ERROR:  All built-in functions are hard coded, and they should not be updated.

• 将sysadmin权限授予u1 用户后，可以使用pg_get_userbyid函数

postgres=> \du u1
                 List of roles
 Role name |       Attributes       | Member of 
-----------+------------------------+-----------
 u1        | Sysadmin, Monitoradmin | {}

postgres=> select pg_get_userbyid(roloid) from pg_catalog.pg_auth_history;
 pg_get_userbyid 
-----------------
 omm
 pwadmin
 pwsso
 pwaudit
 pwdb_exporter
 repl
 u1
(7 rows)