94 lines
3.3 KiB
PL/PgSQL
94 lines
3.3 KiB
PL/PgSQL
create type public.perm_mode as enum ('-', 'r', 'w');
|
|
|
|
create table public.perm
|
|
( id int not null primary key generated always as identity
|
|
, uid human_uuid.huid not null unique default human_uuid.huid()
|
|
, owner_user int not null references public.usr(id)
|
|
, owner_group int not null references public.grp(id)
|
|
, owner_user_mode public.perm_mode not null
|
|
, owner_group_mode public.perm_mode not null
|
|
, everyone_mode public.perm_mode not null
|
|
, path text not null unique
|
|
);
|
|
|
|
create function do_insert_usr_perm() returns trigger language plpgsql as $$
|
|
declare
|
|
admins int;
|
|
begin
|
|
admins := (public.grp_admins()).id;
|
|
|
|
insert into public.perm
|
|
(path, owner_user, owner_group, owner_user_mode, owner_group_mode, everyone_mode)
|
|
values
|
|
('/users/' || human_uuid.huid_to_string(NEW.uid) || '/tag', NEW.id, admins, 'w', 'w', 'r')
|
|
, ('/users/' || human_uuid.huid_to_string(NEW.uid) || '/email', NEW.id, admins, 'w', 'w', '-')
|
|
, ('/users/' || human_uuid.huid_to_string(NEW.uid) || '/deleted', NEW.id, admins, 'w', 'w', '-')
|
|
, ('/users/' || human_uuid.huid_to_string(NEW.uid) || '/password', NEW.id, admins, 'w', 'w', '-')
|
|
;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
create trigger insert_usr_perm
|
|
after insert on public.usr
|
|
for each row execute function do_insert_usr_perm();
|
|
|
|
create function do_insert_grp_perm() returns trigger language plpgsql as $$
|
|
begin
|
|
insert into public.perm
|
|
(path, owner_user, owner_group, owner_user_mode, owner_group_mode, everyone_mode)
|
|
values
|
|
('/groups/' || human_uuid.huid_to_string(NEW.uid) || '/members', (public.get_acting_usr()).id, NEW.id, 'w', 'w', '-')
|
|
, ('/groups/' || human_uuid.huid_to_string(NEW.uid) || '/name', (public.get_acting_usr()).id, NEW.id, 'w', 'w', 'r')
|
|
;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
create trigger insert_grp_perm
|
|
after insert on public.grp
|
|
for each row
|
|
execute function do_insert_grp_perm();
|
|
|
|
create function do_insert_community_perm() returns trigger language plpgsql as $$
|
|
begin
|
|
-- TODO: insert some default community groups, use community_XX_admin as group owner
|
|
|
|
insert into public.perm
|
|
(path, owner_user, owner_group, owner_user_mode, owner_group_mode, everyone_mode)
|
|
values
|
|
('/communities/' || human_uuid.huid_to_string(NEW.uid) || '/posts', (public.get_acting_usr()).id, NEW.id, 'w', 'w', 'r')
|
|
, ('/communities/' || human_uuid.huid_to_string(NEW.uid) || '/tag', (public.get_acting_usr()).id, NEW.id, 'w', 'w', 'r')
|
|
, ('/communities/' || human_uuid.huid_to_string(NEW.uid) || '/deleted', (public.get_acting_usr()).id, NEW.id, 'w', 'w', '-')
|
|
;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
create trigger insert_community_perm
|
|
after insert on public.community
|
|
for each row
|
|
execute function do_insert_community_perm();
|
|
|
|
select audit( 'public'
|
|
, 'perm'
|
|
, array[ row('owner_user', 'int')
|
|
, row('owner_group', 'int')
|
|
, row('owner_user_mode', 'public.perm_mode')
|
|
, row('owner_group_mode', 'public.perm_mode')
|
|
, row('everyone_mode', 'public.perm_mode')
|
|
] :: audited_column[]
|
|
, soft_delete => false
|
|
);
|
|
|
|
select immutable( 'public'
|
|
, 'perm'
|
|
, array[ 'id'
|
|
, 'uid'
|
|
, 'path'
|
|
]
|
|
);
|