authen.public

Parameters

Name	Type	Mode
i_token	citext	IN
i_log_id	bigint	IN
i_prop_id	integer	IN
i_user_id	integer	IN
i_function_name	text	IN (DEFAULT NULL)
res_code	smallint	OUT
res_msg	text	OUT
log_id	bigint	OUT
user_id	integer	OUT
user_name	citext	OUT
user_group_id	integer	OUT
user_group_name	text	OUT
user_group_type	character	OUT
full_name	citext	OUT
job_position	text	OUT
email	text	OUT
mobile	text	OUT
telephone	text	OUT
current_shift	character	OUT
prop_id	integer	OUT
prop_name	citext	OUT
prop_group_id	integer	OUT
prop_group_name	text	OUT
system_date	date	OUT
login_time	timestamp with time zone	OUT
service_url	text	OUT
db_host	text	OUT
db_name	text	OUT
db_port	integer	OUT
db_user	text	OUT
db_password	text	OUT
db_connection	text	OUT
permissions	json[]	OUT
image_url	text	OUT
payload	text	OUT

Definition

DECLARE
    logout_time work_log.logout_time%TYPE;
    last_seen work_log.last_seen%TYPE;
    login_token work_log.login_token%TYPE;
    w_prop_id work_log.prop_id%TYPE;
    w_user_id work_log.user_id%TYPE;
    session_duration interval;
    session_timeout interval;
    is_expired bool;
    jwt_payload jsonb;
    v_allowed_functions text[];
BEGIN
  res_code = 0;
  
  jwt_payload := sys.jwt_verify(i_token, fn_get_config(null, 'jwt-secret'));  

  if jwt_payload is null then
     res_code := 1;

  elsif jwt_payload->>'ugt' = 'X' then 
     /* Service token */
     select st.prop_id, sv.code, 'X', 'Service', sv.name, p.name, p.prop_group_id, pg.name, 
            (st.token_expire is not null and st.token_expire < current_date) as is_expired, 
            coalesce(st.allowed_functions, sv.allowed_functions), 
            coalesce(st.payload, sv.payload)
     from service_token st inner join service sv on sv.id = st.service_id
        left join property p on p.id = st.prop_id
        left join property_group pg on pg.id = p.prop_group_id
     where st.id = (jwt_payload->>'id')::int and st.active 
           and ((jwt_payload->>'jti' is not null and jwt_payload->>'jti' = st.jti::text) 
                or 
                (jwt_payload->>'jti' is null and st."token" = i_token))
     into w_prop_id, user_name, user_group_type, user_group_name, full_name, prop_name, 
          prop_group_id, prop_group_name, is_expired, v_allowed_functions, payload;

     res_code := case 
                    when w_prop_id is null then 1
                    when is_expired then 3
                    when (v_allowed_functions is not null) and (i_function_name <> ANY(v_allowed_functions))
                    then 4      
                    else 0
                 end;

  else /* Normal user */
      select w.prop_id, w.user_id, w.login_time, w.logout_time, 
        coalesce(w.last_seen, w.logout_time, w.login_time), w.login_token
      from work_log w
      where id = $2 --and prop_id = :prop_id and user_id = :user_id --and login_token = :token
      into w_prop_id, w_user_id, login_time, logout_time, last_seen, login_token;
    
      if (login_time is null or w_prop_id <> i_prop_id or w_user_id <> i_user_id or login_token <> i_token) then
         res_code = 1;     -- Invalid token, unauthorized
      elsif (logout_time is not null) then
         res_code = 2;     -- Already logged out
      ELSE 
         session_timeout = fn_get_config(prop_id, 'SESSION_TIMEOUT', '@SYSTEM')||' seconds';
         session_duration = age(current_timestamp, last_seen); 
         if (session_duration >= session_timeout) then
            res_code = 3;   -- Session timed out
            
            PERFORM fn_logout($2);
         end if;        
      end if;  

      if res_code = 0 then
         update work_log set last_seen = current_timestamp where id = $2; 
         select 
            u.user_name, 
            u.user_group_id, 
            u.user_group_name, 
            u.user_group_type, 
            u.full_name, 
            u.job_position, 
            u.email, 
            u.mobile, 
            u.telephone, 
            u.work_shift, 
            u.prop_name, 
            u.prop_group_id, 
            u.prop_group_name,
            u.permissions,
            u.image_url
         from fn_get_user_info(w_prop_id, w_user_id) u
         into 
            user_name, 
            user_group_id, 
            user_group_name, 
            user_group_type, 
            full_name, 
            job_position, 
            email, 
            mobile, 
            telephone, 
            current_shift, 
            prop_name, 
            prop_group_id, 
            prop_group_name,
            permissions,
            image_url; 
      end if;    
  end if;  
  
 
  if (res_code = 0) then
  
     log_id = i_log_id; prop_id = i_prop_id; user_id = i_user_id; 
     db_user = 'newsoft'; db_password = 'News0ft@pg';
     
     if (jwt_payload->>'ugt' = 'X') and (lower(jwt_payload->>'name') = 'accsys') then -- ACCSYS Service Token
         system_date := current_date;
         db_host := 'localhost';
         db_port := 5432;   
         db_name := 'accsys_dev';
         db_connection = fn_connection(db_name, db_host, db_port);
     else
         system_date := coalesce(fn_system_date(prop_id), current_date);
         select 
            p.service_url, 
            COALESCE(p.db_name, lower(p.code)), 
         -- COALESCE(lower(p.db_host), inet_server_addr()::text), COALESCE(p.db_port, inet_server_port())
            COALESCE(LOWER(p.db_host), 'localhost'), 
            COALESCE(p.db_port, 5432)
         from property p where p.id = w_prop_id
         into service_url, db_name, db_host, db_port;
         
         db_connection = fn_connection(db_name, db_host, db_port);
     end if;
  else
     login_time = null;
  end if;

  res_msg = case (res_code)
                 when 0 then 'Success'
                 when 1 then 'Invalid token, authentication required'
                 when 2 then 'Already logged out'
                 when 3 then case 
                                when (jwt_payload->>'ugt' = 'X') then 'Token expired' 
                                else 'Session timed out'
                             end
                 when 4 then 'Unathorized'
                 when 5 then 'RPC function not found'
            end;
  --if res_code <> 0 then  
  --   perform sys.log_debug(format(E'log_id: %L, res_code: %L, res_msg: %s\npayload:\n%L', i_log_id, res_code, res_msg, jwt_payload), 'fn_verify_token');  
  --end if;
END