Privilege for seeing queries using pg_stat_get_backend_activity

Marc Munro <marc@bloodnok.com> writes:

I want certain users to be able to examine running queries using
pg_stat_get_backend_activity. Unfortunately, this will only show other
users' activity if you have superuser privilege.
I do not want to give monitoring users superuser privilege, but I do
need to allow them to perform monitoring tasks.
I've tried tricks with security definer functions but this does not help
as pg_stat_get_backend_activity explicitly checks for the caller being a
superuser.

That should work fine, as the test is on the current effective userid
which will change inside a security-definer function. Take a closer
look at what you did, or post a complete example if you can't get it
to work.

regards, tom lane

On Thu, Jan 19, 2006 at 09:17:12AM -0800, Marc Munro wrote:

I've tried tricks with security definer functions but this does not help
as pg_stat_get_backend_activity explicitly checks for the caller being a
superuser.

Works here. Could you post an example?

--
Michael Fuhr

Thanks Tom,
On further investigation it seems that the problem is that I can create
an equivalent function with security definer, and I can create a wrapper
function with security definer but I cannot modify the existing function
for security definer.

This is a problem because the monitoring users use pgadmin which uses
pg_stat_backend_activity directly and also through pg_stat_activity, so
I cannot simply rewrite the monitoring queries to use a wrapper
function.

My solution is to create a new function with the same name in the public
schema, and to redefine pg_stat_activity to call the public function.

This seems a little kludgy though I am content with it for now. It does
make me wonder though if there should be something like a monitoring
privilege so that we don't have to go through this.

FWIW, here is the new function defn:

create or replace
function public.pg_stat_get_backend_activity(integer) returns text as '
begin
return pg_catalog.pg_stat_get_backend_activity($1);
end;
' language plpgsql security definer;

__
Marc