Allow user to see all users in Data collector tables (dc_*)

Hi,

Make sure when you create the role for the user and grant sysmonitor,to the role, that when the user logs in they set role to that role. The visibility doesn't happen automatically

dbadmin=> create user user1 identified by '';
CREATE USER
dbadmin=> \c - user1
You are now connected as user "user1".
dbadmin=> select * from projection_usage;
query_start_timestamp | node_name | user_name | session_id | request_id | transaction_id | statement_id | io_type | projection_id | projection_name | anchor_table_id | anchor_table_schema | anchor_table_name
-----------------------+-----------+-----------+------------+------------+----------------+--------------+---------+---------------+-----------------+-----------------+---------------------+-------------------
(0 rows)

dbadmin=> \c - dbadmin
Password:
You are now connected as user "dbadmin".
dbadmin=> create role monitor;
CREATE ROLE
dbadmin=> GRANT SYSMONITOR to monitor;
GRANT ROLE
dbadmin=> GRANT monitor to user1;
GRANT ROLE
dbadmin=> \c - user1
You are now connected as user "user1".
dbadmin=> select count(*) from dc_projections_used;

count

 4

(1 row)

dbadmin=> set role monitor;
SET
dbadmin=> select count(*) from dc_projections_used;

count

152

Note that the role is not persistent so each time you log in with that user you have to set the role. If you want the user to have that role enabled automatically you can set the default role.

dbadmin=> \c - user1
You are now connected as user "user1".
dbadmin=> show enabled_roles;
name | setting
---------------+---------
enabled roles |
(1 row)

dbadmin=> select count(*) from dc_projections_used;

count

 6

(1 row)

dbadmin=> \c - dbadmin
Password:
You are now connected as user "dbadmin".
dbadmin=> alter user user1 default role monitor;
ALTER USER

dbadmin=> \c - user1
You are now connected as user "user1".
dbadmin=> show enabled_roles;
name | setting
---------------+---------
enabled roles | monitor
(1 row)

dbadmin=> select count(*) from dc_projections_used;

count

155

I hope it helps,

Worked like magic, thank you!