|
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)
|