Обсуждение: who can view pg_stat_activity?

Поиск

Список

Период

Сортировка

who can view pg_stat_activity?

От

Mark Steben

Дата:

07 февраля 2018 г., 22:57:35

Good morning,

We currently run postgres 9.4. The only way to view the pg_stat_activity view that I can see is that you must be a superuser. I couldn't find anything in the documentation to confirm or refute this. Could you please confirm if this is true or if not, what privileges are required?

Thank you for your time.

Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

Re: who can view pg_stat_activity?

От

Shreeyansh Dba

Дата:

07 февраля 2018 г., 23:17:06

Hi Mark Steben,

There is no superuser required to view pg_stat_activity, a normal user can also view or access.

On Wed, Feb 7, 2018 at 10:27 PM, Mark Steben <mark.steben@drivedominion.com> wrote:

Good morning,

We currently run postgres 9.4. The only way to view the pg_stat_activity view that I can see is that you must be a superuser. I couldn't find anything in the documentation to confirm or refute this. Could you please confirm if this is true or if not, what privileges are required?

Thank you for your time.

--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

Re: who can view pg_stat_activity?

От

Mark Steben

Дата:

07 февраля 2018 г., 23:31:17

Thank you for your prompt answer Shreeyansh

I granted all privileges to the pg_stat_activity view to the newmail account and still get this result when trying to access:

select * from pg_stat_activity limit 4;

-------+------------------+-------+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------+-------------+--------------+---------+-------+-------------+--------------+--------------------------

16459 | prime_production | 6133 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>

16459 | prime_production | 57677 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>

16459 | prime_production | 5806 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>

16459 | prime_production | 17532 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>

On Wed, Feb 7, 2018 at 12:17 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:

Hi Mark Steben,

There is no superuser required to view pg_stat_activity, a normal user can also view or access.

On Wed, Feb 7, 2018 at 10:27 PM, Mark Steben <mark.steben@drivedominion.com> wrote:
Good morning,

We currently run postgres 9.4. The only way to view the pg_stat_activity view that I can see is that you must be a superuser. I couldn't find anything in the documentation to confirm or refute this. Could you please confirm if this is true or if not, what privileges are required?

Thank you for your time.

--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

Re: who can view pg_stat_activity?

От

Cory Nemelka

Дата:

07 февраля 2018 г., 23:34:06

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@gmail.com> wrote:

Hi Mark Steben,

There is no superuser required to view pg_stat_activity, a normal user can also view or access.

On Wed, Feb 7, 2018 at 10:27 PM, Mark Steben <mark.steben@drivedominion.com> wrote:
Good morning,

We currently run postgres 9.4. The only way to view the pg_stat_activity view that I can see is that you must be a superuser. I couldn't find anything in the documentation to confirm or refute this. Could you please confirm if this is true or if not, what privileges are required?

Thank you for your time.

--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

Re: who can view pg_stat_activity?

От

Don Seiler

Дата:

07 февраля 2018 г., 23:37:48

On Wed, Feb 7, 2018 at 11:34 AM, Cory Nemelka <cnemelka@gmail.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@gmail.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.

Don.

Don Seiler
www.seiler.us

Re: who can view pg_stat_activity?

От

Cory Nemelka

Дата:

07 февраля 2018 г., 23:38:47

sweet! :)

--cnemelka

On Wed, Feb 7, 2018 at 10:31 AM, Mark Steben <mark.steben@drivedominion.com> wrote:

Thank you for your prompt answer Shreeyansh

I granted all privileges to the pg_stat_activity view to the newmail account and still get this result when trying to access:

select * from pg_stat_activity limit 4;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | backend_xid | backend_xmin | query
-------+------------------+-------+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------+-------------+--------------+---------+-------+-------------+--------------+--------------------------
16459 | prime_production | 6133 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>
16459 | prime_production | 57677 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>
16459 | prime_production | 5806 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>
16459 | prime_production | 17532 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>

On Wed, Feb 7, 2018 at 12:17 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Mark Steben,

There is no superuser required to view pg_stat_activity, a normal user can also view or access.

On Wed, Feb 7, 2018 at 10:27 PM, Mark Steben <mark.steben@drivedominion.com> wrote:
Good morning,

We currently run postgres 9.4. The only way to view the pg_stat_activity view that I can see is that you must be a superuser. I couldn't find anything in the documentation to confirm or refute this. Could you please confirm if this is true or if not, what privileges are required?

Thank you for your time.

--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

Re: who can view pg_stat_activity?

От

Mark Steben

Дата:

07 февраля 2018 г., 23:43:42

ok thanks for everyone's response.

On Wed, Feb 7, 2018 at 12:38 PM, Cory Nemelka <cnemelka@gmail.com> wrote:

sweet! :)

--cnemelka

On Wed, Feb 7, 2018 at 10:31 AM, Mark Steben <mark.steben@drivedominion.com> wrote:
Thank you for your prompt answer Shreeyansh

I granted all privileges to the pg_stat_activity view to the newmail account and still get this result when trying to access:

select * from pg_stat_activity limit 4;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | backend_xid | backend_xmin | query
-------+------------------+-------+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------+-------------+--------------+---------+-------+-------------+--------------+--------------------------
16459 | prime_production | 6133 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>
16459 | prime_production | 57677 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>
16459 | prime_production | 5806 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>
16459 | prime_production | 17532 | 16395 | prime | | | | | | | | | | | | | <insufficient privilege>

On Wed, Feb 7, 2018 at 12:17 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Mark Steben,

There is no superuser required to view pg_stat_activity, a normal user can also view or access.

On Wed, Feb 7, 2018 at 10:27 PM, Mark Steben <mark.steben@drivedominion.com> wrote:
Good morning,

We currently run postgres 9.4. The only way to view the pg_stat_activity view that I can see is that you must be a superuser. I couldn't find anything in the documentation to confirm or refute this. Could you please confirm if this is true or if not, what privileges are required?

Thank you for your time.

--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com

Re: who can view pg_stat_activity?

От

Ashutosh Sharma

Дата:

08 февраля 2018 г., 10:26:46

On Wed, Feb 7, 2018 at 11:07 PM, Don Seiler <don@seiler.us> wrote:
> On Wed, Feb 7, 2018 at 11:34 AM, Cory Nemelka <cnemelka@gmail.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@gmail.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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: who can view pg_stat_activity?