- ActiveRecord::Base.connection.execute %{
-create or replace function search_permission_graph (starting_uuid varchar(27),
- starting_perm integer,
- override_edge_tail varchar(27) default null,
- override_edge_head varchar(27) default null,
- override_edge_perm integer default null)
- returns table (target_uuid varchar(27), val integer, traverse_owned bool)
-STABLE
-language SQL
-as $$
-/*
- From starting_uuid, perform a recursive self-join on the edges
- to follow chains of permissions. This is a breadth-first search
- of the permission graph. Permission is propagated across edges,
- which may narrow the permission for subsequent links (eg I start
- at can_manage but when traversing a can_read link everything
- touched through that link will only be can_read).
-
- Yields the set of objects that are potentially affected, and
- their permission levels granted by having starting_perm on
- starting_uuid.
-
- If starting_uuid is a user, this computes the entire set of
- permissions for that user (because it returns everything that is
- reachable by that user).
-
- Used by the compute_permission_subgraph function.
-*/
-WITH RECURSIVE
- traverse_graph(target_uuid, val, traverse_owned) as (
- values (starting_uuid, starting_perm,
- should_traverse_owned(starting_uuid, starting_perm))
- union
- (select edges.head_uuid,
- least(edges.val,
- traverse_graph.val,
- case traverse_graph.traverse_owned
- when true then null
- else 0
- end,
- case (edges.tail_uuid = override_edge_tail AND
- edges.head_uuid = override_edge_head)
- when true then override_edge_perm
- else null
- end),
- should_traverse_owned(edges.head_uuid, edges.val)
- from permission_graph_edges as edges, traverse_graph
- where traverse_graph.target_uuid = edges.tail_uuid))
- select target_uuid, max(val), bool_or(traverse_owned) from traverse_graph
- group by (target_uuid);
-$$;