[PostgreSQL] 암복화 함수

/* 암호화 KEY 테이블 생성 */     

CREATE TABLE NAQSCOM.ENCRYPT_KEY (

       KEY_STR VARCHAR(100),

       CHG_DTTM TIMESTAMP

)

TABLESPACE NAQSCOM_TBL

 

COMMENT ON TABLE NAQSCOM.ENCRYPT_KEY IS '암호화 key';

COMMENT ON COLUMN NAQSCOM.ENCRYPT_KEY.CHG_DTTM IS '변경일시';

COMMENT ON COLUMN NAQSCOM.ENCRYPT_KEY.KEY_STR IS 'KEY 값';

 

/* 한건 인서트후 일정한 주기로 암호화키를 변경하여 보안 관리를 한다. */

 

/*암/복호화 KEY를 갱신한다.*/ 

UPDATE naqscom.encrypt_key 

  SET KEY_STR = LPAD(COALESCE(trunc(random() * 100000000 + 1)::text,'0'),8,'0') 

      ,CHG_DTTM = NOW() 

; 

 

 

 

/* 암호화 함수 */

CREATE OR REPLACE FUNCTION naqscom.com_encrypt(crypted_string character varying)

 RETURNS character varying

 LANGUAGE plpgsql

AS $function$

 

/*************************************************************************

  CBW 2021.09.09

  POSTGRESQL 암호화 함수

****************************************************************************/

 

DECLARE

           v_encrypt_str varchar(500):= '';   

BEGIN 

select encode(naqscom.encrypt(convert_to( 'encode#' || crypted_string || '#enc_dttm#' || to_char(now(),'yyyymmddHH24MISS') ,'utf8'),key_str::bytea,'aes'),'hex')

  into v_encrypt_str

  from naqscom.encrypt_key;

  

Return v_encrypt_str;   

exception when no_data_found then 

return ''; 

 

END;$function$

;

 

 

 

/* 복호화 함수 */

CREATE OR REPLACE FUNCTION naqscom.com_decrypt(decrypted_string character varying)

 RETURNS character varying

 LANGUAGE plpgsql

AS $function$

 

/*************************************************************************

  CBW 2021.09.09

  POSTGRESQL 복호화 함수 특정시간(60) 이상인 경우 NULL로 RETURN 시킨다.

****************************************************************************/ 

 

DECLARE

           v_decrypt_str varchar(500):= '';   

    v_encrypt_str varchar(500):= '';

    v_startDt_str varchar(50):= '';

    v_enc_term integer;

BEGIN 

 

select convert_from(naqscom.decrypt(decode(decrypted_string,'hex'),key_str::bytea,'aes'),'utf8')

       into v_decrypt_str

      from naqscom.encrypt_key;

 

 SELECT split_part(v_decrypt_str, '#', 2),

        split_part(v_decrypt_str, '#', 4),

        extract(epoch from now())::integer - extract(epoch from to_timestamp(split_part(v_decrypt_str, '#', 4),'YYYYMMDDHH24MISS'))::integer

   into v_encrypt_str,v_startDt_str,v_enc_term;

 

       IF v_enc_term > 60 THEN 

    /* 시간제한 사용 60초보다 크면 에러 */ 

     RETURN ''; 

   END IF; 

 

Return v_encrypt_str;   

exception when no_data_found then 

return ''; 

 

END;$function$

;