Обсуждение: grant select on pg_stat_activity

On 03/18/2016 01:09 PM, avi Singh wrote:
> Guys
>          Whats the best way to grant select on pg_stat_activity so that
> non super user can access this view.

They should be able to, see below. If that is not your case, then more
information is needed.

guest@test=> select current_user;
  current_user


--------------


  guest
(1 row)

guest@test=> \du guest
            List of roles
  Role name | Attributes | Member of
-----------+------------+-----------
  guest     |            | {}


guest@test=> select * from pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------
datid            | 16385
datname          | test
pid              | 2622
usesysid         | 1289138
usename          | guest
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2016-03-18 14:41:43.906754-07
xact_start       | 2016-03-18 14:44:22.550742-07
query_start      | 2016-03-18 14:44:22.550742-07
state_change     | 2016-03-18 14:44:22.550746-07
waiting          | f
state            | active
backend_xid      |
backend_xmin     | 58635
query            | select * from pg_stat_activity;

>
> Thanks
> Avi


--
Adrian Klaver
adrian.klaver@aklaver.com

On 03/21/2016 07:15 AM, Vick Khera wrote:
>
> On Fri, Mar 18, 2016 at 5:46 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com