1 # Copyright (C) The Arvados Authors. All rights reserved.
3 # SPDX-License-Identifier: AGPL-3.0
5 require '20200501150153_permission_table_constants'
10 def update_permissions perm_origin_uuid, starting_uuid, perm_level, edge_id=nil
12 # Update a subset of the permission table affected by adding or
13 # removing a particular permission relationship (ownership or a
16 # perm_origin_uuid: This is the object that 'gets' the permission.
17 # It is the owner_uuid or tail_uuid.
19 # starting_uuid: The object we are computing permission for (or head_uuid)
21 # perm_level: The level of permission that perm_origin_uuid gets for starting_uuid.
23 # perm_level is a number from 0-3
27 # or call with perm_level=0 to revoke permissions
29 # check: for testing/debugging, compare the result of the
30 # incremental update against a full table recompute. Throws an
31 # error if the contents are not identical (ie they produce different
36 # Give a change in a specific permission relationship, we recompute
37 # the set of permissions (for all users) that could possibly be
38 # affected by that relationship. For example, if a project is
39 # shared with another user, we recompute all permissions for all
40 # projects in the hierarchy. This returns a set of updated
41 # permissions, which we stash in a temporary table.
43 # Then, for each user_uuid/target_uuid in the updated permissions
44 # result set we insert/update a permission row in
45 # materialized_permissions, and delete any rows that exist in
46 # materialized_permissions that are not in the result set or have
49 # see db/migrate/20200501150153_permission_table.rb for details on
50 # how the permissions are computed.
53 edge_id = starting_uuid
56 ActiveRecord::Base.transaction do
58 # "Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE
59 # ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This
60 # mode protects a table against concurrent data changes."
61 ActiveRecord::Base.connection.execute "LOCK TABLE #{PERMISSION_VIEW} in SHARE MODE"
64 # BUG #15160: planner overestimates number of rows in join when there are more than 200 rows coming from CTE
65 # https://www.postgresql.org/message-id/152395805004.19366.3107109716821067806@wrigleys.postgresql.org
67 # For a crucial join in the compute_permission_subgraph() query, the
68 # planner mis-estimates the number of rows in a Common Table
69 # Expression (CTE, this is a subquery in a WITH clause) and as a
70 # result it chooses the wrong join order. The join starts with the
71 # permissions table because it mistakenly thinks
72 # count(materalized_permissions) < count(new computed permissions)
73 # when actually it is the other way around.
75 # Because of the incorrect join order, it choose the wrong join
76 # strategy (merge join, which works best when two tables are roughly
77 # the same size). As a workaround, we can tell it not to use that
78 # join strategy, this causes it to pick hash join instead, which
79 # turns out to be a bit better. However, because the join order is
80 # still wrong, we don't get the full benefit of the index.
82 # This is very unfortunate because it makes the query performance
83 # dependent on the size of the materalized_permissions table, when
84 # the goal of this design was to make permission updates scale-free
85 # and only depend on the number of permissions affected and not the
86 # total table size. In several hours of researching I wasn't able
87 # to find a way to force the correct join order, so I'm calling it
88 # here and I have to move on.
90 # This is apparently addressed in Postgres 12, but I developed &
91 # tested this on Postgres 9.6, so in the future we should reevaluate
92 # the performance & query plan on Postgres 12.
94 # https://git.furworks.de/opensourcemirror/postgresql/commit/a314c34079cf06d05265623dd7c056f8fa9d577f
96 # Disable merge join for just this query (also local for this transaction), then reenable it.
97 ActiveRecord::Base.connection.exec_query "SET LOCAL enable_mergejoin to false;"
99 temptable_perms = "temp_perms_#{rand(2**64).to_s(10)}"
100 ActiveRecord::Base.connection.exec_query %{
101 create temporary table #{temptable_perms} on commit drop
102 as select * from compute_permission_subgraph($1, $2, $3, $4)
104 'update_permissions.select',
105 [[nil, perm_origin_uuid],
106 [nil, starting_uuid],
110 ActiveRecord::Base.connection.exec_query "SET LOCAL enable_mergejoin to true;"
112 ActiveRecord::Base.connection.exec_delete %{
113 delete from #{PERMISSION_VIEW} where
114 target_uuid in (select target_uuid from #{temptable_perms}) and
115 not exists (select 1 from #{temptable_perms}
116 where target_uuid=#{PERMISSION_VIEW}.target_uuid and
117 user_uuid=#{PERMISSION_VIEW}.user_uuid and
120 "update_permissions.delete"
122 ActiveRecord::Base.connection.exec_query %{
123 insert into #{PERMISSION_VIEW} (user_uuid, target_uuid, perm_level, traverse_owned)
124 select user_uuid, target_uuid, val as perm_level, traverse_owned from #{temptable_perms} where val>0
125 on conflict (user_uuid, target_uuid) do update set perm_level=EXCLUDED.perm_level, traverse_owned=EXCLUDED.traverse_owned;
127 "update_permissions.insert"
130 check_permissions_against_full_refresh
136 def check_permissions_against_full_refresh
137 # No-op except when running tests
138 return unless Rails.env == 'test' and !Thread.current[:no_check_permissions_against_full_refresh]
140 # For checking correctness of the incremental permission updates.
141 # Check contents of the current 'materialized_permission' table
142 # against a from-scratch permission refresh.
144 q1 = ActiveRecord::Base.connection.exec_query %{
145 select user_uuid, target_uuid, perm_level, traverse_owned from #{PERMISSION_VIEW}
146 order by user_uuid, target_uuid
147 }, "check_permissions_against_full_refresh.permission_table"
149 q2 = ActiveRecord::Base.connection.exec_query %{
150 select pq.origin_uuid as user_uuid, target_uuid, pq.val as perm_level, pq.traverse_owned from (
151 #{PERM_QUERY_TEMPLATE % {:base_case => %{
152 select uuid, uuid, 3, true, true from users
154 :edge_perm => 'edges.val'
155 } }) as pq order by origin_uuid, target_uuid
156 }, "check_permissions_against_full_refresh.full_recompute"
158 if q1.count != q2.count
159 puts "Didn't match incremental+: #{q1.count} != full refresh-: #{q2.count}"
162 if q1.count > q2.count
163 q1.each_with_index do |r, i|
165 puts "+#{r}\n-#{q2[i]}"
170 q2.each_with_index do |r, i|
172 puts "+#{q1[i]}\n-#{r}"
179 def skip_check_permissions_against_full_refresh
180 check_perm_was = Thread.current[:no_check_permissions_against_full_refresh]
181 Thread.current[:no_check_permissions_against_full_refresh] = true
185 Thread.current[:no_check_permissions_against_full_refresh] = check_perm_was
189 # Used to account for permissions that a user gains by having
190 # can_manage on another user.
192 # note: in theory a user could have can_manage access to a user
193 # through multiple levels, that isn't handled here (would require a
194 # recursive query). I think that's okay because users getting
195 # transitive access through "can_manage" on a user is is rarely/never
196 # used feature and something we probably want to deprecate and remove.
197 USER_UUIDS_SUBQUERY_TEMPLATE = %{
198 select target_uuid from materialized_permissions where user_uuid in (%{user})
199 and target_uuid like '_____-tpzed-_______________' and traverse_owned=true and perm_level >= %{perm_level}