postgresql 9.6 普通用户查询 pg_stat_activity、pg_stat_statements 时部分列显示 insufficient privilege,有两种方法可以解决。
postgres=> select pid,client_addr,query from pg_stat_activity;
pid | client_addr | query
------+-------------+-----------------------------------------------------
3078 | | select pid,client_addr,query from pg_stat_activity;
2954 | | <insufficient privilege>
2955 | | <insufficient privilege>
2964 | | <insufficient privilege>
2976 | | <insufficient privilege>
2987 | | <insufficient privilege>
(6 rows)
postgres=> select dbid,queryid,query from pg_stat_statements order by query;
dbid | queryid | query
-------+------------+---------------------------------------------------------------------------------------------------------
13325 | | <insufficient privilege>
13325 | | <insufficient privilege>
13325 | | <insufficient privilege>
13325 | | <insufficient privilege>
这个虽然可行,但是把普通用户的权限提升到 superuser,带来相当高的风险
postgres=# alter user testuser1 with superuser;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
testuser | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
CREATE OR REPLACE FUNCTION pg_catalog.f_pg_stat_activity() RETURNS setof pg_catalog.pg_stat_activity AS
$body$
DECLARE
result record;
BEGIN
for result in select * from pg_catalog.pg_stat_activity
loop
return next result;
end loop;
return;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
CREATE OR REPLACE FUNCTION pg_catalog.f_pg_stat_statements() RETURNS setof public.pg_stat_statements AS
$body$
DECLARE
result record;
BEGIN
for result in select * from public.pg_stat_statements
loop
return next result;
end loop;
return;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER
REVOKE ALL ON FUNCTION pg_catalog.f_pg_stat_activity() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pg_catalog.f_pg_stat_activity() TO testuser1;
REVOKE ALL ON FUNCTION pg_catalog.f_pg_stat_statements() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pg_catalog.f_pg_stat_statements() TO testuser1;
可以通过查询这两个函数获取数据。
select * from pg_catalog.f_pg_stat_activity();
select * from pg_catalog.f_pg_stat_statements();