+# Copyright (C) The Arvados Authors. All rights reserved.
+#
+# SPDX-License-Identifier: AGPL-3.0
+
class MaterializedPermissionView < ActiveRecord::Migration
- @@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
+
+ #
+ # Construct a materialized view for permissions. This is a view which is
+ # derived from querying other tables, but is saved to a static table itself
+ # so that it can be indexed and queried efficiently without rerunning the
+ # query. The view is updated using "REFRESH MATERIALIZED VIEW" which is
+ # executed after an operation invalidates the permission graph.
+ #
+
ActiveRecord::Base.connection.execute(
- "CREATE MATERIALIZED VIEW permission_view AS
+"-- 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
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.
+ #
+ # Our default query uses "ORDER BY modified_by desc, uuid"
+ #
+ # It turns out the existing simple index on modified_by can't be used
+ # because of the additional ordering on "uuid".
+ #
+ # To be able to utilize the index, the index ordering has to match the
+ # ORDER BY clause. For more detail see:
+ #
+ # https://www.postgresql.org/docs/9.3/static/indexes-ordering.html
+ #
@@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
projects / arvados.git / blobdiff