1 # Copyright (C) The Arvados Authors. All rights reserved.
3 # SPDX-License-Identifier: AGPL-3.0
5 require '20200501150153_permission_table_constants'
7 class PermissionTable < ActiveRecord::Migration[5.0]
9 # This is a major migration. We are replacing the
10 # materialized_permission_view, which is fully recomputed any time
11 # a permission changes (and becomes very expensive as the number
12 # of users/groups becomes large), with a new strategy that only
13 # recomputes permissions for the subset of objects that are
14 # potentially affected by the addition or removal of a permission
15 # relationship (i.e. ownership or a permission link).
17 # This also disentangles the concept of "trashed groups" from the
18 # permissions system. Updating trashed items follows a similar
19 # (but less complicated) strategy to updating permissions, so it
20 # may be helpful to look at that first.
23 ActiveRecord::Base.connection.execute "DROP MATERIALIZED VIEW IF EXISTS materialized_permission_view;"
24 drop_table :permission_refresh_lock
26 # This table stores the set of trashed groups and their trash_at
27 # time. Used to exclude trashed projects and their contents when
28 # getting object listings.
29 create_table :trashed_groups, :id => false do |t|
33 add_index :trashed_groups, :group_uuid, :unique => true
35 ActiveRecord::Base.connection.execute %{
36 create or replace function project_subtree_with_trash_at (starting_uuid varchar(27), starting_trash_at timestamp)
37 returns table (target_uuid varchar(27), trash_at timestamp)
41 /* Starting from a project, recursively traverse all the projects
42 underneath it and return a set of project uuids and trash_at times
43 (may be null). The initial trash_at can be a timestamp or null.
44 The trash_at time propagates downward to groups it owns, i.e. when a
45 group is trashed, everything underneath it in the ownership
46 hierarchy is also considered trashed. However, this is fact is
47 recorded in the trashed_groups table, not by updating trash_at field
51 project_subtree(uuid, trash_at) as (
52 values (starting_uuid, starting_trash_at)
54 select groups.uuid, LEAST(project_subtree.trash_at, groups.trash_at)
55 from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)
57 select uuid, trash_at from project_subtree;
61 # Now populate the table. For a non-test databse this is the only
62 # time this ever happens, after this the trash table is updated
63 # incrementally. See app/models/group.rb#update_trash
66 # The table to store the flattened permissions. This is almost
67 # exactly the same as the old materalized_permission_view except
68 # that the target_owner_uuid colunm in the view is now just a
69 # boolean traverse_owned (the column was only ever tested for null
72 # For details on how this table is used to apply permissions to
73 # queries, see app/models/arvados_model.rb#readable_by
75 create_table :materialized_permissions, :id => false do |t|
79 t.boolean :traverse_owned
81 add_index :materialized_permissions, [:user_uuid, :target_uuid], unique: true, name: 'permission_user_target'
82 add_index :materialized_permissions, [:target_uuid], unique: false, name: 'permission_target'
84 ActiveRecord::Base.connection.execute %{
85 create or replace function should_traverse_owned (starting_uuid varchar(27),
86 starting_perm integer)
91 /* Helper function. Determines if permission on an object implies
92 transitive permission to things the object owns. This is always
93 true for groups, but only true for users when the permission level
96 select starting_uuid like '_____-j7d0g-_______________' or
97 (starting_uuid like '_____-tpzed-_______________' and starting_perm >= 3);
101 # Merge all permission relationships into a single view. This
102 # consists of: groups (projects) owning things, users owning
103 # things, users owning themselves, and explicit permission links.
105 # A SQL view gets inlined into the query where it is used as a
106 # subquery. This enables the query planner to inject constraints,
107 # so we only look up edges we plan to traverse and avoid a brute
108 # force query of all edges.
109 ActiveRecord::Base.connection.execute %{
110 create view permission_graph_edges as
111 select groups.owner_uuid as tail_uuid, groups.uuid as head_uuid,
112 (3) as val, groups.uuid as edge_id from groups
114 select users.owner_uuid as tail_uuid, users.uuid as head_uuid,
115 (3) as val, users.uuid as edge_id from users
117 select users.uuid as tail_uuid, users.uuid as head_uuid,
118 (3) as val, '' as edge_id from users
120 select links.tail_uuid,
123 WHEN links.name = 'can_read' THEN 1
124 WHEN links.name = 'can_login' THEN 1
125 WHEN links.name = 'can_write' THEN 2
126 WHEN links.name = 'can_manage' THEN 3
129 links.uuid as edge_id
131 where links.link_class='permission'
134 # Code fragment that is used below. This is used to ensure that
135 # the permission edge passed into compute_permission_subgraph
136 # takes precedence over an existing edge in the "edges" view.
138 case (edges.edge_id = perm_edge_id)
139 when true then starting_perm
145 # The primary function to compute permissions for a subgraph.
146 # This originally was organized somewhat more cleanly, but this
147 # ran into performance issues due to the query optimizer not
148 # working across function and "with" expression boundaries. So I
149 # had to fall back on using string templates for the repeated
152 ActiveRecord::Base.connection.execute %{
153 create or replace function compute_permission_subgraph (perm_origin_uuid varchar(27),
154 starting_uuid varchar(27),
155 starting_perm integer,
156 perm_edge_id varchar(27))
157 returns table (user_uuid varchar(27), target_uuid varchar(27), val integer, traverse_owned bool)
162 /* The purpose of this function is to compute the permissions for a
163 subgraph of the database, starting from a given edge. The newly
164 computed permissions are used to add and remove rows from the main
167 perm_origin_uuid: The object that 'gets' the permission.
169 starting_uuid: The starting object the permission applies to.
171 starting_perm: The permission that perm_origin_uuid 'has' on
172 starting_uuid One of 1, 2, 3 for can_read,
173 can_write, can_manage respectively, or 0 to revoke
177 /* Starting from starting_uuid, determine the set of objects that
178 could be affected by this permission change.
180 Note: We don't traverse users unless it is an "identity"
181 permission (permission origin is self).
183 perm_from_start(perm_origin_uuid, target_uuid, val, traverse_owned) as (
184 #{PERM_QUERY_TEMPLATE % {:base_case => %{
185 values (perm_origin_uuid, starting_uuid, starting_perm,
186 should_traverse_owned(starting_uuid, starting_perm),
187 (perm_origin_uuid = starting_uuid or starting_uuid not like '_____-tpzed-_______________'))
189 :edge_perm => edge_perm
192 /* Find other inbound edges that grant permissions to 'targets' in
193 perm_from_start, and compute permissions that originate from
196 This is necessary for two reasons:
198 1) Other users may have access to a subset of the objects
199 through other permission links than the one we started from.
200 If we don't recompute them, their permission will get dropped.
202 2) There may be more than one path through which a user gets
203 permission to an object. For example, a user owns a project
204 and also shares it can_read with a group the user belongs
205 to. adding the can_read link must not overwrite the existing
206 can_manage permission granted by ownership.
208 additional_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
209 #{PERM_QUERY_TEMPLATE % {:base_case => %{
210 select edges.tail_uuid as origin_uuid, edges.head_uuid as target_uuid, edges.val,
211 should_traverse_owned(edges.head_uuid, edges.val),
212 edges.head_uuid like '_____-j7d0g-_______________'
213 from permission_graph_edges as edges
214 where edges.edge_id != perm_edge_id and
215 edges.tail_uuid not in (select target_uuid from perm_from_start where target_uuid like '_____-j7d0g-_______________') and
216 edges.head_uuid in (select target_uuid from perm_from_start)
218 :edge_perm => edge_perm
221 /* Combine the permissions computed in the first two phases. */
222 all_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
223 select * from perm_from_start
225 select * from additional_perms
228 /* The actual query that produces rows to be added or removed
229 from the materialized_permissions table. This is the clever
234 * For every group, the materialized_permissions lists all users
235 that can access to that group.
237 * The all_perms subquery has computed permissions on on a set of
238 objects for all inbound "origins", which are users or groups.
240 * Permissions through groups are transitive.
244 1) The materialized_permissions table declares that user X has permission N on group Y
245 2) The all_perms result has determined group Y has permission M on object Z
246 3) Therefore, user X has permission min(N, M) on object Z
248 This allows us to efficiently determine the set of users that
249 have permissions on the subset of objects, without having to
250 follow the chain of permission back up to find those users.
252 In addition, because users always have permission on themselves, this
253 query also makes sure those permission rows are always
256 select v.user_uuid, v.target_uuid, max(v.perm_level), bool_or(v.traverse_owned) from
259 least(u.val, m.perm_level) as perm_level,
261 from all_perms as u, materialized_permissions as m
262 where u.perm_origin_uuid = m.target_uuid AND m.traverse_owned
263 AND (m.user_uuid = m.target_uuid or m.target_uuid not like '_____-tpzed-_______________')
265 select target_uuid as user_uuid, target_uuid, 3, true
267 where all_perms.target_uuid like '_____-tpzed-_______________') as v
268 group by v.user_uuid, v.target_uuid
273 # Populate materialized_permissions by traversing permissions
274 # starting at each user.
280 drop_table :materialized_permissions
281 drop_table :trashed_groups
283 ActiveRecord::Base.connection.execute "DROP function project_subtree_with_trash_at (varchar, timestamp);"
284 ActiveRecord::Base.connection.execute "DROP function compute_permission_subgraph (varchar, varchar, integer, varchar);"
285 ActiveRecord::Base.connection.execute "DROP function should_traverse_owned(varchar, integer);"
286 ActiveRecord::Base.connection.execute "DROP view permission_graph_edges;"
288 ActiveRecord::Base.connection.execute(%{
289 CREATE MATERIALIZED VIEW materialized_permission_view AS
290 WITH RECURSIVE perm_value(name, val) AS (
291 VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)
292 ), perm_edges(tail_uuid, head_uuid, val, follow, trashed) AS (
293 SELECT links.tail_uuid,
296 ((pv.val = 3) OR (groups.uuid IS NOT NULL)) AS follow,
297 (0)::smallint AS trashed,
298 (0)::smallint AS followtrash
300 LEFT JOIN perm_value pv ON ((pv.name = (links.name)::text)))
301 LEFT JOIN public.groups ON (((pv.val < 3) AND ((groups.uuid)::text = (links.head_uuid)::text))))
302 WHERE ((links.link_class)::text = 'permission'::text)
304 SELECT groups.owner_uuid,
309 WHEN ((groups.trash_at IS NOT NULL) AND (groups.trash_at < clock_timestamp())) THEN 1
314 ), perm(val, follow, user_uuid, target_uuid, trashed) AS (
315 SELECT (3)::smallint AS val,
317 (users.uuid)::character varying(32) AS user_uuid,
318 (users.uuid)::character varying(32) AS target_uuid,
319 (0)::smallint AS trashed
322 SELECT (LEAST((perm_1.val)::integer, edges.val))::smallint AS val,
325 (edges.head_uuid)::character varying(32) AS target_uuid,
326 ((GREATEST((perm_1.trashed)::integer, edges.trashed) * edges.followtrash))::smallint AS trashed
328 JOIN perm_edges edges ON ((perm_1.follow AND ((edges.tail_uuid)::text = (perm_1.target_uuid)::text))))
330 SELECT perm.user_uuid,
332 max(perm.val) AS perm_level,
334 WHEN true THEN perm.target_uuid
335 ELSE NULL::character varying
336 END AS target_owner_uuid,
337 max(perm.trashed) AS trashed
339 GROUP BY perm.user_uuid, perm.target_uuid,
341 WHEN true THEN perm.target_uuid
342 ELSE NULL::character varying
348 add_index :materialized_permission_view, [:trashed, :target_uuid], name: 'permission_target_trashed'
349 add_index :materialized_permission_view, [:user_uuid, :trashed, :perm_level], name: 'permission_target_user_trashed_level'
350 create_table :permission_refresh_lock
352 ActiveRecord::Base.connection.execute 'REFRESH MATERIALIZED VIEW materialized_permission_view;'