Re: pg_stat_activity

ivan wrote:
>
> You can create function with security definer as super user
> sth like this
> create or replace function func () returns setof pg_stat_activity as '
> select * from pg_stat_activity ; ' language SQL SECURITY DEFINER;
>
> and if you want to se only query of session user you need to replace
> * to columns from view pg_stat_activity , and to column query
> you need to use CASE , when username == SESSION_USER : cur_query,
> else null;

This won't work for what he's asking for. The problem is that
pg_stat_activity is actually a view that uses an internal function to
retrieve the command list. The problem is that the internal function
itself is what contains the logic that decides whether or not to show the
command based on whether or not the user issuing the query is superuser.

I created a patch to address this (so that users can see their own
commands), and 7.4 incorporates the patch. You can probably retrieve
it by searching the pgsql-patches archives, but I can also email the
patch to whomever's interested.

--
Kevin Brown kevin(at)sysexperts(dot)com