#
# SPDX-License-Identifier: AGPL-3.0
-class MaterializedPermissionView < ActiveRecord::Migration
+class MaterializedPermissionView < ActiveRecord::Migration[4.2]
- @@idxtables = [:collections, :container_requests, :groups, :jobs, :links, :pipeline_instances, :pipeline_templates, :repositories, :users, :virtual_machines, :workflows]
+ @@idxtables = [:collections, :container_requests, :groups, :jobs, :links, :pipeline_instances, :pipeline_templates, :repositories, :users, :virtual_machines, :workflows, :logs]
def up
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"
+-- 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
+-- 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 permission_view AS
+CREATE MATERIALIZED VIEW materialized_permission_view AS
WITH RECURSIVE
perm_value (name, val) AS (
VALUES
CASE WHEN trash_at IS NOT NULL and trash_at < clock_timestamp() THEN 1 ELSE 0 END
FROM groups
),
-perm (val, follow, user_uuid, target_uuid, trashed, startnode) AS (
+perm (val, follow, user_uuid, target_uuid, trashed) 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
+ 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)::smallint AS trashed,
- false AS startnode
+ GREATEST(perm.trashed, edges.trashed)::smallint AS trashed
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,
FROM perm
GROUP BY user_uuid, target_uuid, target_owner_uuid;
")
- add_index :permission_view, [:trashed, :target_uuid], name: 'permission_target_trashed'
- add_index :permission_view, [:user_uuid, :trashed, :perm_level], name: 'permission_target_user_trashed_level'
+ 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'
# Indexes on the other tables are essential to for the query planner to
# construct an efficient join with permission_view.
@@idxtables.each do |table|
ActiveRecord::Base.connection.execute("CREATE INDEX index_#{table.to_s}_on_modified_at_uuid ON #{table.to_s} USING btree (modified_at desc, uuid asc)")
end
+
+ create_table :permission_refresh_lock
+ ActiveRecord::Base.connection.execute("REFRESH MATERIALIZED VIEW materialized_permission_view")
end
def down
- remove_index :permission_view, name: 'permission_target_trashed'
- remove_index :permission_view, name: 'permission_target_user_trashed_level'
+ drop_table :permission_refresh_lock
+ remove_index :materialized_permission_view, name: 'permission_target_trashed'
+ remove_index :materialized_permission_view, name: 'permission_target_user_trashed_level'
@@idxtables.each do |table|
- ActiveRecord::Base.connection.execute("DROP INDEX index_#{table.to_s}_on_modified_at_uuid")
+ ActiveRecord::Base.connection.execute("DROP INDEX IF EXISTS index_#{table.to_s}_on_modified_at_uuid")
end
- ActiveRecord::Base.connection.execute("DROP MATERIALIZED VIEW IF EXISTS permission_view")
+ ActiveRecord::Base.connection.execute("DROP MATERIALIZED VIEW IF EXISTS materialized_permission_view")
end
end