Arvados

WITH RECURSIVE

	project_subtree(uuid) as (

--	values ('x1u39-j7d0g-rufsjii9txtu2xg')

--	values ('x1u39-tpzed-fr97h9t4m5jffxs')

	values ('x1u39-tpzed-3kz0nwtjehhl0u4')

	union

	select groups.uuid from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)

	)

	select uuid from project_subtree;

WITH RECURSIVE edges(tail_uuid, head_uuid) as (

	  select groups.owner_uuid, groups.uuid from groups

	  union

	  select links.tail_uuid, links.head_uuid from links

	   where links.link_class='permission'

	),

	project_subtree(uuid) as (

--	values ('x1u39-j7d0g-rufsjii9txtu2xg')

--	values ('x1u39-tpzed-fr97h9t4m5jffxs')

	values ('x1u39-tpzed-3kz0nwtjehhl0u4')

	union

	(select edges.head_uuid from edges join project_subtree on (edges.tail_uuid = project_subtree.uuid)

	))

	select uuid from project_subtree;

WITH RECURSIVE perm_value(name, val) AS (

         VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)

        ),

        edges(tail_uuid, head_uuid, val) as (

	  select groups.owner_uuid, groups.uuid, (3) from groups

	  union

	  select links.tail_uuid, links.head_uuid, pv.val from links

	  JOIN perm_value pv ON ((pv.name = (links.name)::text))

	   where links.link_class='permission'

	),

	project_subtree(target_uuid, val) as (

--	values ('x1u39-j7d0g-rufsjii9txtu2xg')

--	values ('x1u39-tpzed-fr97h9t4m5jffxs')

	values ('x1u39-tpzed-3kz0nwtjehhl0u4', 3)

	union

	(select edges.head_uuid, least(edges.val, project_subtree.val) from edges join project_subtree

	on (edges.tail_uuid = project_subtree.target_uuid)

	))

	select target_uuid, val from project_subtree;

create table computed_permissions (

user_uuid character varying (27), target_uuid character varying (27), perm_level smallint);

create unique index computed_permissions_index on computed_permissions (user_uuid, target_uuid);

-- unique index on (user, target_uuid)

-- Permission in model:

-- tail (owner_uuid)

-- permission level

-- head (or uuid of new project or group)

-- Adding a permission

-- with recursive search from head, get affected nodes

-- next, select (users, permission) with target_uuid = tail (or owner_uuid)

-- insert \forall users \forall nodes (user, node, least(user perm, node perm))

--   on conflict update set greatest(existing perm, new perm)

-- Removing a permission

-- with recursive search from head, get affected nodes

-- delete all permissions with target_uuid in affected nodes

-- select permission links where

--   head is in the affected nodes

--   tail is in computed permissions

-- re-add each link (via adding a permission, above)

begin;

create TEMPORARY table affected_perms on commit drop

as WITH RECURSIVE perm_value(name, val)  AS (

         VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)

        ),

        edges(tail_uuid, head_uuid, val) as (

	  select groups.owner_uuid, groups.uuid, (3) from groups

	  union

	  select links.tail_uuid, links.head_uuid, pv.val from links

	  JOIN perm_value pv ON ((pv.name = (links.name)::text))

	   where links.link_class='permission' and links.head_uuid not like '%-tpzed-%'

	),

	project_subtree(target_uuid, val) as (

--	values ('x1u39-j7d0g-rufsjii9txtu2xg')

--	values ('x1u39-tpzed-fr97h9t4m5jffxs')

--	values ('x1u39-j7d0g-qjt4uv6kq0ntm3y', 3)

	values ('x1u39-tpzed-3kz0nwtjehhl0u4', 3)

	union

	(select edges.head_uuid, least(edges.val, project_subtree.val) from edges join project_subtree

	on (edges.tail_uuid = project_subtree.target_uuid)))

	select target_uuid, val from project_subtree;

	select * from affected_perms;

	insert into computed_permissions select 'x1u39-tpzed-3kz0nwtjehhl0u4', target_uuid, val from affected_perms;

	delete from computed_permissions

	where exists

	(select 1 from affected_perms where

	 computed_permissions.user_uuid='x1u39-tpzed-3kz0nwtjehhl0u4'

	 and computed_permissions.target_uuid=affected_perms.target_uuid

	 and computed_permissions.val=affected_perms.val);

	create TEMPORARY table links_to_recompute on commit drop

	as WITH perm_value(name, val)  AS (

         VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)

        )

	select links.tail_uuid, links.head_uuid, pv.val from links

	  JOIN affected_perms on (links.head_uuid = affected_perms.target_uuid)

	  JOIN perm_value pv ON ((pv.name = (links.name)::text))

	  where links.link_class='permission';

	select * from links_to_recompute;

