authen.public

Parameters

Name	Type	Mode
grant_type	text	IN
reset_code	text	IN
new_password	text	IN
res_code	integer	OUT
res_msg	text	OUT

Definition

declare 
  TYPE_EDIT_USER_INFO text = 'information_user';
  TYPE_ACTIVATE_USER text = 'activate-user';
  TYPE_FORGOT_PASSWORD text = 'reset-password';
  TYPE_EMAIL_ACTIVATE text = 'email_activate_user';
  grant_types text[] = array[TYPE_EDIT_USER_INFO, TYPE_ACTIVATE_USER, TYPE_FORGOT_PASSWORD, TYPE_EMAIL_ACTIVATE];
  reset_data json;
  pass_policy_regex text;
  pass_policy_desc text;
  v_prop_id int;
  v_user_id int;
  has_password boolean;
  user_email text;  
begin 
  res_code   := 0;
  grant_type := lower(grant_type);   
  if grant_type = 'activate_user' then
     grant_type := TYPE_ACTIVATE_USER;
  end if;
  
  perform sys.log_info('sp_reset_password', 'grant_type: '||grant_type);
  perform sys.log_info('sp_reset_password', 'reset_code: '||reset_code);
  
   /*key_data := json_build_object(
        'grant_type', grant_type,
        'user_id', user_id,
        'properties', prop_ids,
        'expire', NOW() + INTERVAL '24 hours'
    )*/
  

  if res_code = 0 then 
      BEGIN
        reset_data := sys.decrypt_text(reset_code, sys.get_secret(grant_type))::json;
      EXCEPTION WHEN OTHERS THEN
        if grant_type = TYPE_ACTIVATE_USER then
            -- retry with grant_type = TYPE_FORGOT_PASSWORD
            grant_type = TYPE_FORGOT_PASSWORD;
            BEGIN
                reset_data := sys.decrypt_text(reset_code, sys.get_secret(grant_type))::json;
            EXCEPTION WHEN OTHERS THEN
               res_code := 9; -- Invalid reset_code  
            END;    
        else
            res_code := 2; -- Invalid reset_code 
        end if;    
      END;    
  end if;

  if res_code = 0 then
     perform sys.log_info('sp_reset_password', reset_data::text);
  else
     perform sys.log_warn('sp_reset_password', 'Invalid reset_code, '||grant_type||', '||reset_code);
  end if;      

  v_prop_id  := (reset_data->'properties'->>0)::int;
  v_user_id  := (reset_data->>'user_id')::int;
  
  if (res_code = 0) and (grant_type <> (reset_data->>'grant_type')) then
    res_code := 3; -- Grant type does not match 
  end if;
  
  if (res_code = 0) and (current_timestamp > (reset_data->>'expire')::timestamptz) then
    res_code := 4; -- The reset_code expired! 
  end if;
  
  
--  if (res_code = 0) then
--    select regex, description 
--    from sp_get_password_policy(v_prop_id)
--    into pass_policy_regex, pass_policy_desc;
--    raise notice 'pass_policy_regex: %', pass_policy_regex;
--    if (new_password not similar to pass_policy_regex) then
--        res_code := 5; -- Password does not match policy
--    end if;
--  end if;
    
  
  --exists(select 1 from work_log w where w.user_id = v_user_id)
  if (res_code = 0) then
    has_password := (select "password" is not null from users where id = v_user_id);

    if (grant_type = TYPE_ACTIVATE_USER) and has_password then 
        res_code := 6; -- User already activated
    elsif (grant_type = TYPE_FORGOT_PASSWORD) and not has_password then
        res_code := 8;
    end if;    
  end if;
  
  if (res_code = 0) then
    if grant_type = TYPE_FORGOT_PASSWORD then
        /* Reset password for all user with same email adress */
        user_email := (select email from users u where u.id = v_user_id);
        with u as (
            select id
            from users
            where lower(email) = user_email
        )
        select max(c.res_code) 
        from u, lateral fn_change_password(u.id, null, new_password) c
        into res_code;
    else
        res_code := (select c.res_code from fn_change_password(v_user_id, null, new_password) c);
    end if;

     --raise notice 'fn_change_password -> v_user_id: %, %', v_user_id, res_code;
     res_code := case when res_code <> 0 then 7 else 0 end; -- Reset password failed
  end if;
  
  res_msg = case res_code
    when 0 then 'Success'
    when 1 then 'Incorrect grant type '||coalesce(grant_type,'null')||' <> '||coalesce(reset_data->>'grant_type','null')
    when 2 then 'Invalid reset code ('||grant_type||')'
    when 3 then 'Grant type does not match'
    when 4 then 'The reset code expired'
    when 5 then pass_policy_desc --'Password does not match policy'
    when 6 then 'Your user account already activated'
    when 7 then 'Reset password failed'
    when 8 then 'Your user account is not activated, please activate account first.'
    when 9 then 'Invalid reset code 2 ('||grant_type||')'
    else 'Unknown error'
  end;
end;