Re: monitoring queries in pg_stat_activity

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>
>
>
> 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;