A multiwavelength analysis of the faint radio sky (COSMOS-XS): the nature of the ultra-faint radio population