Re: [ADMIN] monitoring queries in pg_stat_activity

Поиск
Список
Период
Сортировка
От Keith
Тема Re: [ADMIN] monitoring queries in pg_stat_activity
Дата
Msg-id CAHw75vvMbzC+a5oSLdrNwyG4tLp==q=tGSr1yKRcjbn2uJLhUg@mail.gmail.com
обсуждение исходный текст
Ответ на [ADMIN] monitoring queries in pg_stat_activity  (daniel aristizabal franco <danielaristi@gmail.com>)
Ответы Re: [ADMIN] monitoring queries in pg_stat_activity  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-admin
Дерево обсуждения


On Wed, Feb 1, 2017 at 12:51 PM, daniel aristizabal franco <danielaristi@gmail.com> wrote:
Hi all:

I have created a nosuperuser for monitoring, but I can't do a select on the pg_stat_activity table, I get the follow message:

select datid,xact_start,query  from  pg_catalog.pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------------------
datid            | 204816
xact_start       | 
query            | <insufficient privilege>


I have assigned these grants to my user:
GRANT ALL ON SCHEMA pg_catalog to myuser;
GRANT SELECT ON ALL TABLES IN schema pg_catalog TO myuser;

The user not should be a superuser. does there are other way for solve it?

thanks to all 





You can create a function, owned by a superuser, with the SECURITY DEFINER flag set. Then give your non-superuser role permissions to execute it. SECURITY DEFINER tells postgres to run the given function as the owner, not the user calling it, so have to be careful with what those functions do. In this case, you can just have the function SELECT * FROM pg_stat_activity and return those results.

By default pg_stat_activity only shows running queries that the current user is running and has access to.

CREATE FUNCTION pg_stat_activity() RETURNS SETOF pg_catalog.pg_stat_activity
AS $$
BEGIN
RETURN query(select * from pg_catalog.pg_stat_activity);
END
$$ LANGUAGE PLPGSQL SECURITY DEFINER;

REVOKE ALL ON FUNCTION pg_stat_activity() FROM PUBLIC;

GRANT EXECUTE ON FUNCTION pg_stat_activity() TO myuser;

В списке pgsql-admin по дате отправления:

Предыдущее
От: daniel aristizabal franco
Дата:
Сообщение: [ADMIN] monitoring queries in pg_stat_activity
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: [ADMIN] monitoring queries in pg_stat_activity