#
# SPDX-License-Identifier: AGPL-3.0
-require '20200501150153_permission_table_constants'
+require_relative '20200501150153_permission_table_constants'
REVOKE_PERM = 0
CAN_MANAGE_PERM = 3
-def update_permissions perm_origin_uuid, starting_uuid, perm_level, edge_id=nil
+def update_permissions perm_origin_uuid, starting_uuid, perm_level, edge_id=nil, update_all_users=false
return if Thread.current[:suppress_update_permissions]
#
# 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
+ # https://www.postgresql.org/message-id/152395805004.19366.3107109716821067806@wrigleys.postgresql.org
+ #
+ # For a crucial join in the compute_permission_subgraph() query, the
+ # planner mis-estimates the number of rows in a Common Table
+ # Expression (CTE, this is a subquery in a WITH clause) and as a
+ # result it chooses the wrong join order. The join starts with the
+ # permissions table because it mistakenly thinks
+ # count(materalized_permissions) < count(new computed permissions)
+ # when actually it is the other way around.
+ #
+ # Because of the incorrect join order, it choose the wrong join
+ # strategy (merge join, which works best when two tables are roughly
+ # the same size). As a workaround, we can tell it not to use that
+ # join strategy, this causes it to pick hash join instead, which
+ # turns out to be a bit better. However, because the join order is
+ # still wrong, we don't get the full benefit of the index.
+ #
+ # This is very unfortunate because it makes the query performance
+ # dependent on the size of the materalized_permissions table, when
+ # the goal of this design was to make permission updates scale-free
+ # and only depend on the number of permissions affected and not the
+ # total table size. In several hours of researching I wasn't able
+ # to find a way to force the correct join order, so I'm calling it
+ # here and I have to move on.
+ #
+ # This is apparently addressed in Postgres 12, but I developed &
+ # 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;"
+
+ 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}')
+},
+: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)),
#{PERMISSION_VIEW}.traverse_owned != EXCLUDED.traverse_owned);
},
'update_permissions.select',
- [[nil, perm_origin_uuid],
- [nil, starting_uuid],
- [nil, perm_level],
- [nil, edge_id]]
+ [perm_origin_uuid,
+ starting_uuid,
+ perm_level,
+ edge_id]
+ end
if perm_level>0
check_permissions_against_full_refresh
projects / arvados.git / blobdiff