1 class FixTrashFlagFollow < ActiveRecord::Migration
3 ActiveRecord::Base.connection.execute("DROP MATERIALIZED VIEW materialized_permission_view")
4 ActiveRecord::Base.connection.execute(
5 "-- constructing perm_edges
6 -- 1. get the list of all permission links,
7 -- 2. any can_manage link or permission link to a group means permission should 'follow through'
8 -- (as a special case, can_manage links to a user grant access to everything owned by the user,
9 -- unlike can_read or can_write which only grant access to the user record)
10 -- 3. add all owner->owned relationships between groups as can_manage edges
12 -- constructing permissions
13 -- 1. base case: start with set of all users as the working set
15 -- join with edges where the tail is in the working set and 'follow' is true
16 -- produce a new working set with the head (target) of each edge
17 -- set permission to the least permission encountered on the path
18 -- propagate trashed flag down
20 CREATE MATERIALIZED VIEW materialized_permission_view AS
22 perm_value (name, val) AS (
24 ('can_read', 1::smallint),
29 perm_edges (tail_uuid, head_uuid, val, follow, trashed) AS (
30 SELECT links.tail_uuid,
33 (pv.val = 3 OR groups.uuid IS NOT NULL) AS follow,
34 0::smallint AS trashed,
35 0::smallint AS followtrash
37 LEFT JOIN perm_value pv ON pv.name = links.name
38 LEFT JOIN groups ON pv.val<3 AND groups.uuid = links.head_uuid
39 WHERE links.link_class = 'permission'
41 SELECT owner_uuid, uuid, 3, true,
42 CASE WHEN trash_at IS NOT NULL and trash_at < clock_timestamp() THEN 1 ELSE 0 END,
46 perm (val, follow, user_uuid, target_uuid, trashed) AS (
47 SELECT 3::smallint AS val,
49 users.uuid::varchar(32) AS user_uuid,
50 users.uuid::varchar(32) AS target_uuid,
51 0::smallint AS trashed
54 SELECT LEAST(perm.val, edges.val)::smallint AS val,
55 edges.follow AS follow,
56 perm.user_uuid::varchar(32) AS user_uuid,
57 edges.head_uuid::varchar(32) AS target_uuid,
58 (GREATEST(perm.trashed, edges.trashed) * edges.followtrash)::smallint AS trashed
60 INNER JOIN perm_edges edges
61 ON perm.follow AND edges.tail_uuid = perm.target_uuid
65 MAX(val) AS perm_level,
66 CASE follow WHEN true THEN target_uuid ELSE NULL END AS target_owner_uuid,
67 MAX(trashed) AS trashed
69 GROUP BY user_uuid, target_uuid, target_owner_uuid;
71 add_index :materialized_permission_view, [:trashed, :target_uuid], name: 'permission_target_trashed'
72 add_index :materialized_permission_view, [:user_uuid, :trashed, :perm_level], name: 'permission_target_user_trashed_level'
73 ActiveRecord::Base.connection.execute("REFRESH MATERIALIZED VIEW materialized_permission_view")