X-Git-Url: https://git.arvados.org/arvados.git/blobdiff_plain/fc5742654641a10e765ed81d25ca44cb47976d02..HEAD:/services/api/lib/update_permissions.rb diff --git a/services/api/lib/update_permissions.rb b/services/api/lib/update_permissions.rb index b2cf6595b8..52e3e0c081 100644 --- a/services/api/lib/update_permissions.rb +++ b/services/api/lib/update_permissions.rb @@ -2,31 +2,14 @@ # # SPDX-License-Identifier: AGPL-3.0 -PERMISSION_VIEW = "materialized_permissions" -TRASHED_GROUPS = "trashed_groups" +require_relative '20200501150153_permission_table_constants' -def refresh_permissions - ActiveRecord::Base.transaction do - ActiveRecord::Base.connection.execute("LOCK TABLE #{PERMISSION_VIEW}") - ActiveRecord::Base.connection.execute("DELETE FROM #{PERMISSION_VIEW}") - ActiveRecord::Base.connection.execute %{ -INSERT INTO #{PERMISSION_VIEW} -select users.uuid, g.target_uuid, g.val, g.traverse_owned -from users, lateral search_permission_graph(users.uuid, 3) as g where g.val > 0 -}, - "refresh_permission_view.do" - end -end +REVOKE_PERM = 0 +CAN_MANAGE_PERM = 3 -def refresh_trashed - ActiveRecord::Base.transaction do - ActiveRecord::Base.connection.execute("LOCK TABLE #{TRASHED_GROUPS}") - ActiveRecord::Base.connection.execute("DELETE FROM #{TRASHED_GROUPS}") - ActiveRecord::Base.connection.execute("INSERT INTO #{TRASHED_GROUPS} select * from compute_trashed()") - end -end +def update_permissions perm_origin_uuid, starting_uuid, perm_level, edge_id=nil, update_all_users=false + return if Thread.current[:suppress_update_permissions] -def update_permissions perm_origin_uuid, starting_uuid, perm_level # # Update a subset of the permission table affected by adding or # removing a particular permission relationship (ownership or a @@ -68,12 +51,23 @@ def update_permissions perm_origin_uuid, starting_uuid, perm_level # see db/migrate/20200501150153_permission_table.rb for details on # how the permissions are computed. + if edge_id.nil? + # For changes of ownership, edge_id is starting_uuid. In turns + # out most invocations of update_permissions are for changes of + # ownership, so make this parameter optional to reduce + # clutter. + # For permission links, the uuid of the link object will be passed in for edge_id. + edge_id = starting_uuid + end + ActiveRecord::Base.transaction do - # "Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE - # ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This - # mode protects a table against concurrent data changes." - ActiveRecord::Base.connection.execute "LOCK TABLE #{PERMISSION_VIEW} in SHARE MODE" + # "Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE + # EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS + # EXCLUSIVE lock modes. This mode allows only concurrent ACCESS + # SHARE locks, i.e., only reads from the table can proceed in + # parallel with a transaction holding this lock mode." + ActiveRecord::Base.connection.execute "LOCK TABLE #{PERMISSION_VIEW} in EXCLUSIVE MODE" # Workaround for # BUG #15160: planner overestimates number of rows in join when there are more than 200 rows coming from CTE @@ -106,39 +100,118 @@ def update_permissions perm_origin_uuid, starting_uuid, perm_level # tested this on Postgres 9.6, so in the future we should reevaluate # the performance & query plan on Postgres 12. # + # Update: as of 2023-10-13, incorrect merge join behavior is still + # observed on at least one major user installation that is using + # Postgres 14, so it seems this workaround is still needed. + # # https://git.furworks.de/opensourcemirror/postgresql/commit/a314c34079cf06d05265623dd7c056f8fa9d577f # # Disable merge join for just this query (also local for this transaction), then reenable it. ActiveRecord::Base.connection.exec_query "SET LOCAL enable_mergejoin to false;" - temptable_perms = "temp_perms_#{rand(2**64).to_s(10)}" - ActiveRecord::Base.connection.exec_query %{ -create temporary table #{temptable_perms} on commit drop -as select * from compute_permission_subgraph($1, $2, $3) -}, - 'update_permissions.select', - [[nil, perm_origin_uuid], - [nil, starting_uuid], - [nil, perm_level]] - - ActiveRecord::Base.connection.exec_query "SET LOCAL enable_mergejoin to true;" - - ActiveRecord::Base.connection.exec_delete %{ -delete from #{PERMISSION_VIEW} where - target_uuid in (select target_uuid from #{temptable_perms}) and - not exists (select 1 from #{temptable_perms} - where target_uuid=#{PERMISSION_VIEW}.target_uuid and - user_uuid=#{PERMISSION_VIEW}.user_uuid and - val>0) + if perm_origin_uuid[5..11] == '-tpzed-' && !update_all_users + # Modifying permission granted to a user, recompute the all permissions for that user + + ActiveRecord::Base.connection.exec_query %{ +with origin_user_perms as ( + select pq.origin_uuid as user_uuid, target_uuid, pq.val, pq.traverse_owned from ( + #{PERM_QUERY_TEMPLATE % {:base_case => %{ + select '#{perm_origin_uuid}'::varchar(255), '#{perm_origin_uuid}'::varchar(255), 3, true, true + where exists (select uuid from users where uuid='#{perm_origin_uuid}') }, - "update_permissions.delete" +:edge_perm => %{ +case (edges.edge_id = '#{edge_id}') + when true then #{perm_level} + else edges.val + end +} +} }) as pq), + +/* + Because users always have permission on themselves, this + query also makes sure those permission rows are always + returned. +*/ +temptable_perms as ( + select * from origin_user_perms + union all + select target_uuid as user_uuid, target_uuid, 3, true + from origin_user_perms + where origin_user_perms.target_uuid like '_____-tpzed-_______________' and + origin_user_perms.target_uuid != '#{perm_origin_uuid}' +), + +/* + Now that we have recomputed a set of permissions, delete any + rows from the materialized_permissions table where (target_uuid, + user_uuid) is not present or has perm_level=0 in the recomputed + set. +*/ +delete_rows as ( + delete from #{PERMISSION_VIEW} where + user_uuid='#{perm_origin_uuid}' and + not exists (select 1 from temptable_perms + where target_uuid=#{PERMISSION_VIEW}.target_uuid and + user_uuid='#{perm_origin_uuid}' and + val>0) +) +/* + Now insert-or-update permissions in the recomputed set. The + WHERE clause is important to avoid redundantly updating rows + that haven't actually changed. +*/ +insert into #{PERMISSION_VIEW} (user_uuid, target_uuid, perm_level, traverse_owned) + select user_uuid, target_uuid, val as perm_level, traverse_owned from temptable_perms where val>0 +on conflict (user_uuid, target_uuid) do update +set perm_level=EXCLUDED.perm_level, traverse_owned=EXCLUDED.traverse_owned +where #{PERMISSION_VIEW}.user_uuid=EXCLUDED.user_uuid and + #{PERMISSION_VIEW}.target_uuid=EXCLUDED.target_uuid and + (#{PERMISSION_VIEW}.perm_level != EXCLUDED.perm_level or + #{PERMISSION_VIEW}.traverse_owned != EXCLUDED.traverse_owned); + +} + else + # Modifying permission granted to a group, recompute permissions for everything accessible through that group ActiveRecord::Base.connection.exec_query %{ +with temptable_perms as ( + select * from compute_permission_subgraph($1, $2, $3, $4)), + +/* + Now that we have recomputed a set of permissions, delete any + rows from the materialized_permissions table where (target_uuid, + user_uuid) is not present or has perm_level=0 in the recomputed + set. +*/ +delete_rows as ( + delete from #{PERMISSION_VIEW} where + target_uuid in (select target_uuid from temptable_perms) and + not exists (select 1 from temptable_perms + where target_uuid=#{PERMISSION_VIEW}.target_uuid and + user_uuid=#{PERMISSION_VIEW}.user_uuid and + val>0) +) + +/* + Now insert-or-update permissions in the recomputed set. The + WHERE clause is important to avoid redundantly updating rows + that haven't actually changed. +*/ insert into #{PERMISSION_VIEW} (user_uuid, target_uuid, perm_level, traverse_owned) - select user_uuid, target_uuid, val as perm_level, traverse_owned from #{temptable_perms} where val>0 -on conflict (user_uuid, target_uuid) do update set perm_level=EXCLUDED.perm_level, traverse_owned=EXCLUDED.traverse_owned; + select user_uuid, target_uuid, val as perm_level, traverse_owned from temptable_perms where val>0 +on conflict (user_uuid, target_uuid) do update +set perm_level=EXCLUDED.perm_level, traverse_owned=EXCLUDED.traverse_owned +where #{PERMISSION_VIEW}.user_uuid=EXCLUDED.user_uuid and + #{PERMISSION_VIEW}.target_uuid=EXCLUDED.target_uuid and + (#{PERMISSION_VIEW}.perm_level != EXCLUDED.perm_level or + #{PERMISSION_VIEW}.traverse_owned != EXCLUDED.traverse_owned); }, - "update_permissions.insert" + 'update_permissions.select', + [perm_origin_uuid, + starting_uuid, + perm_level, + edge_id] + end if perm_level>0 check_permissions_against_full_refresh @@ -149,7 +222,7 @@ end def check_permissions_against_full_refresh # No-op except when running tests - return unless Rails.env == 'test' and !Thread.current[:no_check_permissions_against_full_refresh] + return unless Rails.env == 'test' and !Thread.current[:no_check_permissions_against_full_refresh] and !Thread.current[:suppress_update_permissions] # For checking correctness of the incremental permission updates. # Check contents of the current 'materialized_permission' table @@ -161,9 +234,12 @@ order by user_uuid, target_uuid }, "check_permissions_against_full_refresh.permission_table" q2 = ActiveRecord::Base.connection.exec_query %{ -select users.uuid as user_uuid, g.target_uuid, g.val as perm_level, g.traverse_owned -from users, lateral search_permission_graph(users.uuid, 3) as g where g.val > 0 -order by users.uuid, target_uuid + select pq.origin_uuid as user_uuid, target_uuid, pq.val as perm_level, pq.traverse_owned from ( + #{PERM_QUERY_TEMPLATE % {:base_case => %{ + select uuid, uuid, 3, true, true from users +}, +:edge_perm => 'edges.val' +} }) as pq order by origin_uuid, target_uuid }, "check_permissions_against_full_refresh.full_recompute" if q1.count != q2.count @@ -196,3 +272,27 @@ def skip_check_permissions_against_full_refresh Thread.current[:no_check_permissions_against_full_refresh] = check_perm_was end end + +def batch_update_permissions + check_perm_was = Thread.current[:suppress_update_permissions] + Thread.current[:suppress_update_permissions] = true + begin + yield + ensure + Thread.current[:suppress_update_permissions] = check_perm_was + refresh_permissions + end +end + +# Used to account for permissions that a user gains by having +# can_manage on another user. +# +# note: in theory a user could have can_manage access to a user +# through multiple levels, that isn't handled here (would require a +# recursive query). I think that's okay because users getting +# transitive access through "can_manage" on a user is is rarely/never +# used feature and something we probably want to deprecate and remove. +USER_UUIDS_SUBQUERY_TEMPLATE = %{ +select target_uuid from materialized_permissions where user_uuid in (%{user}) +and target_uuid like '_____-tpzed-_______________' and traverse_owned=true and perm_level >= %{perm_level} +}