Return combination of verbs if user is assigned multiple roles on the same project

[ktuite]

This is for the unusual case (not even supported by frontend) that the same user is assigned multiple roles on the same project.

The endpoint /v1/projects/<id> (extended version) returns a list of verbs the user can do on that project that is computed with this auth.verbsOn query:

central-backend/lib/model/query/auth.js

Lines 54 to 65 in 56757ca

	const verbsOn = (actorId, actee) => ({ all }) => {
	const acteeId = actee.acteeId || actee;
	return all(sql`
	${_impliedActees(acteeId)}
	select verbs from roles
	inner join (select "roleId" from assignments
	inner join implied on implied.id=assignments."acteeId"
	where "actorId"=${actorId})
	as assignments on assignments."roleId"=roles.id`)
	// TODO: it miiiiight be possible to make postgres do this work?
	.then(compose(uniq, flatten, map((r) => r.verbs)));
	};

and assigned to the Project here:

central-backend/lib/resources/projects.js

Line 32 in 56757ca

? auth.verbsOn(project).then((verbs) => Object.assign({ verbs }, project.forApi()))

The endpoint /v1/projects?forms=true also returns verbs for each project (added in Central v1.5), but is computed with the Projects.getAllByAuthWithForms query. Currently. this query chooses the verbs of the role with the lowest ID. There are some assumptions baked in here about the lower the ID, the more permissive the role, which is true for the current set of roles, but could certainly change.

central-backend/lib/model/query/projects.js

Lines 96 to 105 in 56757ca

	inner join
	(select id, min("roleId") as "bestRole" from projects
	inner join
	(select "acteeId", role.id as "roleId", role.verbs from assignments
	inner join (select id, verbs from roles where verbs ?& array['project.read', 'form.list']) as role
	on role.id=assignments."roleId"
	where "actorId"=${actorId}) as assignment
	on assignment."acteeId" in ('*', 'project', projects."acteeId")
	group by id) as filtered
	on filtered.id=projects.id

Instead of this approach, we might want to change the query to return the combined list of verbs for both roles. A different approach to try is in this comment of the PR adding verbs to the project + form list: #484 (comment)

From @matthew-white:

Just to recap our conversation from yesterday, things get tricky when a user has multiple roles (for example, if a user is both an administrator and a project manager for some reason). Right now in that case, this query returns the verbs from a single role. It selects the role with the smallest id, with the thought that in Central right now, more powerful roles have had smaller ids. I think it'd be awesome if we could somehow aggregate the verbs from all the roles. That way, /v1/projects and /v1/projects/:id would return the same set of verbs for any given project. I took a look at the list of JSON functions, but I didn't see a function that would do that aggregation specifically. However, one strategy seems to be to convert each element of each array to a row, then aggregate the rows. Based on a Stack Overflow answer that looks promising, here's a rough attempt at such a subquery:

inner join (
  select id, jsonb_agg(distinct value) as verbs
  from (
    select projects.id, roles.verbs
    from projects
    join assignments on
      assignments."actorId" = ${actorId} and
      assignments."acteeId" in ('*', 'project', projects."acteeId")
    join roles on roles.id = assignments."roleId"
    group by projects.id, roles.verbs
  ) as project_verbs, jsonb_array_elements_text(verbs)
  group by id
  having verbs ? 'project.read'
) as filtered on filtered.id = projects.id