commit;

create or replace function project_subtree (starting_uuid varchar(27))

returns table (target_uuid varchar(27))

STABLE

language SQL

as $$

WITH RECURSIVE

	project_subtree(uuid) as (

	values (starting_uuid)

	union

	select groups.uuid from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)

	)

	select uuid from project_subtree;

$$;

create or replace function search_permission_graph (starting_uuid varchar(27), starting_perm smallint)

returns table (target_uuid varchar(27), val smallint)

STABLE

language SQL

as $$

WITH RECURSIVE perm_value(name, val)  AS (

         VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)

        ),

        edges(tail_uuid, head_uuid, val) as (

	  select groups.owner_uuid, groups.uuid, (3) from groups

	  union

	  select links.tail_uuid, links.head_uuid, pv.val from links

	  JOIN perm_value pv ON ((pv.name = (links.name)::text))

	   where links.link_class='permission'

	),

	project_subtree(target_uuid, val) as (

	values (starting_uuid, starting_perm)

	union

	(select edges.head_uuid, least(edges.val, project_subtree.val)::smallint from edges join project_subtree

	on (edges.tail_uuid = project_subtree.target_uuid)

	where edges.tail_uuid like '_____-j7d0g-_______________'

	     or edges.tail_uuid = starting_uuid))

	select target_uuid, max(val) from project_subtree

	group by (target_uuid) ;

$$;

create or replace function add_permission (tail_uuid varchar(27), head_uuid varchar(27), perm_value smallint)

returns table(user_uuid character varying (27), target_uuid character varying (27), val smallint)

VOLATILE

language SQL

as $$

with

  affected_nodes(target_uuid, val)

    as (select target_uuid, val from search_permission_graph(head_uuid, perm_value)),

  affected_users(user_uuid, val)

    as (select user_uuid, perm_level from computed_permissions where target_uuid = tail_uuid),

  new_perms(user_uuid, target_uuid, val)

    as (select affected_users.user_uuid, affected_nodes.target_uuid, least(affected_nodes.val, affected_users.val)

      from affected_nodes cross join affected_users)

  insert into computed_permissions (user_uuid, target_uuid, perm_level)

    (select user_uuid, target_uuid, val from new_perms)

    on conflict (user_uuid, target_uuid) do update set perm_level=greatest(computed_permissions.perm_level, excluded.perm_level)

    returning *;

$$;

create or replace function clear_permission (head_uuid varchar(27))

returns table(user_uuid character varying (27), target_uuid character varying (27), val smallint)

VOLATILE

language SQL

as $$

with

  affected_nodes(target_uuid)

    as (select target_uuid from search_permission_graph(head_uuid, 0::smallint))

  delete from computed_permissions where

    target_uuid in (select target_uuid from affected_nodes where target_uuid <> user_uuid)

    returning *;

$$;

create or replace function remove_permission_helper (starting_uuid varchar(27))

returns table(link_uuid character varying (27), tail_uuid character varying (27), head_uuid character varying (27), val smallint)

VOLATILE

language SQL

as $$

