这是由于pg_stat_activity是一张系统视图，普通用户是没权限查看到其他用户全部的任务信息的。
背景
在上一篇文章中，我们学习了如何使用pg_stat_activity，但在实际运用中发现，这张视图只有superuser才能愉快地使用，当使用普通用户去操作时会发生如下的情况：
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)
可以看到，当查询结果是其他用户时，这里很多信息是看不到的，或者为。
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了。
