drop table if exists user_roles;
drop type if exists custom_user_roles;

CREATE TYPE custom_user_roles AS ENUM ('user','approver','admin');

create table user_roles (
  id uuid references auth.users not null unique,
  role custom_user_roles not null default 'user',

  primary key (id)
);

alter table user_roles enable row level security;

create or replace function add_default_user()
  returns trigger as
$$
begin
  insert into public.user_roles(id, role) VALUES (NEW.id, 'user');
  return NEW;
end;
$$
language 'plpgsql' SECURITY DEFINER;

drop trigger if exists default_user_role on auth.users;
create trigger default_user_role after insert on auth.users execute procedure add_default_user();