Re: who can view pg_stat_activity?

On Wed, Feb 7, 2018 at 11:07 PM, Don Seiler <don(at)seiler(dot)us> wrote:
> On Wed, Feb 7, 2018 at 11:34 AM, Cory Nemelka <cnemelka(at)gmail(dot)com> wrote:
>>
>> this seems to be a security hole. this means I can see query text for
>> queries that aren't mine. anyone else concerned?
>>
>> --cnemelka
>>
>> On Wed, Feb 7, 2018 at 10:17 AM, Shreeyansh Dba <shreeyansh2014(at)gmail(dot)com>
>> wrote:
>>>
>>> Hi Mark Steben,
>>>
>>> There is no superuser required to view pg_stat_activity, a normal user
>>> can also view or access.
>
>
> I believe Shreeyansh is incorrect. You can view some fields as a normal user
> but you can't view query text (in addition to some others) unless you are
> superuser, or perhaps the new monitoring role in Pg10.
>

Yeah, that's right, normal users can't view the fields like query,
state, state_change, client details , xact_start time, query_start
time e.t.c. All he can see is, may be the database and user related
stuffs. You may try running 'select * from
pg_stat_get_activity(NULL);' to know on what all fields appear to be
null for normal users. The system view pg_stat_activity eventually
collects data from pg_stat_get_activity and shows it the users. Also,
please note that normal user can't reset the stats. You would get a
permission denied error when trying to run pg_stat_reset() function as
a normal user. Thanks.

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com

> Don.
>
> --
> Don Seiler
> www.seiler.us