cancel
Showing results for 
Search instead for 
Did you mean: 

Read only user's access to pg_stat_activity

SOLVED
Highlighted
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

1 ACCEPTED SOLUTION

Accepted Solutions
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

2 REPLIES
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

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