1 class PermissionTable < ActiveRecord::Migration[5.0]
3 create_table :materialized_permissions, :id => false do |t|
7 t.boolean :traverse_owned
9 add_index :materialized_permissions, [:user_uuid, :target_uuid], unique: true, name: 'permission_user_target'
11 ActiveRecord::Base.connection.execute %{
12 create or replace function project_subtree (starting_uuid varchar(27))
13 returns table (target_uuid varchar(27))
18 project_subtree(uuid) as (
19 values (starting_uuid)
21 select groups.uuid from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)
23 select uuid from project_subtree;
27 ActiveRecord::Base.connection.execute %{
28 create or replace function project_subtree_with_trash_at (starting_uuid varchar(27), starting_trash_at timestamp)
29 returns table (target_uuid varchar(27), trash_at timestamp)
34 project_subtree(uuid, trash_at) as (
35 values (starting_uuid, starting_trash_at)
37 select groups.uuid, LEAST(project_subtree.trash_at, groups.trash_at)
38 from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)
40 select uuid, trash_at from project_subtree;
44 create_table :trashed_groups, :id => false do |t|
48 add_index :trashed_groups, :group_uuid, :unique => true
50 ActiveRecord::Base.connection.execute %{
51 create or replace function compute_trashed ()
52 returns table (uuid varchar(27), trash_at timestamp)
56 select ps.target_uuid as group_uuid, ps.trash_at from groups,
57 lateral project_subtree_with_trash_at(groups.uuid, groups.trash_at) ps
58 where groups.owner_uuid like '_____-tpzed-_______________'
62 ActiveRecord::Base.connection.execute("INSERT INTO trashed_groups select * from compute_trashed()")
64 ActiveRecord::Base.connection.execute %{
65 create or replace function should_traverse_owned (starting_uuid varchar(27),
66 starting_perm integer)
71 select starting_uuid like '_____-j7d0g-_______________' or
72 (starting_uuid like '_____-tpzed-_______________' and starting_perm >= 3);
77 ActiveRecord::Base.connection.execute %{
78 create or replace function permission_graph_edges ()
79 returns table (tail_uuid varchar(27), head_uuid varchar(27), val integer)
83 select groups.owner_uuid, groups.uuid, (3) from groups
85 select users.owner_uuid, users.uuid, (3) from users
87 select links.tail_uuid,
90 WHEN links.name = 'can_read' THEN 1
91 WHEN links.name = 'can_login' THEN 1
92 WHEN links.name = 'can_write' THEN 2
93 WHEN links.name = 'can_manage' THEN 3
96 where links.link_class='permission'
100 # Get a set of permission by searching the graph and following
101 # ownership and permission links.
103 # edges() - a subselect with the union of ownership and permission links
105 # traverse_graph() - recursive query, from the starting node,
106 # self-join with edges to find outgoing permissions.
107 # Re-runs the query on new rows until there are no more results.
108 # This accomplishes a breadth-first search of the permission graph.
110 ActiveRecord::Base.connection.execute %{
111 create or replace function search_permission_graph (starting_uuid varchar(27),
112 starting_perm integer)
113 returns table (target_uuid varchar(27), val integer, traverse_owned bool)
117 WITH RECURSIVE edges(tail_uuid, head_uuid, val) as (
118 select * from permission_graph_edges()
120 traverse_graph(target_uuid, val, traverse_owned) as (
121 values (starting_uuid, starting_perm,
122 should_traverse_owned(starting_uuid, starting_perm))
124 (select edges.head_uuid,
125 least(edges.val, traverse_graph.val,
126 case traverse_graph.traverse_owned
130 should_traverse_owned(edges.head_uuid, edges.val)
132 join traverse_graph on (traverse_graph.target_uuid = edges.tail_uuid)))
133 select target_uuid, max(val), bool_or(traverse_owned) from traverse_graph
134 group by (target_uuid) ;
139 # owned_by_user_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
140 # select users.owner_uuid as perm_origin_uuid, u.target_uuid, u.val, u.traverse_owned
141 # from users, lateral search_permission_graph(users.uuid, 3) as u
142 # where users.owner_uuid not in (select target_uuid from perm_from_start) and
143 # users.uuid in (select target_uuid from perm_from_start)
146 # owned_by_group_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
147 # select groups.owner_uuid as perm_origin_uuid, groups.uuid, 3, true
149 # where groups.owner_uuid not in (select target_uuid from perm_from_start) and
150 # groups.uuid in (select target_uuid from perm_from_start)
154 ActiveRecord::Base.connection.execute %{
155 create or replace function compute_permission_subgraph (perm_origin_uuid varchar(27),
156 starting_uuid varchar(27),
157 starting_perm integer)
158 returns table (user_uuid varchar(27), target_uuid varchar(27), val integer, traverse_owned bool)
163 perm_from_start(perm_origin_uuid, target_uuid, val, traverse_owned) as (
164 select perm_origin_uuid, target_uuid, val, traverse_owned
165 from search_permission_graph(starting_uuid, starting_perm)),
167 edges(tail_uuid, head_uuid, val) as (
168 select * from permission_graph_edges()),
170 additional_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
171 select edges.tail_uuid as perm_origin_uuid, ps.target_uuid, ps.val,
172 should_traverse_owned(ps.target_uuid, ps.val)
173 from edges, lateral search_permission_graph(edges.head_uuid, edges.val) as ps
174 where (not (edges.tail_uuid = perm_origin_uuid and
175 edges.head_uuid = starting_uuid and
176 edges.val = starting_perm)) and
177 edges.tail_uuid not in (select target_uuid from perm_from_start) and
178 edges.head_uuid in (select target_uuid from perm_from_start)),
180 partial_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
181 select * from perm_from_start
183 select * from additional_perms
186 user_identity_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
187 select users.uuid as perm_origin_uuid, ps.target_uuid, ps.val, ps.traverse_owned
188 from users, lateral search_permission_graph(users.uuid, 3) as ps
189 where users.owner_uuid not in (select target_uuid from partial_perms where traverse_owned) and
190 users.uuid in (select target_uuid from partial_perms)
193 all_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
194 select * from partial_perms
196 select * from user_identity_perms
199 select v.user_uuid, v.target_uuid, max(v.perm_level), bool_or(v.traverse_owned) from
200 (select materialized_permissions.user_uuid,
202 least(u.val, materialized_permissions.perm_level) as perm_level,
205 join materialized_permissions on (u.perm_origin_uuid = materialized_permissions.target_uuid)
206 where materialized_permissions.traverse_owned
208 select perm_origin_uuid as user_uuid, target_uuid, val as perm_level, traverse_owned
210 where perm_origin_uuid like '_____-tpzed-_______________') as v
211 group by v.user_uuid, v.target_uuid
215 ActiveRecord::Base.connection.execute "DROP MATERIALIZED VIEW IF EXISTS materialized_permission_view;"
219 drop_table :materialized_permissions
220 drop_table :trashed_groups
222 ActiveRecord::Base.connection.execute "DROP function project_subtree (varchar);"
223 ActiveRecord::Base.connection.execute "DROP function project_subtree_with_trash_at (varchar, timestamp);"
224 ActiveRecord::Base.connection.execute "DROP function compute_trashed ();"
225 ActiveRecord::Base.connection.execute "DROP function search_permission_graph(varchar, integer);"
226 ActiveRecord::Base.connection.execute "DROP function compute_permission_subgraph (varchar, varchar, integer);"
227 ActiveRecord::Base.connection.execute "DROP function should_traverse_owned(varchar, integer);"
228 ActiveRecord::Base.connection.execute "DROP function permission_graph_edges();"