如何让普通用户也能愉快地分析Greenplum实时查询？

岳涛 • 2023-01-02 • 云技术社区 • 278 阅读

说明

本文延续上一篇文章 Greenplum管理员的好帮手：统计视图——pg_stat_activity，继续介绍pg_stat_activity的使用问题。其中描述的问题及解决方法同样适用于 腾讯云云数据仓库 PostgreSQL（CDWPG）。

背景

在上一篇文章中，我们学习了如何使用pg_stat_activity，但在实际运用中发现，这张视图只有superuser才能愉快地使用，当使用普通用户去操作时会发生如下的情况<insufficient privilege>：

dy_test=> SELECT pid, datname, usename, query FROM pg_stat_activity;
  pid  |  datname  |   usename    |                           query                            
-------+-----------+--------------+------------------------------------------------------------
 27116 | gpperfmon | gpadmincloud | <insufficient privilege>
 22999 | gpperfmon | gpadmincloud | <insufficient privilege>
 10706 | dy_test   | dy1          | SELECT pid, datname, usename, query FROM pg_stat_activity;
 21222 | test      | johnjing     | <insufficient privilege>
 21407 | dy_test   | dy2          | <insufficient privilege>
 21591 | dy_test   | dy3          | <insufficient privilege>
 13308 | dy_test   | gpadmincloud | <insufficient privilege>
 22106 | test      | gpadmincloud | <insufficient privilege>
(8 rows)

这是由于pg_stat_activity是一张系统视图，普通用户是没权限查看到其他用户全部的任务信息的。但是现实使用中又有这种需求，那么该如何解决呢？有两个方案可以实现。

解决方案

方案一：将普通用户赋予superuser的角色权限

dy_test=# ALTER USER dy WITH SUPERUSER;
ALTER ROLE
dy_test=# 
dy_test=# \\du
                                                            List of roles
   Role name   |                                                Attributes                                                | Member of 
---------------+----------------------------------------------------------------------------------------------------------+-----------
 dy            | Superuser                                                                                                | {}
 dy1           |                                                                                                          | {}
 dy2           |                                                                                                          | {}
 dy3           |                                                                                                          | {}
 gpadmincloud  | Superuser, Create role, Create DB, Ext gpfdist Table, Wri Ext gpfdist Table, Ext http Table, Replication | {}
 gpmon         | Superuser, Create DB                                                                                     | {}
 johnjing      | Create role, Create DB, Ext gpfdist Table                                                                | {}
 johnjing_test | Cannot login                                                                                             | {}

此方案虽然可行，但是失去了普通用户的意义，且有较高风险，需谨慎选择。

方案二：使用SECURITY DEFINER函数来实现对pg_stat_activity的查询

1）创建SECURITY DEFINER函数

dy_test=# CREATE OR REPLACE FUNCTION pg_catalog.pg_stat_activity() RETURNS setof pg_catalog.pg_stat_activity AS 
dy_test-# $body$ 
dy_test$# DECLARE 
dy_test$#     result record; 
dy_test$# BEGIN 
dy_test$# for result in select * from pg_catalog.pg_stat_activity
dy_test$# loop 
dy_test$#    return next result; 
dy_test$# end loop; 
dy_test$# return; 
dy_test$# END; 
dy_test$# $body$ 
dy_test-# LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
CREATE FUNCTION

2）将函数授权给用户

postgres=# REVOKE ALL ON FUNCTION pg_catalog.pg_stat_activity() FROM PUBLIC;
REVOKE
postgres=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_stat_activity() TO dy1;
GRANT

3）作为对比，先使用普通用户查询pg_stat_activity系统视图表

dy_test=> SELECT usename 用户,
dy_test->         client_addr 请求源IP,
dy_test->         query 执行SQL,
dy_test->         current_timestamp - query_start AS 执行时长,
dy_test->         state 运行状态
dy_test->  FROM pg_catalog.pg_stat_activity;
     用户     |  请求源ip    |                       执行sql                        | 执行时长 | 运行状态 
--------------+-------------+------------------------------------------------------+----------+----------
 gpadmincloud |             | <insufficient privilege>                             |          | 
 gpadmincloud |             | <insufficient privilege>                             |          | 
 dy1          | 10.0.26.169 | SELECT usename 账号,                                +| 00:00:00 | active
              |             |         client_addr 请求源IP,                       +|          | 
              |             |         query 执行SQL,                              +|          | 
              |             |         current_timestamp - query_start AS 执行时长,+|          | 
              |             |         state 运行状态                              +|          | 
              |             |  FROM pg_catalog.pg_stat_activity;                   |          | 
 dy2          |             | <insufficient privilege>                             |          | 
 dy3          |             | <insufficient privilege>                             |          | 
(5 rows)

可以看到，当查询结果是其他用户时，这里很多信息是看不到的，或者为<insufficient privilege>。

4）再使用普通用户查询pg_stat_activity()函数

dy_test=> SELECT usename 用户,
dy_test->         client_addr 请求源IP,
dy_test->         query 执行SQL,
dy_test->         current_timestamp - query_start AS 执行时长,
dy_test->         state 运行状态
dy_test->  FROM pg_catalog.pg_stat_activity();
     用户     |  请求源ip    |                       执行sql                        |    执行时长     | 运行状态 
--------------+-------------+------------------------------------------------------+-----------------+----------
 gpadmincloud | ::1         | select count(*) from pg_stat_activity where 1=1      | 00:00:09.907866 | idle
 gpadmincloud | ::1         | select count(*) from pg_stat_activity where 1=1      | 00:00:09.904608 | idle
 dy1          | 10.0.26.169 | SELECT usename 用户,                                +| 00:00:00        | active
              |             |         client_addr 请求源IP,                       +|                 | 
              |             |         query 执行SQL,                              +|                 | 
              |             |         current_timestamp - query_start AS 执行时长,+|                 | 
              |             |         state 运行状态                              +|                 | 
              |             |  FROM pg_catalog.pg_stat_activity();                 |                 | 
 dy2          | 10.0.26.169 | SELECT COUNT(1) FROM test_timestamp;                 | 00:05:57.229775 | idle
 dy3          | 10.0.26.169 | SELECT * FROM test_u0001_head;                       | 00:05:09.078105 | idle
(5 rows)

至此，普通用户也能愉快地使用pg_stat_activity了。

本站文章资源均来源自网络，除非特别声明，否则均不代表站方观点，并仅供查阅，不作为任何参考依据！
如有侵权请及时跟我们联系，本站将及时删除！
如遇版权问题，请查看本站版权声明

THE END