public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Vick Khera <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: grant select on pg_stat_activity
Date: Mon, 21 Mar 2016 08:19:25 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CALd+dceU27n42dkCfyhPGyGgjXe2a_Dxk5b7NHS63yONc=9O-Q@mail.gmail.com>
References: <CAETvN5hKTWmAT7LLnzPnemFWwPi9odGSzKDbHEa5EtXBPL7OOg@mail.gmail.com>
<[email protected]>
<CALd+dceU27n42dkCfyhPGyGgjXe2a_Dxk5b7NHS63yONc=9O-Q@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-general>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: grant select on pg_stat_activity
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox