File size: 9,527 Bytes
a51a15b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 |
/**
* -------------------------------------------------------
* Section - Invitations
* -------------------------------------------------------
*/
/**
* Invitations are sent to users to join a account
* They pre-define the role the user should have once they join
*/
create table if not exists basejump.invitations
(
-- the id of the invitation
id uuid unique not null default extensions.uuid_generate_v4(),
-- what role should invitation accepters be given in this account
account_role basejump.account_role not null,
-- the account the invitation is for
account_id uuid references basejump.accounts (id) on delete cascade not null,
-- unique token used to accept the invitation
token text unique not null default basejump.generate_token(30),
-- who created the invitation
invited_by_user_id uuid references auth.users not null,
-- account name. filled in by a trigger
account_name text,
-- when the invitation was last updated
updated_at timestamp with time zone,
-- when the invitation was created
created_at timestamp with time zone,
-- what type of invitation is this
invitation_type basejump.invitation_type not null,
primary key (id)
);
-- Open up access to invitations
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE basejump.invitations TO authenticated, service_role;
-- manage timestamps
CREATE TRIGGER basejump_set_invitations_timestamp
BEFORE INSERT OR UPDATE
ON basejump.invitations
FOR EACH ROW
EXECUTE FUNCTION basejump.trigger_set_timestamps();
/**
* This funciton fills in account info and inviting user email
* so that the recipient can get more info about the invitation prior to
* accepting. It allows us to avoid complex permissions on accounts
*/
CREATE OR REPLACE FUNCTION basejump.trigger_set_invitation_details()
RETURNS TRIGGER AS
$$
BEGIN
NEW.invited_by_user_id = auth.uid();
NEW.account_name = (select name from basejump.accounts where id = NEW.account_id);
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER basejump_trigger_set_invitation_details
BEFORE INSERT
ON basejump.invitations
FOR EACH ROW
EXECUTE FUNCTION basejump.trigger_set_invitation_details();
-- enable RLS on invitations
alter table basejump.invitations
enable row level security;
/**
* -------------------------
* Section - RLS Policies
* -------------------------
* This is where we define access to tables in the basejump schema
*/
create policy "Invitations viewable by account owners" on basejump.invitations
for select
to authenticated
using (
created_at > (now() - interval '24 hours')
and
basejump.has_role_on_account(account_id, 'owner') = true
);
create policy "Invitations can be created by account owners" on basejump.invitations
for insert
to authenticated
with check (
-- team accounts should be enabled
basejump.is_set('enable_team_accounts') = true
-- this should not be a personal account
and (SELECT personal_account
FROM basejump.accounts
WHERE id = account_id) = false
-- the inserting user should be an owner of the account
and
(basejump.has_role_on_account(account_id, 'owner') = true)
);
create policy "Invitations can be deleted by account owners" on basejump.invitations
for delete
to authenticated
using (
basejump.has_role_on_account(account_id, 'owner') = true
);
/**
* -------------------------------------------------------
* Section - Public functions
* -------------------------------------------------------
* Each of these functions exists in the public name space because they are accessible
* via the API. it is the primary way developers can interact with Basejump accounts
*/
/**
Returns a list of currently active invitations for a given account
*/
create or replace function public.get_account_invitations(account_id uuid, results_limit integer default 25,
results_offset integer default 0)
returns json
language plpgsql
as
$$
BEGIN
-- only account owners can access this function
if (select public.current_user_account_role(get_account_invitations.account_id) ->> 'account_role' <> 'owner') then
raise exception 'Only account owners can access this function';
end if;
return (select json_agg(
json_build_object(
'account_role', i.account_role,
'created_at', i.created_at,
'invitation_type', i.invitation_type,
'invitation_id', i.id
)
)
from basejump.invitations i
where i.account_id = get_account_invitations.account_id
and i.created_at > now() - interval '24 hours'
limit coalesce(get_account_invitations.results_limit, 25) offset coalesce(get_account_invitations.results_offset, 0));
END;
$$;
grant execute on function public.get_account_invitations(uuid, integer, integer) to authenticated;
/**
* Allows a user to accept an existing invitation and join a account
* This one exists in the public schema because we want it to be called
* using the supabase rpc method
*/
create or replace function public.accept_invitation(lookup_invitation_token text)
returns jsonb
language plpgsql
security definer set search_path = public, basejump
as
$$
declare
lookup_account_id uuid;
declare new_member_role basejump.account_role;
lookup_account_slug text;
begin
select i.account_id, i.account_role, a.slug
into lookup_account_id, new_member_role, lookup_account_slug
from basejump.invitations i
join basejump.accounts a on a.id = i.account_id
where i.token = lookup_invitation_token
and i.created_at > now() - interval '24 hours';
if lookup_account_id IS NULL then
raise exception 'Invitation not found';
end if;
if lookup_account_id is not null then
-- we've validated the token is real, so grant the user access
insert into basejump.account_user (account_id, user_id, account_role)
values (lookup_account_id, auth.uid(), new_member_role);
-- email types of invitations are only good for one usage
delete from basejump.invitations where token = lookup_invitation_token and invitation_type = 'one_time';
end if;
return json_build_object('account_id', lookup_account_id, 'account_role', new_member_role, 'slug',
lookup_account_slug);
EXCEPTION
WHEN unique_violation THEN
raise exception 'You are already a member of this account';
end;
$$;
grant execute on function public.accept_invitation(text) to authenticated;
/**
* Allows a user to lookup an existing invitation and join a account
* This one exists in the public schema because we want it to be called
* using the supabase rpc method
*/
create or replace function public.lookup_invitation(lookup_invitation_token text)
returns json
language plpgsql
security definer set search_path = public, basejump
as
$$
declare
name text;
invitation_active boolean;
begin
select account_name,
case when id IS NOT NULL then true else false end as active
into name, invitation_active
from basejump.invitations
where token = lookup_invitation_token
and created_at > now() - interval '24 hours'
limit 1;
return json_build_object('active', coalesce(invitation_active, false), 'account_name', name);
end;
$$;
grant execute on function public.lookup_invitation(text) to authenticated;
/**
Allows a user to create a new invitation if they are an owner of an account
*/
create or replace function public.create_invitation(account_id uuid, account_role basejump.account_role,
invitation_type basejump.invitation_type)
returns json
language plpgsql
as
$$
declare
new_invitation basejump.invitations;
begin
insert into basejump.invitations (account_id, account_role, invitation_type, invited_by_user_id)
values (account_id, account_role, invitation_type, auth.uid())
returning * into new_invitation;
return json_build_object('token', new_invitation.token);
end
$$;
grant execute on function public.create_invitation(uuid, basejump.account_role, basejump.invitation_type) to authenticated;
/**
Allows an owner to delete an existing invitation
*/
create or replace function public.delete_invitation(invitation_id uuid)
returns void
language plpgsql
as
$$
begin
-- verify account owner for the invitation
if basejump.has_role_on_account(
(select account_id from basejump.invitations where id = delete_invitation.invitation_id), 'owner') <>
true then
raise exception 'Only account owners can delete invitations';
end if;
delete from basejump.invitations where id = delete_invitation.invitation_id;
end
$$;
grant execute on function public.delete_invitation(uuid) to authenticated; |