1 # Copyright (C) The Arvados Authors. All rights reserved.
3 # SPDX-License-Identifier: AGPL-3.0
5 # These constants are used in both
6 # db/migrate/20200501150153_permission_table and update_permissions
8 # This file allows them to be easily imported by both to avoid duplication.
10 # Don't mess with this! Any changes will affect both the current
11 # update_permissions and the past migration. If you are tinkering
12 # with the permission system and need to change how
13 # PERM_QUERY_TEMPLATE, refresh_trashed or refresh_permissions works,
14 # you should make a new file with your modified functions and have
15 # update_permissions reference that file instead.
17 PERMISSION_VIEW = "materialized_permissions"
19 TRASHED_GROUPS = "trashed_groups"
21 # We need to use this parameterized query in a few different places,
22 # including as a subquery in a larger query.
24 # There's basically two options, the way I did this originally was to
25 # put this in a postgres function and do a lateral join over it.
26 # However, postgres functions impose an optimization barrier, and
27 # possibly have other overhead with temporary tables, so I ended up
28 # going with the brute force approach of inlining the whole thing.
30 # The two substitutions are "base_case" which determines the initial
31 # set of permission origins and "override" which is used to ensure
32 # that the new permission takes precedence over the one in the edges
33 # table (but some queries don't need that.)
35 PERM_QUERY_TEMPLATE = %{
37 traverse_graph(origin_uuid, target_uuid, val, traverse_owned, starting_set) as (
40 (select traverse_graph.origin_uuid,
45 should_traverse_owned(edges.head_uuid, edges.val),
47 from permission_graph_edges as edges, traverse_graph
48 where traverse_graph.target_uuid = edges.tail_uuid
49 and (edges.tail_uuid like '_____-j7d0g-_______________' or
50 traverse_graph.starting_set)))
51 select traverse_graph.origin_uuid, target_uuid, max(val) as val, bool_or(traverse_owned) as traverse_owned from traverse_graph
52 group by (traverse_graph.origin_uuid, target_uuid)
56 ActiveRecord::Base.transaction do
57 ActiveRecord::Base.connection.execute("LOCK TABLE #{TRASHED_GROUPS}")
58 ActiveRecord::Base.connection.execute("DELETE FROM #{TRASHED_GROUPS}")
60 # Helper populate trashed_groups table. This starts with
61 # each group owned by a user and computes the subtree under that
62 # group to find any groups that are trashed.
63 ActiveRecord::Base.connection.execute(%{
64 INSERT INTO #{TRASHED_GROUPS}
65 select ps.target_uuid as group_uuid, ps.trash_at from groups,
66 lateral project_subtree_with_trash_at(groups.uuid, groups.trash_at) ps
67 where groups.owner_uuid like '_____-tpzed-_______________'
72 def refresh_permissions
73 ActiveRecord::Base.transaction do
74 ActiveRecord::Base.connection.execute("LOCK TABLE #{PERMISSION_VIEW}")
75 ActiveRecord::Base.connection.execute("DELETE FROM #{PERMISSION_VIEW}")
77 ActiveRecord::Base.connection.execute %{
78 INSERT INTO materialized_permissions
79 #{PERM_QUERY_TEMPLATE % {:base_case => %{
80 select uuid, uuid, 3, true, true from users
84 }, "refresh_permission_view.do"