From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Vick Khera <vivek(at)khera(dot)org>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: grant select on pg_stat_activity |
Date: | 2016-03-21 15:19:25 |
Message-ID: | 56F010FD.4020907@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-performance |
On 03/21/2016 07:15 AM, Vick Khera wrote:
>
> On Fri, Mar 18, 2016 at 5:46 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> They should be able to, see below. If that is not your case, then
> more information is needed.
>
>
> You can see your own queries, however non-superuser will not see the
> query for other users. You will be able to see the other info, though.
Did not think of that.
>
> I do not know what permission is necessary to make that visible. My
> hunch is it will require superuser privileges
Hmm, I would hesitate to mess with permissions on a system view.
A quick and dirty fix as a superuser:
CREATE FUNCTION pg_stat_allusers( )
RETURNS setof pg_stat_activity
LANGUAGE sql SECURITY DEFINER
AS $function$
SELECT * FROM pg_stat_activity;
$function$
test=> select current_user;
-[ RECORD 1 ]+------
current_user | guest
test=> select * from pg_stat_allusers();
-[ RECORD 1 ]----+----------------------------------------------
datid | 983301
datname | test
pid | 5886
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2016-03-21 08:03:43.60797-07
xact_start |
query_start | 2016-03-21 08:14:47.166341-07
state_change | 2016-03-21 08:14:47.166953-07
waiting | f
state | idle
backend_xid |
backend_xmin |
query | SELECT pg_catalog.pg_get_functiondef(1730587)
-[ RECORD 2 ]----+----------------------------------------------
datid | 983301
datname | test
pid | 5889
usesysid | 432800
usename | guest
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2016-03-21 08:03:48.559611-07
xact_start | 2016-03-21 08:18:40.245858-07
query_start | 2016-03-21 08:18:40.245858-07
state_change | 2016-03-21 08:18:40.245862-07
waiting | f
state | active
backend_xid |
backend_xmin | 119564
query | select * from pg_stat_allusers();
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Bert | 2016-03-21 15:29:25 | Re: [SQL] plan not correct? |
Previous Message | David G. Johnston | 2016-03-21 15:17:33 | Re: [GENERAL] Request - repeat value of \pset title during \watch interations |
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2016-03-21 15:33:12 | Re: Searching GIN-index (FTS) and sort by timestamp-column |
Previous Message | Oleg Bartunov | 2016-03-21 15:13:07 | Re: Searching GIN-index (FTS) and sort by timestamp-column |