#
# 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
# 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
# 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
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
}, "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
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}
+}
projects / arvados.git / blobdiff