Read only user's access to pg_stat_activity

SOLVED
Highlighted
Level 3 Adventurer

Read only user's access to pg_stat_activity

Hello All,

 

I have created a read only user.Provided usage grant on schema pg_catalog and select access on pg_stat_activity to the read only user so that the user can check what is currently running in the database,but when user fires a select query on table pg_stat_activity it shows "<insufficient privilege>" for the query column.Superuser's are able to see the contents of query column in pg_stat_activity.

 

Why the read only user is not allowed to see the contents of query column in pg_stat_activity?

 

Regards

Nikhil

Archived Discussions


Effective March 31st, we will no longer engage on PostgresRocks.


How to engage with us further?


  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Moderator

Re: Read only user's access to pg_stat_activity


@Nikhil wrote:

Why the read only user is not allowed to see the contents of query column in pg_stat_activity?


Hi Nikhil,

 

Only the administrators/superusers can see the queries that are being run against the database in pg_stat_activity.  I believe the reason for this is because of security and privacy concerns.  pg_stat_activity is a view designed to be a tool for administrators to locate problematic queries and activity in the database, so while non-administrators can query the view, the server masks the queries for the privacy and security of all users.

 

HTH!

--Richard

View solution in original post

Highlighted
PostgreSQL Core Team - EDB

Re: Read only user's access to pg_stat_activity

In Postgres 10 you can grant the pg_read_all_stats role to users to allow them to read the stats views that would otherwise be superuser-only.

 

https://www.postgresql.org/docs/10/static/default-roles.html

View solution in original post

3 REPLIES 3
Highlighted
Moderator

Re: Read only user's access to pg_stat_activity


@Nikhil wrote:

Why the read only user is not allowed to see the contents of query column in pg_stat_activity?


Hi Nikhil,

 

Only the administrators/superusers can see the queries that are being run against the database in pg_stat_activity.  I believe the reason for this is because of security and privacy concerns.  pg_stat_activity is a view designed to be a tool for administrators to locate problematic queries and activity in the database, so while non-administrators can query the view, the server masks the queries for the privacy and security of all users.

 

HTH!

--Richard

View solution in original post

Highlighted
PostgreSQL Core Team - EDB

Re: Read only user's access to pg_stat_activity

In Postgres 10 you can grant the pg_read_all_stats role to users to allow them to read the stats views that would otherwise be superuser-only.

 

https://www.postgresql.org/docs/10/static/default-roles.html

View solution in original post

Highlighted
Level 3 Adventurer

Re: Read only user's access to pg_stat_activity

Thank you for the update!

 

 

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks