Lightroom's default color space is ProPhoto (documented
here). Aperture uses something called 'wide gamut', which is supposed to have a gamut larger than ProPhoto. Photoshop lets you work in whatever color space you specify, with aRGB as the default space. ACR uses aRGB as the default, but you can override that. PSE defaults to sRGB, but supports aRGB if the user specifies it.
If the incoming image is already associated with a color space that the program supports, then that becomes the working color space. Raw files don't have color spaces until they are demosaiced by the raw converter. But TIFF and JPEG and other file types do have a color space association.
It doesn't really matter much which color space you work in, so long as you are mindful of the color space requirement for the target for your file. You need to convert that file to a color space appropriate for that target, and soft proof the result prior to sending it off. That target is usually some specific combination of a printer and paper, or a screen, or maybe something else.
Also, it is pointless to worry much about color management if you don't use a hardware device to color calibrate your monitor. 'Beauty is in the eye of the beholder' also applies to measuring color - your eyeball isn't all that accurate.