I am creating a new user and granting them the pg_read_all_stats privilege so they can monitor all pg_stat_* views. However, when I monitor pg_stat_activity through this user, other users' operations appear as NULL and the query column displays <insufficient privilege>. I also tried granting the user the pg_monitor privilege to resolve this, but I got the same result.
My steps in order:
CREATE ROLE testuser NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN NOREPLICATION NOBYPASSRLS PASSWORD ‘Abc123’;
And
grant pg_read_all_stats to testuser;
These two steps alone are not sufficient.
I was only able to resolve the issue as follows: First, I grant the user the inherit privilege:
alter user testuser with inherit;
Then, I grant the user the pg_read_all_stats privilege:
grant pg_read_all_stats to testuser;
And my user can now view the operations performed by other users (including super users) without any issues.
If I grant the inherit privilege after pg_read_all_stats, the user still cannot access the monitoring feature. Therefore, I must grant the inherit privilege first.
Furthermore, revoking the inherit privilege from the user does not affect the user's pg_read_all_stats privilege.
Why does revoking the inherit feature later have no effect when it was required first when granting the pg_read_all_stats privilege? The same situation applies to the pg_monitor privilege. And probably to other monitoring privileges as well.
Postgresql Version: 16.10
