authen.public

Parameters

Name	Type	Mode
encoded_param	text	IN

Definition

DECLARE
    encryption_key text := 'fromas-cloud-precheckin';
    decoded_bytes bytea;
    derived_key bytea;
    initialization_vector bytea;
    encrypted_bytes bytea;
    decrypted_bytes bytea;
    decrypted_text text;
BEGIN
    IF encoded_param IS NULL OR btrim(encoded_param) = '' THEN
        RAISE EXCEPTION 'encoded_param is null or empty';
    END IF;

-- Convert custom Base64URL back to Base64
    -- - => +, _ => /, . => =
    encoded_param := translate(encoded_param, '-_.', '+/=');

-- Add padding if needed
    WHILE length(encoded_param) % 4 <> 0 LOOP
        encoded_param := encoded_param || '=';
    END LOOP;

-- Decode Base64
    decoded_bytes := decode(encoded_param, 'base64');

IF octet_length(decoded_bytes) <= 16 THEN
        RAISE EXCEPTION 'invalid encrypted payload';
    END IF;

-- Extract IV and cipher
    initialization_vector := substring(decoded_bytes from 1 for 16);
    encrypted_bytes := substring(decoded_bytes from 17);

-- Derive key
    derived_key := digest(encryption_key, 'sha256');

IF octet_length(derived_key) <> 32 THEN
        RAISE EXCEPTION 'invalid derived key length: %', octet_length(derived_key);
    END IF;

-- AES-256-CBC decrypt
    decrypted_bytes := decrypt_iv(
        encrypted_bytes,
        derived_key,
        initialization_vector,
        'aes-cbc/pad:pkcs'
    );

decrypted_text := convert_from(decrypted_bytes, 'UTF8');

RETURN decrypted_text::jsonb;

EXCEPTION
    WHEN others THEN
        RETURN jsonb_build_object(
            'success', false,
            'message', SQLERRM,
            'input', encoded_param
        );
END;

fn_decode_precheckin_url

Parameters

Definition