1 # Copyright (C) The Arvados Authors. All rights reserved.
3 # SPDX-License-Identifier: AGPL-3.0
5 class PermissionTable < ActiveRecord::Migration[5.0]
7 ActiveRecord::Base.connection.execute "DROP MATERIALIZED VIEW IF EXISTS materialized_permission_view;"
8 drop_table :permission_refresh_lock
10 create_table :materialized_permissions, :id => false do |t|
14 t.boolean :traverse_owned
16 add_index :materialized_permissions, [:user_uuid, :target_uuid], unique: true, name: 'permission_user_target'
17 add_index :materialized_permissions, [:target_uuid], unique: false, name: 'permission_target'
19 ActiveRecord::Base.connection.execute %{
20 create or replace function project_subtree_with_trash_at (starting_uuid varchar(27), starting_trash_at timestamp)
21 returns table (target_uuid varchar(27), trash_at timestamp)
26 project_subtree(uuid, trash_at) as (
27 values (starting_uuid, starting_trash_at)
29 select groups.uuid, LEAST(project_subtree.trash_at, groups.trash_at)
30 from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)
32 select uuid, trash_at from project_subtree;
36 create_table :trashed_groups, :id => false do |t|
40 add_index :trashed_groups, :group_uuid, :unique => true
42 ActiveRecord::Base.connection.execute %{
43 create or replace function compute_trashed ()
44 returns table (uuid varchar(27), trash_at timestamp)
48 select ps.target_uuid as group_uuid, ps.trash_at from groups,
49 lateral project_subtree_with_trash_at(groups.uuid, groups.trash_at) ps
50 where groups.owner_uuid like '_____-tpzed-_______________'
54 ActiveRecord::Base.connection.execute("INSERT INTO trashed_groups select * from compute_trashed()")
56 ActiveRecord::Base.connection.execute %{
57 create or replace function should_traverse_owned (starting_uuid varchar(27),
58 starting_perm integer)
63 select starting_uuid like '_____-j7d0g-_______________' or
64 (starting_uuid like '_____-tpzed-_______________' and starting_perm >= 3);
68 ActiveRecord::Base.connection.execute %{
69 create view permission_graph_edges as
70 select groups.owner_uuid as tail_uuid, groups.uuid as head_uuid, (3) as val from groups
72 select users.owner_uuid as tail_uuid, users.uuid as head_uuid, (3) as val from users
74 select links.tail_uuid,
77 WHEN links.name = 'can_read' THEN 1
78 WHEN links.name = 'can_login' THEN 1
79 WHEN links.name = 'can_write' THEN 2
80 WHEN links.name = 'can_manage' THEN 3
83 where links.link_class='permission'
86 # Get a set of permission by searching the graph and following
87 # ownership and permission links.
89 # edges() - a subselect with the union of ownership and permission links
91 # traverse_graph() - recursive query, from the starting node,
92 # self-join with edges to find outgoing permissions.
93 # Re-runs the query on new rows until there are no more results.
94 # This accomplishes a breadth-first search of the permission graph.
96 ActiveRecord::Base.connection.execute %{
97 create or replace function search_permission_graph (starting_uuid varchar(27),
98 starting_perm integer)
99 returns table (target_uuid varchar(27), val integer, traverse_owned bool)
104 traverse_graph(target_uuid, val, traverse_owned) as (
105 values (starting_uuid, starting_perm,
106 should_traverse_owned(starting_uuid, starting_perm))
108 (select edges.head_uuid,
109 least(edges.val, traverse_graph.val,
110 case traverse_graph.traverse_owned
114 should_traverse_owned(edges.head_uuid, edges.val)
115 from permission_graph_edges as edges, traverse_graph
116 where traverse_graph.target_uuid = edges.tail_uuid))
117 select target_uuid, max(val), bool_or(traverse_owned) from traverse_graph
118 group by (target_uuid);
122 ActiveRecord::Base.connection.execute %{
123 create or replace function compute_permission_subgraph (perm_origin_uuid varchar(27),
124 starting_uuid varchar(27),
125 starting_perm integer)
126 returns table (user_uuid varchar(27), target_uuid varchar(27), val integer, traverse_owned bool)
131 perm_from_start(perm_origin_uuid, target_uuid, val, traverse_owned) as (
132 select perm_origin_uuid, target_uuid, val, traverse_owned
133 from search_permission_graph(starting_uuid, starting_perm)),
135 additional_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
136 select edges.tail_uuid as perm_origin_uuid, ps.target_uuid, ps.val,
137 should_traverse_owned(ps.target_uuid, ps.val)
138 from permission_graph_edges as edges, lateral search_permission_graph(edges.head_uuid, edges.val) as ps
139 where (not (edges.tail_uuid = perm_origin_uuid and
140 edges.head_uuid = starting_uuid)) and
141 edges.tail_uuid not in (select target_uuid from perm_from_start) and
142 edges.head_uuid in (select target_uuid from perm_from_start)),
144 partial_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
145 select * from perm_from_start
147 select * from additional_perms
150 user_identity_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
151 select users.uuid as perm_origin_uuid, ps.target_uuid, ps.val, ps.traverse_owned
152 from users, lateral search_permission_graph(users.uuid, 3) as ps
153 where (users.owner_uuid not in (select target_uuid from partial_perms) or
154 users.owner_uuid = users.uuid) and
155 users.uuid in (select target_uuid from partial_perms)
158 all_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
159 select * from partial_perms
161 select * from user_identity_perms
164 select v.user_uuid, v.target_uuid, max(v.perm_level), bool_or(v.traverse_owned) from
167 least(u.val, m.perm_level) as perm_level,
169 from all_perms as u, materialized_permissions as m
170 where u.perm_origin_uuid = m.target_uuid AND m.traverse_owned
172 select perm_origin_uuid as user_uuid, target_uuid, val as perm_level, traverse_owned
174 where all_perms.perm_origin_uuid like '_____-tpzed-_______________') as v
175 group by v.user_uuid, v.target_uuid
179 ActiveRecord::Base.connection.execute %{
180 INSERT INTO materialized_permissions
181 select users.uuid, g.target_uuid, g.val, g.traverse_owned
182 from users, lateral search_permission_graph(users.uuid, 3) as g where g.val > 0
187 drop_table :materialized_permissions
188 drop_table :trashed_groups
190 ActiveRecord::Base.connection.execute "DROP function project_subtree_with_trash_at (varchar, timestamp);"
191 ActiveRecord::Base.connection.execute "DROP function compute_trashed ();"
192 ActiveRecord::Base.connection.execute "DROP function search_permission_graph(varchar, integer);"
193 ActiveRecord::Base.connection.execute "DROP function compute_permission_subgraph (varchar, varchar, integer);"
194 ActiveRecord::Base.connection.execute "DROP function should_traverse_owned(varchar, integer);"
195 ActiveRecord::Base.connection.execute "DROP view permission_graph_edges;"
197 ActiveRecord::Base.connection.execute(%{
198 CREATE MATERIALIZED VIEW materialized_permission_view AS
199 WITH RECURSIVE perm_value(name, val) AS (
200 VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)
201 ), perm_edges(tail_uuid, head_uuid, val, follow, trashed) AS (
202 SELECT links.tail_uuid,
205 ((pv.val = 3) OR (groups.uuid IS NOT NULL)) AS follow,
206 (0)::smallint AS trashed,
207 (0)::smallint AS followtrash
209 LEFT JOIN perm_value pv ON ((pv.name = (links.name)::text)))
210 LEFT JOIN public.groups ON (((pv.val < 3) AND ((groups.uuid)::text = (links.head_uuid)::text))))
211 WHERE ((links.link_class)::text = 'permission'::text)
213 SELECT groups.owner_uuid,
218 WHEN ((groups.trash_at IS NOT NULL) AND (groups.trash_at < clock_timestamp())) THEN 1
223 ), perm(val, follow, user_uuid, target_uuid, trashed) AS (
224 SELECT (3)::smallint AS val,
226 (users.uuid)::character varying(32) AS user_uuid,
227 (users.uuid)::character varying(32) AS target_uuid,
228 (0)::smallint AS trashed
231 SELECT (LEAST((perm_1.val)::integer, edges.val))::smallint AS val,
234 (edges.head_uuid)::character varying(32) AS target_uuid,
235 ((GREATEST((perm_1.trashed)::integer, edges.trashed) * edges.followtrash))::smallint AS trashed
237 JOIN perm_edges edges ON ((perm_1.follow AND ((edges.tail_uuid)::text = (perm_1.target_uuid)::text))))
239 SELECT perm.user_uuid,
241 max(perm.val) AS perm_level,
243 WHEN true THEN perm.target_uuid
244 ELSE NULL::character varying
245 END AS target_owner_uuid,
246 max(perm.trashed) AS trashed
248 GROUP BY perm.user_uuid, perm.target_uuid,
250 WHEN true THEN perm.target_uuid
251 ELSE NULL::character varying
257 add_index :materialized_permission_view, [:trashed, :target_uuid], name: 'permission_target_trashed'
258 add_index :materialized_permission_view, [:user_uuid, :trashed, :perm_level], name: 'permission_target_user_trashed_level'
259 create_table :permission_refresh_lock
261 ActiveRecord::Base.connection.execute 'REFRESH MATERIALIZED VIEW materialized_permission_view;'