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