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 On Thu, 2006-01-19 at 12:38 -0500, Tom Lane wrote: > Marc Munro <marc@xxxxxxxxxxxx> 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
Attachment:
signature.asc
Description: This is a digitally signed message part
