Re: Privilege for seeing queries using

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.

Aside from implementing my own version of pg_stat_get_backend_activity
in C, does anyone have any suggestions?

Should there be a standard privilege that allows this (please say yes)?

__
Marc

Marc Munro <marc(at)bloodnok(dot)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

On Thu, 2006-01-19 at 12:38 -0500, Tom Lane wrote:
> Marc Munro <marc(at)bloodnok(dot)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