Create user to monitor database activity on Postgres

Question

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?

Answer 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.

Answer 2

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

Answer 3

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