with perm_value(name, val)  AS (

    VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)),

  removed_rows(user_uuid, target_uuid, val) as (

    select user_uuid, target_uuid, val from clear_permission(starting_uuid))

  select links.uuid, links.tail_uuid, links.head_uuid, pv.val::smallint from links

      JOIN perm_value pv ON ((pv.name = (links.name)::text))

      where links.link_class='permission' and

        exists (select 1 from computed_permissions where

	  links.tail_uuid=computed_permissions.target_uuid) and

        exists (select 1 from removed_rows where links.head_uuid=removed_rows.target_uuid)

$$;

create or replace function delete_permission_link (link_uuid varchar(27), link_head_uuid varchar(27))

returns table(tail_uuid character varying (27), head_uuid character varying (27), val smallint)

VOLATILE

language SQL

as $$

with links_to_recompute(link_uuid, tail_uuid, head_uuid, val) as

    (select * from remove_permission_helper(link_head_uuid)),

  _delete_link as (delete from links where links.uuid=link_uuid)

  select ap.user_uuid, ap.target_uuid, ap.val

    from links_to_recompute,

      lateral add_permission(links_to_recompute.tail_uuid, links_to_recompute.head_uuid, links_to_recompute.val::smallint) ap

    where links_to_recompute.link_uuid <> link_uuid

$$;

create or replace function delete_group (group_uuid varchar(27))

returns table(tail_uuid character varying (27), head_uuid character varying (27), val smallint)

VOLATILE

language SQL

as $$

with links_to_recompute(link_uuid, tail_uuid, head_uuid, val) as

    (select * from remove_permission_helper(group_uuid)),

  _delete_group as (delete from groups where groups.uuid=group_uuid)

  select ap.user_uuid, ap.target_uuid, ap.val

    from links_to_recompute,

      lateral add_permission(links_to_recompute.tail_uuid, links_to_recompute.head_uuid, links_to_recompute.val::smallint) ap

$$;

create or replace function move_group (group_uuid varchar(27), new_owner_uuid varchar(27))

returns table(tail_uuid character varying (27), head_uuid character varying (27), val smallint)

VOLATILE

language SQL

as $$

with links_to_recompute(link_uuid, tail_uuid, head_uuid, val) as

    (values ('', new_owner_uuid, group_uuid, 3::smallint)

    union

    select link_uuid, tail_uuid, head_uuid, val from remove_permission_helper(group_uuid)),

  update_group as (update groups set owner_uuid=new_owner_uuid where groups.uuid=group_uuid)

  select ap.user_uuid, ap.target_uuid, ap.val

    from links_to_recompute,

      lateral add_permission(links_to_recompute.tail_uuid, links_to_recompute.head_uuid, links_to_recompute.val::smallint) ap

$$;

insert into computed_permissions (user_uuid, target_uuid, perm_level) values ('x1u39-tpzed-3kz0nwtjehhl0u4', 'x1u39-tpzed-3kz0nwtjehhl0u4', 3);

select * from add_permission('x1u39-tpzed-3kz0nwtjehhl0u4', 'x1u39-tpzed-3kz0nwtjehhl0u4', 3::smallint);

-- x1u39-j7d0g-sh5mwu9t8b71al6 -> Project under old user (owned by x1u39-j7d0g-7mj64tj4fe3snmo)

-- x1u39-j7d0g-qjt4uv6kq0ntm3y -> Foghord (can_write x1u39-j7d0g-7mj64tj4fe3snmo) (can_read x1u39-tpzed-3kz0nwtjehhl0u4)

-- x1u39-j7d0g-sij00wivb8v3hkt -> Thinger holder (owned by x1u39-tpzed-3kz0nwtjehhl0u4)

-- x1u39-j7d0g-7mj64tj4fe3snmo -> Data from old user

-- x1u39-tpzed-3kz0nwtjehhl0u4 -> c c (can_manage x1u39-j7d0g-qjt4uv6kq0ntm3y)