Re: monitoring queries in pg_stat_activity

On Wed, Feb 1, 2017 at 12:05 PM, Keith <keith(at)keithf4(dot)com> wrote:

> On Wed, Feb 1, 2017 at 12:51 PM, daniel aristizabal franco <
> danielaristi(at)gmail(dot)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>
>>
>>
> 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.
>
>
​I think that behavior is version dependent among the currently supported
releases - so what is the version here?

I ask specifically because I thought that activity was shown for all
sessions but details were based on permissions. The single result shown
would have to be the executing user's session which they should also have
permission to view (in recent releases). That presumes that RECORD 2 and
so on weren't simply omitted.

> 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;
>
>
​Yes, this the work-around.

David J.
​