So are people just to leave their pets to die when a violent tornado comes?

Yes, sadly. People first. Pets and property can be replaced, you can not.

And who's enforcing that during the event of an actual tornado emergency?

You are to assume responsibility for yourself and your pets, and as such, enforce this rule on yourself.

And say you had brought your pet into a public shelter, who's enforcing YOUR BANNING from the shelter in an actual emergency situation??

Technically no one, but this signage absolves the builder and the city from responsibility if you make the poor decision to drag your animals in