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