Re: monitoring queries in pg_stat_activity

[Keith <keith@xxxxxxxxxxx>]

On Wed, Feb 1, 2017 at 12:51 PM, daniel aristizabal franco <danielaristi@xxxxxxxxx> 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;