We are doing some work related to Bulk Delete UX and wanted to see how many users actually have the can_bulk_delete_reports permission. I wanted to share some SQL that might be helpful if you need to look into Roles, Permissions, and/or Users.
The results of the SQL provided below will return a table like this.
| cht_role | count_users | offline | permission_abc | permission_xyz |
|---|---|---|---|---|
| role_123 | 155 | TRUE | TRUE | FALSE |
| role_456 | 22 | FALSE | FALSE | TRUE |
| role_789 | 0 | NULL | NULL | NULL |
cht_role: Dynamically identified, looks at the roles you have configured here as well as any other roles assigned to Users.
count_users: The number of users that have this role assigned.
offline: Whether or not this role is an offline user. If a user has been configured to have a role that is not in app_settings.json .roles{}, This will be NULL.
permission_abc: Whether or not the role has the specified permission. You will need to update the query to specify which permission you are interested in.
SQL
WITH all_roles AS
(
--These aren't really as important because there can be roles specified here
--that are not used (ie no users have the role).
SELECT
jsonb_object_keys((doc#>>'{settings,roles}')::jsonb) AS cht_role
FROM
couchdb
WHERE
doc->>'_id' = 'settings'
UNION
SELECT
distinct(jsonb_array_elements_text(doc->'roles')) AS cht_role
FROM
couchdb
WHERE
doc->>'type' = 'user-settings'
), count_per_role AS
(
--This pulls in how many users actually have the specified role
SELECT
(jsonb_array_elements_text(doc->'roles')) AS cht_role,
count(*) AS count_users
FROM
couchdb
WHERE
doc->>'type' = 'user-settings'
GROUP BY
cht_role
), offline AS
(
--Whether or not the Role is specified as "Offline" or not
SELECT
jsonb_object_keys((doc#>>'{settings,roles}')::jsonb) AS cht_role,
position('offline' IN jsonb_each_text((doc#>>'{settings,roles}')::jsonb)::text) > 0 AS offline
FROM
couchdb
WHERE
doc->>'_id' = 'settings'
), delete_perms AS
(
/*
Returns a table that looks like this. Probably a nicer way to do it but this works!
cht_role permission_1 permission_2
-------- ------------ ------------
role_abc FALSE TRUE
role_xyz TRUE FALSE
*/
SELECT
cht_role,
count(*) FILTER (WHERE perm = 'can_bulk_delete_reports') > 0 AS can_bulk_delete_reports,
count(*) FILTER (WHERE perm = 'can_delete_reports') > 0 AS can_delete_reports
FROM
(
SELECT
'can_bulk_delete_reports'::text AS perm,
jsonb_array_elements_text(doc#>'{settings,permissions,can_bulk_delete_reports}') AS cht_role
FROM
couchdb
WHERE
doc->>'_id' = 'settings'
UNION
SELECT
'can_delete_reports'::text AS perm,
jsonb_array_elements_text(doc#>'{settings,permissions,can_delete_reports}') AS cht_role
FROM
couchdb
WHERE
doc->>'_id' = 'settings'
) AS delete_perms
GROUP BY
cht_role
)
SELECT
all_roles.cht_role,
COALESCE(count_per_role.count_users,0) AS count_users,
offline,
can_bulk_delete_reports,
can_delete_reports
FROM
all_roles
NATURAL LEFT JOIN count_per_role
NATURAL LEFT JOIN offline
NATURAL LEFT JOIN delete_perms
ORDER BY
count_users DESC