3

Postgres 9.3 Debian 7

I want to create a monitor user called monitor that the only permission is to run queries to monitor all the database activity. I created the user and it can select from pg_stat_activity, the problem is that it only sees it's own activity and I want this user to see all users activity.

Is there any special privilege to do that?

3 Answers 3

1

At this time, only superusers can see others' activity details in pg_stat_activity.

PostgreSQL could use a finer-grained rights model, where you can GRANT the MONITOR_QUERIES right to a user, for example. But right now it doesn't have one, and quite a few things are superuser-only. This is one of them.

1

We can create a normal user and provide the default pg_monitor role to get all monitoring metrics. Simple two steps:

Create a user:

CREATE USER db_monitor WITH PASSWORD 'password';

Grant the role to monitor:

GRANT pg_monitor TO db_monitor;

pg_monitor:

  • This role read/execute various monitoring views and functions.
  • Its is a combination of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables roles
3
  • Is this a new feature? Do you know what version it was included? Mar 28, 2022 at 16:54
  • Currently i use them in latest postgresql version v13 but i believe this feature is available from postgresql v10
    – Jerry
    Mar 29, 2022 at 6:21
  • I am using v14.11; doing the query select * from pg_stat_all_tables where schemaname = 'public'; returns nothing for this db_monitor user, whereas the owner of the database can get good results; Mar 3 at 5:48
0

Keep in mind: pg_monitor role was introduced in PostgreSQL 14. As mentioned in https://www.postgresql.org/docs/current/predefined-roles.html Also have a look to https://www.postgresql.org/docs/current/default-roles.html

2
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Apr 11 at 10:57
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review Apr 11 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.