From 0e3fc1c56dc4b0aea228dc3de045e218749de2e3 Mon Sep 17 00:00:00 2001 From: Peter Amstutz Date: Fri, 8 Dec 2017 15:56:33 -0500 Subject: [PATCH] 12765: Add explicit flag whether "trashed" propagates across an edge Arvados-DCO-1.1-Signed-off-by: Peter Amstutz --- .../20171208203841_fix_trash_flag_follow.rb | 75 +++++++++++++++++++ services/api/db/structure.sql | 10 ++- 2 files changed, 82 insertions(+), 3 deletions(-) create mode 100644 services/api/db/migrate/20171208203841_fix_trash_flag_follow.rb diff --git a/services/api/db/migrate/20171208203841_fix_trash_flag_follow.rb b/services/api/db/migrate/20171208203841_fix_trash_flag_follow.rb new file mode 100644 index 0000000000..b93dc54fcd --- /dev/null +++ b/services/api/db/migrate/20171208203841_fix_trash_flag_follow.rb @@ -0,0 +1,75 @@ +class FixTrashFlagFollow < ActiveRecord::Migration + def change + ActiveRecord::Base.connection.execute("DROP MATERIALIZED VIEW materialized_permission_view") + ActiveRecord::Base.connection.execute( +"-- constructing perm_edges +-- 1. get the list of all permission links, +-- 2. any can_manage link or permission link to a group means permission should 'follow through' +-- (as a special case, can_manage links to a user grant access to everything owned by the user, +-- unlike can_read or can_write which only grant access to the user record) +-- 3. add all owner->owned relationships between groups as can_manage edges +-- +-- constructing permissions +-- 1. base case: start with set of all users as the working set +-- 2. recursive case: +-- join with edges where the tail is in the working set and 'follow' is true +-- produce a new working set with the head (target) of each edge +-- set permission to the least permission encountered on the path +-- propagate trashed flag down + +CREATE MATERIALIZED VIEW materialized_permission_view AS +WITH RECURSIVE +perm_value (name, val) AS ( + VALUES + ('can_read', 1::smallint), + ('can_login', 1), + ('can_write', 2), + ('can_manage', 3) + ), +perm_edges (tail_uuid, head_uuid, val, follow, trashed) AS ( + SELECT links.tail_uuid, + links.head_uuid, + pv.val, + (pv.val = 3 OR groups.uuid IS NOT NULL) AS follow, + 0::smallint AS trashed, + 0::smallint AS followtrash + FROM links + LEFT JOIN perm_value pv ON pv.name = links.name + LEFT JOIN groups ON pv.val<3 AND groups.uuid = links.head_uuid + WHERE links.link_class = 'permission' + UNION ALL + SELECT owner_uuid, uuid, 3, true, + CASE WHEN trash_at IS NOT NULL and trash_at < clock_timestamp() THEN 1 ELSE 0 END, + 1 + FROM groups + ), +perm (val, follow, user_uuid, target_uuid, trashed) AS ( + SELECT 3::smallint AS val, + true AS follow, + users.uuid::varchar(32) AS user_uuid, + users.uuid::varchar(32) AS target_uuid, + 0::smallint AS trashed + FROM users + UNION + SELECT LEAST(perm.val, edges.val)::smallint AS val, + edges.follow AS follow, + perm.user_uuid::varchar(32) AS user_uuid, + edges.head_uuid::varchar(32) AS target_uuid, + (GREATEST(perm.trashed, edges.trashed) * edges.followtrash)::smallint AS trashed + FROM perm + INNER JOIN perm_edges edges + ON perm.follow AND edges.tail_uuid = perm.target_uuid +) +SELECT user_uuid, + target_uuid, + MAX(val) AS perm_level, + CASE follow WHEN true THEN target_uuid ELSE NULL END AS target_owner_uuid, + MAX(trashed) AS trashed + FROM perm + GROUP BY user_uuid, target_uuid, target_owner_uuid; +") + add_index :materialized_permission_view, [:trashed, :target_uuid], name: 'permission_target_trashed' + add_index :materialized_permission_view, [:user_uuid, :trashed, :perm_level], name: 'permission_target_user_trashed_level' + ActiveRecord::Base.connection.execute("REFRESH MATERIALIZED VIEW materialized_permission_view") + end +end diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index d2da430102..60fd88a98b 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -778,7 +778,8 @@ CREATE MATERIALIZED VIEW materialized_permission_view AS links.head_uuid, pv.val, ((pv.val = 3) OR (groups.uuid IS NOT NULL)) AS follow, - (0)::smallint AS trashed + (0)::smallint AS trashed, + (0)::smallint AS followtrash FROM ((links LEFT JOIN perm_value pv ON ((pv.name = (links.name)::text))) LEFT JOIN groups ON (((pv.val < 3) AND ((groups.uuid)::text = (links.head_uuid)::text)))) @@ -791,7 +792,8 @@ CREATE MATERIALIZED VIEW materialized_permission_view AS CASE WHEN ((groups.trash_at IS NOT NULL) AND (groups.trash_at < clock_timestamp())) THEN 1 ELSE 0 - END AS "case" + END AS "case", + 1 FROM groups ), perm(val, follow, user_uuid, target_uuid, trashed) AS ( SELECT (3)::smallint AS val, @@ -805,7 +807,7 @@ CREATE MATERIALIZED VIEW materialized_permission_view AS edges.follow, perm_1.user_uuid, (edges.head_uuid)::character varying(32) AS target_uuid, - (GREATEST((perm_1.trashed)::integer, edges.trashed))::smallint AS trashed + ((GREATEST((perm_1.trashed)::integer, edges.trashed) * edges.followtrash))::smallint AS trashed FROM (perm perm_1 JOIN perm_edges edges ON ((perm_1.follow AND ((edges.tail_uuid)::text = (perm_1.target_uuid)::text)))) ) @@ -3035,3 +3037,5 @@ INSERT INTO schema_migrations (version) VALUES ('20170824202826'); INSERT INTO schema_migrations (version) VALUES ('20170906224040'); INSERT INTO schema_migrations (version) VALUES ('20171027183824'); + +INSERT INTO schema_migrations (version) VALUES ('20171208203841'); -- 2.39.5