Merge branch '15370-install-docker'

[arvados.git] / services / api / lib / create_permission_view.sql

diff --git a/services/api/lib/create_permission_view.sql b/services/api/lib/create_permission_view.sql
index 639610ac2a6efd53a7be2cbaf9b29a0608e805e6..0c4c77b7504d69fd102e0e3ba0009c837857bf18 100644 (file)
--- a/services/api/lib/create_permission_view.sql
+++ b/services/api/lib/create_permission_view.sql
@@ -2,20 +2,8 @@
 --
 -- SPDX-License-Identifier: AGPL-3.0
 
--- 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
+-- Note: this is not the current code used for permission checks (that is
+-- materialized_permission_view), but is retained here for migration purposes.
 
 CREATE TEMPORARY VIEW permission_view AS
 WITH RECURSIVE
@@ -26,45 +14,35 @@ perm_value (name, val) AS (
      ('can_write',  2),
      ('can_manage', 3)
      ),
-perm_edges (tail_uuid, head_uuid, val, follow, trashed) AS (
+perm_edges (tail_uuid, head_uuid, val, follow) 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
+              (pv.val = 3 OR groups.uuid IS NOT NULL) AS follow
               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
-              FROM groups
+       SELECT owner_uuid, uuid, 3, true FROM groups
        ),
-perm (val, follow, user_uuid, target_uuid, trashed, startnode) AS (
+perm (val, follow, user_uuid, target_uuid) AS (
      SELECT 3::smallint             AS val,
-            false                   AS follow,
+            true                    AS follow,
             users.uuid::varchar(32) AS user_uuid,
-            users.uuid::varchar(32) AS target_uuid,
-            0::smallint             AS trashed,
-            true                    AS startnode
+            users.uuid::varchar(32) AS target_uuid
             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)::smallint AS trashed,
-            false                                 AS startnode
+     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
             FROM perm
             INNER JOIN perm_edges edges
-            ON (perm.startnode or perm.follow) AND edges.tail_uuid = perm.target_uuid
+            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;
+       val AS perm_level,
+       CASE follow WHEN true THEN target_uuid ELSE NULL END AS target_owner_uuid
+       FROM perm;