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

agardner

I'm working on a task which tracks long term which tables / projections are being utilized. I have a task running on a user which is looking at dc_projections_used to get the last read/write time for each projection. The problem is my user can only see interactions for that user, not all. We've tried granting it SysMonitor but that doesn't seem to help. Is there a way to grant a user outside of DBADMIN permission to see all user data on the dc* tables? The same also seems to apply to the "projection_usage" table too.

s_crossman

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,

agardner

Worked like magic, thank you!