public inbox for [email protected]
help / color / mirror / Atom feedgrant select on pg_stat_activity
4+ messages / 3 participants
[nested] [flat]
* grant select on pg_stat_activity
@ 2016-03-18 20:09 avi Singh <[email protected]>
2016-03-18 21:46 ` Re: [GENERAL] grant select on pg_stat_activity Adrian Klaver <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: avi Singh @ 2016-03-18 20:09 UTC (permalink / raw)
To: pgsql-performance; [email protected]
Guys
Whats the best way to grant select on pg_stat_activity so that non
super user can access this view.
Thanks
Avi
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] grant select on pg_stat_activity
2016-03-18 20:09 grant select on pg_stat_activity avi Singh <[email protected]>
@ 2016-03-18 21:46 ` Adrian Klaver <[email protected]>
2016-03-21 14:15 ` Re: grant select on pg_stat_activity Vick Khera <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2016-03-18 21:46 UTC (permalink / raw)
To: avi Singh <[email protected]>; pgsql-performance; [email protected]
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
[email protected]
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: grant select on pg_stat_activity
2016-03-18 20:09 grant select on pg_stat_activity avi Singh <[email protected]>
2016-03-18 21:46 ` Re: [GENERAL] grant select on pg_stat_activity Adrian Klaver <[email protected]>
@ 2016-03-21 14:15 ` Vick Khera <[email protected]>
2016-03-21 15:19 ` Re: grant select on pg_stat_activity Adrian Klaver <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Vick Khera @ 2016-03-21 14:15 UTC (permalink / raw)
To: pgsql-general <[email protected]>
On Fri, Mar 18, 2016 at 5:46 PM, Adrian Klaver <[email protected]>
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.
I do not know what permission is necessary to make that visible. My hunch
is it will require superuser privileges.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: grant select on pg_stat_activity
2016-03-18 20:09 grant select on pg_stat_activity avi Singh <[email protected]>
2016-03-18 21:46 ` Re: [GENERAL] grant select on pg_stat_activity Adrian Klaver <[email protected]>
2016-03-21 14:15 ` Re: grant select on pg_stat_activity Vick Khera <[email protected]>
@ 2016-03-21 15:19 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2016-03-21 15:19 UTC (permalink / raw)
To: Vick Khera <[email protected]>; pgsql-general <[email protected]>
On 03/21/2016 07:15 AM, Vick Khera wrote:
>
> On Fri, Mar 18, 2016 at 5:46 PM, Adrian Klaver
> <[email protected] <mailto:[email protected]>> 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
[email protected]
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-03-21 15:19 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-03-18 20:09 grant select on pg_stat_activity avi Singh <[email protected]>
2016-03-18 21:46 ` Adrian Klaver <[email protected]>
2016-03-21 14:15 ` Vick Khera <[email protected]>
2016-03-21 15:19 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox